Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 17, 2011

Using Schemas in SQL Server 2008

By Danny Lesandrini

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


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM