What good are SQL Server schemas?

sql-server-2005 schema theory
Erik Funkenbusch picture Erik Funkenbusch · Feb 9, 2009 · Viewed 120.5k times · Source

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 specifier 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

SQLMenace picture SQLMenace · Feb 9, 2009

Schemas logically group tables, procedures, views together. All employee-related objects in the employee schema, etc.

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

Related questions

How do I query if a database schema exists

As part of our build process we run a database update script as we deploy code to 4 different environments. Further, since the same query will get added to until we drop a release into production it has to be able …

Read More
SQL server schema and default schema

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema ... SELECT * FROM [myschema].table I set the default schema for my user using management studio and also ran …

Read More
SQL Server: How to permission schemas?

Inspired by various schema related questions I've seen... Ownership chaining allows me to GRANT EXECUTE on a stored procedure without explicit permissions on tables I use, if both stored procedure and tables are in the same schema. If we use …

Read More