How to Change the Compatibility Level of a Database in SQL Server

Tutorialsrack 04/11/2023 SQL SQL Server

In this article, you’ll learn how to change the compatibility level of a database in SQL Server.

In SQL Server, to change the compatibility level of a database in SQL Server, you can use the ALTER DATABASE statement. The compatibility level determines which version of SQL Server the database should emulate for query optimization and certain behaviors. Here's how you can change the compatibility level:

Read Here: How to Check a Database’s Compatibility Level in SQL Server

USE YourDatabaseName; -- Replace with your database name

-- Change the compatibility level to a specific version
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 140; -- Replace with the desired compatibility level (e.g., 100, 110, 120, 130, 140, 150, etc.)

Here are some common compatibility level values and the SQL Server versions they correspond to:

• 100: SQL Server 2008 and 2008 R2
• 110: SQL Server 2012
• 120: SQL Server 2014
• 130: SQL Server 2016
• 140: SQL Server 2017
• 150: SQL Server 2019
• 160: SQL Server 2022

I hope this article will help you to understand how to change the compatibility level of a database in SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts