SQL Server Compatibility Level: Check & Change

-->

Have you recently upgraded your SQL server database from lower to upper version by any of the method attach/detach or backup/restore and not changed the compatibility level for upgraded SQL server version? If your answer is YES then it is the right time to change the compatibility level of upgraded version because your database is still acting same as it was running on earlier version of SQL server. Compatibility level of database does not change automatically. It is not a major problem for overall standpoint but you will not take advantage of upgraded version unless your database compatibility is change. Here I will give you some tips regarding how to check the compatibility level of SQL server database, & how to change the compatibility level of upgraded database.

How to check the compatibility level of SQL server database? It is the first step towards resolving above described problem. You will have to check the compatibility level of SQL server under that your database is running. You can do this by following methods:

Using SQL server management studio: Perform following steps to check the compatibility level of SQL server using SSMS:

• Right click on the database
• Select property & go-to the options tab, repeat this for all databases.

Note: In the above picture AdventureWorks is the name of database & compatibility level is 90.

Using Query: Run (“SELECT * FROM sys.databases”) query to get information for the all databases. It will show you compatibility level for all databases in current SQL server.

Bellow is the compatibility level list that will appear when you check compatibility level of database.

1. SQL server 6.0-------- 60
2. SQL server 6.5-------- 65
3. SQL server 7.0-------- 70
4. SQL server 2000-------80
5. SQL server 2005-------90
6. SQL server 2008-------100
7. SQL server 2008 R2---100
8. SQL server 2012------- 110

How to Change the Compatibility Level of Upgraded SQL server? So once you have identified the compatibility level of upgraded SQL server, you can change it by ALTER command and the command is...

ALTER Database AdventureWorks

Set Compatibility Level = 90;

Go

Best Practices: Attempting to change the compatibility of database while users are connected to the database may produce some problem so Microsoft recommends following procedure to change the compatibility level of database:

1. Convert your database from multi_user mode to single user mode.

Syntax: ALTER database set single_user

2. Now change the compatibility level of the database
3. Convert back to multi_user mode.

Syntax: ALTER database set multi_user.

4. Now run DBCC checkdb on the database to check the integrity of database.

Permission Required: ALTER permission on the database.