Launch Deal · 50% off 1st month ·

1.1 — Design Tables, Data Types and Indexes

DP-800 Domain 1 · Skill 1.1

What You Need to Know

Every other skill on this exam (security, performance, AI search) rests on well-typed tables with the right indexes. Questions describe a workload (OLTP lookups, analytics scans or both) and ask for the table and index design that fits.

Data types

Need Choose Avoid
Whole-number keys int or bigint (as an IDENTITY, or with a SEQUENCE) uniqueidentifier as a clustered key (random inserts cause page splits); strings as keys
Money and exact decimals decimal(p,s) float (approximate)
Dates and times date, datetime2(n), datetimeoffset for time zones datetime for new designs (lower precision)
Text nvarchar(n) for Unicode, varchar(n) with a UTF-8 collation nvarchar(max) "just in case": it can't be an index key and hurts memory grants
JSON documents The native json type (lesson 1.3) nvarchar(max) plus ISJSON checks, on platforms that have json
Embeddings vector(n), float32 by default (up to 1,998 dimensions) or float16 (lesson 3.4) Storing vectors as JSON text

Size matters: smaller rows mean more rows per 8-KB page, less I/O and smaller indexes.

Rowstore indexes

Index Use it for
Clustered index (one per table) The table's physical order. Pick a narrow, unique, ever-increasing key (an identity or sequence) for OLTP
Nonclustered index Seeks on filter and join columns. Add INCLUDE columns to make it covering, which avoids key lookups
Filtered index A subset of rows: WHERE Status = 'Open'. Smaller, and cheaper to maintain
Unique index Enforces uniqueness (also created by UNIQUE and PRIMARY KEY constraints)

Columnstore indexes

Index Use it for
Clustered columnstore (CCI) Large analytical fact tables. Column-wise compression (often 10x) and batch-mode scans
Nonclustered columnstore (NCCI) on a rowstore table HTAP: OLTP keeps its B-tree indexes, while analytical queries scan the columnstore
Ordered columnstore Better segment elimination on a sorted column, such as dates

Columnstore suits scans and aggregations over millions of rows. B-tree indexes suit singleton lookups and short ranges. One table can combine them.

Design checklist

  1. Define the grain and the primary key.
  2. Use the narrowest correct types, and NOT NULL where the data allows it.
  3. Cluster on a stable, increasing key for OLTP, or on a CCI for big analytical tables.
  4. Add nonclustered indexes for the real query patterns (check them against the missing index DMVs and Query Store, not guesses).
  5. Don't over-index tables with heavy writes. Every index slows INSERT, UPDATE and DELETE.

Key Concept: Lookups → B-tree, with covering INCLUDE columns. Large scans and aggregations → columnstore. Mixed workloads → a rowstore table plus a nonclustered columnstore. Keep keys narrow and increasing.

Exam Traps

  • A clustered index on a random GUID for a high-insert table
  • Using float for currency
  • Indexing every column "for performance" on a write-heavy table
  • Choosing a clustered columnstore for a table that serves mostly single-row lookups

Check your understanding

Practice Scenario

An Azure SQL Database order table serves thousands of single-order lookups per second through its clustered primary key. Finance now runs hourly aggregations over all orders, and those queries scan the whole table and slow down the application. What should you add?

Report issue

Log in to save your progress.

Sources