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 Sunday, May 09, 2010 11:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 9:22 AM
Points: 201, Visits: 402
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
Post #918670
Posted Sunday, May 09, 2010 3:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 31, 2013 10:37 PM
Points: 1, Visits: 26
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.
Post #918693
Posted Sunday, May 09, 2010 4:46 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #918698
Posted Sunday, May 09, 2010 6:27 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:32 AM
Points: 12, Visits: 239
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/


Town of Mount Royal, QC
514 812 5087 (txt also)
hugo@intellabase.com (msn im also)
Post #918713
Posted Sunday, May 09, 2010 7:58 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 2:38 PM
Points: 102, Visits: 202
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.




Post #918737
Posted Sunday, May 09, 2010 11:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:00 PM
Points: 4, Visits: 71
I've started adopting schemas, and have migrated a system that originally was designed with multiple databases to serve a single end data warehouse into schemas for two reasons:

1. DRI across schemas - before schemas, i had the ugly option of a trigger, or would even sync multiple copies of the same data between databases within the same server to provide a base in each db to DRI against. If a deletion or update in one, or in a master set, conflicted, it would be discovered at sync time rather than at the time of the change itself. Generally, in an OLTP application avoided the architecture that requires DRI to cross schemas. In the OLAP-oriented solution, it has worked well to have a single schema containing measure metadata, to provide validation for user entries in another schema, and provide a base for star schema tables in another schema.

2. Database objects and scripts can be written polymorphically - in a multi-tenant architecture, multiple databases required any views, sprocs, user-defined functions, or ad hoc scripts to reference the databases they touched (i.e. "SELECT * FROM {Customer}SourceData.dbo.RawData" with the {Customer} parameter having to precisely and manually be filled in...each... and...every... time a script was ran... and carefully too, to avoid accidental cross-polinations. With schemas, the SQL becomes "SELECT * FROM SourceData.RawData", and can be deployed across customer databases with no changes, and no risk of accidental cross-polination of data.
Post #918793
Posted Monday, May 10, 2010 6:52 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 1,487, Visits: 4,300
I believe that referential integrity constraints are important in a database. If you have data models for two or three tightly integrated applications with overlapping foreign keys and sharing of reference tables (ex: Customer, Billing, Workflow, Inventory), it's great to now have the options of containing them all in the same database, creating all appropruiate constraints and backing them up as a total unit, while still keep the tables logically segregated using schemas.


"Winter Is Coming" - April 6, 2014
Post #918954
Posted Monday, May 10, 2010 8:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:10 AM
Points: 318, Visits: 1,447
I do not like different schemas within the same database, since they break the ownership chain. Joining different schemas in a proc forces me to grant permssions on the underlying objects rather than just on the proc.
Post #919043
Posted Monday, May 10, 2010 10:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 3:13 PM
Points: 306, Visits: 1,458
Henry_Lee (5/10/2010)
I do not like different schemas within the same database, since they break the ownership chain. Joining different schemas in a proc forces me to grant permissions on the underlying objects rather than just on the proc.


Unless you're using SQL 2000 (where we really shouldn't be talking about using Schemas as they aren't fully implemented) I have to disagree with your assertion.
When you use schemas for logical separation in SQL Server 2005+ you don't have to have different owners on the schemas. If both schemas are owned by DBO then you get ownership chaining just like if all your SPROCs were in the same schema, but you can organize your tables into logical groups.
Personally I like to put all of my tables in a set of schemas and my SPROCs in a different schema (all owned by dbo). Then I just GRANT EXECUTE ON SCHEMA::MySchema and voila, no permissions chaining problems, execute permissions easily granted, and I don't have to worry about permissions leaking for all of the system SPROCs in DBO.

-DW
Post #919140
Posted Monday, May 10, 2010 10:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:10 AM
Points: 318, Visits: 1,447
If both schemas are owned by DBO then you get ownership chaining just like if all your SPROCs were in the same schema, but you can organize your tables into logical groups.


Hmmm...pretty poor on my part...I flat out missed this. Looks like I have some cleaning up to do this week.

Thanks for the correction, Darren.
Post #919158
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse