What is the SQL Server Compatibility Level and why is it important?

Each SQL Server database has a setting called the compatibility level. This setting controls which SQL language features and query optimizer behaviors are used when working with the database. It allows an older database to run on a newer SQL Server engine while maintaining predictable, backward compatibale behavior.

You can check the compatibility levels of your databases like this:

SELECT name, compatibility_level
FROM sys.databases

Here are some common values for SQL Server environments:

• Level 100: SQL Server 2008
• Level 110: SQL Server 2012
• Level 120: SQL Server 2014
• Level 130: SQL Server 2016
• Level 140: SQL Server 2017

You can change the compatibility level simply with:

ALTER DATABASE MyDb
SET COMPATIBILITY_LEVEL = 140

Why it matters:

• Newer compatibility levels unlock new optimizer features.
• Legacy applications may rely on older SQL behavior.
• Upgrades can be staged safely (engine first, compatibility later).

For migrations, it is a common practice to upgrade the SQL Server engine first, verify performance on the existing compatibility level, and then test raising the level to take advantage of newer features.