Schema - Do you avoid using user-schema separation?
7/11/2019 5:27 AM by
User-schema separation is a new feature introduced in SQL 2005.
In SQL 2000:
- All objects are owned by users
- If a user is deleted, all these objects must be deleted or have the owner reassigned
- In script the naming convention is databaseName.ownerName.objectName
- You need to update all scripts when a user changes.
User-schema separation solves this problem by adding another level of naming, and shifting ownership of database objects to the schema, not the user. So, is it worth doing? Unless you are working with a very large database (100+ tables), the answer is "no". Most smaller databases have all objects with owner "dbo", which is fine in most cases.
- Figure: Bad Example - AdventureWorks using user schema - instead, keep it simple and avoid using user schema unnecessarily
- Figure: Good Example - Adventure works with user schema cleaned out (Good). Much simpler and more readable
Do you feel this rule needs an update?