If you’ve been working with SQL Server for a while, you’ve probably come across the term “compatibility level” at some point. Maybe you’ve seen it in database properties or heard someone mention it during a migration discussion. But what exactly does it mean, and why should you care?
The Basics
Database compatibility level is essentially SQL Server’s way of letting you control which version’s features and behavior your database uses. Think of it as a dial that determines how “modern” your database acts, regardless of what version of SQL Server you’re actually running.
When you set a compatibility level, you’re telling SQL Server to make your database behave as if it’s running on that specific version. This means the query optimizer, T-SQL language features, and various engine behaviors will match that target version.
Why It Exists
The main reason compatibility levels exist is backward compatibility. Microsoft understands that upgrading SQL Server instances is a big deal for organizations. You might have applications that were written years ago, and suddenly changing how queries behave could break things in unexpected ways.
Let’s say you’re running SQL Server 2022, but you have a database that was originally built for SQL Server 2016. By keeping the compatibility level at 130 (SQL Server 2016), you ensure that your queries continue to work exactly as they did before, even though you’re on newer hardware with a newer SQL Server version.
It’s important to understand that compatibility level is applied at the database level – not at the server level. So you could have multiple databases on the same server, each with a different compatibility level.
Understanding the Numbers
Each SQL Server version has its own compatibility level number. Here are some examples:
- SQL Server 2022: 160
- SQL Server 2019: 150
- SQL Server 2017: 140
- SQL Server 2016: 130
- SQL Server 2014: 120
- SQL Server 2012: 110
The pattern is pretty straightforward once you see it. The good news is that SQL Server always supports a good number of previous compatibility levels, so you’re not forced to upgrade immediately. For example, SQL Server 2025 supports compatibility levels from 100 to 170 and everything in between.
Compatibility Levels by Product
Here’s a table that shows the compatibility levels supported by each edition of SQL Server and Azure SQL Database.
| Product | Database Engine version | Default compatibility level designation | Supported compatibility level values |
|---|---|---|---|
| Azure SQL Database | 17 | 170 | 170, 160, 150, 140, 130, 120, 110, 100 |
| Azure SQL Managed Instance (Always-up-to-date update policy) | 17 | 170 | 170, 160, 150, 140, 130, 120, 110, 100 |
| Azure SQL Managed Instance (SQL Server 2025 update policy) | 17 | 170 | 170, 160, 150, 140, 130, 120, 110, 100 |
| Azure SQL Managed Instance (SQL Server 2022 update policy) | 16 | 150 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2025 (17.x) Preview | 17 | 170 | 170, 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2022 (16.x) | 16 | 160 | 160, 150, 140, 130, 120, 110, 100 |
| SQL Server 2019 (15.x) | 15 | 150 | 150, 140, 130, 120, 110, 100 |
| SQL Server 2017 (14.x) | 14 | 140 | 140, 130, 120, 110, 100 |
| SQL Server 2016 (13.x) | 13 | 130 | 130, 120, 110, 100 |
| SQL Server 2014 (12.x) | 12 | 120 | 120, 110, 100 |
| SQL Server 2012 (11.x) | 11 | 110 | 110, 100, 90 |
| SQL Server 2008 R2 (10.50.x) | 10.5 | 100 | 100, 90, 80 |
| SQL Server 2008 (10.0.x) | 10 | 100 | 100, 90, 80 |
| SQL Server 2005 (9.x) | 9 | 90 | 90, 80 |
| SQL Server 2000 (8.x) | 8 | 80 | 80 |
Source: Microsoft
What Changes Between Levels
When you move up compatibility levels, you’re essentially unlocking new features and potentially changing existing behavior. The query optimizer might choose different execution plans, new T-SQL syntax becomes available, and certain deprecated features might stop working.
For example, moving from compatibility level 130 to 140 might give you access to new string functions, improved query performance through better cardinality estimation, and updated statistical algorithms. But it might also mean that some older, less-efficient query patterns that used to work will now behave differently.
The main thing to understand is that these changes can affect performance, both positively and negatively. Most of the time, newer compatibility levels bring performance improvements, but there’s always the chance that a specific query that worked well before might now run slower due to the optimizer making different choices.
Checking and Changing Compatibility Levels
Finding out what compatibility level your database is using is straightforward. You can check it through SQL Server Management Studio by right-clicking on your database, selecting Properties, and looking at the Options page. Alternatively, you can run a quick query:
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseName';Changing the compatibility level is equally simple, but you should definitely test thoroughly before doing it in production:
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 150;Best Practices and Considerations
The general recommendation is to eventually move your databases to the latest supported compatibility level, but don’t rush it. Plan for it, test extensively, and have a rollback strategy ready.
Start by testing in a development environment that mirrors your production setup as closely as possible. Run your typical workloads and watch for any performance changes or unexpected behavior. Pay special attention to complex queries, stored procedures, and any code that relies on specific SQL Server behaviors.
It’s also worth noting that some new SQL Server features are only available at certain compatibility levels. If you want to take advantage of the latest performance improvements or language features, you’ll need to upgrade your compatibility level eventually.
When Things Go Wrong
If you upgrade compatibility level and run into issues, don’t panic. You can always roll back to the previous level temporarily while you figure out what’s going on. This is one of the biggest advantages of the compatibility level system – it gives you an escape hatch.
However, rolling back should be a temporary solution. The goal should always be to identify what’s causing the problem and fix it so you can move forward with the newer compatibility level.
The Bottom Line
Compatibility level is a handy feature that gives you control over how your database behaves. It’s designed to make SQL Server upgrades less daunting by letting you move at your own pace. While it might seem like a minor setting, it can have significant impacts on performance and functionality.
The main thing is to approach compatibility level changes methodically. Test thoroughly, understand what you’re changing, and always have a plan for rolling back if needed. With the right approach, you can take advantage of newer SQL Server features while maintaining the stability your applications depend on.