SQL Query to View or Set the Compatibility Mode in SQL Server 2012

Steps to View or Set the compatibility mode:

1. Connect to a particular DB Server using MS SQL Server Management Studio and create a new query

2. Run the Queries to view the compatibility mode

use mydbname

select compatibility_levelfrom sys.databases where name= ‘mydbname’

 

Output:

compatibility_level will be either 100 or 101.

- 100 = SQL Server 2008 & 2008 R2

- 110 = SQL Server 2012

3. Run the following Query to set the compatibility mode to SQL Server 2012.

Alter database mydbname set compatibility_level = 110

 

4. Here is the compatibility level for different SQL Server versions

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 & 2008 R2

110 = SQL Server 2012