New in 2000

Before you upgrade your SQL Server 6.5 machine to SQL Server 2000, you need to review the production and development code that accesses the data stored in the database. With SQL Server 2000 (when you set the compatibility level to 80), you'll notice many changes in the way code is interpreted and even in the syntax of allowable code. For the most part, Microsoft made these changes to bring T-SQL into closer compliance with the 1992 IEEE/ANSI SQL standard.

In SQL Server 2000 (and 7.0), the database compatibility level determines how SQL Server behaves compared with earlier releases. Valid compatibility levels are 60, 65, 70, and 80. After you upgrade a SQL Server 6.5 database to SQL Server 2000, the compatibility level for that database is 65 until you manually change it to a higher value. However, the compatibility level for the model database is 80, so any new databases you create will have a compatibility level of 80. (For reference, if you upgrade a SQL Server 7.0 database to SQL Server 2000, the compatibility level changes from 70 to 80.) Compatibility levels 65 and 80 interpret code very differently, so you need to review all your code and modify it as necessary as soon as possible after the database upgrade.

To find the compatibility level of an existing SQL Server 2000 or 7.0 database, use the command

sp_dbcmptlevel database_name

where database_name is the name of the database (e.g., pubs).To change the compatibility level of an existing SQL Server 2000 or 7.0 database, you can use the command

sp_dbcmptlevel database_name, new_compatibility_level

where new_compatibility_level is the new compatibility level you want to set for the database (must be 80, 70, 65, or 60). You can also check and change the compatibility level from the Database Properties dialog box in Enterprise Manager.

For a useful table that outlines the behavioral changes between the compatibility levels, in SQL Server Books Online (BOL), find sp_dbcmptlevel in the index. Click it, and select Transact SQL reference. This table is a summary of the code changes and the interpretive differences between the 60-65 compatibility levels and the 70-80 compatibility levels. Here are a few changes in compatibility levels 70 and 80 that are worth noting:

Here are some notes about what you can and can't do with compatibility levels:

Note that Microsoft strongly advises against changing the compatibility level within a batch of T-SQL statements.

New ANSI Settings

Most client connections to SQL Server 6.5 use DB-Library, which by default treats the following ANSI settings as off: ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS, and QUOTED_IDENTIFIER. The client interfaces to SQL Server 2000 all use ODBC or OLE DB; on connection, they automatically issue a session command that sets these ANSI options to on. Any code that uses the equality operators "=" or "<>" to reference NULL produces different results from those returned under SQL Server 6.5. Always use the IS NULL or IS NOT NULL construct when you're dealing with NULL.

NULLs in Fixed-Length Columns

Some DBAs have seen their databases grow much larger than expected after upgrading from SQL Server 6.5 to 2000 or 7.0. One possible reason is the way the different versions handle fixed-length character fields that allow NULL values. In SQL Server 6.5, setting the NULL property to on for a fixed-length field effectively makes it a variable-length field. If the field contains character data, it's set to the fixed length you specified. If it's NULL, it becomes a zero-length field.

In SQL Server 2000 and 7.0, a fixed-length field is always the same fixed length. If it contains a NULL, the NULL flag is set, but SQL Server still allocates the space to the row. So if you had a wide, sparsely populated column in SQL Server 6.5, you'd see a big increase in the space that table occupies when you upgrade. For example, suppose a developer included a column in the Products table for recall notices. Less than 1 percent of all products have recall notices, but the developer set the column to char(200) because the NULL values took up no space. After an upgrade to SQL Server 2000, the other 99 percent of products also have the 200 bytes allocated. So by allowing NULLs, the developer incurred the overhead of a variable-length field.

System Schema Views

The system tables have changed in the SQL Server 2000 SQL catalog. Microsoft recommends that, rather than accessing the system tables directly, you use the information schema views supplied with SQL Server when you want to access system-level metadata. Modifying your code initially to use information schema views is a big job, but you have to do it only once, for this upgrade. Microsoft plans to constantly modify the information schema views to return the same information regardless of future changes to the underlying system tables. For a complete list of which columns each view displays, you can use the new Object Browser feature of Query Analyzer, as Figure A shows. If you've written code that accesses sysusages, be forewarned: Sysusages is no longer in the SQL catalog. The only other system tables that are no longer in the SQL catalog are sysbackuphistory, sysbackupdetail, sysrestorehistory, and sysrestoredetail.

Although you might be tempted to stay at compatibility level 65 after an upgrade and refrain from making what will be, in some cases, massive code changes, we advise you not to do this. SQL Server 2000 works better in a mixed environment of different database platforms than any of its predecessors, and the heterogeneous database environment is the model of the future. Don't get left behind.

Security Changes

SQL Server 2000's security model is completely different from that of SQL Server 6.5. SQL Server 2000 uses the security model Microsoft introduced in SQL Server 7.0; this security is more integrated with Windows 2000 and Windows NT. You can give permissions to Win2K and NT groups instead of having to list individual users, and permissions are cumulative depending on which groups a user belongs to. In SQL Server 2000, defined roles have specific permissions, and you can place the users or groups into those roles. Use the SQL Script generator to print out the old security information so that you can determine who needs which permissions. Although the new model might seem complex, it's easier to use than the 6.5 model when you have to control and track permissions.

Backups

Backup and restore functions are also greatly improved in SQL Server 2000 and 7.0. Besides the conventional database and transaction log backups, SQL Server 2000 and 7.0 have the option to perform a differential backup, which backs up any pages that have changed since the last full backup. When combined with full and log backups, differential backups can make restores much faster. Another major change is the ability to back up files and filegroups individually. (For more information about SQL Server 2000 and 7.0 backups, see BOL or Michael Reilly’s articles "Backup Strategies," August 2000, InstantDoc ID 9629, and "Top 11 Backup Tips," September 2001, InstantDoc ID 21701.)

You also need to consider the recovery model, which controls how much data you can recover from your backups if you lose the database. (For details about SQL Server 2000 recovery models, see Kalen Delaney, Inside SQL Server, "Database Recovery Models," June 2000, InstantDoc ID 8551.) The default setting is full recovery. In this mode, SQL Server logs even bulk inserts and other "nonlogged" transactions. If you run nonlogged data-loading tasks, you might find that your transaction log grows much faster than you expect and your backups are larger than they were with SQL Server 6.5.

Here are some SQL Server 2000 features that might affect your upgrade and subsequent operations: