When a developer asks whether they can use newer SQL syntax, the answer lives in the database compatibility level — not the version string shown in Management Studio.
One morning, a developer on my team asked what SQL version our production database was running — they wanted to know if newer syntax would work in a query. The answer turned out to be more nuanced than expected, especially on Azure SQL Database where server version numbers don't map cleanly to on-premises SQL Server labels.
Note: Compatibility levels and Azure defaults have evolved since this was written. Verify current defaults in Microsoft documentation before changing production settings.
At a glance
- SSMS object explorer and
SELECT @@VERSIONcan show different version strings on Azure SQL — don't rely on either alone for syntax decisions. - Compatibility level determines which T-SQL features are available, not the physical server version label.
- Levels 100–140 mapped to SQL Server 2008 through 2017; newer levels exist today (150, 160, etc.).
- Always check compatibility level explicitly before adopting new syntax in production queries.
Two ways to check version — and why they disagree
If you use SQL Management Studio, you can see the SQL Server version in the object explorer:
In this example, the server version is 12.0.2000.8 — which could be interpreted as SQL Server 2014.
Reference: How to determine SQL Server version
But using SELECT @@VERSION can return a different result:
In this example, it appears to be SQL Server 2016. Physical SQL Server and Azure SQL Server version numbers aren't the same.
What really matters: compatibility level
What really matters is the compatibility level — that's what determines whether newer SQL syntax will work.
The alignment of SQL versions to default compatibility levels (at the time of writing) was as follows:
| Level | SQL Server / Azure SQL Database |
|---|---|
| 100 | SQL Server 2008 |
| 110 | SQL Server 2012 |
| 120 | SQL Server 2014 |
| 130 | SQL Server 2016 |
| 140 | SQL Server 2017 |
Reference: Default compatibility level 140 for Azure SQL Databases
How to check compatibility level
Run this against your database:
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
Before changing compatibility level in production, test in a non-production environment. Some query plans and deprecated features behave differently across levels.
Related on this site
Working through platform migrations or query modernization? Let's talk.