SQL Gets Schema

When I first started working with SQL Server (v 7.0), I found two part / four part names a bit confusing:

            SELECT * FROM ownername.tablename
            SELECT * FROM servername.databasename.ownername.tablename

What would confuse me was why anybody would want an owner name - a login - appearing as part of a fully qualified database object name. This creates all sorts of limitations. If an admin needed to remove a user, all the objects belonging to the user had to be dropped or reassigned – breaking all queries, views, and stored procedures with two part names. Since nobody wanted to tie an object to a user, we created databases where dbo owned all objects – forcing permissions to be granted on an object-by-object basis.

Linking users to objects also created all sorts of unfortunate naming conventions. Take .Text for instance, which prefixes all database object names with “blog_”. Likwise, the Community Starter Kit prefixes all object names with “Community_”. This practice allows these applications to work in hosted environments and share a common database without object naming collisions, but it feels ugly.

Thankfully, SQL Server 2005 gives us schema. Objects now belong to a schema. A database may have multiple schemas.

            SELECT * FROM schemaname.tablename
            SELECT * FROM servername.databasename.schemaname.tablename

No longer does the designer have to worry about naming conventions to group tables. A database designer can instead think about what the functional area of a table will be. For instance, the new AdventureWorks sample database in 2005 groups user tables into schemas like “HumanResources” and “Shipping”.

Schema separates users from database objects and functions similar to a namespace. The schema is also a container, and permissions can be assigned at the schema level instead of on individual objects. Schemas are an additional layer of indirection. Admins can drop users without worrying about reassigning object ownership. Schemas are flexible. Queries with two part names don’t feel so brittle anymore.

I thought of a little saying. It goes like this:

Users come and go, but schemas are forever.

Is this poetic? Or Corny? Does it belong in a book of prose? Or printed on some t-shirt and sold at a discount stand by the beach?