Home
Schema - Do you avoid using user-schema separation?
  v2.0 Posted at 7/11/2019 5:27 AM by Tiago Araujo

​​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.​

SQLDatabases_UserSchema_Bad.jpg
​Figure: Bad Example - AdventureWorks using user schema - instead, keep it simple and avoid using user schema unnecessarily
SQLDatabases_UserSchema_Good.jpg
Figure: Good Example -​ Adventure works with user schema cleaned out (Good). Much simpler and more readable​​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: