Other Database Considerations

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

Database Doesn't Equal Schema

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 notion of a database within SQL Server is long standing and much richer than its concept 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 SQL Server concept of a database. These tools control backup, loading, security, enumeration of objects, and configuration; counterparts don't exist for schemas. The SQL Server implementation of schema is essentially a check box feature providing conformance with the ANSI standard; it's not the preferred choice. Generally speaking, you'll want to use databases, not schemas.

Removable Media

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

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), even though the data itself is likely to remain on a read-only device.

Because removable media devices such as CDs are typically slower than hard drives , it's also possible to distribute on removable media a database that will then 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). We 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 for 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 needs to 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 the restrictions just noted. Use the sp_dbinstall stored procedure the first time someone at a site wants to use a database sent on removable media. Subsequently, to use a removable media database, use the offline option of the sp_dboption stored procedure to toggle its availability. This book's companion CD contains an example 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.

Compatibility Level

SQL Server 7 includes a tremendous amount of new functionality and changes certain behaviors that existed prior to version 7. To provide the most complete level of backward compatibility, Microsoft allows you set the compatibility level of a database to one of three modes: 70, 65, or 60. A database that has been upgraded (using the Upgrade Wizard) will have its compatibility level set to the SQL Server version under which the database was last used (either 65 or 60).

All the examples and explanations in this book assume that you're using a database that's in 70 compatibility mode, unless otherwise mentioned. If you find that your SQL statements behave differently than the ones in the book, you should first verify that your database is in 70 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 a complete list of the behavioral differences in the three modes, please see the online documentation for the sp_dbcmptlevel procedure.

The compatibility-level options are meant to only allow a transition period while you're upgrading a database and application to SQL Server 7. We 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.