Datasheet

When you first load SQL Server, you will start with four system databases:

❑ master

❑ model

❑ msdb

❑ tempdb

All of these need to be installed for your server to run properly. (Indeed, for some of them, it won’t

run at all without them.) From there, things vary depending on which installation choices you made.

Examples of some of the databases you may see include the following:

❑ AdventureWorks (the sample database)

❑ AdventureWorksDW (sample for use with Analysis Services)

The master Database

Every SQL Server, regardless of version or custom modifications, has the master database. This database

holds a special set of tables (system tables) that keeps track of the system as a whole. For example, when

you create a new database on the server, an entry is placed in the

sysdatabases table in the master

database. All extended and system stored procedures, regardless of which database they are intended

for use with, are stored in this database. Obviously, since almost everything that describes your server is

stored in here, this database is critical to your system and cannot be deleted.

The system tables, including those found in the master database, can, in a pinch, be extremely useful.

That said, their direct use is diminishing in importance as Microsoft continues to give more and more

other options for getting at system level information.

The model Database

The model database is aptly named, in the sense that it’s the model on which a copy can be based. The

model database forms a template for any new database that you create. This means that you can, if you

wish, alter the

model database if you want to change what standard, newly created databases look like.

For example, you could add a set of audit tables that you include in every database you build. You could

also include a few user groups that would be cloned into every new database that was created on the

If you’re quite cavalier, you may be saying to yourself, “Cool, I can’t wait to mess

around in there!”

Don’t go there! Using the system tables in any form is fraught with

peril. Microsoft has recommended against using the system tables for at least the last

three versions of SQL Server. They make absolutely no guarantees about compatibil-

ity in the

master database between versions — indeed, they virtually guarantee that

they will change. The worst offense comes when performing updates on objects in the

master database. Trust me when I tell you that altering these tables in any way is ask-

ing for a SQL Server that no longer functions. Fortunately, several alternatives (for

example, system functions, system stored procedures, and information_schema views)

are available for retrieving much of the metadata that is stored in the system tables.

Being Objective: Re-Examining Objects in SQL Server

04_584340 ch01.qxp 10/18/06 2:11 PM Page 3