Post reply

Creating Seperate Schema

  • January 7, 2016 at 6:19 am

    #307571

    Hello Guys,

    I'm in the process of initial design of a db and I'm new to this. Should I create separate schemas for db objects or I should keep all under the default schema? Why should I create separate schemas? Please share your thoughts.

    Thanks!

  • January 7, 2016 at 6:38 am

    #1850166

    SQL Server Best Practices – Implementation of Database Object Schemas

    https://technet.microsoft.com/en-us/library/dd283095%28v=sql.100%29.aspx

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • January 7, 2016 at 9:14 am

    #1850237

    Each schema is associated with set of users and permissions/access can be set up easily for particular schema by functionality/usage

  • January 7, 2016 at 4:52 pm

    #1850384

    SQL!$@w$0ME (1/7/2016)

    Hello Guys,

    I'm in the process of initial design of a db and I'm new to this. Should I create separate schemas for db objects or I should keep all under the default schema? Why should I create separate schemas? Please share your thoughts.

    Thanks!

    Just a couple of notes... not a complete set of guidelines...

    1. Never assign schemas as being owned by any individual human user.

    2. There should be no human users assigned as logins on the server. They should be Active Directory Groups.

    3. Make sure that there is a schema common to all groups. I call mine "Scratch" as in "scratch pad". Note that no permanent data should ever reside in this schema.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 7, 2016 at 10:39 pm

    #1850405

    Why should I create separate schemas?

    There may be an impact on the performance of the query optimizer, albeit a small one, depending on your coding style

  • January 8, 2016 at 5:40 am

    #1850475

    SQL!$@w$0ME (1/7/2016)

    Why should I create separate schemas?

    Depending on what you need to protect from group to group, the use of schemas can make privs a bit easier to assign to the groups instead of having to do it by object. But, to be honest, I usually have no such need and only use different schemas as a convenient form of grouping objects by logical section. For example, tables and code used exclusively for reporting are kept in a "rpt" schema and general utility features used by all sections are kept in a "util" schema. We also have an "imp" schema for objects associated with importing data, etc. It can help visually in SSMS when you're looking for something and you know which logical section it should be in.

    As a bit of a sidebar, I've found that some people get a bit carried away with the number of schemas.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 8, 2016 at 6:15 am

    #1850483

    Thank you!

  • January 8, 2016 at 6:56 am

    #1850503

    johnwalker10 (1/7/2016)

    Why should I create separate schemas?

    There may be an impact on the performance of the query optimizer, albeit a small one, depending on your coding style

    Do you have a test or a link on that? That would be very interesting. The only thing I've seen in that area is that 2 part naming supposedly saves a bit of time because it avoids a search as the current user and a search in the Master database before finally checking for "dbo" in the current database. Even with that, I've not tested it. I always use 2 part naming as a matter of rote just because we do have multiple schemas that have otherwise identically named objects.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply