DP-800 · The Master Decision Guide · Every "which should I use?"

The Questions Documentation Can't Answer

I told you not to waste exam time looking up "which should I use?" questions, because the docs describe features and never choose between them. This is the file that chooses. Fifty-one decisions, each as a trigger table: what the stem says → what you pick.

Built from your handoff, your practice misses, and the Learn module you're stuck on

⚠ Read this first — the meta-rule that makes every table below work

Requirements are elimination rules, not descriptions. Every requirement in a stem exists to kill exactly one wrong option. So don't read a scenario asking "what fits?" — read it asking "what does each sentence forbid?"

This has been the shape of essentially every mark you've dropped, across both chats: "CPU and I/O are low" · "capacity in Canada" · "on every execution path" · "distinct price levels" · "normalized 0–100" · "unless they are granted key access" · "or a secret store". In each case you knew the technology and lost the mark on a clause you read as background.

So use these tables backwards. Don't memorise "Hyperscale is for big databases". Memorise "the words 'more than 4 TB' eliminate General Purpose and Business Critical". The trigger column is the one that scores.

1 · Platform & hosting ★ 2 · Tiers & compute 3 · Table & storage design 4 · Programmability 5 · JSON 6 · Finding text 7 · Window functions 8 · Security 9 · AI, vectors & search 10 · Integration & DevOps 11 · Diagnosis ★ Master index

1 — Platform & hosting

Your stuck point. Grounded in the Learn module you linked, then turned into a decision procedure.

Azure SQL Database vs Managed Instance vs SQL Server on VM vs SQL database in Fabric

The module describes four options but doesn't tell you how to choose. Here's what it actually says about each, then the algorithm.

Platform	What it is	The module's stated fit
Azure SQL Database	Fully managed PaaS database	Enterprise performance and availability without infrastructure management; 99.99% SLA; read-intensive work via Hyperscale; variable workloads via serverless
Azure SQL Managed Instance	Managed PaaS instance	Near 100% compatibility with SQL Server Enterprise; migrations with minimal downtime; hybrid, read offloading and DR; latency-sensitive in-memory work
SQL Server on Azure VMs	IaaS — you control the instance	Maximum compatibility and customisation; specific SQL Server versions; OS-level access; configurations unavailable in PaaS
SQL database in Microsoft Fabric	Transactional DB inside Fabric	OLTP + analytics together; auto-mirrors to OneLake as Delta Parquet; read-only SQL analytics endpoint so reporting never slows transactions

★ The decision procedure — run it in this order and stop at the first yes

Does it need OS-level access, a specific SQL Server version, or software installed on the host?
→ SQL Server on Azure VM. Nothing else can do this. Stop.
Does it need instance-level features? SQL Server Agent · Service Broker · linked servers · cross-database queries · CLR · Database Mail · instance-level collation
→ Azure SQL Managed Instance. Stop.
Does it need OLTP and analytics in one place, with OneLake / mirroring / Delta Parquet / "analytics must not affect operations"?
→ SQL database in Microsoft Fabric. Stop.
Otherwise → Azure SQL Database. It's the default, and the exam's default too.

The stem says…	Pick
"minimal infrastructure management", "fully managed", "99.99% SLA", "a single database"	Azure SQL Database
"lift and shift with minimal application changes", "near 100% SQL Server compatibility"	Managed Instance
"SQL Server Agent jobs", "linked servers", "cross-database queries", "Service Broker"	Managed Instance (or VM)
"must run SQL Server 2019 specifically", "access to the operating system", "install an agent on the server"	SQL Server on Azure VM
"analytics without impacting the transactional workload", "OneLake", "Delta Parquet", "mirroring", "Fabric workspace"	SQL database in Fabric
"multiple filegroups", "filegroup backup", "place partitions on separate storage"	Not Azure SQL Database — MI or VM
"unpredictable, intermittent usage with idle periods"	Azure SQL Database, serverless
"database will exceed 4 TB", "rapid scale-out of read replicas"	Azure SQL Database, Hyperscale

The two eliminators that do the most work Instance-level features kill Azure SQL Database. Agent, linked servers, cross-database queries and Service Broker are the classic four. If any appears, Azure SQL Database is out — and it's the option people default to.

OS or version control kills both PaaS options. Only a VM gives you the operating system. If a stem mentions a named version, a third-party agent, or a registry/OS setting, it's a VM question and everything else is a distractor.

🔑 Hook: OS → VM · instance features → MI · OneLake/analytics-together → Fabric · everything else → Azure SQL Database. Four questions, asked in that order.

2 — Service tiers, compute and pools

Three separate decisions people run together. Handoff flags all three as repeat misses.

General Purpose vs Business Critical vs Hyperscale

Tier	Storage & architecture	Choose when
General Purpose	Remote (network-attached) storage	The default. Balanced budget-oriented workloads with no special latency or size demands.
Business Critical	Local SSD, several replicas	Lowest latency, highest resilience, a free built-in read replica, and In-Memory OLTP.
Hyperscale	Distributed page servers	> 4 TB, or rapid scale-out of read replicas, or fast backup/restore independent of size.

The stem says…	Pick
"the database will grow beyond 4 TB"	Hyperscale — GP and BC both cap at 4 TB
"add read replicas quickly", "scale out reads"	Hyperscale
"backups and restores must be near-instant regardless of size"	Hyperscale
"lowest possible latency", "local SSD"	Business Critical
"In-Memory OLTP", "memory-optimized tables"	Business Critical
"a read replica at no additional cost"	Business Critical
"cost-effective", "balanced", no special demand stated	General Purpose

🔑 Hook (your own trap note): > 4 TB kills GP and BC. In-memory OLTP means Business Critical. Everything unremarkable is General Purpose.

Provisioned vs serverless · vCore vs DTU · single vs elastic pool

The stem says…	Pick
"usage is intermittent / unpredictable", "idle for long periods", "pay only for storage when paused"	Serverless
"resources must be continuously allocated", "steady, predictable load", "must not pause"	Provisioned — "continuously allocated" is the phrase that kills serverless
"scale CPU and memory independently", "specific hardware generation", "AHB / licence reuse"	vCore
"simple bundled pricing", small predictable database, no hardware requirement stated	DTU
"CPU and memory baselines must be met"	vCore — DTU bundles them, so it can't answer this
"many databases with varying, non-overlapping peaks", "share resources to reduce cost"	Elastic pool
"one database with a predictable profile"	Single database

Your two recorded traps, verbatim "Continuously allocated" kills serverless — serverless auto-pauses by design, so a requirement forbidding pauses eliminates it outright.
"CPU/memory baselines" kills DTU — DTU is a blended unit, so you cannot state a CPU floor in it.

3 — Table & storage design

Rowstore vs columnstore · clustered vs nonclustered

The stem says…	Pick
"OLTP", "look up a single row", "high-volume inserts"	Rowstore (clustered index)
"data warehouse", "fact table", "aggregate billions of rows"	Clustered columnstore
"real-time operational analytics", "reporting on the live OLTP table", "without a separate warehouse", "without degrading transactions"	Nonclustered columnstore (HTAP)
"the main access path", "range scans by date", "primary sort order"	Clustered index
"selective lookups on another column"	Nonclustered index
Plan shows a Key Lookup	Add INCLUDE columns (your own memory trigger)
Queries always filter on the same narrow condition	Filtered index

The columnstore trap A clustered columnstore replaces the rowstore, so it wrecks OLTP singleton lookups. When a stem wants analytics and keeps transactional performance, the answer is nonclustered.

Partitioning — whether, and how

The stem says…	Pick
"archive a month in seconds", "minimal logging", "avoid a large DELETE"	Partitioning + SWITCH
"queries filtering on the partition column must read only relevant data"	Partition on that column (elimination)
"improve overall query performance"	Not partitioning — that's an indexing answer
Boundaries are the first of each period	RANGE RIGHT
Boundaries are the last of each period	RANGE LEFT (rare, fragile with time)
"cannot create the unique index / primary key"	Add the partitioning column to the key
"the SWITCH fails"	A non-aligned index, or a non-empty target
"no next used filegroup"	`ALTER PARTITION SCHEME … NEXT USED` before SPLIT
Azure SQL Database + partitioning	`ALL TO ([PRIMARY])` — one filegroup only

Counting: N boundaries → N+1 partitions, whatever the direction. Sliding window order: SWITCH out → MERGE → NEXT USED → SPLIT.

Tracking change over time: temporal vs ledger vs CDC vs Change Tracking vs CES

Feature	Answers
Temporal tables	"What did this row look like at time T?" Full history, queryable with `FOR SYSTEM_TIME`.
Ledger tables	"Can you prove nothing was altered?" Cryptographic tamper-evidence.
Change Data Capture (CDC)	"What changed, with before and after values?" Poll-based, heavier.
Change Tracking	"Which rows changed?" No values, lightweight, poll-based.
Change Event Streaming (CES)	Push changes to Event Hubs. No polling.

The stem says…	Pick
"reconstruct the record as of a date", "audit history of changes"	Temporal
"prove / attest / tamper-proof / regulator"	Ledger
"need the old and new values of what changed"	CDC
"only need to know which rows to sync", "minimal overhead"	Change Tracking
"push to Event Hubs", "no polling", "near real-time stream"	CES
"versions created and replaced within a period"	`FOR SYSTEM_TIME CONTAINED IN`

🔑 Hook: Temporal = what it looked like · Ledger = prove it wasn't changed · CDC = what changed and to what · Change Tracking = which rows · CES = push it out.

Keys, numbers and types

The stem says…	Pick
"one number series across several tables", "get the value before inserting"	SEQUENCE
"auto-numbering within one table"	IDENTITY
"reserve a block of values in advance"	`sp_sequence_get_range` — not CACHE
Gaps appeared after an unclean shutdown	CACHE lost values → `NO CACHE`
Money, prices, anything financial	DECIMAL(p,s) — exact. Never FLOAT.
"very high concurrent inserts", `PAGELATCH_EX`	In-memory OLTP, or `OPTIMIZE_FOR_SEQUENTIAL_KEY` if "minimal change"
Index needed on a computed or JSON expression	PERSISTED computed column + index

Clustering key ideal: Narrow, Unique, Static, Ever-increasing — but ever-increasing is exactly what causes last-page contention at extreme insert rates. Read the scenario.

4 — Programmability: what object, what return shape

Handoff flags scalar-vs-iTVF as missed in both directions, and return-shape as one of your five meta-patterns.

View vs scalar UDF vs inline TVF vs multi-statement TVF vs stored procedure

Object	Returns	Can modify data?	Parameters?
View	A rowset	Limited	No
Scalar UDF	One value	No	Yes
Inline TVF	A rowset	No	Yes ✅
Multi-statement TVF	A rowset	No	Yes — but opaque to the optimiser
Stored procedure	Result sets + OUTPUT params	Yes	Yes

The stem says…	Pick
"return one value usable inside an expression / SELECT list"	Scalar UDF
"a parameterised rowset", "like a view but takes an argument"	Inline TVF + `CROSS APPLY`
"a reusable rowset with no parameters"	View
"must modify data", "must handle errors", "returns multiple result sets"	Stored procedure
"the table-valued function performs badly / bad row estimate"	Rewrite as an inline TVF
Application must have no table permissions	GRANT EXECUTE on procedures (ownership chaining)

Your recorded trigger — keep it One value inside an expression → scalar. A parameterised rowset → inline TVF + CROSS APPLY. You've missed this in both directions, so check the return shape before anything else.

Getting values out of a procedure · DML vs DDL trigger

The stem says…	Pick
"rows and a scalar available after the procedure exits"	Result set + OUTPUT parameter
"indicate success or an error code"	RETURN code — status only, never data
"must be correct on every execution path"	Initialise the OUTPUT parameter explicitly — a declared default is not enough
Respond to INSERT / UPDATE / DELETE	DML trigger
Respond to CREATE / ALTER / DROP, or audit schema change	DDL trigger
"test expects the database to raise an error"	`ExpectedSqlException` — your twice-lost item; the answer contains "Sql" + "Exception"
Dynamic SQL with user input	`sp_executesql` with parameters — never concatenation
"repeated deployments", "idempotent", "seed data"	Post-deployment MERGE, never INSERT

5 — JSON: construct, convert, parse

Your densest historical miss cluster — three losses. The fix is naming the three verbs.

D10

Which JSON function?

The three verbs — say these before you look at the options CONSTRUCT (build JSON from values) → JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG
CONVERT (turn a result set into JSON) → FOR JSON PATH / AUTO
PARSE (read JSON) → JSON_VALUE, JSON_QUERY, OPENJSON, JSON_CONTAINS, JSON_PATH_EXISTS, ISJSON

The stem says…	Pick
Extract one scalar (name, price, status)	`JSON_VALUE`
Extract a nested object or array	`JSON_QUERY`
Turn a JSON array into rows for reporting or joins	`OPENJSON … WITH (…)`
Does the array contain this value?	`JSON_CONTAINS`
Does the key exist, whatever its value?	`JSON_PATH_EXISTS`
Reject invalid JSON at insert	`CHECK (ISJSON(col) = 1)`
Build an object inline from columns, one per row	`JSON_OBJECT`
Fold many rows into one array	`JSON_ARRAYAGG`
Fold key/value rows into one object	`JSON_OBJECTAGG`
Update one field inside a stored document	`JSON_MODIFY`
Convert a result set to a required shape	`FOR JSON PATH` + dotted aliases
Single object, no `[ ]` — a REST body	`FOR JSON PATH, WITHOUT_ARRAY_WRAPPER`
NULL properties are missing from the output	`INCLUDE_NULL_VALUES`
Value longer than 4,000 characters	`OPENJSON … WITH (col NVARCHAR(MAX) '$.x')` — `JSON_VALUE` caps at 4,000

The four JSON traps that have cost you marks 1. JSON_VALUE on an array/object path returns NULL silently — it does not error. Use JSON_QUERY.
2. WITHOUT_ARRAY_WRAPPER = single row. INCLUDE_NULL_VALUES = multi-row nulls. Different problems.
3. JSON_VALUE returns nvarchar(4000). Longer values need OPENJSON with NVARCHAR(MAX).
4. Nesting existing JSON inside FOR JSON output escapes it — wrap in JSON_QUERY.

6 — Finding text: the whole ladder

Five different technologies answer "find this text", and the exam expects you to rank them.

D11

LIKE vs regex vs full-text vs fuzzy vs vector

Technology	Finds	Can seek an index?
LIKE	Simple patterns; `'ABC%'` prefix	Yes, for a trailing wildcard only
REGEXP_*	Real patterns — classes, quantifiers, anchors, groups	Never
Full-text (CONTAINS/FREETEXT)	Words and their forms, with ranking	Uses a full-text index
Fuzzy (EDIT_DISTANCE etc.)	Near-matches — typos, spelling variants	No
Vector search	Meaning, regardless of wording	DiskANN vector index

The stem says…	Pick
"starts with", a simple prefix, performance matters	LIKE 'x%' — the only one that seeks
"validate a format" (email, postcode, NI number)	`REGEXP_LIKE` in a CHECK constraint
"redact / reformat matched text"	`REGEXP_REPLACE`
"split on inconsistent delimiters"	`REGEXP_SPLIT_TO_TABLE` (`STRING_SPLIT` takes one character)
"boolean / proximity / prefix / inflectional word search"	`CONTAINS`
"users type a question in their own words", no syntax to learn	`FREETEXT`
"need a relevance score" to sort or fuse	`CONTAINSTABLE` / `FREETEXTTABLE`, join on `[KEY]`
"names may be misspelled", deduplicate customers	Fuzzy matching
"find conceptually similar content", "semantic"	Vector search
Exact codes and natural language in one search	Hybrid + RRF

Fuzzy — match the scale in the stem

Requirement wording	Function	Scale
"number of changes"	`EDIT_DISTANCE`	a count — lower is closer
"normalized 0–100"	`EDIT_DISTANCE_SIMILARITY`	0–100 — higher is closer
"0–100", weighted to a shared prefix (names)	`JARO_WINKLER_SIMILARITY`	0–100 — higher is closer
"sounds like"	`SOUNDEX` / `DIFFERENCE`	0–4

🔑 Hook: Distance counts (lower better) · Similarity scores (higher better). Then match the scale — a count, 0–100, a float distance or 0–4 — to the number in the stem.

7 — Window functions

D12

Which ranking / offset / distribution function

The stem says…	Pick
"top N distinct levels", "price bands", ties must be returned, no gaps	DENSE_RANK
Competition ranking where a gap after a tie is correct	RANK
"exactly one row per group", deduplication, paging	ROW_NUMBER
"divide into n groups of equal size"	NTILE(n) — by row count, not by value
Value-based cut-offs / percentile thresholds	PERCENT_RANK / PERCENTILE_CONT — not NTILE
"compare to the previous period"	LAG (LEAD for the next)
"the final value in the partition"	`LAST_VALUE` + `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
Median that must exist in the data	PERCENTILE_DISC
Median interpolated	PERCENTILE_CONT
Running total, and rows can share the same date	State `ROWS` explicitly — the default is RANGE and lumps ties together
Filter on a window result	Wrap in a CTE — window functions run after WHERE

The default-frame trap, since it's silent ORDER BY inside OVER() with no frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE pulls in all rows tied on the ordering value, so tied dates get identical running totals — and LAST_VALUE returns the current row. Write ROWS.

8 — Security

Every question here names an adversary. Match the adversary to the control.

D13

Which protection — the adversary table

Who are you stopping?	Control
Someone who steals a backup or a disk	TDE
Someone intercepting the network	TLS
The DBA / cloud operator	Always Encrypted
A legitimate user seeing other people's rows	Row-Level Security
A casual viewer reading a screen	Dynamic Data Masking

The stem says…	Pick
"administrators must never read it" + "must still search by exact value"	Always Encrypted, DETERMINISTIC
"administrators must never read it", never queried	Always Encrypted, RANDOMIZED
…and must support range / LIKE / BETWEEN	Always Encrypted with secure enclaves
"protect backups" + "no application changes"	TDE — Always Encrypted needs driver changes
"must control and rotate the key" / revoke Microsoft's access	TDE with a customer-managed key in Key Vault
"reduce on-screen exposure", "not a compliance control"	DDM — the one place it's correct
"GDPR / HIPAA / PCI", "must not be able to determine"	Never DDM — inference defeats it
"see only their own rows"	RLS filter predicate
…"and must not create or move a row to another tenant"	Add BLOCK predicates `AFTER INSERT` / `AFTER UPDATE`

Your two recorded security traps RLS: filter predicate hides reads; BLOCK predicates AFTER INSERT / AFTER UPDATE validate new row values. A filter alone lets a user insert a row they'll then never see.
Always Encrypted "unless they are granted key access" — that clause describes the mechanism, it is not an instruction to grant access.

D14

Identity, permissions, auditing, network

The stem says…	Pick
"no credentials in code or config", "eliminate the API key"	Managed identity + an RBAC role assignment
"the same identity across several resources", must survive re-creation	User-assigned managed identity
One service, simplest case	System-assigned managed identity
401 calling an Azure service from SQL	Missing / unpropagated role assignment (can take up to 10 minutes; longer if granted through a group)
"can manage the resource but gets Access Denied on the data"	Needs the data-plane role ("Data" in the name)
Least privilege	The narrowest boring permission — SELECT on the table; VIEW DEFINITION is only metadata
Granted in one role, denied in another	DENY wins (except db_owner / sysadmin)
"all databases including future ones", failed logins	Server-level audit
"analyse / query / alert" on audit data	Log Analytics (KQL)
"send to a SIEM", stream	Event Hubs
"retain for N years at lowest cost"	Storage account
"detect SQL injection / anomalous access"	Microsoft Defender for SQL
"identify which columns hold sensitive data"	Data Discovery & Classification
"must not traverse the public internet", private IP	Private endpoint + public access Disabled

Your handoff triggers, preserved: "current + future DBs" → server-level. "KQL + alerts" → Log Analytics.

9 — AI, vectors & search

D15

Retrieval design

The stem says…	Pick
"exact / guaranteed / must not miss a match", small or pre-filtered set	`VECTOR_DISTANCE` (ENN, no index)
"millions of rows", "sub-second", "low latency"	`VECTOR_SEARCH` + DiskANN index (ANN)
Default metric for text	cosine
Want dot product speed	`VECTOR_NORMALIZE` first
Content already in SQL, one system preferred	SQL Server vectors
Many heterogeneous sources, skillsets, semantic ranker	Azure AI Search
Ground an LLM answer in your own data	RAG — not fine-tuning
Model invents details	Grounding system message ("only from context, say when you can't") + low temperature
Calling a completions or embeddings endpoint	POST — GET has no body

★ Chunking strategy — the one that beat you twice

The document set is described as…	Pick
"consistent headings, numbered sections, section-level citations"	Structural
"exact steps but must include surrounding context", multi-page procedures	Hierarchical (match small, return large)
"free-form, no headings, topics shift within a document"	Semantic
Unstructured, huge volume, "minimise cost"	Fixed-size
Short atomic facts, FAQ, clauses	Sentence-based
Well-edited prose, disciplined paragraphs	Paragraph-based
"don't lose content at boundaries"	Add overlap — a modifier, not a strategy

The sentence that flips the whole chunking answer key "Prioritise retrieval quality over ingestion cost" → semantic and hierarchical are permitted.
"Minimise cost" / "simplest" / "highest throughput" → fixed-size with overlap, for everything.
Find that sentence before you read the document descriptions.

10 — Integration & DevOps

Handoff calls DAB "her densest miss group". It's largely rote — treat it as vocabulary.

D16

Data API Builder

The stem says…	Pick
"related data in one request", clients need different shapes, reduce round trips	GraphQL
Simple CRUD, HTTP caching, existing REST clients	REST
Expose a view	`--source.type view --source.key-fields`
Expose a stored procedure	permission `execute`; GraphQL mutation if it writes
Order the CLI steps	init → add (per entity) → validate → start
Credentials in the config	Managed identity, else `@env('...')` — never a literal
Environment-specific config	`DAB_ENVIRONMENT`
Cache must be shared across instances	L1L2 cache (L1 alone is per-instance)
Per-tenant row filtering through the API	RLS + `"set-session-context": true` — permissions control actions, not rows
Static Web Apps endpoint path	`/data-api/` — fixed; Cosmos DB is GraphQL only
403 despite correct role membership	Missing `X-MS-API-ROLE` header
API leaks errors / schema explorer is public	`host.mode` → production
Host it with minimal ops	Azure Container Apps (slots → App Service; static site → SWA; existing k8s → AKS)

D17

Build, deploy and the project file

The stem says…	Pick
"validate schema changes before merging"	`dotnet build` — compiles offline, no database needed
"deploy the dacpac to a database"	`sqlpackage /Action:Publish`, or `azure/sql-action` in GitHub Actions
Capture a schema from a live database	`sqlpackage /Action:Extract` → dacpac
Move schema and data	`Export` / `Import` → bacpac
"preview the changes before deploying"	`/Action:DeployReport` or `/Action:Script`
Block a merge on a failed build — Azure DevOps	Build Validation branch policy on main (one object)
Block a merge — GitHub	`on: pull_request` workflow + required status check (two objects)
Deploy a dacpac from GitHub Actions	`azure/sql-action` (upload-artifact is only the courier)
A seed script breaks the build (SQL70001)	`<Build Remove>` + `<None Include>`, then `:r` from the post-deploy script
Unresolved reference (SQL71501)	The object isn't in the project — the model is self-contained

11 — Diagnosis & performance

D18

Which tool, and what the symptom means

The stem says…	Pick
"query regressed", "was fast before", "force the previous plan"	Query Store
What's happening right now	DMVs (`dm_exec_requests`)
Azure SQL DB resource usage, last hour	`sys.dm_db_resource_stats`
Low CPU + low I/O + high latency, many short queries	Query Performance Insight → Custom view, Execution count
Plan without running the query	`SET SHOWPLAN_TEXT / ALL / XML ON`
Compare estimated vs actual rows	`SET STATISTICS PROFILE / XML ON`
How much data did it touch?	`SET STATISTICS IO ON` → logical reads
Estimated ≪ actual rows	Update statistics — not a new index
Key Lookup in the plan	Add INCLUDE columns
Index ignored, function around the column	Rewrite SARGably
Index maintenance, must stay online, least resource cost	REORGANIZE, Microsoft’s preferred method. It doesn’t update statistics, so update them separately.
Statistics must be refreshed with the index, or reorganizing isn’t enough	REBUILD (refreshes statistics itself; online only with `ONLINE = ON`)
Updated Oct 2026: Microsoft no longer recommends fixed 5% / 30% fragmentation thresholds. Older material still uses them, so recognise them if they appear.
Second writer gets an update conflict, no blocking	SNAPSHOT — first committer wins
Error 1205	Deadlock → retry with a random delay
Sessions waiting on one session	Find the head blocker

Query Store verb map (your own): Regressed Queries view finds · plan forcing fixes · hints shape · wait stats explain.

★ Master index — the eighteen decisions

If you can state both sides of each of these, you can answer any "which should I use" question on this exam.

#	The decision	The one phrase that decides it
D1	Platform: SQL DB / MI / VM / Fabric	OS access → VM · instance features → MI · OneLake → Fabric · else SQL DB
D2	GP / BC / Hyperscale	>4 TB or scale-out → Hyperscale · latency or in-memory → BC · else GP
D3	Serverless, vCore, pool	"continuously allocated" kills serverless · "CPU baselines" kills DTU
D4	Rowstore vs columnstore	Analytics on a live OLTP table → nonclustered columnstore
D5	Partitioning	"archive in seconds" → SWITCH · first-of-month → RANGE RIGHT
D6	Temporal / ledger / CDC / CT / CES	"prove" → ledger · "as of" → temporal · "which rows" → Change Tracking
D7	IDENTITY vs SEQUENCE	"across tables" or "before insert" → sequence · "reserve a block" → sp_sequence_get_range
D8	View / scalar / iTVF / MSTVF / proc	Return shape first: one value → scalar · parameterised rowset → iTVF
D9	Return shape & triggers	"scalar after the procedure exits" → OUTPUT param · DML vs DDL by statement type
D10	JSON: construct / convert / parse	Name the verb first · WITHOUT_ARRAY_WRAPPER = single row
D11	LIKE / regex / full-text / fuzzy / vector	Simple prefix → LIKE (only one that seeks) · meaning → vector · both → hybrid
D12	Window functions	"distinct levels" → DENSE_RANK · say ROWS, never rely on the default frame
D13	Which encryption / masking / RLS	Name the adversary · "not a compliance control" is the only place DDM wins
D14	Identity, audit, network	Identity ≠ authorisation — the RBAC role is a separate step
D15	Vector retrieval & chunking	Exact vs scale · "quality over ingestion cost" licenses semantic/hierarchical
D16	DAB	"one request" → GraphQL · permissions control actions, RLS controls rows
D17	Build vs deploy	build = artifact (no DB) · publish = apply · dacpac schema, bacpac data
D18	Which diagnostic	"regressed" → Query Store · estimate gap → statistics · Key Lookup → INCLUDE

How to revise this file Cover the right-hand column of every trigger table and work down the left. Anything you hesitate on, jump to the section. Twenty minutes covers the whole file once you've read it properly once — which makes it the right thing to open the night before, and the right thing to skim on the morning.

And the thing worth remembering about where you started Your baseline in July was 56%. You're now at 89% against a 70% pass mark, with partitioning down from five misses to zero and no security misses at all. The decisions in this file are the last category of thing that can cost you marks — and they're all now in one place, on one page, in the form the exam asks them.