SA0093 : The compatibility level of the database is lower than the SQL Server version default compatibility level

Introduction

Using database compatibility level lower than the SQL Server instance’s compatibility level can limit access to newer features, affect query performance, and introduce inconsistencies in behavior.

Description

In SQL Server, each database has a compatibility level setting that determines the version of SQL Server it is designed to be compatible with. When a database runs with a compatibility level lower than that of the hosting SQL Server instance, it can lead to various issues.

For example:

-- Example of a potential issue
ALTER DATABASE CurrentDb SET COMPATIBILITY_LEVEL = 110;

Running a database at a compatibility level of 110 in a SQL Server 2019 instance (compatibility level 150) may prevent you from using newer T-SQL features, and could potentially introduce unpredictable behavior.

Compatibility issues may restrict access to new SQL Server features and optimizations that improve performance and security.
Running in a lower compatibility level might cause performance degradation due to the lack of newer query optimizations.

How to fix

Ensure the database compatibility level matches the SQL Server instance’s compatibility level to leverage new features and optimizations.

Follow these steps to address the issue:

1.Check the current compatibility level of your database using the sys.databases catalog view:

2.If the compatibility level is lower than your SQL Server instance, consider upgrading it. Use the ALTER DATABASE statement:

3.Validate the application and queries to ensure they are compatible with the new features of the SQL Server instance.

For example:

-- Check the current compatibility level
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseName';

-- Upgrade the compatibility level to SQL Server 2019 (Level 150)
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 150;

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

ALTER DATABASE Compatibility Level

Analysis Rules