Checkpoints

Non-Blocking Consistent Fuzzy

Same as in (Microsoft SQL Server).

Compression

Dictionary Encoding Bit Packing / Mostly Encoding Prefix Compression

Same as in (Microsoft SQL Server).

Concurrency Control

Multi-version Concurrency Control (MVCC) Two-Phase Locking (Deadlock Prevention)

Same as in (Microsoft SQL Server).

Data Model

Relational

Foreign Keys

Supported

Same as in (Microsoft SQL Server).

Indexes

B+Tree Hash Table

Refer to (Microsoft SQL Server). Along with the indexes provided from the SQL Server codebase, Azure SQL Database produces recommendations for ADD/DROP INDEX in order to optimize its plans. It does this by monitoring the performance of queries across all SQL Database instances on the Azure cloud. It performance statistics of queries across all databases to develop models to estimate performance gains on a non-existent index for a certain query in a database instance. These estimates are used to create recommendations for adding and dropping indexes and the application can choose to automatically have these recommendations applied.

Isolation Levels

Read Uncommitted Read Committed Serializable Snapshot Isolation Repeatable Read

Same as in (Microsoft SQL Server).

Joins

Nested Loop Join Hash Join Sort-Merge Join

Same as in (Microsoft SQL Server).

Logging

Physical Logging

Same as in (Microsoft SQL Server).

It is important to note that in service tiers such as Hyperscale that rely on logically boundless Azure Storage for persistent long-term storage, log-truncation happens much less frequently than in SQL Server.

Query Compilation

Code Generation Stored Procedure Compilation

Same as in (Microsoft SQL Server).

Query Execution

Tuple-at-a-Time Model

Same as in (Microsoft SQL Server).

Query Interface

SQL

Like in (Microsoft SQL Server), Azure SQL DB supports a subset variant of the SQL extension, T-SQL.

Storage Architecture

Hybrid

Same as in (Microsoft SQL Server).

Storage Model

N-ary Storage Model (Row/Record)

Same as in (Microsoft SQL Server).

Storage Organization

Log-structured

Same as in (Microsoft SQL Server).

Stored Procedures

Supported

Same as in (Microsoft SQL Server).

System Architecture

Shared-Disk

There are three types of compute-storage high-level architecture for this cloud database service.

Standard service tier: This service tier entails a simple compute layer backed by a data hosting layer in Azure Blob storage which has built-in reliability.

Business critical tier: This service tier is one level below Hyperscale. This service tier essentially consists of replicated sets of singular SSD-enabled nodes that entail compute and storage activity. It consists of a primary node that handles read-writes and also keeps the rest of the replicas synchronized with it. The rest of the replicas are used for backup and read-only endpoints.

Hyperscale: The hyperscale service tier has support for database sizes of up to 100TB. It is meant to support rapid scaling and recovery. As per standard cluster compute models these days, the hyperscale architecture involves a synergy of compute and storage nodes. The compute node tier caches disk pages in its buffer pool based on SSD and is replicated a few times. Read-only transactions go to these replicas while read-write transactions go to the primary compute node. The compute tier gets its buffer pages from a page server tier whose nodes contain around 1 TB of cached disk pages in them. Azure Blob Storage is used for long term storage. The primary compute node forward log records to a log service tier whose nodes push out the changes to the compute replicas, page servers, and the long-term Azure Storage. It is important to note that each of these tiers is scalable and have high availability on Azure as the replicas are only used for reads.

Views

Virtual Views Materialized Views

Same as in (Microsoft SQL Server).