Database Compatibility Level is a valuable tool to assist in database modernization, by allowing the SQL Server Database Engine to be upgraded, while keeping connecting applications functional status by maintaining the same pre-upgrade Database Compatibility Level. This means that it is possible to upgrade from an older version of SQL Server (such as SQL Server 2008) to SQL Server or Azure SQL Database (including Managed Instance) with no application changes (except for database connectivity). Please see some related MSSQL guides I have written. What are the differences between various Editions of Microsoft SQL Server, and how to install MSSQL Server 2019 Developer Edition and SSMS on Windows, and how to download and install Microsoft SQL Server 2019 Express edition and SSMS.
A compatibility level is associated with each database. It allows the behaviour of the database to be compatible with the specific version of SQL Server it is running on.
- I had the need to alter MSSQL Server 2019 compatibility level from 150 to 140 to support MBAM deployment, before switching to a different SQL version 2017 in my test environment. Here is a command and options to set any of the compatibility levels, where the database_name Is the name of the database to be modified “ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }“. Therefore, to modify my compatibility level, I had to run the command below. . Here i will be able to set an SA account. See more on SA “how to reset MSSQL Server SA Password on Ubuntu via the command line and SQL Server Management Studio“.
ALTER DATABASE MBAM SET COMPATIBILITY_LEVEL = 140
Never change the compatibility level while users are connected. It is preferable that you follow these steps to properly do the job:. Remember to commit your changes to the database. The following compatibility level values can be configured (not all versions support all of the above-listed compatibility level.
| 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 | 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 |
Note: You can use a Db with backward compatibility. The database compatibility level setting provides backward compatibility with earlier versions of SQL Server in what relates to Transact-SQL and query optimization behaviors only for the specified database, not for the entire server. You may want to learn more about the difference between compatibility 140 and 150. Please refer to the table below.
| Compatibility level setting of 140 or lower | Compatibility level setting of 150 |
|---|---|
| Relational data warehouse and analytic workloads may not be able to leverage columnstore indexes due to OLTP-overhead, lack of vendor support or other limitations. Without columnstore indexes, these workloads cannot benefit from batch execution mode. | The batch execution mode is now available for analytic workloads without requiring column store indexes. For more information, see batch mode on rowstore. |
| Row-mode queries that request insufficient memory grant sizes that result in spills to disk may continue to have issues on consecutive executions. | Row-mode queries that request insufficient memory grant sizes that result in spills to disk may have improved performance on consecutive executions. For more information, see row mode memory grant feedback. |
| Row-mode queries that request an excessive memory grant size that results in concurrency issues may continue to have issues on consecutive executions. | Row-mode queries that request an excessive memory grant size that results in concurrency issues may have improved concurrency on consecutive executions. For more information, see row mode memory grant feedback. |
| Queries referencing T-SQL scalar UDFs will use iterative invocation, lack costing, and force serial execution. | T-SQL scalar UDFs are transformed into equivalent relational expressions that are “inlined” into the calling query, often resulting in significant performance gains. For more information, see T-SQL scalar UDF inlining. |
| Table variables use a fixed guess for the cardinality estimate. If the actual number of rows is much higher than the guessed value, performance of downstream operations can suffer. | New plans will use the actual cardinality of the table variable encountered on the first compilation instead of a fixed guess. For more information, see table variable deferred compilation. |
Since we have decided to use SQL Server 2017, I would like to show you how you can quickly check the compatibility level via the SQL Server Management Studio (SSMS). To see the compatibility level of each database, right-click the database in SQL Server Management Studio and select Properties, then click the Options tab. See the image below for more information.
I hope you found this blog post helpful. If you have any questions, please let me know in the comment session.