Working with schemas in SQL Server 2005(zz)

                           Working with schemas in SQL Server 2005

Adam Machanic, Contributor
04.25.2006
Rating: -4.40- (out of 5)


link: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1184503,00.html

If you were to ask a group of database administrators which key personality trait they have that helps them do their jobs well, the most important and popular answer would be an excellent sense of organization. Great DBAs organize everything in the database -- from the data itself to the backup schedule -- with a level of precision that has given them a reputation as a slightly anal-retentive group in the IT world.

DBAs use their sense of organization to keep the database working properly, but many things can get in the way -- not the least of which is "object bloat," the sense that there are just too many objects in a given database. Managing databases that have become overfilled with tables, stored procedures and views can be utterly confusing on the best of days, and they quickly become tiresome. A tired DBA can't do a good job of ensuring data quality.

Unfortunately, previous versions of SQL Server didn't help much in this regard. The only way to easily manage a huge number of objects was to split them up and create one database for each logical subset -- one database for the sales department, another for marketing, still another for human resources, etc.

Creating all of these databases helped address the table management problem; now there was no question about where to look for a specific type of data. But, alas, the multi-database solution created other problems. DBAs soon discovered the joys of backing up all of those databases and then worrying about creating a separate maintenance plan for each. This is in addition to cross-database foreign keys, which don't exist in SQL Server. And need I bring up permissions? If the mere mention of the term "cross-database ownership chaining" makes you shudder, you know what I'm talking about.

If you're still stuck in this multiple-database nightmare, it might be time to wake up and start thinking about consolidation. SQL Server 2005, thanks to its implementation of an ANSI SQL feature called schemas, fixes many issues that drove many DBAs to split things up -- and it brings a few nice bonus features as well.

Creating and using schemas

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement. For instance, you might create a schema for sales-related data by using the following T-SQL:

CREATE SCHEMA Sales
GO

Once created, you can add objects, such as tables or stored procedures, to the schema. To do so, you must prefix them with the schema name:

CREATE TABLE Sales.SalesPeople
            (
            SalesPersonId INT,
            SalesPersonName VARCHAR(50)
            )
            GO
            

Just as you might expect, you can access an object by referencing it by schema name:

SELECT *
            FROM Sales.SalesPeople
            GO
            

In SQL Server 2005, every object participates in a schema, even if you don't explicitly specify one. Creating an object without specifying the schema will cause it to be created in your default schema. A default schema can be assigned to a user when the user is created, but if it is not specified, the dbo schema will be used. Since any given database user might belong to a different default schema, it's a good idea to always scope tables by schema name. Even if your table is in the dbo schema, you should use two-part naming, such as the following:

SELECT *
            FROM dbo.myTable
            GO
            

You can also transfer objects between schemas. That's done by using the TRANSFER option of ALTER SCHEMA. To transfer the SalesPeople table from the Sales schema into the dbo schema, the following T-SQL would be used:

ALTER SCHEMA dbo
            TRANSFER Sales.SalesPeople
            GO
            

Database organization

Schemas are truly great from an organizational point of view. The following screenshot is an image of some tables from the AdventureWorks sample database, as viewed in SQL Server Management Studio:

This cross-section of tables includes human resources-related data, data about people, and production information -- all logically separated by virtue of participating in separate schemas. These tables are all in the same database, so they can share references, filegroups, backup and maintenance schemas, or anything else that tables in the same database can do together. In other words, you get all of the benefits of splitting up the database and none of the problems that might have driven you to want to split it up in the first place.

Assigning schema permissions

Another great benefit of schemas is that you no longer need to worry about assigning users' rights to objects on a granular basis. Rather than create roles for every combination of users, or having to maintain permissions for groups of objects on a per-user basis, schemas allow you to assign rights in blocks. Once assigned schema-level permission, a user will have that right for any object in the schema.

Keep in mind that granting permissions on a schema is a bit different than granting permissions for many other objects. The following does not work:

GRANT SELECT ON SchemaName TO UserName

Instead, you need to scope the grant and specify that it's for a schema:

GRANT SELECT ON SCHEMA::SchemaName TO UserName

Database build scripts

Finally, I'd like to touch upon a little-known feature that's actually been around in SQL Server for quite a while. You might be surprised to learn that the CREATE SCHEMA statement is not actually new to SQL Server; it was added to the product in SQL Server 2000, but it didn't have much functionality until now.

What it did have then and still has today is the ability to allow a user to create objects that reference each other, out of order. That can be tremendously useful for development teams that keep database objects scripted in a source-control system, broken up into one file per object. The problem occurs when building a database out of the objects, which begs the question: How do you know the order in which to concatenate the files? For example, assume you have the DDL (data description language) for a table of Web site users and a table of logins for those users. If you concatenate the files in alphabetical order, you'll end up with the following T-SQL, which fails when run:

CREATE TABLE Logins
            (
            UserId INT NOT NULL
            REFERENCES Users (UserId),
            LoginDate DATETIME
            )
            CREATE TABLE Users
            (
            UserId INT NOT NULL PRIMARY KEY
            )
            

Reversing the order doesn't help either. How will you deal with the Products table, which contains products that are sold, and the Sales table, which details which products were sold to which customers?

Luckily, schemas solve this problem nicely. When creating a schema, you can specify additional DDLs to define the objects that will go in the schema -- and SQL Server takes care of the ordering. To create the Logins and Users' tables shown above, just put them into a schema together:

CREATE SCHEMA UserInfo
            CREATE TABLE Logins
            (
            UserId INT NOT NULL
            REFERENCES Users (UserId),
            LoginDate DATETIME
            )
            CREATE TABLE Users
            (
            UserId INT NOT NULL PRIMARY KEY
            )
            GO
            

Summary

Support for schemas can help solve a surprisingly large number of issues that have faced DBAs in past versions of SQL Server. Database consolidation and security management are easier, organization is better defined and even build scripts are easier to create. Schemas may not fix all of your problems as a DBA, but they will make things a lot more straightforward in many cases