How to alter a DATABASE compatibility level
In this article, we will learn how to alter a DATABASE compatibility level. Please see some related MSSQL guides I have written. What are the differences between various Editions of Microsoft SQL Server. How to install MSSQL Server 2019 Developer Edition and SSMS on Windows. Database Compatibility Level is a valuable tool to assist in database modernization. it does this 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. This includes Managed Instances) with no application changes (except for database connectivity).
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. See more on SA “how to reset MSSQL Server SA Password on Ubuntu via the command line and SQL Server Management Studio“. See how to download and install Microsoft SQL Server 2019 Express edition and SSMS.
Modifying Compatibility Levels in MSSQL Server
In addition, 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, I had to run the command below to modify my compatibility level. Nonetheless, Here i will be able to set an SA account.
ALTER DATABASE MBAM SET COMPATIBILITY_LEVEL = 140Never change the compatibility level while users are connected. It is preferable that you follow these steps to properly do the job.
Note: 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 database with backward compatibility. The database compatibility level setting ensures backward compatibility with earlier SQL Server versions. Affecting only the specified database’s Transact-SQL and query optimization behaviours, not the entire server.
You may want to learn more about the difference between compatibility 140 and 150. Please refer to the table below.
Understanding Database Compatibility Levels
| 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. |
View Microsoft SQl Server compatibility level
Furthermore, as we’ve chosen SQL Server 2017. Let me demonstrate how to easily check the compatibility level using SQL Server Management Studio (SSMS).
To view the compatibility level of each database. Right-click the database in SQL Server Management Studio, select Properties, and click the Options tab. See the image below for more information.
I hope you found this blog post on how to alter a DATABASE compatibility level helpful. Moreover, please let me know in the comment session if you have any questions.
