Specialised Tables and Graph Queries
Go beyond standard tables — learn when to use temporal, in-memory, external, ledger, and graph tables, plus how to partition large tables and write graph queries with MATCH.
Not every table is a regular table
Think of different types of filing cabinets.
A standard filing cabinet stores documents in folders — simple and reliable. But sometimes you need something special: a temporal cabinet that automatically keeps every version of every document (so you can see what it looked like last Tuesday), an in-memory cabinet that holds everything in your hands for ultra-fast access, an external cabinet that is actually a window into someone else’s filing system, a ledger cabinet with tamper-proof seals (like a blockchain for your data), or a graph cabinet that maps relationships between people, places, and things.
Each type solves a specific problem. The exam tests whether you know which cabinet fits which scenario.
Specialised table types at a glance
| Table Type | What It Does | Best For | Key Limitation |
|---|---|---|---|
| Temporal | Automatically tracks full history of row changes | Audit trails, point-in-time queries, regulatory compliance | History table can grow very large — plan retention |
| In-Memory | Stores data in memory (not on disk) for ultra-fast access | High-throughput OLTP: shopping carts, session state, IoT ingestion | Some T-SQL features restricted; requires MEMORY_OPTIMIZED filegroup on SQL Server (not needed on Azure SQL) |
| External | Queries data stored outside the database (Blob Storage, Hadoop, another SQL DB) | Data lake queries, cross-database joins, data virtualisation | Read-only by default, performance depends on external source |
| Ledger | Tamper-evident rows with cryptographic verification | Financial records, regulatory data, any data requiring proof of integrity | Append-only (updatable ledger) or insert-only, cannot be disabled once enabled |
| Graph (node/edge) | Models entities and relationships as a graph | Social networks, recommendation engines, fraud detection, org charts | Requires special MATCH syntax, limited tooling compared to dedicated graph DBs |
Temporal tables: automatic history
A temporal table automatically maintains a history table. Every time you UPDATE or DELETE a row, the old version is saved with start/end timestamps.
CREATE TABLE Employees (
EmployeeId INT NOT NULL PRIMARY KEY,
Name NVARCHAR(200) NOT NULL,
Department NVARCHAR(100) NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
-- System-versioning columns
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));Query data as it was at a specific point in time:
-- What was the employee's salary on 1 January 2026?
SELECT Name, Salary
FROM Employees FOR SYSTEM_TIME AS OF '2026-01-01'
WHERE EmployeeId = 42;Scenario: Ingrid's compliance requirement
Nordic Shield Insurance must prove what policy terms were active at any past date — regulators can request this during audits. Ingrid creates the Policies table as temporal. Now she can answer “What were the terms for policy P-12345 on March 15, 2025?” with a single query. No custom audit triggers needed.
In-memory tables: speed when it matters
In-memory OLTP (Hekaton) stores entire tables in memory. Transactions use optimistic concurrency — no locks, no latches. This can deliver 10-30x throughput improvement for write-heavy workloads.
-- Requires a MEMORY_OPTIMIZED filegroup (one-time setup)
CREATE TABLE ShoppingCart (
CartId INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
CustomerId INT NOT NULL,
ProductId INT NOT NULL,
Quantity INT NOT NULL DEFAULT 1,
AddedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE()
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);DURABILITY options:
SCHEMA_AND_DATA— survives restarts (data is persisted)SCHEMA_ONLY— data is lost on restart (great for temp/session data)
Ledger tables: tamper-proof data
Ledger tables create a cryptographic chain of evidence. Every change is recorded and can be verified — you can prove data has not been altered after the fact.
CREATE TABLE FinancialTransactions (
TransactionId INT NOT NULL PRIMARY KEY,
AccountId INT NOT NULL,
Amount DECIMAL(15,2) NOT NULL,
TransactionDate DATETIME2 NOT NULL
) WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);Two types:
- Updatable ledger tables — allow UPDATE and DELETE, but every change is recorded in the ledger
- Append-only ledger tables — only INSERT allowed, no modifications ever
Exam tip: Ledger vs temporal
Both track history, but for different reasons:
- Temporal = “What was the value at a point in time?” (time-travel queries)
- Ledger = “Can I prove this data was not tampered with?” (cryptographic verification)
A financial audit may need BOTH: temporal for point-in-time lookups, ledger for tamper evidence. They can be combined on the same table.
Graph tables: modelling relationships
SQL Server graph tables represent data as nodes (entities) and edges (relationships between entities). This is powerful for modelling many-to-many relationships that are awkward with traditional foreign keys.
-- Create node tables (entities)
CREATE TABLE Person (Name NVARCHAR(200), Department NVARCHAR(100)) AS NODE;
CREATE TABLE Project (ProjectName NVARCHAR(200), Budget DECIMAL(12,2)) AS NODE;
-- Create edge table (relationship)
CREATE TABLE WorksOn (Role NVARCHAR(100), HoursPerWeek INT) AS EDGE;The MATCH operator
Graph queries use the MATCH clause to traverse relationships:
-- Find all people who work on the "AI Search" project
SELECT p.Name, w.Role, w.HoursPerWeek
FROM Person p, WorksOn w, Project pr
WHERE MATCH(p-(w)->pr)
AND pr.ProjectName = 'AI Search';
-- Find people who work on the same project as 'Priya'
SELECT p2.Name, pr.ProjectName
FROM Person p1, WorksOn w1, Project pr, WorksOn w2, Person p2
WHERE MATCH(p1-(w1)->pr<-(w2)-p2)
AND p1.Name = 'Priya'
AND p2.Name <> 'Priya';The arrow syntax -(edge)-> indicates direction. p-(w)->pr means “person connected to project through the WorksOn edge.”
Partitioning: managing large tables
When tables grow to hundreds of millions of rows, partitioning splits them across multiple filegroups based on a column value (usually a date).
-- Step 1: Create a partition function (defines the boundaries)
CREATE PARTITION FUNCTION PF_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01');
-- Step 2: Create a partition scheme (maps partitions to filegroups)
CREATE PARTITION SCHEME PS_OrderDate
AS PARTITION PF_OrderDate TO (FG_Archive, FG_2024, FG_2025, FG_Current);
-- Step 3: Create the table on the partition scheme
CREATE TABLE Orders (
OrderId BIGINT NOT NULL,
OrderDate DATE NOT NULL,
CustomerId INT NOT NULL,
Amount DECIMAL(10,2) NOT NULL
) ON PS_OrderDate(OrderDate);Benefits: faster queries (partition elimination skips irrelevant partitions), easier maintenance (archive old partitions), independent backup/restore per filegroup.
Priya at Vault Bank needs to store financial transaction records that regulators may audit. The requirements are: (1) must be able to query data as it was at any past date, and (2) must be able to cryptographically prove the data was not tampered with after recording. Which table type should she use?
Dev at PixelForge Studios is building a content management system. Creative assets (images, videos, documents) have complex many-to-many relationships: an image can belong to multiple projects, a project can reference other projects, and team members collaborate across projects. Which approach best models these relationships?
Next up: JSON in SQL: Store, Query, and Index — work with semi-structured JSON data using native T-SQL functions.