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.