Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

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.

Navigation

Google Ads

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
  2. SET SINGLE_USER
  3. GO
  4. EXEC sp_dbcmptlevel YourDatabaseName, 90;
  5. GO
  6. ALTER DATABASE YourDatabaseName
  7. SET MULTI_USER
  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
  2. SET SINGLE_USER
  3. GO
  4. ALTER DATABASE YourDatabaseName
  5. SET COMPATIBILITY_LEVEL = 100;
  6. GO
  7. ALTER DATABASE YourDatabaseName
  8. SET MULTI_USER
  9. GO

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

751 Rating: 2.5/5 (73 votes cast)