SQL Server 2005 Compatibility Level

January 24, 2006 by Leave a Comment

SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server.

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005
Here is a sample script that will show you the current compatibility level, set it to 90, then show you the current level after it has been set.
sp_dbcmptlevel dbname;
GO
sp_dbcmptlevel dbname, 90;
GO
sp_dbcmptlevel dbname;
GO

This TSQL will generate the following output:

The current compatibility level is 90.
The current compatibility level is 80.

Once you change the compatibility level, you will want to be sure that your system still runs correctly.

Filed under DBA, TSQL Tagged with , ,