Specialised Tables and Graph Queries

Not every table is a regular table

Specialised table types at a glance

Specialised table types — the exam expects you to pick the right one for each scenario

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	Limited data types, no LOB columns, requires MEMORY_OPTIMIZED filegroup
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 AS NODE;
ALTER TABLE Person ADD Name NVARCHAR(200), Department NVARCHAR(100);

CREATE TABLE Project AS NODE;
ALTER TABLE Project ADD ProjectName NVARCHAR(200), Budget DECIMAL(12,2);

-- Create edge table (relationship)
CREATE TABLE WorksOn AS EDGE;
ALTER TABLE WorksOn ADD Role NVARCHAR(100), HoursPerWeek INT;

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.

Question

What is the difference between temporal and ledger tables?

Click or press Enter to reveal answer

Answer

Temporal tables track row history for point-in-time queries ('what was the value on date X?'). Ledger tables provide cryptographic tamper-evidence ('can I prove this data was not altered?'). Both track changes, but temporal is for time-travel and ledger is for trust verification. They can be combined.

Click to flip back

Question

When should you use an in-memory table?

Click or press Enter to reveal answer

Answer

Use in-memory tables for extreme throughput requirements — high-frequency inserts, session state, shopping carts, IoT ingestion. They eliminate lock contention using optimistic concurrency. Limitations: no LOB columns, limited data types, requires a MEMORY_OPTIMIZED filegroup.

Click to flip back

Question

What does the MATCH clause do in graph queries?

Click or press Enter to reveal answer

Answer

MATCH specifies a traversal pattern across node and edge tables. The syntax p-(e)->n means 'traverse from node p through edge e to node n.' It replaces complex self-joins for many-to-many relationship queries.

Click to flip back

Question

What is partition elimination?

Click or press Enter to reveal answer

Answer

When a query includes the partition column in its WHERE clause, SQL Server skips partitions that cannot contain matching rows. For a table partitioned by year, a query for 2025 data only scans the 2025 partition — not all years.

Click to flip back

Knowledge Check

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?

AA temporal table with system versioningBA ledger table with append-only modeCA temporal table with both system versioning and ledger enabledDA standard table with audit triggers

Check Answer

Knowledge Check

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?

AStandard tables with junction tables for each many-to-many relationshipBGraph tables with nodes for Assets, Projects, and TeamMembers, and edges for relationshipsCA single denormalised table with JSON columns for relationshipsDExternal tables pointing to a dedicated graph database

Check Answer

🎬 Video coming soon

Next up: JSON in SQL: Store, Query, and Index — work with semi-structured JSON data using native T-SQL functions.