A Manual Adjustment: Compatibility Level Settings in SQL Server

A database on a specific SQL Server version doesn’t automatically have all features enabled. For example, a database on SQL Server 2019 can still use SQL Server 2014 compatibility, missing some 2019 features.

Each database has a Compatibility Level setting that activates certain features of its version. When SQL Server 2014 introduced the Cardinality Estimator, you could disable it by setting the Compatibility Level to SQL Server 2012. For a full list of enabled features at each level, see the Differences Between Compatibility Level sections of the MSDN Docs.

Unfortunately, most people don’t realize that updating the compatibility level setting is a manual process. SQL Server won’t automatically match the database compatibility level to the server version. This also applies when upgrading an existing SQL Server. It’s actually very common to see databases on newer servers with older compatibility levels.

The one exception to this rule

When migrating a database to a new server, SQL Server automatically upgrades the database’s compatibility level to the lowest version supported by the new server. For example, a database with SQL Server 2005 compatibility will be upgraded to 2008 when restored on SQL Server 2019, as 2008 is the earliest version supported by SQL Server 2019.

How can I find my database’s compatibility level?

Find the compatibility level for you database by querying the sys.databases DMV.

-- Find Compatibility Level for all databases on the server
SELECT name, compatibility_level  
FROM sys.databases; 
 

You can also view the compatibility level under Options in the Database Properties:

How do I change my database’s compatibility level?

To change your database compatibility level, simply change the Property above. You can also run the following ALTER DATABASE command:

--change compatibility level to 2019 for your database
ALTER DATABASE [YourDatabase]
SET COMPATIBILITY_LEVEL = 150;  
GO

Notice that the compatibility level setting in the ALTER DATABASE command is actually a numerical value. As seen in the database properties screenshot above, each compatibility level has a numerical value assigned to it, and that is what we use in the database settings. Be sure to check this list of Supported Compatibility Level Values for the version of SQL Server you are running.

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.