How to Use SQL Server Compatibility Level

How to Use SQL Server Compatibility Level

Using this guide, you will understand how to change the compatibility level of a SQL Server database using the SSMS and T-SQL Queries.

What is SQL Server Compatibility Level?

SQL Server compatibility level is a database feature that allows you to ensure a database is compatible with various SQL Server versions. By default, a database adopts the compatibility level of the SQL Server it was created. This remains the case even for database backups.

For example, if a database was created and backed up on SQL Server 2017, even if you restore it on SQL Server 2019, the compatibility level remains that of SQL Server 2017.

Database compatibility level is one feature that determines how a database functions based on the features available in the set and earlier versions. Using this guide, you will learn how to modify the compatibility level of a database.

NOTE: Before performing any changes on the compatibility level, it is prudent to familiarize yourself with the effects that come with it.

Consider the docs to learn more.

Set Compatibility Level in SSMS

Let us now discuss how to set and change compatibility levels of a database in SQL Server. The first method we will use is a graphical method using SSMS.

Start by creating a test database as:

create database compatibility_level_db;

Once the database is created, we can change or view the compatibility level. In SSMS, select your target database and right-click. Select the Properties option.

Select the Options tab from the left menu in the Database Properties Window. You should see the current compatibility level of the database as:

To change the compatibility level of the database, select your level from the drop-down menu. Click OK to save the changes.

Set Compatibility in T-SQL

You can also use a Transaction SQL Query to view and change the compatibility level of a database.

The query below shows you how to view the compatibility level of a database.

select physical_database_name, compatibility_level from sys.databases;

The query above should return all the databases in the server and their corresponding compatibility levels.

An example output is as shown:

The following table shows the SQL Server versions and their corresponding default compatibility levels.

To change the compatibility level of a database using T-SQL query:

alter database compatibility_level_db set compatibility_level = 150;

The example query will update the specified database to the compatibility level of 150.

POINT: As mentioned, be mindful of the database’s compatibility level as doing so affects the features a database supports. Some features may be removed, and others will be added.

Final Thoughts

This guide covers the basics of viewing and modifying the compatibility levels of databases in SQL Server.

Leave a Reply

Your email address will not be published. Required fields are marked *