Other Database Considerations | Inside Microsoft SQL Server 2000

Keep these additional points in mind about databases in SQL Server.

Databases vs. Schemas

The ANSI SQL-92 standard includes the notion of a schema—or, more precisely, an SQL-schema, which in many ways is similar to SQL Server's database concept. Per the ANSI standard, an SQL-schema is a collection of descriptors, each of which is described in the documentation as "a coded description of an SQL object." Basically, a schema is a collection of SQL objects, such as tables, views, and constraints. ANSI SQL-schemas are similar to SQL Server databases.

SQL Server 6.5 introduced support for the ANSI SQL-schema. However, the concept of a database in SQL Server is longstanding and much richer than that of a schema. SQL Server provides more extensive facilities for working with a database than for working with a schema. SQL Server includes commands, stored procedures, and powerful tools such as SQL Server Enterprise Manager that are designed around the fundamental concept of a database. These tools control backup, restoring, security, enumeration of objects, and configuration; counterparts of these tools don't exist for schemas. The SQL Server implementation of a schema is essentially a check box feature that provides conformance with the ANSI standard; it's not the preferred choice. Generally speaking, you should use databases, not schemas.

Using Removable Media

After you've created a database, you can package it so that you can distribute it via removable media such as CD. This can be useful for distributing large datasets. For example, you might want to put a detailed sales history database on a CD and send a copy to each of your branch offices. Typically, such a database is read-only (because CDs are read-only), although this isn't mandatory.

To create a removable media database, you create the database using the stored procedure sp_create_removable instead of the CREATE DATABASE statement. When calling the procedure, you must specify three or more files (one for the system catalog tables, one for the transaction log, and one or more for the user data tables). You must have a separate file for the system tables because when the removable media database is distributed and installed, the system tables will be installed to a writable device so that users can be added, permissions can be granted, and so on. The data itself is likely to remain on the read-only device.

Because removable media devices such as CDs are typically slower than hard drives, you can distribute on removable media a database that will be moved to a hard disk. If you're using a writable removable device, such as an optical drive, be sure that the device and controller are both on the Hardware Compatibility List (HCL). (You can find the HCL at www.microsoft.com/hcl.) I also recommend that you run the hard-disk test discussed in Chapter 4 on any such device. The failure rates of removable media devices are typically higher than those of standard hard disks.

A database can use multiple CDs or removable media devices. However, all media must be available simultaneously. For example, if a database uses three CDs, the system must have three CD drives so that all discs can be available when the database is used.

You can use the sp_certify_removable stored procedure to ensure that a database created with the intention of being burned onto a CD or other removable media meets certain restrictions. The main restriction is that the login sa must own the database and all the objects must be owned by the user dbo. There can be no users in the database other than dbo and guest. You can, however, have roles defined in the database, and permissions can be assigned to those roles. The stored procedure sp_certify_removable ensures that the database was created properly with the system tables separate from any user tables.

The first time you use a database sent on removable media, you use the sp_attach_db stored procedure to see the location of each file. You'll probably want to move the file containing the system tables to a writable disk so that you can create users, stored procedures, and additional permissions. You can keep the data on the removable media if you won't be modifying it. You can subsequently set the OFFLINE database option using ALTER DATABASE to toggle the database's availability. This book's companion CD contains a sample script that creates a database, ensures that it's appropriate for removable media use, and then installs it on your system. However, a database with no tables or data is pretty useless, so in the next chapter you'll learn how to create tables.

Detaching and Reattaching a Database

The ability to detach and reattach databases offers much broader benefits than just allowing the creation of removable databases. You can use the procedures sp_detach_db and sp_attach_db to move a database to a new physical drive—for example, if you're upgrading your hardware. You can use these stored procedures to make a copy of the database for testing or development purposes or as an alternative to the backup and restore commands.

Detaching a database requires that no one is using the database. If you find existing connections that you can't terminate, you can use the ALTER DATABASE command and set the database to SINGLE_USER mode using one of the termination options that automatically breaks existing connections. Detaching a database ensures that there are no incomplete transactions in the database and that there are no dirty pages for this database in memory. If these conditions cannot be met, the detach operation will not succeed. Once the database is detached, the entry for it is removed from the sysdatabases table in the master database, and from SQL Server's perspective, it's as if you had dropped the database. The command to detach a database is shown here:

 EXEC sp_detach_db <name of database>

NOTE
You can also drop the database with the DROP DATABASE command, but using this command is a little more severe. SQL Server makes sure that no one is connected to the database before dropping it, but no check of dirty pages or open transactions is made. Dropping a database also removes the physical files from the operating system, so unless you have a backup, the database is really gone.

The files for a detached database still exist, but the operating system considers them closed files, so they can be copied, moved, or even deleted like any other operating system files. A database that has been detached can be reattached using the stored procedure sp_attach_db. This procedure has a few more options than its detaching counterpart. If all the files still exist in their original locations, which would be the case if you detached the database just so you could copy the files to another server, all you need to specify is the location of the primary file. Remember that the primary file's header information contains the location of all the files belonging to the database. In fact, if some of the files exist in the original locations and only some of them have moved, you must specify only the moved files' locations when you execute the sp_attach_db procedure.

Although the documentation says that you should use sp_attach_db only on databases that were previously detached using sp_detach_db, sometimes following this recommendation isn't necessary. If you shut down the SQL server, the files will be closed, just as if you had detached the database. However, you will not be guaranteed that all dirty pages from the database were written to disk before the shutdown. This should not cause a problem when you attach such a database if the log file is available. The log file will have a record of all completed transactions, and a full recovery will be done when the database is attached to make sure that the database is consistent. One benefit of using the sp_detach_db procedure is that SQL Server will know that the database was cleanly shut down, and the log file does not have to be available to attach the database. SQL will build a new log file for you. This can be a quick way to shrink a log file that has become much larger than you would like, because the new log file that sp_attach_db creates for you will be the minimum size—less than 1 MB. Note that this trick for shrinking the log will not work if the database has more than one log file.

Here is the syntax for the sp_attach_db procedure:

 sp_attach_db [ @dbname  = ] 'dbname' , [ @filename1 = ] 'filename_n' [ ,...16  ]

Note that all you need to specify is the current filenames, regardless of whether the current names are the same as the original names. SQL Server will find the specified files and use them when attaching the database. You can even supply a new database name as you're attaching the files. You are limited to specifying up to 16 files for the database. Remember that you have to specify the filenames only if they are not in the original location stored in the header of the primary file. If you have a database for which you must specify more than 16 files, you can use the CREATE DATABASE command and specify the FOR ATTACH option.

Compatibility Levels

SQL Server 7 included a tremendous amount of new functionality and changed certain behaviors that existed in earlier versions. SQL Server 2000 has added even more new features. To provide the most complete level of backward compatibility, Microsoft allows you to set the compatibility level of a database to one of four modes: 80, 70, 65, or 60. All newly created databases in SQL Server 2000 have a compatibility level of 80 unless you change the level for the model database. A database that has been upgraded (using the Upgrade Wizard) from SQL Server version 6.0 or 6.5 will have its compatibility level set to the SQL Server version under which the database was last used (either 60 or 65). If you upgrade a server for SQL Server 7 to SQL Server 2000, all the databases on that server will have their compatibility level set to 80, although you can force SQL Server 2000 to behave like SQL Server 7 by setting this level to 70.

All the examples and explanations in this book assume that you're using a database that's in 80 compatibility mode unless otherwise noted. If you find that your SQL statements behave differently than the ones in the book, you should first verify that your database is in 80 compatibility mode by executing this procedure:

 EXEC sp_dbcmptlevel 'database name'

To change to a different compatibility level, run the procedure using a second argument of one of the three modes:

 EXEC sp_dbcmptlevel 'database name', compatibility-mode

NOTE
Not all changes in behavior from older versions of SQL Server can be duplicated by changing the compatibility level. For the most part, the differences have to do with whether new keywords and new syntax are recognized and have no effect on how your queries are processed internally. For a complete list of the behavioral differences between the four modes, see the online documentation for the sp_dbcmptlevel procedure.

The compatibility-level options merely provide a transition period while you're upgrading a database or an application to SQL Server 2000. I strongly suggest that you carefully consider your use of this option and make every effort to change your applications so that compatibility options are no longer needed. Microsoft doesn't guarantee that these options will continue to work in future versions of SQL Server.