Add some curves to your SQL Server Model database

by Arthur Fuller | Oct 16, 2006 5:50:00 PM

Tags: Storage, Databases, Arthur Fuller, database, Microsoft SQL Server, server, SQL Server Newsletter

Takeaway: Find out why Arthur Fuller believes that the Model database is the most overlooked and underused feature in SQL Server. He presents a sample demonstration of how you might use the Model database.

Every installation of SQL Server installs a number of system databases. In keeping with the late Dr. E. F. Codd's precepts, information about the database must be available using the same commands as you would use to interrogate your own databases. What better way to fulfill this mandate than to store the system information in a database?

SQL Server installs these system databases:

• Master: Stores the description of all other databases, plus database logins and master-only system tables.
• Model: Serves as a snapshot of every new database you create.
• Msdb: Houses the code and data that support SQL Server Agent and SQL Server Management Studio (and, in the old days, Enterprise Manager).
• Tempdb: Whenever you create a temp table (i.e., a table whose name begins with "#" or "##"), it is created in this database. In addition, SQL Server uses this database to house its own temp tables (i.e., when you use ORDER BY, SQL Server sorts the results here). You modify this database numerous times per hour, and even per minute.
• Mssqlresourcedb: Houses system objects. Unless you go through the back door, this database is hidden and read-only. It is also internal to SQL Server.

If you are using replication, SQL Server may also install this database:

• Distribution: Houses information about publishers, subscribers, server targets, etc..

(Note: You should never modify the Master, Msdb, or Mssqlresourcedb databases.)

Now I'll turn my attention to the Model database, which is a template for every database you create. So basically, you can take the Model database, do anything you want with it, and every subsequent database you create will inherit your changes.

Suppose you are an independent consultant who creates YAFOES (yet another friendly order-entry system). The specifics always differ, but in general they all look like piggy banks: Customers, Orders, Details, Products, Payments, and so on. So why not add these tables to Model?

I created a backup of Model, and then I created a few sample tables in Model, on the assumption that I am a YAFOES builder. (Note: Before proceeding, I strongly urge you to back up Model!)

Now run the script in Listing A to create some simple tables in Model. I also added a view, a UDF, and a stored procedure, as well as table and column descriptions and one or two default values.

Now create a new database that contains all the new database objects, exactly as I specified them. For various reasons, database X is created separately from database Y and Z (sales, marketing, production), yet they all must eventually resolve to the enterprise view, using SSIS or Cognos or whatever you choose. I suggest that the place to begin such consistency is in the Model database. Once you customize the Model database as illustrated above (and to suit your situation), every database you create will have precisely the same tables and column definitions.

This is just a sample demonstration, but I believe its implications go far beyond that. I assumed the independent DBA posture to build this sample, but I think that the perspective holds within a single organization as well.

These experiments have led me to the opinion that the Model database is the most overlooked and underused feature in SQL Server.

Miss a tip?

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