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_ONLYtables 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_ONLYdurability 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?
Log in to save your progress.
Sources
- Temporal tables, Microsoft Learn
- Ledger overview, Microsoft Learn
- In-memory OLTP overview and usage scenarios, Microsoft Learn
- Data virtualization with external tables, Microsoft Learn
- Graph processing with SQL Server and Azure SQL Database, Microsoft Learn