I am not a Schema

Question

Post reply

I am not a Schema

Phil Factor

SSC-Insane

Points: 20244
More actions
May 8, 2010 at 12:29 pm

#144688

Comments posted to this topic are about the item I am not a Schema
Best wishes,
Phil Factor

Viewing 15 posts - 1 through 15 (of 31 total)

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

Michael DePouw SSC Enthusiast Points: 165 More actions · Answer 1

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 SSC Enthusiast Points: 198 More actions · Answer 2

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 SSC-Insane Points: 20244 More actions · Answer 3

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

RobertYoung Ten Centuries Points: 1130 More actions · Answer 4

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 Old Hand Points: 317 More actions · Answer 5

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 SSC Enthusiast Points: 198 More actions · Answer 6

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 Say Hey Kid Points: 669 More actions · Answer 7

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 SSC Guru Points: 1004307 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

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

James Stover Hall of Fame Points: 3393 More actions · Answer 9

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

SqlNightOwl SSCrazy Points: 2332 More actions · Answer 10

I use schemas to make separate logical divisions within a database to partition off common functionality. I’ll also create a file group to hold data for some schemas. One I have is “ref” for common lookup values that are very stable (man of schema = ref file group = Reference). Another schema I have is “io” – guess what I do with that one?

I also use schemas for security purposes (create role and assign roll access to objects based on schema). Schemas make it simple to have DRI between common objects (list of states, departments, employees, etc.) and application specific objects (accounts, loans, sales orders, etc.).

I can easily see how to apply this in a datawarehouse (fact, dim, stage, load, rpt, etc.)

When it comes to SSMS, I like the idea of having a flat view of all objects by type but having a schema organized listing would also be nice. How about a user option to permit a filter at the database level that cascades down thru all object types?

@jeff Moden – I don’t want to shill for a product but HyperBac (recently acquired by Red Gate) allows you to connect to backups via an ODBC driver so you can recover specific items from a backup. We use their product for backup encryption/compression and have used this feature several times. One of nice features is that the backup is smaller so you can avoid the T-Byte connection. I guess you can mark me down as a fan on this one.

--Paul Hunter

Paul (scotchy) Scotchford SSC Rookie Points: 29 More actions · Answer 11

I use schemas all the time in datawarehouse db's I created, they are especially useful when dealing with smaller client Data Marts and I dont want to use a seperate db for staging , I just create a staging. schema for all the ETL related work.

To extend this further I use a dimension. schema and a measures. schema too , in order to keep order for the Data Mart objects.

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 12

I haven't seen them used much, especially in third party products, but I always wished that back in 2000 we would have had them instead of the owner.

I think they could provide some benefits, and I'd have to work with them more in some larger databases to see. The user of roles to me, has always worked well, and it's eliminated the need to qualify everything. I can see people getting confused with separate schemas, especially if they have different defaults.

My thoughts in the past have been like Jeff's. Use separate databases if you need them, however I do know that there are better tools available now, like Hyperbac and SQL Prompt, to make development easier.

HuggyBear Old Hand Points: 378 More actions · Answer 13

Great Topic, wrote on this last year (and should update it too): http://www.sqlservercentral.com/blogs/hugo/archive/2009/07/06/object-grouping-best-practices-using-schemas-for-logical-separation.aspx

Love Oracle, and whenever I hear the argument that MSSQL is cheaper because it is easier to manage more databases...I can't help but scream you are comparing Apples and Oranges. Simply cannot compare the two, because Oracle prod installations use Schemas way, way, more than in SQL Server. In corporate environments this started changing a load last year, within the several groups I worked with.

Thanks Phil, for pointing this great database organisational tool out in the newsletter.

1506 Pauline-Julien

Montreal, QC H2J 4E4

Mobile: (514) 812-5087

http://Intellabase.com

hugo@intellabase.com

SQL Server Most Valuable Professional 2010

Simple-Talk.com Technical Author

http://www.simple-talk.com/author/hugo-shebbeare/

SQL Server Central (weekly) Blogger

http://www.sqlservercentral.com/blogs/hugo/

[font="Verdana"]Town of Mount Royal, QC
SQL Server DBA since '99
MCDBA, MCITP, PMP, MVP '10, Azure Data Platform Data Engineer
hugo@intellabase.com [/font]
https://drive.google.com/file/d/1qnyiGWyGvDz6Q2VtLPGEsRufy9CUqw-t/view (MCDBA 2001, data eng associate coming asap)

ricva Default port Points: 1404 More actions · Answer 14

I have been using schemas during the last 8 months and like the ability to "group" the objects. One spot I use it is with partitioned tables. I have a schema used for truncating data (call it trunc). You can switch a partition into this trunc schema (the two tables structures must be identical) and then TRUNCATE TABLE in the trunc schema. Very fast way to delete data on a very large table.