Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

I am not a Schema

By Phil Factor,

A colleague joked to me the other day that the only database that ever fully exploited the use of schemas was AdventureWorks. He then told me the story that Microsoft had been keen to add it so as to encourage Oracle users to switch to SQL Server, by reassuring them that one could have more than one database on a server.       

It is true that the use of schemas solves a problem that few people have. Before user-defined schemas, we just used to split a database into several databases within the same server if we needed to partition the logic, the security or development teams to into several separate units. We could always assign different parts of a database to different principal other than the ubiquitous DBO if we needed to. There were problems with doing this though: Each database required its own maintenance, and where the boundaries became blurred, you couldn’t use cross-database foreign keys or any other Declarative Referential Integrity (DRI) so had to use triggers. It was more complicated to move objects such as tables or procedures between databases.

Schemas give a lot more flexibility. A schema, despite its rather grand name, is just a container of database objects. You can move database objects between schema easily, or transfer the ownership of schemas and their objects. It allows a convenient mapping between database users and database object owners. Most of all, it makes it easier for DBAs to ensure that database users have access to only that information they need. Schemas are great way to make databases modular, and avoid naming collisions, so that more than one team can work on a database.

Schemas show their true value in larger databases. Am I right in believing, from my own experience, that the use of schemas in database design for large commercial databases is now becoming the norm? Or is this, on the contrary, a feature like extended properties that is yet to be properly exploited? I’d be fascinated to hear of interesting ways that schemas are used.

Total article views: 1492 | Views in the last 30 days: 2
 
Related Articles
FORUM

how to check the Schema Objects Details in Each Database using SP

Schema Objects History for all Databases

FORUM

How to get a list of database objects changed between specific time sql server

How to get a list of database objects changed between specific time

FORUM

Schema versus Database

Schema versus Database

FORUM

Copy Tables between Multiple Schemas

Copying same set of tables between 30 Schemas

FORUM

How to sync database schema from a database to another?

sync database design, database schema

Tags
database weekly    
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones