Compatibility levels with MSSQL

  • Microsoft SQL Server keeps track of the compatibility level at the database level.
  • The features of the new version/upgrade are only available if the database compatibility level are changed to the new level level.
  • The current compatibility levels are:
    1. 60 for MSSQL 6
    2. 65 for MSSQL 6.5
    3. 70 for MSSQL 7
    4. 80 for MSSQL 2000
    5. 90 for MSSQL 2005
  • The compatibility levels represent the version numbers.
  • The changes/upgrades of the SQL Server may/do not work with specific database unless you change the database compatibility level.

Applied to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Database compatibility level

You can view the database compatibility level and change them with: sp_dbcomptlevel.

  1. PRINT 'Master: ' EXEC sys.SP_DBCMPTLEVEL 'master';
  2. PRINT '-----------------------';
  3. go
  4. PRINT 'Tempdb: ' EXEC sys.SP_DBCMPTLEVEL 'tempdb';
  5. PRINT '-----------------------';
  6. go
  7. PRINT 'Model: ' EXEC sys.SP_DBCMPTLEVEL 'model';
  8. PRINT '-----------------------';
  9. go
  10. PRINT 'Msdb: ' EXEC sys.SP_DBCMPTLEVEL 'msdb';
  11. PRINT '-----------------------';
  12. go
  13. PRINT 'ReportServer: ' EXEC sys.SP_DBCMPTLEVEL 'ReportServer';
  14. PRINT '-----------------------';
  15. go
  16. PRINT 'ReportServerTempDB: ' EXEC sys.SP_DBCMPTLEVEL 'ReportServerTempDB';
  17. PRINT '-----------------------';
  18. go
  19. PRINT 'AdventureWorksDW: ' EXEC sys.SP_DBCMPTLEVEL 'AdventureWorksDW';
  20. PRINT '-----------------------';
  21. go
  22. PRINT 'AdventureWorks: ' EXEC sys.SP_DBCMPTLEVEL 'AdventureWorks';
  23. PRINT '-----------------------';
  24. go
  25. PRINT 'AdventureWorksLT: ' EXEC sys.SP_DBCMPTLEVEL 'AdventureWorksLT';
  26. PRINT '-----------------------';
  27. go
  28. PRINT 'sql911: ' EXEC sys.SP_DBCMPTLEVEL 'sql911';
  29. PRINT '-----------------------';
  30. go
Master: 
The current compatibility level is 90.
-----------------------
Tempdb: 
The current compatibility level is 90.
-----------------------
Model: 
The current compatibility level is 90.
-----------------------
Msdb: 
The current compatibility level is 90.
-----------------------
ReportServer: 
The current compatibility level is 90.
-----------------------
ReportServerTempDB: 
The current compatibility level is 90.
-----------------------
AdventureWorksDW: 
The current compatibility level is 90.
-----------------------
AdventureWorks: 
The current compatibility level is 90.
-----------------------
AdventureWorksLT: 
The current compatibility level is 90.
-----------------------
sql911: 
The current compatibility level is 90.
-----------------------
  1. Each sp_dbcmptlevel must be submitted individually.
  2. sp_dbcmptlevel can only be called ad hoc. If you called sp_dbcmptlevel from a batch or procedure, exec or a batch, you will get:
    Msg 15432, Level 16, State 1, Procedure sp_dbcmptlevel, Line 28
    Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.
  • To get a list of all the databases use:
  1. EXEC sp_msforeachdb 'print ''?'' '
  2. go
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
AdventureWorksDW
AdventureWorks
AdventureWorksLT
sql911

Change the database compatibility level

To change the database compatibility level just add the level needed after the database.

  1. EXEC SP_DBCMPTLEVEL AdventureWorks, 80;
  2. go
  3. PRINT 'AdventureWorks: ' EXEC sys.SP_DBCMPTLEVEL 'AdventureWorks';
  4. PRINT '-----------------------';
  5. go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
AdventureWorks: 
The current compatibility level is 80.
-----------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
AdventureWorks: 
The current compatibility level is 90.
-----------------------