Find databases running on an older compatibility level

The SQL Server compatibility level is a SQL Server setting. This is the setting that controls some of the features of how the SQL Server database engine runs, for instance the cardinality estimator.

Some people have found problems with certain queries when updating to SQL Server 2016 for instance due to the changes in the cardinality estimator.

If you want to look for databases that have an older compatibility level than the current version of SQL Server, you can use the following query.

SELECT d.name, d.compatibility_level, @@VERSION, SERVERPROPERTY('ProductVersion') ,
'Database ' + d.name + ' running at older compatibility level of ' + cast(d.compatibility_level as VARCHAR(100)) as message
FROM sys.databases d
WHERE d.compatibility_level <> CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30)), 1, 4) AS FLOAT) * 10
AND d.name <> 'distribution';

Changing the compatibility level is certainly something that you would want to verify in a test environment before deploying to a production server. Running the latest compatibility level can have some great performance benefits, especially in SQL Server 2019.

Note from Microsoft documentation on the distribution database (used for Replication):  A distribution database that was created in an earlier version of SQL Server and is upgraded to SQL Server 2016 (13.x) RTM or Service Pack 1 has a compatibility level of 90, which is not supported for other databases. This does not have an impact on the functionality of replication. Upgrading to later service packs and versions of SQL Server will result in the compatibility level of the distribution database to be increased to match that of the master database.

See Also:

• SQL Server 2019 compatibility level.
• SQL Server 2019

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!