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
- SQL Server Management Studio (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 SSMS, 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 — don't trust either label alone when deciding which syntax is safe to use.
What really matters: compatibility level
What really matters is the compatibility level — that's what determines whether newer SQL syntax will work.
| 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.
This note sits outside the main Operations → Automation → AI → Data path — but platform clarity prevents the kind of technical debt that fuels unplanned work.
Where you are
This is a standalone Appendix · Technical note — not part of a numbered series. Return to the main path with Progressive AI for SMBs, or explore The hidden cost of technical debt.
Working through platform migrations or query modernization? Let's talk — we can often clarify compatibility level in a few minutes.