Query to Check Compatibility Level in SQL Server

Upgrading SQL Server to a newer version, such as SQL Server 2019 or 2022, requires verifying and updating the compatibility levels of your databases. Compatibility levels control database behaviors and enable certain SQL Server features, making them a crucial part of your upgrade process.

In this guide, we’ll explore how to check compatibility levels, update them, and automate the process for multiple databases. We’ll also cover the compatibility level for SQL Server 2022 (160).

What Is a Compatibility Level?

Compatibility levels in SQL Server help maintain backward compatibility with older versions while allowing you to use features of the newer SQL Server versions. Each SQL Server version has an associated compatibility level:

• 60: SQL Server 6.0
• 65: SQL Server 6.5
• 70: SQL Server 7.0
• 80: SQL Server 2000
• 90: SQL Server 2005
• 100: SQL Server 2008 and SQL Server 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

Checking Compatibility Levels

Option 1: Using SQL Server Management Studio (SSMS)

1. Open SSMS and connect to your SQL Server instance.
2. Right-click on the database and select Properties.
3. In the Database Properties dialog, go to the Options page.
4. Check the Compatibility level field to see the current level.

This method works well for a single database but becomes time-consuming for multiple databases.

Option 2: Using T-SQL to Query Compatibility Levels

For a faster approach, you can query compatibility levels for all databases using the following T-SQL script:

SELECT name AS DatabaseName,        compatibility_level AS CompatibilityLevelFROM sys.databasesORDER BY name;    

This script lists all databases on the server along with their compatibility levels, allowing you to quickly identify databases that may need updates.

Updating Compatibility Levels

To update a database’s compatibility level, use the ALTER DATABASE command. The syntax is:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 };    

For example, to set a database named MyDatabase to SQL Server 2022 compatibility level (160), run:

ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 160;    

Automating Updates for Multiple Databases

If you have many databases, manually updating their compatibility levels can be tedious. You can automate this process with a script that generates the required ALTER DATABASE statements for databases below a specific compatibility level.

SELECT [name] AS DatabaseName,       'ALTER DATABASE [' + [name] + '] SET COMPATIBILITY_LEVEL = 160;' AS SetCompatibilityLevelFROM sys.databasesWHERE compatibility_level < 160  AND database_id > 4; -- Exclude system databases    

This script generates commands to update all user databases with a compatibility level lower than 160 (SQL Server 2022). The output might look like this:

ALTER DATABASE [Database1] SET COMPATIBILITY_LEVEL = 160;ALTER DATABASE [Database2] SET COMPATIBILITY_LEVEL = 160;    

Simply copy and paste the output into a new query window in SSMS and execute it to update the databases.

Best Practices for Compatibility Level Updates

• Test Before Updating: Test applications and queries in a non-production environment to ensure they work with the new compatibility level.
• Back Up Databases: Always perform a full BACKUP before making changes to the compatibility level.
• Monitor Performance: After updating, monitor query performance to identify potential regressions.
• Review Feature Changes: Newer compatibility levels may introduce changes that affect behavior or deprecate older features.

Conclusion

Compatibility levels are a vital part of managing SQL Server upgrades and ensuring your databases operate effectively. Using the scripts and techniques outlined in this post, you can quickly check and update compatibility levels, saving time and reducing the risk of errors. Whether you’re upgrading to SQL Server 2019 (150) or SQL Server 2022 (160), these tools and best practices will help streamline the process.

If you need assistance managing your SQL Server environment, consider reaching out to Stedman Solutions. We offer expert support and managed services to help you with upgrades, Performance Tuning, and much more.

For additional tools to monitor and manage your databases, check out Database Health Monitor.