SQL Server Database Compatibility Levels

Why You Shouldn’t Overlook This Quiet but Critical SQL Server Setting

 If you’ve ever upgraded a SQL Server instance and something just broke in your application, chances are good you’ve run into database compatibility Level issues.

This quiet little setting determines how the SQL engine behaves, and it doesn’t always match the version of SQL Server you're running.

Let’s unpack why this matters and how to keep it from biting you in production.

What Is a Compatibility Level, anyway?

Every SQL Server database has a compatibility level that controls how certain features and behaviors operate, especially around T-SQL syntax, optimizer decisions, and deprecated functionality.

It’s Microsoft’s way of helping your database survive version upgrades without immediately breaking your app.

Levels:

• 100 = SQL Server 2008

• 110 = 2012

• 120 = 2014

• 130 = 2016

• 140 = 2017

• 150 = 2019

• 160 = 2022

• 170 = 2025 (presumably, still in CTP as of this writing)

Running SQL Server 2022 with a database at level 110? That means it’s still behaving like SQL 2012 in many ways.

Why This Can Cause Real Problems

Let’s say you upgrade your SQL Server from 2014 to 2019 and expect performance improvements, but instead things slow down, or worse, some queries fail entirely.

Possible reasons:

• Because your database might still be running in compatibility level 120, and:

• You're missing optimizer enhancements introduced in later versions

• Some new T-SQL features won’t work

• You might even see unexpected errors or deprecated behaviors still being supported

On the flip side:

If you change the compatibility level too soon, you can break app functionality that relied on older behaviors.

Best Practices for Compatibility Levels

Check the current level before and after any upgrade:

SELECT name, compatibility_level FROM sys.databases;

Test thoroughly before changing it, ideally in a lower environment with production-like workloads.

Upgrade the compatibility level manually (it doesn’t change automatically with SQL Server version upgrades):

ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 150;

Monitor performance after changing it. You may need to update stats or review execution plans. 

The Bottom Line:

Database compatibility level is easy to forget until it causes downtime or mysterious issues. Even then its rarely the first thing investigated (Query code and Indexes are usually first). Make it part of your upgrade checklist, not an afterthought.

New Pocket DBA® clients get the first month FREE!

https://pocket-dba.com/

Book a call, and mention “Newsletter”

SQL tidBITs:

If you change compatibility level, Query Store can help you analyze before/after performance. Enable QS pre-upgrade, then compare plan regressions post-change. In your test environment.

Please share with your friends that are still on SQL 6.5 compatibility