Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

I am not a Schema Expand / Collapse
Author
Message
Posted Saturday, May 8, 2010 12:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 579, Visits: 2,520
Comments posted to this topic are about the item I am not a Schema


Best wishes,

Phil Factor
Simple Talk
Post #918537
Posted Saturday, May 8, 2010 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 6:21 PM
Points: 3, Visits: 9
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.
Post #918540
Posted Saturday, May 8, 2010 2:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 3, 2011 4:21 PM
Points: 6, 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.
Post #918554
Posted Saturday, May 8, 2010 3:21 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 579, Visits: 2,520
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
Post #918559
Posted Saturday, May 8, 2010 4:01 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:49 AM
Points: 76, Visits: 231
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.
Post #918561
Posted Saturday, May 8, 2010 4:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:55 PM
Points: 253, Visits: 132
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.



Post #918563
Posted Saturday, May 8, 2010 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 3, 2011 4:21 PM
Points: 6, 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
Post #918565
Posted Saturday, May 8, 2010 4:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 26, 2012 7:59 PM
Points: 169, Visits: 62
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.



Post #918566
Posted Saturday, May 8, 2010 7:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #918590
Posted Saturday, May 8, 2010 8:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 8, 2013 5:39 PM
Points: 263, 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
Post #918602
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse