SQL Server Compatibility Levels: What They Control and When to Change Them
A SQL Server database compatibility level controls certain Transact-SQL and query optimiser behaviours for that database.
It does not downgrade the SQL Server instance. A database at compatibility level 130 on SQL Server 2022 is not running on SQL Server 2016.
Raising the level can make newer query-processing features available. It can also produce different execution plans. Some queries may improve. Others may regress. Fun times!
That is why the correct advice can’t simply be “set every database to the latest level” or “it depends”.
An older compatibility level might be:
- a deliberate part of an upgrade plan;
- an application-vendor requirement;
- protection against a known performance regression;
- or evidence that nobody has reviewed the database for years.
The setting takes seconds to change. Establishing whether it is safe takes rather longer.
What is a SQL Server compatibility level?
Compatibility level is a database-level setting. Databases on the same SQL Server instance can use different values.
It allows organisations to separate two potentially risky activities:
- upgrading the SQL Server Database Engine;
- adopting the query-processing behaviour associated with the newer release.
This reduces the number of changes introduced during the initial upgrade. The team can move the database to the newer SQL Server instance, establish a performance baseline and then raise the compatibility level as a separate controlled change.
That is useful when it forms part of a documented plan. It is less impressive when the upgrade happened four years ago and nobody can find the plan. We’ll pretend there was one.
What does compatibility level control?
Compatibility level can affect several important areas.
Query optimiser behaviour
The query optimiser decides how SQL Server should execute a query. It considers alternatives such as index selection, join order, parallelism, memory requirements, and available optimisation techniques.
Changing compatibility level can alter those decisions. A query that previously used one execution plan may compile to a different plan afterwards.
The newer plan may be better. It may also be considerably worse for that workload.
Cardinality estimation
Cardinality estimation is SQL Server’s attempt to predict how many rows each stage of a query will process.
Those estimates influence join selection, memory grants, parallelism, and other plan decisions. Changes to cardinality-estimation behaviour are one reason that compatibility-level changes can affect performance even when the application code has not changed.
Intelligent Query Processing
Newer compatibility levels make additional query-processing features available.
| Level | SQL Server release | Example functionality |
|---|---|---|
| 140 | SQL Server 2017 | Adaptive joins and batch-mode memory grant feedback |
| 150 | SQL Server 2019 | Scalar UDF inlining, table-variable deferred compilation and batch mode on rowstore |
| 160 | SQL Server 2022 | Parameter Sensitive Plan optimisation and cardinality-estimation feedback |
| 170 | SQL Server 2025 | Optional Parameter Plan Optimisation |
These features can solve genuine performance problems. A database left at an old level may be unable to use them.
That does not mean every feature will benefit every workload. Testing still applies.
What compatibility level does not do
Compatibility level is often described as making a database “behave like an older SQL Server version”. That is convenient shorthand, but it is incomplete.
| Compatibility level can influence | Compatibility level does not |
| Query optimiser behaviour | Downgrade the SQL Server instance |
| Cardinality-estimation behaviour | Recreate the complete behaviour of an older engine |
| Some query-processing features | Restore features removed from SQL Server |
| Certain Transact-SQL behaviours | Reverse a database file-format upgrade |
| Plans generated after the change | Guarantee application support |
The database still uses the installed Database Engine, storage engine, servicing model and server-level configuration.
Compatibility level only protects the behaviours Microsoft has explicitly placed behind that setting. An application can still fail after an upgrade if it depends on functionality removed from the Database Engine.
SQL Server compatibility-level table
The current on-premises SQL Server releases use these default levels for newly created databases:
| SQL Server version | Default compatibility level |
| SQL Server 2016 | 130 |
| SQL Server 2017 | 140 |
| SQL Server 2019 | 150 |
| SQL Server 2022 | 160 |
| SQL Server 2025 | 170 |
A database restored or upgraded from an older version normally retains its existing compatibility level, provided that level remains supported.
This is on purpose. Microsoft does not automatically introduce all newer query-processor behaviour into an existing user database during the engine upgrade. Except when they do, Microsoft always tries to limit breaking changes. Automatically changing the compatibility level would be a breaking change due to potential query regression.
It also explains why old levels can remain unnoticed for years.
How to check database compatibility levels
The following query returns the compatibility level of every database:
SELECT
d.name AS DatabaseName,
d.compatibility_level AS CompatibilityLevel,
d.state_desc AS DatabaseState
FROM sys.databases AS d
ORDER BY d.name;Finding a database below the default level for the installed SQL Server version is not proof of a fault.
It is evidence that requires an explanation.
A useful review should establish:
- why the level was retained;
- whether the application vendor supports a newer level;
- whether representative testing has been completed;
- whether Query Store contains a usable baseline;
- and who accepted the risk of remaining at the older level.
“Probably left over from the last upgrade” is not change control.
Why is the database using an old level?
The SQL Server instance was upgraded
An engine upgrade does not normally raise every existing user database to the newest compatibility level.
This allows the platform upgrade to be completed first and the compatibility change to be tested separately.
The application vendor requires it
Some commercial applications are certified only against particular SQL Server versions or compatibility levels.
Changing the setting without checking the vendor’s support position could place the application outside its supported configuration. Retaining the old level may be correct, but the dependency should be documented and reviewed.
It’s also possible the vendor has a create database script with a set compatibility level instead of using the default value even if they support the latest features. This is something we see quite a lot; new releases occur but vendors do not update their installers accordingly.
A previous change caused a regression
A database may have been returned to its old level after an important query became slower.
That can be a sensible emergency response. It should not be the end of the investigation.
The problem may be limited to a small number of queries that can be tuned or controlled through Query Store. Keeping the entire database at an old level indefinitely may discard useful improvements because one regression was never analysed properly.
Nobody knew it was there
Inherited SQL Server estates often contain databases moved through several versions without anyone reviewing compatibility levels or upgrade readiness.
In that case, the old level represents technical debt. It does not grant permission to change it immediately, but it does justify investigation.
Can changing compatibility level affect performance?
Yes. Changing compatibility level can cause queries to compile using different optimiser rules and features. Cached plans for the database can also be invalidated, requiring queries to compile again.
Possible outcomes include:
- improved execution plans;
- reduced CPU or logical reads;
- better memory-grant behaviour;
- slower critical queries;
- different join strategies;
- increased resource consumption;
- and changes to workload concurrency.
The average workload becoming slightly faster is little comfort if month-end processing takes three times as long.
Testing must include the business processes that matter, not merely a few convenient queries and a successful application login.
Should you change to the latest level?
The long-term objective should normally be to certify databases at the latest appropriate compatibility level supported by the SQL Server instance and application.
Use the following decision framework:
| Situation | Recommended response |
| The lower level is a documented vendor requirement | Retain it and review the vendor’s upgrade roadmap |
| It is part of a controlled SQL Server upgrade | Capture a baseline, test and raise it separately |
| A previous change caused a regression | Identify the affected queries and assess targeted mitigation |
| Nobody knows why the database uses the old level | Treat it as technical debt requiring investigation |
| The application is tested and Query Store is ready | Plan a controlled compatibility-level change |
| There is no baseline or rollback plan | Do not make the change yet |
Blindly raising every database to the highest available value is not a best practice.
How to change compatibility level safely
The command itself is straightforward:
ALTER DATABASE [YourDatabase]
SET COMPATIBILITY_LEVEL = 160;Replace the database name and target level with values appropriate to the environment.
The safe process is more important than the syntax. These steps should be performed once on a non production environment prior to a production environment.
1. Confirm the current position
Record the SQL Server version, patch level, current compatibility level, target level and any vendor or availability dependencies.
2. Check application support
Confirm that the application and its vendor support the target SQL Server and compatibility level. Obtain a written answer where the documentation is unclear.
3. Prepare Query Store
Query Store should contain a representative performance baseline before the change.
Confirm that it is enabled, collecting useful data and retaining enough history for before-and-after comparison. An empty, read-only or badly configured Query Store is not a useful baseline.
4. Test representative workloads
Testing should include normal activity, scheduled jobs, reporting, imports, overnight processing and known performance-sensitive queries.
5. Make one controlled change
Change the level during an agreed window. Avoid combining it with an application release, index maintenance or unrelated infrastructure work. Otherwise, finding the cause of a regression becomes needlessly difficult.
6. Monitor the result
Review query duration, CPU, logical reads, waits, execution-plan changes, memory grants, blocking, job failures and application errors.
Do not rely solely on the absence of support tickets.
7. Address individual regressions
Where most of the workload improves but a few queries regress, investigate those queries directly.
Potential responses include indexing changes, code corrections, statistics maintenance, Query Store plan forcing or an appropriate database-scoped configuration.
Returning the entire database to the old level may be necessary during an incident. It should not automatically become the permanent answer.
Common compatibility-level mistakes
The usual mistakes are predictable:
- Assuming old automatically means wrong.
- Assuming latest automatically means faster.
- Raising every database with a generated script.
- Testing only whether the application starts.
- Ignoring vendor support requirements.
- Changing the level without Query Store history.
- Combining the change with several other releases.
- Failing to monitor critical business processes.
- Leaving a temporary lower level in place indefinitely.
- Treating compatibility level as a substitute for upgrading an unsupported SQL Server version.
The correct setting is not determined by the highest number the server accepts, but by supportability, testing, and evidence.
Compatibility level belongs in a wider SQL Server review
A low compatibility level is rarely the only issue in an SQL Server estate.
It often appears alongside unsupported SQL Server versions, missing patches, inconsistent database settings, poorly configured Query Store, undocumented application dependencies, and no credible upgrade plan.
Reviewing compatibility level in isolation identifies a setting. It does not establish whether the database platform is healthy.
That requires a broader assessment of configuration, resilience, security, performance and operational risk.
Arrange a DatAIbase SQL Server Health Check
Changing a compatibility level takes seconds. Knowing whether it is safe requires considerably more evidence.
A DatAIbase SQL Server Health Check reviews compatibility levels alongside the wider SQL Server environment, including configuration, Query Store, performance risks, resilience, and upgrade readiness.
You receive a prioritised assessment showing:
- what requires attention;
- what can reasonably remain as it is;
- which changes are low risk;
- and which changes require testing or specialist investigation.
Do not wait for an upgrade or performance incident to reveal that nobody understood the current configuration.
Arrange a SQL Server Health Check