April 27, 2013 at 12:32 pm
I was just wondering if anyone out there uses SQL Server schemas for logical organization, instead of security purposes. I'm self-trained and have yet to work in the field due to a few health issues (I used my time out of the workforce to train myself in SQL Server) and am just wondering what I'll encounter in a professional environment.
Since I come from a VB background I think like a programmer, and have found it useful to reuse certain schemas in various test databases just to keep the logical functional of tables separate. You can tell the function from some of the names: Import, Testing, CodeGeneration, ConsistencyLogical, ConsistencyPhysical, Testing, Traces, Backup, Documentation, Performance etc. On a professional production server I'm sure some of these functions would be off in other databases, but some of them, like the Legacy schema for dead code or Documentation might remain in the original databases. Could I still use schemas for purposes like this, or are they strictly for security? And if this is a permissible, wouldn't it be a helpful feature to have hierarchical relationships between schemas? I find myself wishing I could create parent-child relationships between them quite often.
-- Steve
Edit: "Schema" in the title should be plural obviously, LOL - My keyboard can't keep up with my fingers ;):-)
May 1, 2013 at 11:08 am
Schemas are not folders and you do not want to add any overhead to your database.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 1, 2013 at 11:31 am
Using what MS calls schemas to separate different parts of the data is fine, but I don't think a hierarhic relation between schemas would make sense: SQLServer is a (nearly) relational database, not a hierarchic datastore.
Also, it is important to remember that all the data in a database has to use the same recovery model, even data in different schemas, which is just one of several reasons why the ability to put things into different schemas doesn't always allow you to put things into the same database without unpleasant consequences.
Tom
May 1, 2013 at 11:58 am
L' Eomot Inversé (5/1/2013)
Using what MS calls schemas to separate different parts of the data is fine, but I don't think a hierarhic relation between schemas would make sense: SQLServer is a (nearly) relational database, not a hierarchic datastore.Also, it is important to remember that all the data in a database has to use the same recovery model, even data in different schemas, which is just one of several reasons why the ability to put things into different schemas doesn't always allow you to put things into the same database without unpleasant consequences.
Thanks for the replies. Just to illustrate what I mean about hierarchical schemas, I thought of the idea because I was treating the schemas like object names. Each database I'm running has a discrete and separate business function, but I just found it easier to encapsulate certain tasks that are common to them all in the databases they originated from, which is why I'm using the same basic set of schemas like this. For example, I have one common schema called ConsistencyLogical and another ConsistencyPhysical. I use the first for tables related to logical maintenance of a database, such as the daily output of an SSIS Data Profiling Task, while the other's for tables which kept daily records of physical maintenance tasks like DBCC checks. I just thought it would be easier to refer to them through object syntax, like Consistency.Logical and Consistency.Physical, as a convenience, rather than any real substantive purpose. I reasoned that if I were responsible for maintaining 100 databases, a standard schema structure like this would might be easier to perform certan administration tasks faster, since I would instantly know where to find all the right procedures, functions, tables, views, etc.; it also makes it easier on the eyes to scan through long lists of tables and other objects in Management Studio. For example, if I want to check for any new missing indexes found by last week's SSIS job, I could look for the ConsistencyLogical.MissingIndexes table in any database to instantly find out. I don't want to get accustomed to doing this though if it isn't an acceptable practice. 🙂
I didn't think of the implications for backups - I'll have to take that into account.
Thanks again,
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply