How to change compatibility level of a SQL Database

Posted by     "Erwin Bierens" on Monday, February 10, 2020

TOC

Get Compatibility level of databases

First start checking your version of SQL, start a new query.

    SELECT SERVERPROPERTY('ProductVersion');

Run Query

Now start a new query to check the compatibility level of the databases.

    SELECT name, compatibility_level FROM sys.databases;

Run Query

Set Compatibility level

Create a new query.

    ALTER DATABASE databasename
    SET COMPATIBILITY_LEVEL = 120

Run Query

Compatibility Levels

Product Database Engine Version Default Compatibility Level Designation Supported Compatibility Level Values
SQL Server 2019 (15.x) 15 150 150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x) 14 140 140, 130, 120, 110, 100
Azure SQL Database single database/elastic pool 12 150 150, 140, 130, 120, 110, 100
Azure SQL Database managed instance 12 150 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.x) 9 90 90, 80
SQL Server 2000 (8.x) 8 80 80

comments powered by Disqus