How to Setup Compatibility in Microsoft SQL Server?

In SQL Server, managing the compatibility level of a database is crucial for ensuring that your database operates with the appropriate features and behaviors for your application. Compatibility levels allow us to use features and syntax from specific versions of SQL Server while running your database on a different version.

In this article, We will learn about How to Setup Compatibility in Microsoft SQL Server along with their syntax and so on.

How to Setup Compatibility in Microsoft SQL Server?

When creating a new database in SQL Server and keeping it on the same version, we don't need to worry about compatibility levels. The default settings will be suitable for that version of SQL Server.

However, if we migrate our application from an older version of SQL Server to a newer one, we need to ensure that our database compatibility level matches the new version. This is important because some new features and functionalities in the latest SQL Server version might not be supported if our database compatibility level is still set to an older version.

To understand this, let’s look at an example. We'll first create a database and set its compatibility level to match different SQL Server versions. We'll explore two methods for setting the compatibility level using the SQL Server Management Studio (SSMS) user interface and using SQL queries.

Method 1: Using GUI

Step 1: Create a Database

In this step, we will create a database and name it geeksforgeeks. We need to use the CREATE operator.

Query:

CREATE DATABASE geeksforgeeks;

Step 2: Properties and Options Tab

In order to see the compatibility level of the databases, right-click on the database in Microsoft SQL Server Management Studio and select Properties, then click the Options tab. Go to root-database > right-click > properties > options. Below is the screenshot attached for reference.

Now, you have to click over the options tab in order to see the compatibility level of the particular database. Below is the screenshot for the same.

Step 3: Change the Compatibility Level

In this step we will change the compatibility level of the database, for this, you can use the drop-down attribute and choose a different compatibility level, and hit the OK button. This will update the compatibility level of your database. See the below image.

Step 4: Output

After saving all the changes when you again follow the above step and check the compatibility level of your database it will be updated. Here, we have updated the compatibility level to 140, below is the screenshot for the same.

Method 2: Using SQL Query

In this method, we will update the compatibility level of the database using a SQL query.

Step 1: Checking The Compatibility Level Of The Database

First of all, we need to execute a query to see the compatibility level of the databases. For this, we use the following query.

Query:

select name, compatibility_level
 from sys.databases;

Output:

This will give the compatibility level of the databases present in the system. Following output is generated.

Step 2: Changing The Compatibility Level Of The Database

We can also see that in Method 1, we have updated the compatibility level to 140. Now, we will change the compatibility level to 120. For this we will use the following query.

Query:

ALTER DATABASE [geeksforgeeks] 
SET COMPATIBILITY_LEVEL = 120;

Output:

Step 3: Check Updated Compatibility

In this step, we will check the updated compatibility level by using the following query.

SELECT compatibility_level FROM 
sys.databases WHERE name = 'geeksforgeeks';

Output:

On the execution of the query, we will get the following output with an updated compatibility level for our database geeksforgeeks to 120.

Conclusion

Adjusting the compatibility level of a database in SQL Server helps align your database's behavior with specific SQL Server versions. Whether you prefer using the GUI for a more visual approach or SQL queries for direct commands, both methods are effective for managing and verifying the compatibility level.