Using Schemas in SQL Server 2008

I’m not an expert on SQL Server Schemas. I first saw them implemented in the
Adventure Works database and I have to admit I didn’t understand what I was
looking at. The following explanation, right from an MSDN article, clearly describes
schemas in newer versions of SQL Server:

The behavior of schemas changed in SQL Server 2005. Schemas are no
longer equivalent to database users; each schema is now a distinct namespace
that exists independently of the database user who created it. In other words,
a schema is simply a container of objects. A schema can be owned by any user,
and its ownership is transferable. (User-Schema Separation)

Database schemas act as namespaces or containers for objects, such as
tables, views, procedures, and functions, that can be found in the sys.objects
catalog view. (
sys.schemas
(Transact-SQL)
)

 

Prior to SQL Server 2005, a database object (for example, a table) is
owned by a user. That user could be DBO or any valid user account. That table
is now directly linked to that user – the user cannot be deleted without
removing the table or changing the owner of the table. The table can only ever
be owned by one user.(
SQL
Server Best Practices – Implementation of Database Object Schemas
)

 

User-schema separation, introduced in SQL Server 2005, means that the
table is no longer owned by any user; it belongs to a schema. In turn, the
schema is owned by a user. .(
SQL
Server Best Practices – Implementation of Database Object Schemas
)

 

That’s the "big picture" explanation. There’s a plethora of
articles on the subject at MSDN and elsewhere to help one understand all the
subtleties, including this article about best practices:

SQL Server Best
Practices – Implementation of Database Object Schemas

The more I read, the more I realized that my reasons for implementing schemas
are different from what it appears they were originally intended, namely
security. I mean, security is a factor in my decision but my most important
reason for using them has to do with organization.

Take a look at this screen shot that shows a sampling of my tables. Each one
is prefixed with its schema name, which neatly clusters the tables of a
particular group together. I have a bucket for audit (AUDIT) tables, one for
common tables (COM) and groups that correspond to our major applications (BLA,
BUS, etc.).

In fact, this separation is precisely why we implemented schemas. Our data
had previously been in a number of separate, small SQL Server 2000 databases,
which we wanted to integrate into a single SQL Server 2008 database. In
addition to common, audit and admin schemas, I built one for each of our
application areas:

  • BDC for business development
  • BLA for business labor accounting
  • HR for human resources
  • NCD for network contacts
  • PTS for our project tracking

While this may resemble the "mother-of-all-databases" design
paradigm, keep in mind that the total size of the database is less than 300 MB.
In fact, size was the least of my concerns. It is the sheer number of tables
that is overwhelming. We have data tables, lookup tables and audit tables.
Schemas allow us, as developers, to quickly and easily find the entities we
need for a given application. As mentioned, this applies equally well to
tables, views, procs and functions.

We had been migrating a series of Microsoft Access desktop applications to a
web interface built with MVC2 (Model View Controller version 2.0) technology.
MVC2 recently introduced something called Application Areas, which we leveraged
to match our SQL Server 2008 Schemas exactly.

This solution is clean, neat and intuitive. That being the case, it’s
amazing how much push-back I get from my SQL Server DBA friends when I tell
them how I implemented schemas in this database. It’s almost as if the DBA
community is not ready to embrace SQL Server Schemas. Or perhaps they are
thinking of pre-2005 schemas where object ownership was tightly bound.
Regardless, with a year’s experience under my belt, I’d like to go on record in
favor of implementing Schemas.

Why SQL Server Schemas Make Sense

1) I’ve already mentioned the first reason they make sense: ORGANIZATION.

I don’t have any empirical data to prove that using schemas is more
efficient, I do, however, have allegorical evidence. During the migration process,
I was working in both the new and old databases simultaneously. Finding objects
in the SQL Server 2008 Schema-Implemented database was much faster and easier,
requiring fewer "clicks" to locate a database object. This facet
alone makes the paradigm worth it.

2) The second reason relates directly to COST.

While there is effectively no additional charge to add databases to SQL
Server running in your server room, there is a direct expense when migrating to
SQL Azure or other Cloud SQL Server implementation. When told I would have to
pay on a per-database basis, I didn’t need a calculator to realize I needed to
consolidate the data for all my application areas into a single database.

3) The last reason schemas make sense is EASE OF USE.

In my limited experience, SQL Server Schemas are very simple to use. Below
is a script that creates a schema named PROD, creates a table associated with
that schema, creates another schema named DBJ, transfers the table to the new
schema and then drops the original schema. It’s so simple, I can remember it
without having to refer to the help file every time I need a new schema.

CREATE SCHEMA PROD
GO
CREATE TABLE PROD.Article (ID INT, Name CHAR(32))
GO
CREATE SCHEMA DBJ
GO
ALTER SCHEMA DBJ TRANSFER PROD.Article;
GO
DROP SCHEMA PROD
GO

I still use SYSOBJECTS to query the system for objects but the SCHEMA is not
included in the table name so I had to find another way to list my objects. The
script that follows queries the INFORMATION_SCHEMA table to return a list of
tables with their schema assignment. This example also includes some
information about columns and this screen shot shows the result set it
generated.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM MoM.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ‘DBO’
ORDER BY TABLE_NAME, COLUMN_NAME

According to MSDN, securables can be managed with greater precision than in
earlier releases. The T-SQL commands to manage security for many users or roles
could get verbose, but once again, they are very easy and intuitive, as is
shown in the following sample script:

CREATE SCHEMA DBJ

GRANT SELECT ON SCHEMA :: DBJ TO guest;
GRANT INSERT ON SCHEMA :: DBJ TO guest;
GRANT EXECUTE ON SCHEMA :: DBJ TO guest;
GRANT DELETE ON SCHEMA :: DBJ TO guest;
GRANT UPDATE ON SCHEMA :: DBJ TO guest;

Alternatively, you can use the properties page to assign permissions. Here is a screen shot showing the result of executing the above described script, an action that could
have been executed through the GUI.

Should You Implement Schemas?

As with most things, the answer to that question is "it depends".
For the case I described, it makes a lot of sense because I save time and
money. I’ve been told by DBA friends that it could create a security management
nightmare but frankly, I don’t see why. If your SQL Server has to stay in your
server room, maybe there is no pay-off. On the other hand, if you’re moving
your application to The Cloud, you should definitely consider implementing
Schemas in SQL Server 2008.





Danny Lesandrini

Danny J. Lesandrini currently works as the IT Director for Pharmatech
Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft
Certifications in Access, Visual Basic and SQL Server and has been programming
with Microsoft development tools since 1995. Danny maintains a web site with
free code samples at http://lesandrini.com/datafast/.

See all articles by Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles