How to Setup Compatibility in Microsoft SQL Server?

SQL Server compatibility level is one of the database settings. It helps to keep the database compatible with SQL server versions. By default compatibility level of the database is lower than the SQL server version. It impacts many objects in the server. 

For Example: If we are creating a database from scratch on a particular version of SQL server and keeping the database in the same version then we do not have to worry about the compatibility level at all. But if we migrate our application from a lower to a higher version of the SQL server then we have to make sure the compatibility level matches. Otherwise, it may be possible that a few newer features are not supported by the older compatibility level of the database. So, in order to maintain it, we need the compatibility level that matches with the SQL server.

Let’s understand this concept with an example. First, we will create a database and set up its compatibility level with different SQL server versions. Now we will try to set up with the help of UI(without using SQL query) and after that, we will set it up with the help of query.

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.