Programmer's Rules

SQL Server

How to set compatibility level in SQL Server

Compatibility level in SQL Server allows to adapt to syntax of previous versions of SQL Server DBMS.

SQL Server

The list of compatibility levels is shown below:

Server Version Database Engine Version Current Compatibility Level Supported Compatibility Levels
SQL Server 2017 (14.x) 14 140 140, 130, 120, 110, 100
Azure SQL Database logical server 12 130 150, 140, 130, 120, 110, 100
Azure SQL Database Managed Instance 12 130 150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x) 13 130 130, 120, 110, 100
SQL Server 2014 (12.x) 12 120 120, 110, 100
SQL Server 2012 (11.x) 11 110 110, 100, 90
SQL Server 2008 R2 10.5 100 100, 90, 80
SQL Server 2008 10 100 100, 90, 80
SQL Server 2005 9 90 90, 80
SQL Server 2000 8 80 80

It is not recommended to change the compatibility level while users are connected. It can lead to unreliable results of queries.

In order to safely change the compatibility level:

1. Set database to single-user access mode:

2. Change the compatibility level of database:

3. Set database to multiuser access mode:

Tutorial statistics