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
| 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 |
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
