SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do you use custom schemas?


Do you use custom schemas?

Author
Message
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9458 Visits: 1084
Comments posted to this topic are about the item Do you use custom schemas?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)SSC Guru (896K reputation)

Group: General Forum Members
Points: 896065 Visits: 48245
Heh... I find custom schemas to be incredibly annoying but not because anything you said about them isn't true. They ARE incredibly useful for all the reasons you stated.

The reason why I find them incredibly annoying is because, just as with other object names, many people don't actually have a plan and so you end up with some pretty stupid usage of custom schemas. And, no, you don't need a thousand different schemas... that's what different object names are for.

I will say that I have a very strong dislike for schema names that are more than 4 characters or so. There are useful exceptions but they shouldn't be used like table names (for example), which some folks seem to do/prefer.

Of course, that's just my opinion and preferences. If I'm doing work in someone else's shop and regardless of what my opinion and preferences are, I'll do it their way.

The bottom line is just like anything else... you can destroy an excellent tool by the way you name things. Have a plan and a standard.

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

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
DinoRS
DinoRS
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 588
I like to use Custom Schemas for segregation of applications or application functions. One thing I like is using an [Error] Schema in DWH Scenarios, there I have things of interest like certain tables might need correction when lookups fail, you want to know which rows failed so you have a [Error]LKP_TblName table in there, of course the naming schema might look different if the DWH serves multiple smaller applications.

It makes Filegroup Partitioning much easier, too if you have sensible ETL Schemas for each step of the process which helps you avoid file contention when data from one stage is being loaded or processed into the next stage.
Some might argue I look way too much at details but I like details like these, they make my designs quite fly with the right hardware beneath.
John Corkett
John Corkett
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1797 Visits: 310
Completely agree with everything you say here.
I have lots of pet hates and one of them is the use of dbo schema for everything. One culprit I have encountered is the use of ORM systems by developers. Whilst the ones I have encountered do permit the use of schemas most developers seem to take the easy way out and just let the ORM take care of creating everything in dbo.

John Corkett
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8900 Visits: 2017
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. BigGrin

Second, there are tables that should logically span multiple schemas, so which schema should they go into? If you're using schemas for security reasons (which is really the purpose of security groups so why be redundant?) then you'd have to grant access to an entire second schema to get to the one table needed by members of another schema--which defeats the purpose of schema security!

Third, there's no reason you can't use SPs exclusively with the dbo schema, again, that's what security groups are for.

I'm a BIG proponent of vanilla design--use the bare minimum number of features you can get away with, especially avoiding redundant features. The more features you involve, the more things that can go wrong.

Sure, if you MUST HAVE some feature, by all means use it. But if it's redundant then don't.

KISS for the win!
druid84
druid84
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 34
I am new in the world of DBAs. Is there any documentation/books you would suggest I read to get more accustomed with custom schemas?
Thanks!
DinoRS
DinoRS
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 588
Well one reason for having multiple Applications within one DB could be sharing the same base data for different Applications.

Of course you could import the data 20 times to 20 different DBs which is most likely according to your statement what you would do and I admire anyone being allowed to waste resources woefully. If you have Sales data and different Sales departments which each use a subset of the available data, go ahead with your approach and let us know what your customer thinks about your design strategies. I guess your most basic design aswell includes one DB File that can grow to 16 TB before you realize you've reached a limit, alternatively you have never been close something like that, take your pick of "most basic vanilla design" sincerely.
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9458 Visits: 1084
DinoRS - Thursday, November 15, 2018 1:00 AM
I like to use Custom Schemas for segregation of applications or application functions. One thing I like is using an [Error] Schema in DWH Scenarios, there I have things of interest like certain tables might need correction when lookups fail, you want to know which rows failed so you have a [Error]LKP_TblName table in there, of course the naming schema might look different if the DWH serves multiple smaller applications.

It makes Filegroup Partitioning much easier, too if you have sensible ETL Schemas for each step of the process which helps you avoid file contention when data from one stage is being loaded or processed into the next stage.
Some might argue I look way too much at details but I like details like these, they make my designs quite fly with the right hardware beneath.

I hadn't thought of using an Error schema before. I will keep that in mind for future projects. Thanks for sharing.

Ben

Y.B.
Y.B.
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9612 Visits: 2779
I don't use schemas everywhere for the sake of just using them but I DO use them and they are fantastic! I have a datawarehouse that contains data from several different locations. Who can have access to all this data can be very messy. This is where schemas come in, I create few logical schemas with their own database role that has appropriate permissions to that schema (the schemas in this scenario only contain views and procs). I then add AD groups to the correct role and done. Setup is probably the worst part but after that managment is so easy.

EDIT: Another time I use schemas is during data load for table switching. It's easy to keep the names of the table the same. I use load schema that is swaped in to the final table when the load and other processing is complete.


SELECT quote FROM brain WHERE original = 1
0 rows returned
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8900 Visits: 2017
DinoRS - Thursday, November 15, 2018 6:25 AM
Well one reason for having multiple Applications within one DB could be sharing the same base data for different Applications.

Of course you could import the data 20 times to 20 different DBs which is most likely according to your statement what you would do and I admire anyone being allowed to waste resources woefully. If you have Sales data and different Sales departments which each use a subset of the available data, go ahead with your approach and let us know what your customer thinks about your design strategies. I guess your most basic design aswell includes one DB File that can grow to 16 TB before you realize you've reached a limit, alternatively you have never been close something like that, take your pick of "most basic vanilla design" sincerely.

Um, no?

In that scenario you'd probably separate the sales departments with a simple field in the relevant table (invoices, maybe?), then in the SP either supply the department you wanted or (more likely) allow the appropriate users to see all departments.

Applications are front-ends, independent of databases. If they use the same data they use the same database. Or am I misinterpreting your point?

Note I did say use the minimum needed features. Simple does not mean brainless. Elegant design is both simple and efficient at the same time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search