November 15, 2018 at 8:06 am
roger.plowman - Thursday, November 15, 2018 5:39 AMI 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
Change is inevitable... Change for the better is not.
November 15, 2018 at 8:23 am
Jeff Moden - Thursday, November 15, 2018 8:06 AMroger.plowman - Thursday, November 15, 2018 5:39 AMI 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?
November 15, 2018 at 8:24 am
Jeff Moden - Thursday, November 15, 2018 8:06 AMroger.plowman - Thursday, November 15, 2018 5:39 AMI 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.
November 15, 2018 at 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.
November 15, 2018 at 8:37 am
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
Change is inevitable... Change for the better is not.
November 15, 2018 at 9:16 am
RonKyle - Thursday, November 15, 2018 8:30 AMMy 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
Change is inevitable... Change for the better is not.
November 15, 2018 at 10:11 am
Jeff Moden - Thursday, November 15, 2018 8:06 AMroger.plowman - Thursday, November 15, 2018 5:39 AMI 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.
November 15, 2018 at 10:18 am
fahey.jonathan - Thursday, November 15, 2018 10:11 AMJeff Moden - Thursday, November 15, 2018 8:06 AMroger.plowman - Thursday, November 15, 2018 5:39 AMI 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.
November 15, 2018 at 11:14 am
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.
November 15, 2018 at 12:43 pm
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.
November 15, 2018 at 1:10 pm
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/
November 16, 2018 at 3:12 pm
Jonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou 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.
November 16, 2018 at 3:38 pm
fahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou 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
Change is inevitable... Change for the better is not.
November 16, 2018 at 8:17 pm
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.
November 17, 2018 at 5:59 am
Jeff Moden - Friday, November 16, 2018 3:38 PMfahey.jonathan - Friday, November 16, 2018 3:12 PMJonathan AC Roberts - Thursday, November 15, 2018 10:18 AMYou 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 39 total)
You must be logged in to reply to this topic. Login to reply