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


I am not a Schema


I am not a Schema

Author
Message
Phil Factor
Phil Factor
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 2971
Comments posted to this topic are about the item I am not a Schema


Best wishes,

Phil Factor
Simple Talk
Michael DePouw
Michael DePouw
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 15
I think schemas are great. I use them to better organize a database.

Our primary line of business db has around 300 tables and without schemas it would be a mess to find tables.

I'm not a DB admin though, I'm an app developer.

I was wish Management Studio behaved like LinqPad when it comes to the object explorer. Rather than DB->Tables, I'd prefer DB->Schemas->Tables.
Brad Hale
Brad Hale
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 35
I use them a LOT. Especially for applications (SaaS, CRM's and so on) where I have a Global set of tables, etc... that all my end users need. That way I have all the global data, procs etc... in one place for doing upgrades and maintainance and can still isolate my users data.
Phil Factor
Phil Factor
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 2971
I was wish Management Studio behaved like LinqPad when it comes to the object explorer. Rather than DB->Tables, I'd prefer DB->Schemas->Tables

I agree. I find the object browser puzzling in the way it mixes objects from different schema. Where should the separation be? I'd expect that, when you click on the database, you should see a list of your schema. Each schema would contain tables, procedures etc, just as it does at the database level now.


Best wishes,

Phil Factor
Simple Talk
RobertYoung
RobertYoung
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 232
Having spent most of the last decade mostly in DB2 (both mainframe and server), and lessly in SQL Server, I've come to take schemas for granted and get confused when they're not there. The question of total addressable data is less clear. I'm not sure that any of the schema supporting databases engines can hold as much data per schema as other databases do per database. So, while schemas allow for segregating tables by some factor, they don't necessarily allow for larger integrated databases.
Anil Das
Anil Das
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 139
I'm a big fan of schema, both to logically partition a database and to make it easy to assign appropriate permissions. Re: SSMS, I use the filter option all the time, and schema is one of the filter criteria.

Re: extended properties, I use them a lot also. I commit all my view, procedure, function, etc. code to Perforce and populate extended propertiy statements in the script with RCS keywords. When I deploy, RCS keywords like $File$ and $Revision$ are then in the object definitions in the database making it easy to determine later what exactly was deployed.



Brad Hale
Brad Hale
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 35
Phil Factor (5/8/2010)
I was wish Management Studio behaved like LinqPad when it comes to the object explorer. Rather than DB->Tables, I'd prefer DB->Schemas->Tables

I agree. I find the object browser puzzling in the way it mixes objects from different schema. Where should the separation be? I'd expect that, when you click on the database, you should see a list of your schema. Each schema would contain tables, procedures etc, just as it does at the database level now.


Just use filters. Right click -> Filter -> Filter Settings
lancea
lancea
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 67
From my observations (my role is only "advice from a distance" for our larger developments), schema are simply not used. Business areas of the model are split into separate databases which results in a mess. Administrators, developers and users (those directly querying the databases) never have easy access to the big picture. I don't see this changing anytime soon because of the way large developments are run. Maybe if there was a "SQL Server architect", as opposed to generic architects who are invariably only slightly aware of any product-specific features. The people who are most aware of "features" such as schema sadly don't get much of a say in a big project.

I still like separate databases for staging areas, where all or most of the tables are truncated between loads. The danger with schema becoming popular is that you'll get people deciding they are the answer to everything.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84721 Visits: 41069
There are two reasons why I don't like and will avoid the use of schemas if at all possible...

1. "All the eggs in one basket". More specifically, all the "restores" in one basket. If anything goes wrong and you don't have a separate tera-byte capable server to do a restore to get back one lousy schema or table when something goes wrong, the whole bloody company is OOC for the duration. I'd rather put up with the bit of headache that goes along with separate databases for separate functions and the creation of synonyms or pass through views where needed.

2. It reminds me of Oracle... and even though I worked with it for 3 years, I hate Oracle. ;-)

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
James Stover
James Stover
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 862
I use an "Error" schema in my data mart. I re-direct error rows from the SSIS data flow to an identical table in the error schema. This makes sense to me because the domain of the data is the same and is used for the same application. However, I'm not a fan of mixing different data domains or different application data in the same database using different schemas. For example, I have seen "staging" and "prod" schemas live in the same DB. I typically wouldn't do this because the two serve different purposes with different recovery & support models. Of course, when you are being gouged and screwed for third-party, per-database support I can understand the desire to stuff everything into one database using different schemas.


James Stover, McDBA

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