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:

  • SELECT statements with GROUP BY clauses no longer return ordered result sets.
  • Nullability of columns with a bit data type may produce differing behavior based on the setting of ANSI_NULL_DFLT, which you can modify at the session or database level.
  • Stored procedures that contain invalid object names (as when you use a reserved keyword as a name), instead of failing on parse or compile, will compile with no warning. You get the error message at execution time (this is called deferred name resolution). Note that several reserved keywords have been added with each compatibility level.
  • These compatibility levels don't allow non-ANSI variations of the INSERT statement.
  • SQL Server 2000 and 7.0 don't treat empty-string literals as blank; it treats them as empty strings, with resulting changes in the way certain functions (DATALENGTH, LEFT, LTRIM, REPLICATE, RIGHT, RTRIM, SPACE, SUBSTRING, and UPDATETEXT) behave.
  • The functions CHARINDEX() and PATINDEX() return NULL under more circumstances.
  • Referencing text or image data types in the inserted and deleted tables isn't allowed in SQL Server 2000.
  • Concat null yields null
  • is on by default within the SQL Server 2000 client connections to the database, with resulting changes in the way SQL Server interprets results.
  • ROLLBACKS called by INSERT statements embedded in stored procedures can now cause SQL Server to roll back the entire transaction.

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

  • You can change the compatibility level for any user database.
  • You can't change the compatibility level setting for the master database.
  • You can change the compatibility level for the model database; any new databases you create will inherit that compatibility level.
  • You can't change the compatibility level inside a stored procedure or in any T-SQL code that's executed with the EXEC('code_string') syntax.

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.


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:

  • Page size increased from 2KB to 8KB, which can affect file I/O.
  • Locking can now happen at the row level, which can affect concurrency.
  • You can have two or more instances of SQL Server (with associated databases) on one physical computer.
  • Devices and segments have been replaced with filegroups.
  • Traditional (nonclustered) indexing has changed, creating a potential for much larger traditional indexes if the clustering key is large.
  • Quoted identifiers can be handled differently, potentially affecting the behavior of your stored procedures.
  • SQL Server handles NULLs in fixed-length columns differently, which can cause the database to grow unexpectedly.
  • SQL Server 2000's security model is integrated with NT users and groups—you can't have mixed-mode security.
  • A new set of roles (e.g., database creators, disk administrators, security administrators) covers many of the basic SQL Server administrative tasks.
  • You can set compatibility levels database by database.
  • You can set sort order and collations database by database (the default is general Unicode, case insensitive, width insensitive, Kana insensitive).
  • An improved query processor uses new algorithms.
  • SQL Server Profiler replaces SQL Trace.
  • Osql (ODBC-based) replaces isql (based on DB-Library)
  • Support for DECnet Library sockets has been dropped.
  • SQL Server 2000 doesn't support the Named Pipes or Banyan VINES server network libraries on Windows 9x.
  • English Query and Analysis Services are installed separately from SQL Server 2000 Server.