When Upgrading, Check Compatibility Level

When you upgrade a SQL Server 2000 instance inplace, you might have noticed that the compatibility level of existing user databases doesn't change to a value of 90—the compatibility level of a native SQL Server 2005 database. During the upgrade process, this value is supposed to change from 80 to 90, but sometimes it doesn't. If the compatibility level stays at 80, SQL Server 2005 and your applications will run, but you won't be able to take advantage of the newT-SQL features and you won't get access to all of the performance gains that the query optimizer provides.

To check the compatability level, enter the following command, replacing TestMe with the name of your database.

EXEC sp_dbcmptlevel 'TestMe' 

A value of 90 indicates SQL Server 2005, and a value of 80 indicates SQL Server 2000. If you have to change the compatibility code, you'll also want to update statistics on the database. Failure to do so could result is suboptimal query performance because SQL Server 2005 has an improved model for managing statistics. For more information, read the Microsoft article "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005."