locked
Pros and cons of using schemas in general and in SQL Server 2008 or 2012 RRS feed

  • Question

  • What are the pros and cons of using schemas?  I.e., what are the objective, material advantages of having a single database with multiple schemas versus having a single database with a single schema?

    I'm asking this question because, although I've always been a fan of schemas, a coworker of mine dislikes them and says that they don't add any value.  The only value I could see was providing a top-level only namespace and slight convenience for security setup, but nothing that can't be done without schemas.

    I've often used database schemas as a way of organizing tables into smaller subsets related by functional areas.  However, you can do this with a naming convention as well.  Anywhere you do mySchema.myTable, you could do mySchema_myTable or even mySchema_subSchema_myTable.  So, as a namespace mechanism, schemas don't really add much.

    I've also used schemas as a role recipient for security, but that can also be done on a per table or per sproc basis.  Maybe schemas make security slightly easier by grouping tables automatically.  You don't have to add roles to mySchema.myTable when you create myTable if mySchema already has been assigned the role.  Still, that's just a slight convenience, not enough to justify to my friend that they are useful.  And, if I can't justify the use of schemas, maybe he's right.

    So, I wanted to put the question to this community.  Given that namespace organization and role assignment can easily be done with any reasonable naming convention, what are advantages and disadvantages of using many schemas over just one?  Let's assume a single database for this discussion.

    Thank you.

    Monday, April 8, 2013 7:51 PM

Answers

  • So, I wanted to put the question to this community.  Given that namespace organization and role assignment can easily be done with any reasonable naming convention, what are advantages and disadvantages of using many schemas over just one?  Let's assume a single database for this discussion.

    I think you've already done a good job of discussing the pros and cons of schemas in your post.  Personally, I like using schemas in larger databases to better organize objects without employing special naming conventions that basically server the same purpose that ANSI-standard schemas provide intrinsically.  There isn't much of a downside, especially if one follows the best practice of schema-qualifying object names anyway.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi NEditor Tuesday, April 9, 2013 5:21 PM
    • Marked as answer by Maggie Luo Tuesday, April 16, 2013 10:41 AM
    Tuesday, April 9, 2013 3:37 AM
    Answerer
  • I completely agree that there really isn't much of a downside (unlike a prefix which really starts to get messy. I treat table names as the first line of documentations for programmers and users alike. Hence I like the table name to be pronouncable as a "thing" and when you are discussing it within the context of the schema and the schema and name should sound natural together as well.

    Using them for security is another useful application as well, but I wouldn't have a schema per security application, but rather it usually just naturally falls out of the process as common tables are grouped together and usually you will grant rights to common oblects.

    Unless an archaic tool can't use schemas (and then there are views you could use), I can't see any downsides to their use.

    My only warning is that you need to really think out the schemas as they are really hard to rename once you have stuff accessing them, because changing 1 table name is tough, but changing 20 because of the schema can be nuts!


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Marked as answer by Maggie Luo Tuesday, April 16, 2013 10:41 AM
    Tuesday, April 9, 2013 6:32 PM

All replies

  • I think if you can mange with assigning permissions to individual objects within the database and other security/permissions issues on each object level, I think one schema would suffice for the db. I am also eager to know if there are any intriguing things around schema's that cannot be done otherwise.

    Hope it Helps!!


    • Edited by Stan210 Tuesday, April 9, 2013 2:58 AM
    Tuesday, April 9, 2013 2:57 AM
  • So, I wanted to put the question to this community.  Given that namespace organization and role assignment can easily be done with any reasonable naming convention, what are advantages and disadvantages of using many schemas over just one?  Let's assume a single database for this discussion.

    I think you've already done a good job of discussing the pros and cons of schemas in your post.  Personally, I like using schemas in larger databases to better organize objects without employing special naming conventions that basically server the same purpose that ANSI-standard schemas provide intrinsically.  There isn't much of a downside, especially if one follows the best practice of schema-qualifying object names anyway.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi NEditor Tuesday, April 9, 2013 5:21 PM
    • Marked as answer by Maggie Luo Tuesday, April 16, 2013 10:41 AM
    Tuesday, April 9, 2013 3:37 AM
    Answerer
  • Yes, I suspect that everything that can be done with multiple schemas can be done with a single schema and vice-versa.  It may be that the only difference is convenience.

    I also personally like using schemas as namespaces for organization, but that isn't a strong argument to use them.  And one could make a counter-argument that without a strong reason for using schemas, one should avoid using them because not all RDMS support schemas.  Thus using schemas makes your SQL less portable.  For example, the open-source forum software, PHPBB, uses the prefix "phpbb_" (can be changed by installation process) instead of schemas to keep its tables, views, and sprocs separate from others in a database because many of the RDMS that PHPBB supports don't have the concept of schemas.

    Tuesday, April 9, 2013 5:05 PM
  • I completely agree that there really isn't much of a downside (unlike a prefix which really starts to get messy. I treat table names as the first line of documentations for programmers and users alike. Hence I like the table name to be pronouncable as a "thing" and when you are discussing it within the context of the schema and the schema and name should sound natural together as well.

    Using them for security is another useful application as well, but I wouldn't have a schema per security application, but rather it usually just naturally falls out of the process as common tables are grouped together and usually you will grant rights to common oblects.

    Unless an archaic tool can't use schemas (and then there are views you could use), I can't see any downsides to their use.

    My only warning is that you need to really think out the schemas as they are really hard to rename once you have stuff accessing them, because changing 1 table name is tough, but changing 20 because of the schema can be nuts!


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Marked as answer by Maggie Luo Tuesday, April 16, 2013 10:41 AM
    Tuesday, April 9, 2013 6:32 PM