TAJUZZAMAN

Experienced Senior Software Engineer with a demonstrated history of working in the information technology and services industry. Skilled in .Net Software Development, MSSQL, Oracle Database, C#, PHP, Google Cloud Platform and Microsoft Azure. Strong engineering professional with a Bachelor’s Degree focused in Artificial Intelligence from Universiti Teknologi MARA Malaysia. Currently work as Lead Solution Architect and Director of new startup IT company named Aurora Cloud Works Sdn. Bhd.

MSSQL Check Compatibility Level

To check compatibility level for your mssql database, you can execute this statement :

SELECT compatibility_level , *
FROM sys.databases
WHERE name = 'YourDatabaseName'

The number return from the compatibility_level is the version of SQL Server with which the database is to be made compatible. In some cases you can change the value of that field by execute the Alter command statement.

ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 130

For example, i want to call the API and process the JSON data from my stored procedure. Unfortunately my legacy database is made for MSSQL 2012. Which is MSSQL 2012 compatibility level = 110. To execute JSON is need minimum compatibility level for MSSQL 2016. Since now i already use MSSQL 2017, there is no problem for me to change the compatibility_level value. I will tell more about this JSON from MSSQL on my next post.

Below is the Compatibility level table for every MSSQL

ProductDatabase Engine VersionDefault Compatibility Level DesignationSupported Compatibility Level Values
SQL Server 2019 (15.x)15150150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x)14140140, 130, 120, 110, 100
Azure SQL Database single database/elastic pool12150150, 140, 130, 120, 110, 100
Azure SQL Database managed instance12150150, 140, 130, 120, 110, 100
SQL Server 2016 (13.x)13130130, 120, 110, 100
SQL Server 2014 (12.x)12120120, 110, 100
SQL Server 2012 (11.x)11110110, 100, 90
SQL Server 2008 R210.5100100, 90, 80
SQL Server 200810100100, 90, 80
SQL Server 2005 (9.x)99090, 80
SQL Server 2000 (8.x)88080

Source :

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15