What are some best practices for using schemas in SQL Server?

Question

I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, but what is typical out there? I often, in my experience, don't see many custom made schemas utilized. Is this typical? Is it the less-frequent scenario where custom schemas should be used?

Answer 1

We use them

• to separate permissions per client (eg we have desktop, WebGUI etc schemas)
• for logical grouping eg (Data and Staging schemas for our tables).

Useful and practical observations past the white paper mentioned by Marian:

• GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions of the schema
• Nice groupings in SSMS Object Explorer
• OBJECT_SCHEMA_NAME
• You are forced to qualify object names (which is best practice)

Answer 2

I think that an answer can be found in this MSDN article: SQL Server Best Practices – Implementation of Database Object Schemas.

Quote: "This white paper discusses the opportunities for improvements in the security administration of a user database, and it outlines some best practices around using schemas to manage database objects in development and production databases. Specifically, it addresses three real-world scenarios:

• Protecting database objects from being altered by users without the knowledge of the database owner
• Preventing database base objects, independent software vendor (ISV) databases in particular, from ad hoc or incorrect user access leading to poor application performance
• Bringing related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead".

I especially use the third part - relate group of objects together based on their logical meaning - different schemas belonging to different projects inside the same database.

Answer 3

You may want to go to some of the foundational texts on this to address some of your questions. Documents on the relational database model from E. F. Codd and C. J. Date address many common questions related to design, performance, security, optimal schema design etc. Ultimately, as the fathers of the modern relational database model, their works stand as the foundation for DB2, ORACLE, SQL Server (Microsoft and Sybase), Ingres, MySQL and every other relational database solution set available.

• AakashM: This appears to be talking about database design in the general sense, and not at all about SQL Server Schemas.