Checking Compatibility Level Of All Databases In SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is a server-based system that can be accessed by multiple users simultaneously. It offers many features, including data security, scalability, and high performance.

One important feature of SQL Server is its compatibility level. This refers to the ability of the database to run different versions of SQL Server. For example, if a database has a compatibility level of SQL Server 2012 (110), it can run on any version of SQL Server from 2012 onwards. However, it cannot run on an earlier version of SQL Server.

The compatibility level can be changed by using the ALTER DATABASE command. This can be useful if you want to upgrade to a new version of SQL Server but you’re not ready to make all your databases compatible with the new version. You can change the compatibility level back to the previous version at any time.

It’s important to check the compatibility level of all databases in SQL Server because it can impact performance and stability. Incompatible databases can cause errors and crashes. Therefore, it’s best to ensure that all databases are compatible with the current version of SQL Server before upgrading.

Checking the compatibility level of all databases in SQL Server

As organizations move to newer versions of SQL Server, they should check the compatibility level of all their databases. The compatibility level determines which version of SQL Server a database can be used with. To check the compatibility level of all databases on a server, run the following query:

Solution

Here is a simple TSQL to simplify the work:

Query

SELECT
    name,
    compatibility_level,
    SQL_version_name = 
        CASE compatibility_level
            -- When compatibility_level is 80, it corresponds to SQL Server 2000
            WHEN 80 THEN 'SQL Server 2000'
            -- When compatibility_level is 90, it corresponds to SQL Server 2005
            WHEN 90 THEN 'SQL Server 2005'
            -- When compatibility_level is 100, it corresponds to SQL Server 2008/R2
            WHEN 100 THEN 'SQL Server 2008/R2'
            -- When compatibility_level is 110, it corresponds to SQL Server 2012
            WHEN 110 THEN 'SQL Server 2012'
            -- When compatibility_level is 120, it corresponds to SQL Server 2014
            WHEN 120 THEN 'SQL Server 2014'
            -- When compatibility_level is 130, it corresponds to SQL Server 2016
            WHEN 130 THEN 'SQL Server 2016'
            -- When compatibility_level is 140, it corresponds to SQL Server 2017
            WHEN 140 THEN 'SQL Server 2017'
            -- When compatibility_level is 150, it corresponds to SQL Server 2019
            WHEN 150 THEN 'SQL Server 2019'
	    -- When compatibility_level is 150, it corresponds to SQL Server 2019
            WHEN 160 THEN 'SQL Server 2022'
            -- For any other compatibility_level value, display a message with the unknown version
            ELSE 'New version (compatibility level ' + CAST(compatibility_level AS VARCHAR(50)) + ')'
        END
FROM sys.databases

Result

Scenario

To alter the compatibility level of a database

Solution

Here is a simple TSQL to simplify the work:

Query

ALTER DATABASE [your db name]
SET COMPATIBILITY_LEVEL = 140 -- 140 Compatibility level is for SQL Server 2017