It’s easy to get confused about schema in SQL Server, especially when veteran DBAs use the term in seemingly contradictory circumstances. As of SQL Server 2005, schema has gained multiple meanings. Understanding the difference and similarities between the meanings is key to understanding how Microsoft SQL Server works.
In it’s original usage, schema referenced the physical structure of a database (user or system). The physical structure consists of the database’s objects: columns, tables, views, stored procedures, and functions. The most granular schema is a column’s datatype. If I create a table called Orders and describe a column in that table called OrderID as INT, I am defining a schema on that column. If I change that column to a VARCHAR(10), I am changing the schema (the physical structure) of that column.
When SQL Server 2005 came along, schema had a new meaning added to it. Books Online describes a schema as “…a collection of database entities that form a single namespace.” An easier definition for schema is that is a container for database objects. Like a toolbox contains tools, nails, and screws, the schema contains tables, views, functions, and stored procedures.
A few things to note about the new schemas.
-
A database can contain multiple schemas but a schema cannot contain multiple databases.
Columns are never contained directly underneath the schema umbrella because they cannot exist separately from their table. While a lot of things can be defined on a column level, schemas are not one of them.
Owners no longer exist in SQL Server, having been replaced by schemas.
Schemas and owners are essentially the same thing (containers for database objects), but owners were directly tied to database logins and the people who created the objects. This meant that if Nancy created a bunch of views and tables, and then left the company, the objects she created were left orphaned (and often inaccessible) until the DBA changed the object owners to someone else. It created a maintenance nightmare that DBAs hated.
The new schemas have resolved this problem. Now we can use the CREATE SCHEMA statement to define a container that is login independent. If Nancy creates a container called Sales, it doesn’t matter if she leaves the company or takes a position on another team. The Sales schema will not be orphaned because it can be owned by DBO. We can still make the mistake of assigning the schema to a current user (this is the AUTHORIZATION clause), but we’re no longer forced to.
The new schemas also allow us finer granularity and control on permissions grants. Which I absolutely love, BTW.
The main reason why schema can be confusing, though, is that DBAs still use the term in its original sense. I myself talking about schema reviews as often as I talk about schema permissions. And I’m not the only one.
The easiest way to tell the difference between the two is this: Schema comments referencing permissions, security, or filtering often refer to the new definition of schema. Schema comments referencing datatypes, triggers, reviews, space issues, or the alteration of a schema often to the original definition of schema.
