Design and develop database solutions Study Guide — DP-800

Design and Develop Database Solutions

This domain covers the full lifecycle of database object design and T-SQL development across SQL Server, Azure SQL Database/Managed Instance, and Microsoft Fabric SQL analytics endpoints and warehouses. It accounts for 37% of the exam, making it the heaviest-weighted domain. Questions test precise syntax, correct feature selection by platform, and practical reasoning about design trade-offs — not just definitions.

Tables, Data Types, Indexes, and Columnstore Indexes

Heap vs. Clustered vs. Nonclustered Indexes

A heap is a table with no clustered index; rows are stored without order. A clustered index determines the physical row order — only one per table. A nonclustered index is a separate B-tree structure that contains index key columns plus a row locator (RID for heaps, clustered key for clustered tables).

Columnstore Indexes

Columnstore indexes store data column-by-column in compressed segments (~1 million rows per rowgroup). Key terms:

• Delta store: a B-tree that holds recently inserted rows not yet compressed into columnstore segments.
• Tuple mover: background process that moves closed delta stores into compressed columnstore segments.
• Batch mode execution: columnstore queries process 64–900 rows per batch, dramatically reducing CPU overhead.
• A clustered columnstore index (CCI) replaces the B-tree entirely. Supports updates via the delta store.
• A nonclustered columnstore index (NCCI) sits alongside a B-tree clustered index — useful for HTAP (operational analytics + transactional workloads on the same table).

-- Clustered columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX cci_Sales ON dbo.Sales;

-- Nonclustered columnstore on an existing table
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_Sales ON dbo.Sales (OrderDate, Amount, Region);

Common traps:

• Exam distractors claim columnstore indexes cannot be updated — they can (via delta store).
• NCCIs on a heap require WITH (DROP_EXISTING = ON) patterns when rebuilding; do not confuse with the 1-per-table limit for NCCIs on B-tree tables.
• Batch mode is available for B-tree tables in SQL Server 2019+ and Azure SQL (Intelligent Query Processing), but columnstore is the primary trigger.
• In Fabric Warehouse, tables use distributed columnstore by default; you cannot create a traditional B-tree clustered index.

Specialized Tables

In-Memory OLTP (Memory-Optimized Tables)

Memory-optimized tables reside entirely in RAM with optional durable checkpointing. Require a MEMORY_OPTIMIZED_DATA filegroup. Key properties:

• DURABILITY = SCHEMA_AND_DATA (default, survives restart) vs. SCHEMA_ONLY (lost on restart, useful for staging).
• Natively compiled stored procedures compile T-SQL to machine code at creation time — maximum throughput.
• No support for: ALTER TABLE (must drop/recreate), foreign keys to disk-based tables (from memory-optimized side only), or MAX data types in natively compiled procs.

Temporal Tables

System-versioned temporal tables automatically track row history using two DATETIME2 columns. SQL Server and Azure SQL fully support them; Fabric does not.

CREATE TABLE dbo.Employee (
    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 (HISTORY_TABLE = dbo.Employee_History));

Query history with FOR SYSTEM_TIME AS OF '2024-01-01'. The history table has the same schema but no period definition.

External Tables

External tables use PolyBase (SQL Server, Azure SQL MI) or Elastic Query (Azure SQL Database) to query data in external sources without importing it. In Fabric, external tables are defined over files in OneLake using CREATE EXTERNAL TABLE with a Parquet or Delta format and an external data source.

Ledger Tables

Ledger tables provide cryptographically verifiable, tamper-evident history. Two types:

The database ledger stores a Merkle tree of transaction hashes. Use sys.database_ledger_transactions and VERIFY_DATABASE_LEDGER (or sp_verify_database_ledger) to validate.

Graph Tables

Graph tables model nodes and edges. Created with AS NODE or AS EDGE. Edge tables automatically include $from_id and $to_id pseudo-columns.

Common traps:

• Temporal table history tables cannot themselves be system-versioned.
• Ledger tables cannot be truncated (append-only) or have ledger columns altered.
• Memory-optimized tables require WITH (MEMORY_OPTIMIZED = ON, DURABILITY = ...) syntax — forgetting DURABILITY is a common error.
• External tables in Fabric require an external data source and file format object before CREATE EXTERNAL TABLE.