Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Database compatibilty level

From Wiki

Jump to: navigation, search

Newer versions of the SQL Server database engine may not always be compatible with your database. To alleviate potential problems, you can set the compatibility level of a database to a previous version of the database engine. For example, you could be using a SQL2005 database engine with your database compatibility set to SQl2000 (compatibility level = 80).

The following list shows the compatibility value for each SQL Server database engine:

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005
  • 100 = SQL Server 2008

The following query will list all of the databases in your SQL instance that is using an older compatibility level.

  1. SELECT  Name, cmptlevel
  2. FROM    master.dbo.sysdatabases
  3. WHERE   cmptlevel != 10 * CONVERT(Int, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))

To change the compatibility level of a database, you must first put it in to single user mode, change the compatibility level, and then put it back in to multi user mode. Before doing this, it is important that you test all of the functionality in your database to make sure the higher compatibility level does not cause any of your existing functionality to break.

To change the compatibility level:

  1. ALTER DATABASE YourDatabaseName
  3. GO
  4. EXEC sp_dbcmptlevel YourDatabaseName, 90;
  5. GO
  6. ALTER DATABASE YourDatabaseName
  8. GO

Microsoft is planning on deprecating the sp_dbcmptlevel system stored procedure. With SQL2008 and above, your should use the following code to alter the compatibility level of your database.

  1. ALTER DATABASE YourDatabaseName
  3. GO
  4. ALTER DATABASE YourDatabaseName
  6. GO
  7. ALTER DATABASE YourDatabaseName
  9. GO

--George Mastros 13:34, 29 April 2010 (GMT)

751 Rating: 2.2/5 (103 votes cast)