What good are 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? Why would I assign any of the built-in schemas?

EDIT: To clarify, I guess i'm looking for the benefits of schemas. If you're only going to use it as a security scheme, it seems like database roles already filled that.. er.. um.. role. And using it a as a namespace sepcifier seems to have been something you could have done with ownership (dbo versus user, etc..).

I guess what i'm getting at is, what do Schemas do that you couldn't do with owners and roles? What are their specifc benefits?

Answer 1

to logically group tables, proc, views together, all employee related objects in the employee schema etc etc etc

you can also give permissions to just one schema so that users can only see the schema they have access too and nothing else

Answer 2

development - each of our devs get their own schema as a sandbox to play in.

Answer 3

They can also provide a kind of naming collision protection for plugin data. For example, the new Change Data Capture feature in SQL Server 2008 puts the tables it uses in a separate cdc schema. This way, they don't have to worry about a naming conflict between a CDC table and a real table used in the database.

Answer 4

I think schemas are like a lot of new features (whether to SQL Server or any other software tool). You need to carefully evaluate whether the benefit of adding it to your development kit offsets the loss of simplicity in design and implementation.

It looks to me like schemas are roughly equivalent to optional namespaces. If you're in a situation where object names are colliding and the granularity of permissions is not fine enough, here's a tool. (I'd be inclined to say there might be design issues that should be dealt with at a more fundamental level first.)

The problem can be that, if it's there, some developers will start casually using it for short-term benefit; and once it's in there it can become kudzu.

Answer 5

Just like Namespace of C# codes.