How to Set Compatibility Level SQL Server? Explaining The Best Ways

When a database administrator plans to upgrade their SQL Server databases, their major concern remains compatibility between the versions. This is why it becomes important for them to set compatibility level SQL Server, to ensure a smoother and precise upgrade. But what are these compatibility levels? Let’s take a closer look at the concept and learn the efficient ways to change the compatibility levels in SQL Server databases. 

What Is A Compatibility Level SQL Server? Overview

The compatibility level in SQL Server specifies how the database will behave in terms of the features, query execution, and other rules. The actual purpose of these compatibility levels is to specify how the database behaves with other versions and how it understands the SQL commands. This is the basic explanation of what the SQL Server compatibility level is. Let’s now move to why these compatibility levels are used. 

Why Set Compatibility Level SQL Server?

• To Ensure Backward Compatibility SQL Server:
  With the release of new SQL versions, new features and functions are introduced. These features might behave differently than the other versions and can cause issues for the organizations that require working with the scripts of the older versions. Setting compatibility levels can help users to make the newer SQL version use the older version’s behaviour in the newer version easily. 
• For Smoother Upgrades of SQL Server Database:
  When a database administrator plans to upgrade the database, changing the older features and functionalities suddenly can lead to bigger challenges and issues. This is why it becomes crucial for the database administrators to upgrade the version first and continue working with the older SQL features so that the sudden change might not affect the database altogether. 
• For Safely Testing the New Database Features:
  With the latest compatibility version, once a new version is available, rather than directly upgrading the old SQL Server versions, users can first try these features in a non-production environment. This ensures a safer trial of the features and their behaviour and allows the users to work on their SQL databases more securely. 

Now, as we know the reasons why compatibility levels are used, let’s proceed with learning how these compatibility levels work.

How Does Compatibility Level SQL Server Work?

When a database administrator set compatibility level SQL Server, it adjusts and changes the features and their functionalities for various aspects. These changes allow the users to use the newer SQL Server instance effectively, preventing the issues that might be triggered due to new database features. Here are some of the changes made by the compatibility levels to SQL Server:

• Adjusts the Behaviour of the Query Optimizer
  The query optimizer in SQL Server is responsible for figuring out the most effective way a query should be executed. In the newer versions of SQL Server, they have more effective and advanced query optimizers. If the compatibility level of a SQL Server is high, the database uses new features and the latest cardinality to execute the queries. On the other hand, with lower compatibility levels in the SQL Server, the database follows the older database behaviour, ignoring the newer features.

• Determines New Features and T-SQL Syntaxes
  The compatibility level in SQL Server is also responsible for determining the availability of the T-SQL syntaxes and features. With the availability of new SQL Server versions, newer features, syntaxes, and data types are available. These features only work when the database compatibility is set to higher values. Lower compatibility levels ensure that the database behaviour remains the same as the older version while executing T-SQL commands.

• Adjusts the Behaviour of the Built-in Functions in SQL Server
  In the SQL Server database, even the built-in functions might work differently depending on the compatibility levels of the database. One reason for this is, when Microsoft launches a new version, it comes with improved and enhanced features. These changes might be affected by older versions or lower compatibility levels.

Let’s now take a look at some SQL Server versions and the supported compatibility levels. 

List of SQL Server Versions and Supported Compatibility Levels

1. SQL Server 2022 – 160
2. SQL Server 2019 – 150
3. SQL Server 2017 – 140
4. SQL Server 2016 – 130
5. SQL Server 2014 – 120
6. SQL Server 2012 – 110
7. SQL Server 2008 R2 – 100
8. SQL Server 2005 – 90

Here are the SQL Server versions and their supported compatibility levels. However, the compatibility levels below 100 are not supported in the newer versions of SQL Server Database. 

So far, we have learned what compatibility level is and how it impacts the behavior of the SQL Server database. We will now take a look at the steps to check and compatibility level SQL Server. 

How to Check and Change SQL Server Compatibility Levels? Best Ways

We will now take a look at the methods and ways that will allow the database administrators to check and change the compatibility levels of the SQL Server database as per their requirements. Beginning with the first method, we will now understand the working and implementation of this method. 

Method 1: Set Compatibility Level SQL Server Using SSMS

In this method, we will understand how users can effectively check and change the compatibility levels in SQL Server database with the help of SQL Server Management Studio. Here are the steps to check the compatibility level of a SQL database:

1. Open SSMS and connect to SQL Server instance.
2. Go to Object Explorer, and right-click on the desired SQL database to check its compatibility level.
3. Next, go to properties and move to the Options Page.
4. There, the users will find a dropdown with the compatibility levels of the database. 

Now to change the compatibility level of the desired database, the steps given below will help the users. 

1. Open SSMS and connect to the SQL Server instance.
2. Go to Object Explorer, and right-click on the desired SQL database to check its compatibility level.
3. Next, go to properties and move to the Options Page.
4. From the dropdown displayed on the screen, select the desired compatibility level of the database. 
5. Lastly, click on the OK button to save the changes. 

Moving on to the next method, let’s now take a look at what the method is and how it can be implemented seamlessly. 

Method 2: Change Compatibility Level Using T-SQL Commands

For users who are technically aware and know the SQL commands and scripts well, they can effectively use this method to set a new compatibility level of the SQL Server database. 

To check the database compatibility level, the following we will use the following command:

SELECT name, compatibility_level 
FROM sys.databases;

This command helps you get the name and the compatibility levels of all the SQL Databases present in the specified SQL Server instance. In case, you are looking for a way to find the compatibility level of a single database, you can use the given command:

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

This command will help you get details regarding the desired database name. Now, to change the compatibility level, we will take a look at the T-SQL command:

ALTER DATABASE Database_Name 
SET COMPATIBILITY_LEVEL = 140; (set the desired compatibility level)

With the help of this command, users can change the compatibility level to the required level. This is how the T-SQL command can help users with changing the database compatibility level. Moving on with the next method, that is, using PowerShell for changing compatibility levels. 

Method 3: Change SQL Server Compatibility Level Using PowerShell

With this method we will learn how we can check and change the compatibility level of the SQL Server database using PowerShell. 

Here is the command that will allow the users to check the database compatibility level in PowerShell:

$database = Get-SqlDatabase -ServerInstance "SpecifiedServer_Name" -Name "SpecifiedDatabase_Name"

With the help of this command users can check the database compatibility level of the desired SQL Database. Now, to change the compatibility level, we will use the following command:

$database = Get-SqlDatabase -ServerInstance "SpecifiedServer_Name" -Name "SpecifiedDatabase_Name"
$database.CompatibilityLevel = 160 
$database.Alter()

This command will allow you to change the database compatibility level in a hassle-free way. By following these methods and implementing the steps precisely, users can check and set compatibility level SQL Server in a much secure way. 

Best Practices to Follow While Changing Compatibility Level In SQL Server

With the help of these practices, the users can ensure that the entire process of changing the compatibility level in SQL Server goes smoothly. Here are some best measures to take during the process:

1. Understand what compatibility level in SQL Server is and how it can impact the database performance. 
2. Before beginning the set compatibility level SQL Server process, make sure to take a backup of the entire database. This will help to avoid any issues during the process, like database corruption in SQL Server. 
3. Before directly implementing the changes to the production environment, test the changes in a staging or a non-production environment.
4. Document the entire process for future purposes.
5. Avoid changing the compatibility levels during peak production hours. 

Conclusion

With this write-up, we have learned about the compatibility level SQL Server. Then, we also discussed how they impact the database’s performance. Furthermore, we have discussed the reasons to change the compatibility levels in SQL Server. With this guide, we have suggested the methods that will help users to make these changes. Lastly, we have also discussed the best practices to avoid any issues in the database during the compatibility level changes.