Introduction

When working with Azure SQL Database, you’re operating on a fully managed SQL Server engine that is continually updated behind the scenes. While Microsoft manages the infrastructure, there are still settings and metadata you control that directly affect how your database behaves and how your applications interact with it.

Three key areas often cause confusion for database administrators and developers:

1. Compatibility levels – which influence query optimization and certain feature behaviors.
2. Database creation dates – important for audits, migrations, and lifecycle management.
3. Engine version reporting – which tells you the platform build, but not necessarily the feature set your database uses.

Understanding these concepts is essential for making informed changes, diagnosing performance shifts, and keeping your environment in a healthy, predictable state. This article explains each topic in depth and provides practical T-SQL examples.

Compatibility Levels in Azure SQL Database

A compatibility level defines how certain SQL Server features behave and which version of the query optimizer is in use. Think of it as a way to “freeze” application behavior while still benefiting from Azure’s underlying engine updates.

When Microsoft upgrades the Azure SQL Database engine (for example, to align with a newer SQL Server release), your database does not automatically switch to the newer compatibility level. This behavior is intentional — it avoids breaking applications that may rely on older optimizer rules or T-SQL behaviors.

Key facts about compatibility levels:

• Compatibility levels are set per database, not server-wide.
• They remain fixed until you change them with ALTER DATABASE.
• They are not tied to the Azure SQL Database engine version — the two are separate concepts.

Default compatibility levels over time

• Before June 2024 – New databases defaulted to level 150 (SQL Server 2019 equivalent).
• From June 2024 onward – New databases default to level 160 (SQL Server 2022 equivalent).
• Level 170 – Planned for SQL Server vNext but not yet generally available as a default in Azure SQL Database at the time of writing.

Checking your current compatibility level

SELECT name, compatibility_level FROM sys.databases WHERE name = ‘YourDatabaseName’;

Changing the compatibility level

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;

Why compatibility level matters

Changing the compatibility level can impact:

• Query optimizer behavior – For example, level 160 enables enhancements like Cardinality Estimation (CE) feedback, memory grant feedback, and batch mode on rowstore.
• T-SQL syntax and semantics – Some commands or functions may work differently, or be deprecated, at higher levels.
• Performance – In many cases, newer levels yield better performance. However, if queries depend on older optimizer assumptions, regressions can occur.

Best practice: Test thoroughly in a staging environment before switching. Use Query Store to compare execution plans and runtime metrics before and after the change.

Checking When a Database Was Created

Sometimes you need to know exactly when a database was provisioned — for example, during an audit, when analyzing growth patterns, or when tracing deployment history.

In Azure SQL Database, you can retrieve the creation date from the master database (on the same logical server):

SELECT name, create_date FROM sys.databases WHERE name = ‘YourDatabaseName’;

This date is the original provisioning time of the database — it does not change if you restore from a backup or copy to another server.

Understanding @@VERSION in Azure SQL Database

The @@VERSION function is a quick way to see which SQL Server engine build your Azure SQL Database is currently running. In Azure, the output usually looks something like this:

Microsoft SQL Azure (RTM) - 12.0.xxxx.x <build info>

It’s important to note:

• The “12.0” prefix is fixed for Azure SQL Database and does not change with SQL Server releases — it’s an internal identifier for Azure’s SQL Database service.
• This output reflects the engine build, not your compatibility level.
• Microsoft updates the engine automatically as part of the platform; you cannot control this schedule.

Retrieving the SQL engine version

SELECT @@VERSION;

Retrieving the compatibility level

SELECT compatibility_level FROM sys.databases WHERE name = ‘YourDatabaseName’;

Putting It All Together

Compatibility levels, creation dates, and engine versions each serve different purposes in Azure SQL Database:

• Compatibility level – Controls how features behave and how queries are optimized.
• Creation date – Gives you a historical point of reference for the database.
• Engine version – Tells you the current platform build maintained by Microsoft.

Keeping these distinctions clear helps prevent common misunderstandings — like assuming the engine version determines available query features, or forgetting to adjust compatibility level after a major application upgrade.

Conclusion

Managing Azure SQL Database effectively means understanding the relationship between compatibility levels, creation dates, and engine versions.

• Compatibility levels let you control feature behavior and query optimization without losing stability.
• Creation dates provide valuable historical context for database lifecycle tracking.
• Engine versions reflect the Azure-managed SQL platform updates but do not dictate your compatibility level.

By checking these settings regularly and testing changes before applying them in production, you can ensure smoother upgrades, better performance, and fewer surprises in your environment.