What are the benefits of SQL Server schemas?

Question

I'm no beginner to using SQL databases, and in particular SQL Server. However, I've been primarily a SQL 2000 guy and I've always been confused by schemas in 2005+. Yes, I know the basic definition of a schema, but what are they really used for in a typical SQL Server deployment? I've always just used the default schema. Why would I want to create specialized schemas? What are the benefits of schemas?

Answer 1

Organization. When you are dealing with a huge company that imports and exports data from various sources and destinations, you will quickly end up with a messy bowl of spaghetti if you don't organize your data.

My point of sale system sends me over 100 tables a day. My HR system sends another 25. My reporting schema, which contains ready to hit tables for analysis for reporting platforms (SSRS, tableau), contains another 60 tables. As a BI Manager, I wouldn't be able to manage all of that without proper schemas in place. Also, when I have to ramp up a new employee, it makes it much easier to have the data organized.

The examples above are tiny numbers compared to what I see in real businesses.