With SQL Server 2014, there was a new compatibility level introduced, level 120 the new SQL Server 2014 Compatibility Level.
SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server.
- 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 and SQL Server 2008 R2
- 110 = SQL Server 2012
- 120 = SQL Server 2014
Changing the compatibility level in SQL Server may change the behavior of specific of the functionality of the SQL Server. Be sure to research and test before changing compatibility levels.
SQL Server 2014 Compatibility Level Sample Code
Sp_dbcmptlevel dbname GO Sp_dbcmptlevel dbname, 120 GO Sp_dbcmptlevel dbname GO
This TSQL will generate the following output:
In this example it didn’t change anything as the SQL Server 2014 Compatibility Level was already set to 120, however if your database came over from a previous version of SQL Server it might have an older compatibility level.
Another way to check the compatibility level is with the SQL Server Management Studio UI.
- Right click the database
- Select Properties
- Go to the options tab
Once you change the compatibility level, you will want to be sure that your system still runs correctly.