This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and implement specialized tables, including in-memory, temporal, external, ledger, and graph

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Modern SQL Server and Azure SQL Database environments provide several specialized table types designed to address specific business, performance, security, and analytical requirements. Rather than relying solely on traditional row-based tables, developers can use specialized tables to improve transaction throughput, maintain historical data automatically, access external data sources, provide tamper-evident records, or model complex relationships.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• When to use each specialized table type
• How each table is implemented
• Their advantages and limitations
• Common business scenarios
• Performance considerations
• Best practices

Understanding these table types is especially important because many modern AI-enabled applications require historical analysis, secure auditing, graph-based relationships, or access to large external datasets.

Overview of Specialized Tables

SQL Server and Azure SQL support several specialized table types:

Each solves a different architectural challenge.

In-Memory Tables

What Are In-Memory Tables?

In-memory tables are stored primarily in memory instead of traditional disk-based storage. They are part of the In-Memory OLTP feature in SQL Server and Azure SQL Database.

Unlike conventional tables, in-memory tables are optimized for extremely fast transaction processing.

They use:

• Memory-optimized data structures
• Lock-free algorithms
• Latch-free architecture
• Optimistic concurrency

These improvements reduce contention and dramatically improve throughput.

Benefits

Advantages include:

• Extremely low latency
• High transaction throughput
• Reduced locking
• Reduced blocking
• Improved scalability
• Better concurrency

Applications can often process several times more transactions compared to equivalent disk-based tables.

Typical Use Cases

Common scenarios include:

• Financial trading
• E-commerce transactions
• Reservation systems
• Gaming platforms
• Real-time telemetry
• IoT ingestion
• Session state storage
• High-volume order processing

Memory-Optimized Filegroup

Although data is stored in memory during operation, SQL Server still requires a memory-optimized filegroup for durability.

Example:

ALTER DATABASE SalesDB
ADD FILEGROUP InMemoryFG CONTAINS MEMORY_OPTIMIZED_DATA;

Creating a Memory-Optimized Table

Example:

CREATE TABLE Orders
(
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT,
    OrderDate DATETIME2
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA
);

Durability Options

SCHEMA_AND_DATA

Both schema and data survive server restart.

Recommended for:

• Production applications
• Financial systems
• Business transactions

SCHEMA_ONLY

Only the table definition persists.

Data disappears after restart.

Useful for:

• Temporary data
• Session caches
• ETL staging
• Intermediate processing

Memory-Optimized Indexes

Memory-optimized tables require indexes.

Supported types include:

• Hash indexes
• Nonclustered indexes

Hash indexes perform exceptionally well for equality searches.

Example:

INDEX IX_Order HASH(OrderID)
WITH (BUCKET_COUNT = 100000)

Limitations

Developers should know:

• Not every SQL feature is supported.
• Memory planning is important.
• Some T-SQL functionality differs.
• Requires appropriate database configuration.

Temporal Tables

What Are Temporal Tables?

Temporal tables automatically maintain a complete history of data changes.

Whenever a row is:

• Updated
• Deleted

SQL Server automatically copies the previous version into a history table.

This eliminates the need for custom audit triggers.

Components

A temporal table consists of:

• Current table
• History table
• System versioning

SQL Server manages the history automatically.

Period Columns

Temporal tables include two hidden datetime columns:

ValidFrom
ValidTo

These define the period during which each row version was valid.

Creating a Temporal Table

Example:

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH
(
    SYSTEM_VERSIONING = ON
);

Querying Historical Data

SQL provides temporal query options.

Example:

SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2025-01-01';

This retrieves the table exactly as it existed at the specified point in time.

Other options include:

• FROM…TO
• BETWEEN
• CONTAINED IN
• ALL

Benefits

Temporal tables provide:

• Automatic history
• Point-in-time recovery
• Audit support
• Trend analysis
• Easier reporting

Typical Use Cases

Examples include:

• Employee history
• Customer profile changes
• Insurance records
• Compliance reporting
• AI training using historical snapshots

External Tables

What Are External Tables?

External tables allow SQL Server or Azure SQL to query data stored outside the database without importing it.

The external data remains in its original location.

SQL accesses it through metadata.

Supported Data Sources

External tables can access:

• Azure Data Lake Storage
• Azure Blob Storage
• Hadoop
• Microsoft Fabric OneLake
• SQL Server
• Oracle (through supported virtualization technologies)
• Parquet files
• CSV files
• Delta Lake (supported platforms)

PolyBase

SQL Server commonly uses PolyBase to query external data.

PolyBase allows T-SQL queries against external sources.

Example architecture:

SQL Server
      ↓
External Table
      ↓
Azure Data Lake

Advantages

Benefits include:

• No data duplication
• Large-scale analytics
• Access data lakes
• Simplified ETL
• Lower storage costs

Common AI Scenarios

AI projects frequently use external tables to access:

• Training datasets
• Data lake files
• Feature engineering data
• Parquet datasets
• Fabric Lakehouse data

Considerations

Performance depends on:

• Network speed
• External storage
• Predicate pushdown
• File formats
• Data partitioning

Ledger Tables

What Are Ledger Tables?

Ledger tables provide tamper-evident records.

They use cryptographic hashing to detect unauthorized modifications.

Ledger technology helps organizations prove that data has not been altered.

Why Ledger Tables Matter

Industries with regulatory requirements often require immutable records.

Examples include:

• Banking
• Healthcare
• Government
• Supply chain
• Legal systems

How They Work

Ledger tables automatically maintain:

• Transaction history
• Cryptographic digests
• Verification metadata

Attempts to modify historical records become detectable.

Updatable Ledger Tables

These support:

• INSERT
• UPDATE
• DELETE

While preserving historical versions.

Append-Only Ledger Tables

These allow:

• INSERT

Only.

Rows cannot be updated or deleted.

Useful for:

• Financial journals
• Event logs
• Blockchain-style records

Benefits

Ledger tables provide:

• Data integrity
• Nonrepudiation
• Tamper evidence
• Simplified auditing
• Regulatory compliance

Typical Use Cases

Examples include:

• Financial transactions
• Audit logs
• Medical records
• Government systems
• Digital contracts

Graph Tables

What Are Graph Tables?

Graph tables model relationships between entities.

Instead of relying solely on foreign keys, graph databases represent information as:

• Nodes
• Edges

This structure is ideal for highly connected data.

Node Tables

Node tables store entities.

Examples:

• Customers
• Products
• Employees
• Devices
• Companies

Example:

CREATE TABLE Person
(
    ID INT,
    Name NVARCHAR(100)
)
AS NODE;

Edge Tables

Edge tables define relationships.

Example:

CREATE TABLE FriendOf
AS EDGE;

Possible relationships:

• Friend of
• Purchased
• Works for
• Located in
• Connected to

Graph Queries

Graph queries use the MATCH clause.

Example:

SELECT *
FROM Person,
FriendOf,
Person
WHERE MATCH(Person-(FriendOf)->Person);

Advantages

Graph tables simplify:

• Relationship traversal
• Network analysis
• Recommendation systems
• Fraud detection
• Organizational charts

AI Use Cases

Graph tables are increasingly valuable for AI because they model relationships that traditional relational databases handle less efficiently.

Examples include:

• Knowledge graphs
• Recommendation engines
• Customer relationship analysis
• Social networks
• Supply chain analysis
• Fraud detection

Choosing the Correct Specialized Table

Comparing Specialized Tables

Best Practices

In-Memory Tables

• Use for high-volume OLTP workloads.
• Choose an appropriate bucket count for hash indexes.
• Monitor memory consumption carefully.
• Use SCHEMA_AND_DATA for durable business data.

Temporal Tables

• Use for audit and historical reporting.
• Periodically archive large history tables if appropriate.
• Query historical versions using FOR SYSTEM_TIME.

External Tables

• Prefer efficient file formats such as Parquet.
• Partition large datasets.
• Minimize unnecessary data movement.
• Push filtering to the external source whenever possible.

Ledger Tables

• Use when regulatory compliance or auditability is required.
• Understand the difference between append-only and updatable ledger tables.
• Regularly verify ledger digests as part of governance processes.

Graph Tables

• Use when relationships are central to the data model.
• Avoid replacing well-designed relational models unnecessarily.
• Combine graph and relational tables where appropriate.

Common Exam Tips

For the DP-800 exam, remember these important distinctions:

• In-memory tables improve OLTP performance through memory optimization and optimistic concurrency.
• Temporal tables automatically maintain historical versions of data using system versioning.
• External tables allow SQL queries against data stored outside the database without importing it.
• Ledger tables provide cryptographically verifiable, tamper-evident records for auditing and compliance.
• Graph tables represent entities and relationships using node and edge tables and support graph pattern matching with the MATCH clause.
• SCHEMA_ONLY memory-optimized tables do not preserve data after a restart, while SCHEMA_AND_DATA tables do.
• Append-only ledger tables support inserts only; updatable ledger tables support inserts, updates, and deletes while preserving history.

Practice Exam Questions

Question 1

A financial trading application requires extremely high transaction throughput while minimizing locking and blocking. Which table type should be implemented?

A. Temporal table

B. In-memory table

C. Ledger table

D. External table

Answer: B

Explanation: In-memory tables use memory-optimized storage and lock-free, latch-free processing to maximize OLTP performance and concurrency.

Question 2

A company wants SQL Server to automatically maintain historical versions of customer records whenever updates occur. Which feature should be used?

A. External table

B. Graph table

C. Temporal table

D. Ledger table

Answer: C

Explanation: Temporal tables automatically maintain current and historical versions of rows using system versioning, eliminating the need for custom audit triggers.

Question 3

A database administrator needs to query Parquet files stored in Azure Data Lake without copying the data into SQL Server. Which table type is most appropriate?

A. External table

B. Ledger table

C. In-memory table

D. Temporal table

Answer: A

Explanation: External tables provide metadata that enables SQL queries against external data sources such as Azure Data Lake and Parquet files without importing the data.

Question 4

Which specialized table type provides cryptographic verification that historical records have not been altered?

A. Graph table

B. External table

C. Temporal table

D. Ledger table

Answer: D

Explanation: Ledger tables use cryptographic hashing and transaction digests to create tamper-evident records suitable for auditing and regulatory compliance.

Question 5

Which statement about memory-optimized tables configured with SCHEMA_ONLY durability is correct?

A. Both schema and data survive a server restart.

B. The table cannot contain indexes.

C. The table definition remains, but the data is lost after a restart.

D. The table is automatically converted to a temporal table.

Answer: C

Explanation: SCHEMA_ONLY durability preserves only the table definition. Data stored in the table is not retained after the SQL Server instance restarts.

Question 6

Which SQL Server feature is specifically designed to model entities and their relationships using node and edge tables?

A. Ledger

B. PolyBase

C. Temporal

D. Graph

Answer: D

Explanation: Graph tables use node and edge tables to represent entities and relationships and support graph pattern matching using the MATCH clause.

Question 7

A retail company wants to analyze historical pricing information exactly as it existed on a specific date six months ago. Which query capability supports this requirement?

A. MATCH

B. FOR SYSTEM_TIME AS OF

C. OPENROWSET

D. MERGE

Answer: B

Explanation: The FOR SYSTEM_TIME AS OF clause queries a temporal table as it existed at a specified point in time.

Question 8

Which workload is the best candidate for a clustered in-memory table?

A. A data warehouse containing historical sales data

B. A compliance audit archive

C. A product catalog that rarely changes

D. A high-volume online order processing system

Answer: D

Explanation: In-memory tables are designed for OLTP workloads with frequent inserts, updates, and concurrent transactions.

Question 9

What is the primary advantage of using external tables in AI-enabled database solutions?

A. They automatically encrypt all external files.

B. They eliminate the need for indexes.

C. They enable SQL queries against externally stored datasets without duplicating the data.

D. They automatically convert relational data into graph structures.

Answer: C

Explanation: External tables allow SQL Server and Azure SQL to access external data sources directly, making them ideal for AI workloads that consume large datasets stored in data lakes.

Question 10

Which scenario is the best fit for an append-only ledger table?

A. A customer profile database requiring frequent updates

B. A temporary ETL staging table

C. A social network relationship graph

D. A financial transaction journal where records must never be modified or deleted

Answer: D

Explanation: Append-only ledger tables permit inserts but prevent updates and deletes, making them ideal for immutable transaction logs and regulatory audit records.

Go to the DP-800 Exam Prep Hub main page