Compatibility Levels

Each new version of SQL Server includes a tremendous amount of new functionality, much of which requires new keywords and also changes certain behaviors that existed in earlier versions. To provide maximum backward compatibility, Microsoft allows you to set the compatibility level of a database to one of the following modes: 90, 80, 70, 65, or 60. Compatibility levels 65 and 60 are being deprecated, and 60 is not supported by SQL Server Management Studio or SMO. All newly created databases in SQL Server 2005 have a compatibility level of 90 unless you change the level for the model database. A database that has been upgraded or attached from an older version will have its compatibility level set to the version from which the database was upgraded.

All the examples and explanations in this book assume that you're using a database in 90 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 90 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 possible modes:

EXEC sp_dbcmptlevel '<database name>', <compatibility-level>;

Note

The compatibility-level options merely provide a transition period while you're upgrading a database or an application to SQL Server 2005. I strongly suggest that you try to change your applications so that compatibility options are not needed. Microsoft doesn't guarantee that these options will continue to work in future versions of SQL Server.

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 they do not affect how your queries are processed internally. For example, if you change to compatibility level 80, you don't make the system tables viewable or do away with schemas. But because the word PIVOT is a new reserved keyword in SQL Server 2005 (compatibility level 90), by setting your compatibility level to 80 you can create a table called PIVOT without using any special delimitersor a table you already have in a SQL Server 2000 database will continue to be accessible if the database stays in 80 compatibility level.

For a complete list of the behavioral differences between the compatibility levels and the new keywords, see the online documentation for the sp_dbcmptlevel procedure.