How to alter a DATABASE compatibility level

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.

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. 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 ensures backward compatibility with earlier SQL Server versions, affecting only the specified database’s Transact-SQL and query optimization behaviors, 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.

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.