SQL Server Best Practices: User-Defined Schemas

SQL Server Best Practices: User-Defined Schemas

Rate This
  • Comments 1

Schemas are a feature that gained wide use in SQL Server 2005. They are essentially containers that allow some objects to be grouped together. Actually, there have always been schemas in SQL Server, it's just that we only exposed one - called "dbo" (short for Database Owner). In SQL Server 2005 and higher, you can now create your own schemas and then put objects there - and you should. Leaving everything in the dbo schema is like putting everything in the root directory of your hard drive. We use folders there to group information and programs, and you should do the same using schemas with your database objects.

In addition, you should assign each user a "default schema". This ensures that if they create an object without specifying a schema, it will automatically go into their assigned container.

And there's another thought here as well. Within your code, it's best if you specify the schema name (even if it is the default schema or some other mechanism) along with the object when you select or activate it. It saves the Query Processor a step from having to resolve the schema name out by itself.

You can find more here:

http://msdn.microsoft.com/en-us/library/dd283095.aspx

http://msdn.microsoft.com/en-US/library/ms190387.aspx 

http://www.ignew.com/post/SQL-Server-Select-Query-Best-Practices.aspx

Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • Buck,

    I am so glad that you brought this up.  The #1 reason that I have when I decide to not use a user-schema instead of dbo. is that most of the internal SQL tools only play nice with objects in the dbo schema.  IIRC the best example of this is sp_help.

    If those were fixed might use user-schemas more.

Page 1 of 1 (1 items)