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
- Define the grain and the primary key.
- Use the narrowest correct types, and
NOT NULLwhere the data allows it. - Cluster on a stable, increasing key for OLTP, or on a CCI for big analytical tables.
- Add nonclustered indexes for the real query patterns (check them against the missing index DMVs and Query Store, not guesses).
- 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
floatfor 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?
Log in to save your progress.
Sources
- Index architecture and design guide, Microsoft Learn
- Columnstore indexes: overview, Microsoft Learn
- Create filtered indexes, Microsoft Learn
- Data types (Transact-SQL), Microsoft Learn
- Vector data type, Microsoft Learn