SQL Server

Azure SQL Table Types: Which One Should You Use?

A practical guide to regular, memory-optimized, temporal, ledger, graph and external tables in Azure SQL, including when to use each one.

By JaviPublished 9 min readIntermediate

On this page

When you right-click Tables → New in SQL Server Management Studio against Azure SQL, you can see several options that all appear to create a “table”:

  • Table
  • Memory Optimized Table
  • Temporal Table
  • Ledger Table
  • Graph Table
  • External Table

They look like variations of the same object. They are not.

Each one solves a different problem.

The most important rule is simple:

Use a regular relational table by default. Choose a specialized table type only when you have a specific requirement that it solves better.

A memory-optimized table is not simply a faster table. A temporal table is not the same thing as an audit log. A ledger table is not just “temporal with extra columns.” A graph table is not automatically better for relationships. An external table does not move external data into Azure SQL.

Feature availability and behavior can differ across Azure SQL Database, Azure SQL Managed Instance, SQL Server, and SQL database in Microsoft Fabric. Verify support for the platform you are targeting.

Quick decision table

Table type Best use case Use it when
Regular table General OLTP and relational data Normal relational modeling is enough
Memory-optimized High-throughput, low-latency OLTP Measured contention or transaction throughput is the bottleneck
System-versioned temporal Historical row versions You need point-in-time reconstruction or automatic history
Ledger Tamper-evident records You need cryptographic evidence of data integrity
Graph Relationship-heavy models Traversal and graph patterns are central to the workload
External Query data outside Azure SQL Data should stay in external storage

1. Regular tables: still the default

A standard Azure SQL table is the normal relational table most applications should use.

SQL query: 1. Regular tables: still the defaultT-SQL (Azure SQL Database)
CREATE TABLE dbo.Customer
(
    CustomerId     int           NOT NULL
        CONSTRAINT PK_Customer PRIMARY KEY,
    CustomerName   nvarchar(200) NOT NULL,
    EmailAddress   nvarchar(320) NULL,
    CreatedAtUtc   datetime2(3)  NOT NULL
        CONSTRAINT DF_Customer_CreatedAtUtc
        DEFAULT SYSUTCDATETIME()
);
T-SQL (Azure SQL Database)10 linesRead-only query display. It is not connected to a database.

For most transactional systems, a regular table should be your starting point.

Typical uses include customers, orders, products, application configuration, report metadata, job definitions, and normal business state.

If your requirement is simply “store these rows reliably and query them efficiently,” a regular table is normally enough.

2. Memory-optimized tables: when OLTP contention is the problem

Memory-optimized tables are part of In-Memory OLTP.

They are designed for transaction-processing workloads where concurrency, latency, and throughput matter.

SQL query: 2. Memory-optimized tables: when OLTP contention is the problemT-SQL (Azure SQL Database)
CREATE TABLE dbo.OrderQueue
(
    OrderId      bigint       NOT NULL
        PRIMARY KEY NONCLUSTERED,
    CustomerId   int          NOT NULL,
    CreatedAtUtc datetime2(3) NOT NULL
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA
);
T-SQL (Azure SQL Database)12 linesRead-only query display. It is not connected to a database.

I would consider them when there is evidence of:

  • very high transaction volume
  • heavy concurrent inserts or updates
  • lock or latch contention
  • very low latency requirements
  • queue-like structures with intense concurrency

The important word is evidence.

Do not migrate a table because “memory is faster.” First measure waits, locking, latch contention, transaction throughput, CPU, and memory requirements.

In Azure SQL Database, In-Memory OLTP is not available in every service tier. Microsoft currently documents support in Premium DTU and Business Critical vCore tiers.

You can check whether the current database supports in-memory objects:

SQL query: 2. Memory-optimized tables: when OLTP contention is the problem (2)T-SQL (Azure SQL Database)
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported') AS IsXTPSupported;
T-SQL (Azure SQL Database)1 lineRead-only query display. It is not connected to a database.

A result of 1 means the feature is supported.

When not to use it

Do not reach for memory optimization when the real issue is:

  • a missing index
  • an unnecessary scan
  • poor query design
  • stale or inadequate statistics
  • low-concurrency workload
  • analytical rather than transactional processing

3. System-versioned temporal tables: “What did this row look like yesterday?”

A system-versioned temporal table automatically keeps previous versions of rows when they are updated or deleted.

SQL query: System-versioned temporal tablesT-SQL (Azure SQL Database)
CREATE TABLE dbo.CustomerStatus
(
    CustomerId int         NOT NULL
        CONSTRAINT PK_CustomerStatus PRIMARY KEY,
    Status     varchar(30) NOT NULL,

    ValidFrom datetime2(7)
        GENERATED ALWAYS AS ROW START NOT NULL,

    ValidTo datetime2(7)
        GENERATED ALWAYS AS ROW END NOT NULL,

    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.CustomerStatusHistory
    )
);
T-SQL (Azure SQL Database)21 linesRead-only query display. It is not connected to a database.

You continue to update the current table normally:

SQL query: 3. System-versioned temporal tables: “What did this row look like yesterday?”T-SQL (Azure SQL Database)
UPDATE dbo.CustomerStatus
SET Status = 'Suspended'
WHERE CustomerId = 1001;
T-SQL (Azure SQL Database)3 linesRead-only query display. It is not connected to a database.

Azure SQL preserves the earlier version in the history table.

You can then ask what the row looked like at a point in time:

SQL query: 3. System-versioned temporal tables: “What did this row look like yesterday?” (2)T-SQL (Azure SQL Database)
SELECT *
FROM dbo.CustomerStatus
FOR SYSTEM_TIME AS OF '2026-10-01T14:00:00'
WHERE CustomerId = 1001;
T-SQL (Azure SQL Database)4 linesRead-only query display. It is not connected to a database.

System-time period values are recorded in UTC, so the AS OF value is interpreted as a UTC timestamp, not local time.

Good use cases

Temporal tables are a strong fit for:

  • audit history of row versions
  • data forensics
  • point-in-time reconstruction
  • slowly changing business data
  • configuration history
  • recovering from accidental application updates

They are especially useful when teams are otherwise about to build their own history-table triggers and application logic.

A temporal table preserves row versions: what a row looked like and when it changed. It does not identify who made a change unless that information is captured separately.

The cost

History grows.

For update-heavy tables, you must plan:

  • retention
  • storage growth
  • history-table indexing
  • maintenance
  • temporal query patterns

Automatic history does not mean zero operational responsibility.

4. Ledger tables: “Can I prove this data was not tampered with?”

Temporal and ledger tables are often confused.

A temporal table answers:

What did this row look like in the past?

A ledger table answers:

Can I verify that the data and its history have not been tampered with?

Ledger adds tamper-evidence and cryptographic verification capabilities.

There are two important patterns.

Updatable ledger table

An updatable ledger table allows normal inserts, updates, and deletes while preserving earlier versions and ledger information.

This is suitable for systems of record where values change but integrity must be verifiable.

Examples:

  • financial balances
  • regulated records
  • critical business transactions
  • high-trust audit scenarios

Append-only ledger table

Append-only ledger tables permit inserts but block updates and deletes.

They can fit immutable event patterns such as:

  • access events
  • security events
  • transaction journals
  • evidence records

Temporal vs ledger

Requirement Temporal Ledger
Preserve previous row versions Yes Yes for updatable ledger
Point-in-time analysis Excellent Possible
Tamper evidence No Yes
Cryptographic verification No Yes
Everyday business history Strong fit Usually unnecessary
High-integrity regulated records Sometimes Strong fit

If the requirement is simply “show me the old value,” start with temporal.

If the requirement is “prove that privileged users could not secretly alter the history without evidence,” evaluate ledger.

5. Graph tables: when relationships are the actual problem

Azure SQL supports SQL Graph through node and edge tables.

A node represents an entity:

SQL query: 5. Graph tables: when relationships are the actual problemT-SQL (Azure SQL Database)
CREATE TABLE dbo.Person
(
    PersonId int PRIMARY KEY,
    Name     nvarchar(200)
)
AS NODE;
T-SQL (Azure SQL Database)6 linesRead-only query display. It is not connected to a database.

An edge represents a relationship:

SQL query: 5. Graph tables: when relationships are the actual problem (2)T-SQL (Azure SQL Database)
CREATE TABLE dbo.Follows
(
    SinceDate date NULL
)
AS EDGE;
T-SQL (Azure SQL Database)5 linesRead-only query display. It is not connected to a database.

Graph becomes useful when the workload is fundamentally about traversing relationships.

Examples:

  • social networks
  • recommendation relationships
  • fraud networks
  • dependency graphs
  • identity relationships
  • ownership chains
  • network topology

But normal relational databases already handle relationships very well.

If your queries are mostly:

Customer
JOIN Order
JOIN OrderItem
JOIN Product

keep the relational design.

Graph becomes interesting when questions look more like:

Find every entity connected to this account through multiple relationship types.

or:

Find paths and patterns across a relationship network.

6. External tables: query data without putting it in Azure SQL

External tables are different from the other table types because the data stays outside Azure SQL.

Azure SQL stores metadata that describes the external data and lets you query it through SQL.

For Azure SQL Database, Microsoft currently documents data virtualization over external files such as Parquet and CSV in Azure Data Lake Storage Gen2 or Blob Storage as a Preview capability.

This means you should verify current support and limitations before making it a critical production dependency.

Good use cases

External tables can be useful for:

  • analytical reporting
  • repeated queries over files
  • combining relational data with external data
  • data virtualization without ingesting another copy

External table vs OPENROWSET

A practical distinction:

OPENROWSET

  • ad hoc
  • exploration
  • one-off file queries

External table

  • repeated use
  • named reusable object
  • reporting-friendly access
  • controlled schema definition

External table vs ingestion

Ask one architecture question:

Should this data actually belong inside Azure SQL?

If yes, ingest it into a regular table.

If the source should remain the source of truth and Azure SQL only needs access to query it, external tables can make sense.

The practical decision tree

When choosing a table type, I would work through the requirement in this order.

Is normal relational storage enough?

Yes:

Regular table

Stop there.

Do I need automatic historical row versions?

Yes:

System-versioned temporal table

Do I need tamper evidence?

Yes:

Ledger table

Is extreme OLTP concurrency a measured bottleneck?

Yes:

Memory-optimized table

Benchmark first.

Are relationship traversal and graph patterns central to the workload?

Yes:

Graph node/edge tables

Should the data remain outside Azure SQL?

Yes:

External table

One database can use several types

This is not necessarily one choice for the entire database.

A real system could reasonably contain:

Customer
→ Regular table

CustomerStatus
→ Temporal table

FinancialJournal
→ Ledger table

FraudRelationship
→ Graph tables

HighVolumeQueue
→ Memory-optimized table

HistoricalTelemetry
→ External table

That is the key idea.

There is no universally “best” table type.

There is only a table type that better fits a particular requirement.

Common mistakes

“Memory optimized means every query becomes faster”

No.

Bad indexing, poor query design, unnecessary scans, and inefficient application behavior still matter.

“I need history, so I need ledger”

Not necessarily.

Temporal is usually the first feature to evaluate for ordinary row history.

Ledger is specifically about verifiable integrity and tamper evidence.

“My schema has relationships, so I should use graph”

Nearly every relational database has relationships.

Use graph when traversal and relationship patterns are central to the workload.

“External table means the data is imported”

It does not.

The external data remains outside Azure SQL.

“Temporal history manages itself forever”

The engine captures history, but you still own retention, storage, monitoring, and capacity.

What I would use most often

If I ranked these by how often I would expect them to appear in typical application architecture:

  1. Regular tables — by far the most common.
  2. Temporal tables — extremely useful when business history matters.
  3. External tables — useful for repeated access to external analytical data.
  4. Memory-optimized tables — powerful, but workload-specific.
  5. Ledger tables — valuable where tamper evidence is a real requirement.
  6. Graph tables — useful for relationship-heavy domains, but not needed for normal relational joins.

This is not a ranking of quality.

It is a ranking of how frequently I encounter the requirement each feature is designed to solve.

Final recommendation

When SSMS presents several table options, do not ask:

Which one is newest or fastest?

Ask:

What requirement am I trying to satisfy?

Then map the requirement:

Normal transactional data
→ Regular table

Historical row versions
→ Temporal table

Tamper evidence
→ Ledger table

Extreme transactional concurrency
→ Memory-optimized table

Relationship traversal
→ Graph table

SQL access to external data
→ External table

Specialized table types are useful because they let the database engine solve problems application teams often reimplement themselves.

But specialized should remain exactly that:

Use it because the requirement justifies it — not simply because the option appears in SSMS.

Production checklist

Before selecting a specialized Azure SQL table type, ask:

  • What exact requirement does it solve?
  • Can a regular table solve the requirement cleanly?
  • What Azure SQL service-tier restrictions apply?
  • What extra storage can it create?
  • What are the indexing implications?
  • How will backup, restore, and recovery behave?
  • What monitoring is required?
  • What retention policy is required?
  • What limitations apply to my Azure SQL deployment?
  • Have I benchmarked the real workload?
  • Is the feature generally available or still in preview?

Useful follow-up material:

Tags

  • SQL Server
  • Azure
  • Data Architecture
  • Performance