Benefit from SQL Server 2005's new schema convention

by Arthur Fuller | Aug 14, 2006 2:36:00 PM

Tags: Storage, Databases, Arthur Fuller, benefit, Figure C, Microsoft SQL Server 2005, schema, database, Microsoft SQL Server, SQL Server Newsletter

6 comment(s)

Takeaway: Arthur Fuller highlights the benefits of one of his favorite new features in SQL Server 2005: adding the notion of schemas to object names. He notes that you'll really appreciate schemas if you're working with more than 20 tables.

One of the coolest features of SQL Server 2005 is the new naming convention, which adds the notion of schema to object names. A fully qualified object name in SQL Server 2005 now includes four parts delimited with dots, like this:

[[[server.] [database].] [schema].] database-object]

This syntax convention indicates that only the fourth element is mandatory. To understand what you can (and in some cases must) specify, read the syntax statement from right to left.

Obviously, you must specify the primitive object name, which assumes that you have already used the database of interest. In addition, you can qualify this object by specifying the schema in which it resides. If you're referring to a schema in another database, then both the database and the schema parts are required. Finally, you can tell the SQL engine to look on another server for the database of interest.

Benefits of schemas

The first obvious benefit is the ability to subdivide your database into loci of interest, rather as the Erwin modeling tool lets you create subject areas. The richer your database (i.e., the more tables it contains), the more you can benefit by using schema objects to contain only the tables of interest in a particular part of the application.

You can see this put to use in the AdventureWorks sample database. If you run Management Studio and then drill down in the Object Explorer through Databases, AdventureWorks, Security, and Schemas, you will see the standard roles (db_accessadmin, db_backupoperator, dbo, and so on) now identified as schemas, with the AdventureWorks-specific schemas added: HumanResources, Person, Production, Purchasing, and Sales. If you double-click on any schema in the tree, the Schema Properties dialog box appears, as shown in Figure A.

As Figure A illustrates, the dialog box has three nodes, each of which displays the relevant properties. Figure B shows the Permissions pane of this dialog box. Using the Add button, you can select one or more objects and grant or revoke permissions on them. In the absence of roles, explicit permissions, and schema permissions, the SQL engine will assume that every user may access objects. If you create an object without specifying its schema, then SQL will place it in the dbo schema (the default).

Figure C illustrates the most valuable aspect of schemas: The objects are sorted by subject area and name. And, when creating a new query, you can drag the object(s) of interest into the query and the qualified filenames will appear.

The new schema qualifier might seem unnecessarily cumbersome when you first use it; if you find this to be the case, you can do things as you did in SQL Server 2000, and the engine won't stop you. You'll simply be lumping all of your objects in the dbo schema.

While the AdventureWorks sample database is richer than previous samples (Northwind and Pubs), it is far from a rich database, which I define as consisting of at least 150 tables. One SQL Server 2000 database I worked on contained more than 500 tables and 2,000 procedures. In this context, schemas would have been a godsend. But the organizational value of schemas does not need such demanding databases. The benefits begin as soon as you've got more than 20 tables. They help you focus on one part of the problem at a time, without the clutter of all the other parts.

As an exercise to explore the schema-based way of doing things, I suggest loading some database you worked on a while ago and asking yourself, "What would I have done differently if I had schemas at the time?"

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

6 comment(s)