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.