Do you use custom schemas?

  • Jeff Moden

    SSC Guru

    Points: 994530

    roger.plowman - Thursday, November 15, 2018 5:39 AM

    I don't use schemas for a few reasons. First, why would you ever put multiple application data in the same database? Brr. Besides, that's what four part names are for if you ever need them. 😀

    If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor.  As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc.  Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes.  For example, carry one copy of the database for "Project A" and another for "Project B".  If any of the code uses even 3 part naming, you have a serious problem.

    Even if you don't have more than one copy of a database on a given instance, some shops require that the databases in each environment be named slightly differently for reasons of "safety".  For example, MyDB_Dev in the Development environment, MyDB_Stg in the Staging/UAT environment, and MyDB_Prod in the production environment.  In order to prevent having to change code as code is promoted from dev thru stagng to prod in such cases, you need to stick to a 2 part naming convention supported by either "pass through" views or "synonyms", which also takes the sting out of the idea of having 1 database per app (again, whether anyone agrees with that or not, it does happen).

    If you have no such requirements, you should still stick to the 2 part naming concept religiously because, someday, it will happen and there will be one hell of a scramble to fix a whole lot of code if you don't (been there, done that, don't wanna do it ever again).  It's much easier to do a blanket change on things like synonyms instead of finding all the code that uses 3 or 4 part naming and changing all of that, possibly breaking stuff in the process (e.g.  "Whoops! Someone used the wrong ANSI and ARITHMETIC ABORT settings").

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jonathan AC Roberts

    SSCoach

    Points: 16844

    Jeff Moden - Thursday, November 15, 2018 8:06 AM

    roger.plowman - Thursday, November 15, 2018 5:39 AM

    I don't use schemas for a few reasons. First, why would you ever put multiple application data in the same database? Brr. Besides, that's what four part names are for if you ever need them. 😀

    If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor.  As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc.  Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes.  For example, carry one copy of the database for "Project A" and another for "Project B".  If any of the code uses even 3 part naming, you have a serious problem.

    Even if you don't have more than one copy of a database on a given instance, some shops require that the databases in each environment be named slightly differently for reasons of "safety".  For example, MyDB_Dev in the Development environment, MyDB_Stg in the Staging/UAT environment, and MyDB_Prod in the production environment.  In order to prevent having to change code as code is promoted from dev thru stagng to prod in such cases, you need to stick to a 2 part naming convention supported by either "pass through" views or "synonyms", which also takes the sting out of the idea of having 1 database per app (again, whether anyone agrees with that or not, it does happen).

    If you have no such requirements, you should still stick to the 2 part naming concept religiously because, someday, it will happen and there will be one hell of a scramble to fix a whole lot of code if you don't (been there, done that, don't wanna do it ever again).  It's much easier to do a blanket change on things like synonyms instead of finding all the code that uses 3 or 4 part naming and changing all of that, possibly breaking stuff in the process (e.g.  "Whoops! Someone used the wrong ANSI and ARITHMETIC ABORT settings").

    Just wondering if when you use synonyms do you put them on a separate schema?

  • roger.plowman

    SSChampion

    Points: 10139

    Jeff Moden - Thursday, November 15, 2018 8:06 AM

    roger.plowman - Thursday, November 15, 2018 5:39 AM

    I don't use schemas for a few reasons. First, why would you ever put multiple application data in the same database? Brr. Besides, that's what four part names are for if you ever need them. 😀

    If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor.  As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc.  Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes.  For example, carry one copy of the database for "Project A" and another for "Project B".  If any of the code uses even 3 part naming, you have a serious problem.

    Even if you don't have more than one copy of a database on a given instance, some shops require that the databases in each environment be named slightly differently for reasons of "safety".  For example, MyDB_Dev in the Development environment, MyDB_Stg in the Staging/UAT environment, and MyDB_Prod in the production environment.  In order to prevent having to change code as code is promoted from dev thru stagng to prod in such cases, you need to stick to a 2 part naming convention supported by either "pass through" views or "synonyms", which also takes the sting out of the idea of having 1 database per app (again, whether anyone agrees with that or not, it does happen).

    If you have no such requirements, you should still stick to the 2 part naming concept religiously because, someday, it will happen and there will be one hell of a scramble to fix a whole lot of code if you don't (been there, done that, don't wanna do it ever again).  It's much easier to do a blanket change on things like synonyms instead of finding all the code that uses 3 or 4 part naming and changing all of that, possibly breaking stuff in the process (e.g.  "Whoops! Someone used the wrong ANSI and ARITHMETIC ABORT settings").

    In our case we do have a single instance (budget, SQL Server is EXPENSIVE) and use 3 databases named Dev, etc. on the same instance, with a few third-party databases on the same instance. (Apps in this case don't share data or largely know of each other's existence).

    This isn't much of a problem, we simply have the app point to the appropriate database, no muss no fuss. Connection strings are your friend. 🙂

    We use three part names to access databases on the same instance and this isn't likely to change any time soon (again, budget). If it ever does we'll just modify our own app to use a second connection to the third party database since by design the access is read-only and quite minimal. 

    I can see where extensive connections between databases might be an issue, though.

  • RonKyle

    SSC-Dedicated

    Points: 31457

    My one complaint is "PriceListObj" leaves me hoping that Obj is a placeholder for something more meaningful. Object should simply be banned from names because everything is an object.And much of the time there is no real reason to specify the object's type.

    +1  A database I work with has tables such as Zip_Obj, Ord_Obj, Ord_SKUs_Obj.  Extra typing for absolutely no gain.

  • Jeff Moden

    SSC Guru

    Points: 994530

    Shifting gears back to the subject at hand...

    One of the things we use custom schemas for is to identify the expendability and life expectancy of a table.

    For example, we have a database used mostly for ETL from multiple sources where a whole lot of work/validation/normalization is done on the imported raw data before it's inserted into whatever final database it needs to go to.  In the process of do so, it's really handy to do such things as leave a "breadcrumb trail" of logs-by-file or save interim data in tables especially for post-run troubleshooting. 

    With that thought in mind, we have several schemas to identify the type of data in the table.  For example, the SCRATCH schema is for stuff that only needs to be kept for a week or so for "just in case we missed something" post-run troubleshooting.  I've built a "sweeper" proc to clean up the proverbial cutting room floor that looks to see if the table has been used in the last week or not and, if not, the table is renamed with a suffix of "_ToBeDeleted".  Such renamed tables are allowed to continue to exist for another week and then unceremoniously dropped so no one has to remember to go and clean that junk up.  Heh... and yes... there's a fair bit of dynamic SQL that creates those types of tables.

    We do similar as the data progresses through the necessary steps because we need to either not include columns from the original data file or we need to add columns.  There just no sense in working with a 200 column table if you only need 40 of those columns to get to the final storage.  The tables for those schemas are named according to the step and have similar "sweeper" processes applied.  The reason why we don't just have the code that's doing the processing drop the tables is because the processes can be quite long and it's real handy to "pick up where you left off" if something goes wrong.

    The "Sweeper" processes identify by schema name whether a table can go away in 24 hours or after a given period of time up to 2 years depending on our audit requirements. 

    Why not use separate databases for each major step?  Because there's a whole lot of DRI (Declared Referential Integrity, i.e. FKs) involved and the only way you can really do DRI across databases is through the use of triggers and we just don't want the overhead of doing that.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994530

    RonKyle - Thursday, November 15, 2018 8:30 AM

    My one complaint is "PriceListObj" leaves me hoping that Obj is a placeholder for something more meaningful. Object should simply be banned from names because everything is an object.And much of the time there is no real reason to specify the object's type.

    +1  A database I work with has tables such as Zip_Obj, Ord_Obj, Ord_SKUs_Obj.  Extra typing for absolutely no gain.

    Wow.  Absolutely agreed.  I have to admit I've not seen that particular form of "tbling" before.  That's even worse than tbl_Zip, tbl_Ord, etc, etc.  They didn't make stored procedure names like SomPrc_NotObj (which would also be incorrect for the same reason you stated), did they? 😀

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • fahey.jonathan

    Hall of Fame

    Points: 3532

    Jeff Moden - Thursday, November 15, 2018 8:06 AM

    roger.plowman - Thursday, November 15, 2018 5:39 AM

    I don't use schemas for a few reasons. First, why would you ever put multiple application data in the same database? Brr. Besides, that's what four part names are for if you ever need them. 😀

    If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor.  As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc.  Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes.  For example, carry one copy of the database for "Project A" and another for "Project B".  If any of the code uses even 3 part naming, you have a serious problem.

    Even if you don't have more than one copy of a database on a given instance, some shops require that the databases in each environment be named slightly differently for reasons of "safety".  For example, MyDB_Dev in the Development environment, MyDB_Stg in the Staging/UAT environment, and MyDB_Prod in the production environment.  In order to prevent having to change code as code is promoted from dev thru stagng to prod in such cases, you need to stick to a 2 part naming convention supported by either "pass through" views or "synonyms", which also takes the sting out of the idea of having 1 database per app (again, whether anyone agrees with that or not, it does happen).

    If you have no such requirements, you should still stick to the 2 part naming concept religiously because, someday, it will happen and there will be one hell of a scramble to fix a whole lot of code if you don't (been there, done that, don't wanna do it ever again).  It's much easier to do a blanket change on things like synonyms instead of finding all the code that uses 3 or 4 part naming and changing all of that, possibly breaking stuff in the process (e.g.  "Whoops! Someone used the wrong ANSI and ARITHMETIC ABORT settings").

    I don't have the need to keep two copies of a database in a single server, but your response got me thinking.

    In my current environment, I have two databases, a "Live" database which holds current transactional data and an "Archive" database that holds data over a year old that hardly ever gets used.  My theory is that keeping the Live tables smaller will improve performance, so old data is rolled off once a week at night.  When needed, procedures in Live will UNION data from, for example, Live.dbo.Orders and Archive.dbo.Orders.  If I were to keep LiveA and ArchiveA along with LiveB and ArchiveB on the same server, I can't think of a way to write those references without explicitly referencing the database name.  How would LiveA know to read ArchiveA and not ArchiveB?  Is there a way to do this that I'm missing? 

    Your suggestion is to use views or synonyms.  Those objects would need to be maintained individually each time.  I don't see a way to create a synonym for a database.  Is there a way in a database to create a named reference to another database such that queries could use the named reference, and that reference could be changed once?  In effect, it would create a pointer named "MyArchive"; when adding database LiveC, the reference would be changed to point to ArchiveC, so all queries that use "MyArchive.dbo.Orders" would then go to the database specified in the pointer?  Again, I don't have this need, I'm just trying to expand my knowledge.

  • Jonathan AC Roberts

    SSCoach

    Points: 16844

    fahey.jonathan - Thursday, November 15, 2018 10:11 AM

    Jeff Moden - Thursday, November 15, 2018 8:06 AM

    roger.plowman - Thursday, November 15, 2018 5:39 AM

    I don't use schemas for a few reasons. First, why would you ever put multiple application data in the same database? Brr. Besides, that's what four part names are for if you ever need them. 😀

    If you're talking about 3 and 4 part naming in any of the code itself, caveat emptor.  As databases grow, they sometimes move and you end up needing to use either Linked Servers or things like OPENROWSET, etc, etc.  Also (whether anyone agrees with it or not), there are some shops that want carry the same database on the same instance more than once for "development" purposes.  For example, carry one copy of the database for "Project A" and another for "Project B".  If any of the code uses even 3 part naming, you have a serious problem.

    Even if you don't have more than one copy of a database on a given instance, some shops require that the databases in each environment be named slightly differently for reasons of "safety".  For example, MyDB_Dev in the Development environment, MyDB_Stg in the Staging/UAT environment, and MyDB_Prod in the production environment.  In order to prevent having to change code as code is promoted from dev thru stagng to prod in such cases, you need to stick to a 2 part naming convention supported by either "pass through" views or "synonyms", which also takes the sting out of the idea of having 1 database per app (again, whether anyone agrees with that or not, it does happen).

    If you have no such requirements, you should still stick to the 2 part naming concept religiously because, someday, it will happen and there will be one hell of a scramble to fix a whole lot of code if you don't (been there, done that, don't wanna do it ever again).  It's much easier to do a blanket change on things like synonyms instead of finding all the code that uses 3 or 4 part naming and changing all of that, possibly breaking stuff in the process (e.g.  "Whoops! Someone used the wrong ANSI and ARITHMETIC ABORT settings").

    I don't have the need to keep two copies of a database in a single server, but your response got me thinking.

    In my current environment, I have two databases, a "Live" database which holds current transactional data and an "Archive" database that holds data over a year old that hardly ever gets used.  My theory is that keeping the Live tables smaller will improve performance, so old data is rolled off once a week at night.  When needed, procedures in Live will UNION data from, for example, Live.dbo.Orders and Archive.dbo.Orders.  If I were to keep LiveA and ArchiveA along with LiveB and ArchiveB on the same server, I can't think of a way to write those references without explicitly referencing the database name.  How would LiveA know to read ArchiveA and not ArchiveB?  Is there a way to do this that I'm missing? 

    Your suggestion is to use views or synonyms.  Those objects would need to be maintained individually each time.  I don't see a way to create a synonym for a database.  Is there a way in a database to create a named reference to another database such that queries could use the named reference, and that reference could be changed once?  In effect, it would create a pointer named "MyArchive"; when adding database LiveC, the reference would be changed to point to ArchiveC, so all queries that use "MyArchive.dbo.Orders" would then go to the database specified in the pointer?  Again, I don't have this need, I'm just trying to expand my knowledge.

    You can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.

  • Rod at work

    SSC-Dedicated

    Points: 33125

    In my old job we didn't use any custom schemas. In fact, I didn't quite understand them. In my current job its common practice to define custom schemas. I've learned their importance and have really come to appreciate them.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • cmullican

    SSC-Addicted

    Points: 405

    We use schemas to separate different areas of the app ecosystem that work with the same basic data -- the main web front-end has one schema; different background processes have their own, etc.  Imported data from different sources goes into different schemas -- data mirrored from SalesForce goes into a SalesForce schema; data mirrored from QuickBooks goes into a QuickBooks schema, etc. Summary tables for reporting are in Summary schemas. (One used for driving an external client-visible dashboard; a separate one for internal reporting, summarized differently, etc.)

    I just wish the Object Explorer treated schemas as a tree level.  I found a third-party plug-in that enables this, but I wish it were built in.  DB -> Schema -> Object is a lot cleaner to me than DB -> Schema.Object, especially at scale -- we have 560 tables, 238 functions, and 1661 SPs across 24 schemas. 

    Schemas were taught as an integral part of database design when I was in college, mumble years ago.  While a simple app might not need more than one, I can't imagine designing a system without using them, and I've never worked with a system that simple, so far as I recall.

  • Michael L John

    One Orange Chip

    Points: 25729

    Here's an example of what NOT to do. These are actual schemas and table names from one of our systems.  In this case, the schemas were used to classify the different tables and their functions.  So, the original designers created the schemas, and then proceeded to preface every table with t_, and include the schema name in the table name in a lot of cases.  Coupled with the completely incoherent abbreviations, you waste more time typing garbage.  

    App.t_App
    Archive.t_App_DP
    Archive.t_App_MA
    Archive.t_App_MI
    Client.t_Client
    Client.t_Client_Email_Settings
    Client.t_Client_Locale_link
    Client.t_Client_nls
    Cont.t_Action
    Cont.t_Action_Detail
    Cont.t_Assess_Images
    Cont.t_Choice
    Integration.t_Integration
    Integration.t_Integration_Detail_Xref
    Integration.t_Integration_Partner
    Lookup.t_Locale
    Lookup.t_Locale_nls
    Lookup.t_Quest_Attribute
    Rpt.t_Report
    Rpt.t_Report_Locale_Link
    Rpt.t_Report_Part
    Score.t_Score_Object
    Score.t_Score_Param
    Timer.t_Timer

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • fahey.jonathan

    Hall of Fame

    Points: 3532

    Jonathan AC Roberts - Thursday, November 15, 2018 10:18 AM

    You can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.

    Thank you for responding to my question.  I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database."  I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."

    I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.

  • Jeff Moden

    SSC Guru

    Points: 994530

    fahey.jonathan - Friday, November 16, 2018 3:12 PM

    Jonathan AC Roberts - Thursday, November 15, 2018 10:18 AM

    You can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.

    Thank you for responding to my question.  I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database."  I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."

    I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.

    You could create a specially named linked server that points to the server you're on.  So long as you don't change the name of the linked server, you'd be golden no matter where you pointed the linked server.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • xsevensinzx

    One Orange Chip

    Points: 25531

    I surely use custom schema's a great deal in the data warehouse to help partition certain types of data as well the security models. I would also say this holds true in the overall cloud environment too as I am in Azure. We have separate resource groups that group our resources such as our Azure databases, networks, and so forth in separate resources just like separate schemas within the same cloud environment. It's very handy.

    From an ETL standpoint, it's very handy for sure to have that separation. Even more so in the MPP world where each user has a resource class to define how much memory and CPU that user can use while querying specific schemas within the underlying system. Pretty critical to have that locked down and accounted for. Not just constantly creating another instance that costs the business even more money for being lazy.

  • Jonathan AC Roberts

    SSCoach

    Points: 16844

    Jeff Moden - Friday, November 16, 2018 3:38 PM

    fahey.jonathan - Friday, November 16, 2018 3:12 PM

    Jonathan AC Roberts - Thursday, November 15, 2018 10:18 AM

    You can't create a synonym for a database, just database objects. But you can create a synonym for each object in a database.

    Thank you for responding to my question.  I understand that synonyms cannot be created for databases, which is why I said, " I don't see a way to create a synonym for a database."  I also understand that synonyms could be created for each object, which is why I said, "Those objects would need to be maintained individually each time."

    I'm asking if there is some other way to create a reference to another database such that changing the reference once would affect all statements.

    You could create a specially named linked server that points to the server you're on.  So long as you don't change the name of the linked server, you'd be golden no matter where you pointed the linked server.

    Jeff,
    How do you create a specially named linked server?

Viewing 15 posts - 16 through 30 (of 40 total)

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