Launch Deal · 50% off 1st month ·

1.2 — Specialized Tables: In-Memory, Temporal, External, Ledger and Graph

DP-800 Domain 1 · Skill 1.2

What You Need to Know

SQL platforms offer table types for specific problems. The exam gives you a requirement (history, tamper evidence, extreme throughput, data in a lake, relationships) and expects the matching table type.

Pick the table type

Requirement Table type Key syntax and facts
Very high-throughput OLTP, latch-free inserts, session or staging state Memory-optimized (in-memory OLTP) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA). SCHEMA_ONLY loses data on restart (fine for staging). Natively compiled procedures run fastest. Available in Business Critical and Premium tiers
Query a row as it was at a point in time, keep every change automatically System-versioned temporal PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo), WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory)). Query with FOR SYSTEM_TIME AS OF '2026-06-01', BETWEEN or ALL
Query files in a data lake (Parquet, CSV, Delta) without loading them External table (data virtualization) CREATE EXTERNAL DATA SOURCE + CREATE EXTERNAL FILE FORMAT + CREATE EXTERNAL TABLE, or ad hoc with OPENROWSET. The data stays in storage
Prove that rows were never tampered with (audit, compliance) Ledger table WITH (LEDGER = ON) for updatable ledger tables (history kept), or LEDGER = ON (APPEND_ONLY = ON) for append-only ones. Database digests stored outside the database allow verification with sp_verify_database_ledger
Many-to-many relationships traversed over several hops (social, org charts, fraud rings, bills of materials) Graph tables CREATE TABLE Person (...) AS NODE, CREATE TABLE Knows AS EDGE. Query with MATCH (lesson 1.7)

Temporal versus ledger

Both keep history, but for different reasons:

  • Temporal answers "what did this row look like then?" It's for business history and point-in-time reporting. History can be changed by privileged users (for example by turning versioning off).
  • Ledger answers "can we prove nobody altered this?" It's cryptographic tamper evidence. Once a table is a ledger table, it can't be changed back into a normal table.

In-memory details worth knowing

  • Memory-optimized tables need enough memory for all their rows, and hash indexes (sized with a BUCKET_COUNT) or nonclustered (range) indexes.
  • They remove latch and lock contention, which helps when many sessions insert into the same table, such as IoT ingestion or a shopping-cart state.
  • Memory-optimized table variables and SCHEMA_ONLY tables replace tempdb-heavy patterns.

External tables

  • Push filters down to the source where it's supported. Use them for cold or archived data and data shared with other engines.
  • They're read-only from SQL's perspective, apart from CETAS (CREATE EXTERNAL TABLE AS SELECT) to export.

Key Concept: History for business questions → temporal. Tamper evidence → ledger. Speed with heavy concurrency → memory-optimized. Lake data in place → external. Multi-hop relationships → graph.

Exam Traps

  • Using a temporal table to meet a requirement for cryptographic proof of no tampering (that's ledger)
  • Using SCHEMA_ONLY durability for data that must survive a restart
  • Loading lake files into tables when the requirement is to query them in place
  • Expecting to turn a ledger table back into a regular table

Check your understanding

Practice Scenario

Auditors require cryptographic proof that rows in a payments table haven't been altered, including by database administrators, while normal updates must remain possible. Which table design meets the requirement?

Report issue

Log in to save your progress.