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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
We use them
Useful and practical observations past the white paper mentioned by Marian:
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:
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.
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 Mssrs E. F. Codd and C. J. Date address may 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.