Compatibility Level controls selected query behavior within a database. I baseline the workload before changing it.
SELECT name, compatibility_level
FROM sys.databases
WHERE name = N'WideWorldImporters';
-- Example for SQL Server 2022, after workload testing:
-- ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;| Engine release | Native compatibility level |
|---|---|
| SQL Server 2008 / 2008 R2 | 100 |
| SQL Server 2012 | 110 |
| SQL Server 2014 | 120 |
| SQL Server 2016 | 130 |
| SQL Server 2017 | 140 |
| SQL Server 2019 | 150 |
| SQL Server 2022 | 160 |
| SQL Server 2025 | 170 |
Choose a level supported by the hosting engine. Historical levels 80 and 90 aren’t valid targets on every current release. Compatibility level doesn’t permit restoring a newer database to an older engine.
Changing the level can affect syntax, optimizer behavior and plans. I capture a baseline and test a restored copy. Query Store helps compare the affected workload. Raising the level without testing can introduce regressions.
This change doesn’t require a SQL Server restart. SINGLE_USER isn’t a universal prerequisite and can disconnect applications. Reverting to a supported earlier level can help investigate regressions. It doesn’t undo every other database change.
Reference: Supported compatibility levels and behavior.
Related reading
Compatibility level is not the database file version, it is a setting that affects selected query behavior.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
3 Comments. Leave new
I have a question，how can I migrate datebase from new version to preview version(example from 2012 to 2008, and I don’t use extra function only on 2012),if can’t, this article about compatibility can be used where?
Even a production environment where there are few users accessing via application, a database, need to change to ALTER DATABASE SET SINGLE_USER?
Environment with 3 people using.
Thank you
Those steps do not work for SQL Server 2019 database that is part of an availability group. It says that the operation is not allowed. What will be the steps to complete this change for a database that is part of AO/HA? Remove if from the AO? I would like to know if there is a way to avoid removing it from the AO group.