Why developers still choose Azure SQL

Azure SQL combines a relational engine with features for JSON, graph, spatial, analytical, and transactional workloads. That breadth can reduce the number of data services an application must coordinate.

This article was first published in 2020. The capabilities remain useful design prompts, but service tiers, limits, interfaces, and prices change. Confirm the current documentation before making an architecture decision.

Start with the data shapes your application needs

Azure SQL supports relational tables alongside JSON, graph, and spatial data. That can be useful when one application needs several data shapes but still benefits from transactions and SQL tooling.

Microsoft documents the current behaviour for JSON data, SQL graph, and spatial data. Check the supported syntax and service scope for the Azure SQL option you plan to use.

Choose rowstore, columnstore, or both

Rowstore indexes suit selective lookups and transactional access. Columnstore indexes can improve large scans and aggregations. Some systems use both so operational and analytical queries can share a database without sharing the same access pattern.

The choice should follow measured queries, data volume, write behaviour, and latency targets. Use Microsoft’s current guides to rowstore indexes and columnstore indexes as the starting point.

Plan for concurrency and history

Memory-optimised tables use multi-version concurrency techniques for workloads that need high concurrency. They add operational and design choices, so benchmark them with the transaction shape you expect rather than relying on headline throughput.

Temporal tables keep row history and support “as of” queries. They can help with audits, investigation, and reporting when retention and storage growth are planned. Review memory-optimized tables and temporal tables before selecting either feature.

Combine security controls deliberately

Azure SQL includes encryption, Always Encrypted, row-level security, and dynamic data masking. Each control addresses a different boundary.

• Encryption protects data at defined stages, depending on the feature and configuration.
• Row-level security filters rows according to a policy.
• Dynamic data masking changes how selected values appear to some users.
• Always Encrypted keeps selected values encrypted outside trusted client processing.

None of these controls makes an application compliant by itself. Identity, key management, network access, monitoring, testing, and operating procedures still matter. Microsoft’s encryption overview and row-level security guide define the current boundaries.

Use the optimizer, then inspect the evidence

SQL describes the result a query needs. The optimizer chooses an execution plan from the available statistics, indexes, resources, and operators.

That does not remove the need to inspect slow or expensive queries. Execution plans, Dynamic Management Views, Query Store, and Extended Events expose different parts of database behaviour. Start with the query processing architecture guide and the Dynamic Management Views catalogue.

Treat service tier and deployment as separate decisions

Azure SQL includes several deployment options and service tiers. Serverless compute and elastic pools can help in particular usage patterns, but neither guarantees a lower bill.

Compare measured demand, pause behaviour, storage, resilience, network design, and operational ownership. Then confirm current limits and prices in the vCore service-tier documentation, serverless overview, and elastic pool overview.

Use the ten points as a review, not a verdict

The original ten themes were data shapes, rowstore, columnstore, memory optimisation, temporal history, security controls, transaction isolation, query optimisation, observability, and deployment choice.

For a new project, turn each theme into a test:

1. Which queries and writes matter most?
2. Which controls protect the data at each boundary?
3. Which operational signals will the team watch?
4. Which tier meets the target under realistic load?
5. What would make the team revisit the decision?

Original author: Davide Mauri, then Senior Program Manager for Azure SQL.