How to read a DP-800 question
flowchart TB Q["1 · Read the scenario"] --> P["2 · Which platform, version<br/>and MI update policy?"] P --> S["3 · Is the feature GA there,<br/>or preview behind a switch?"] S --> R["4 · What is the real requirement?<br/>protect · find · change · ship"] R --> E["5 · Eliminate answers that solve<br/>a neighbouring requirement"] E --> C["6 · Choose the most specific,<br/>least-privilege option"]
Key numbers
| Area | Number | Meaning |
|---|---|---|
| Exam | 700 · 100 min · 35–40 / 35–40 / 25–30% | Pass mark (of 1000), typical associate exam time, the three weightings |
| Vectors | 1,998 · 3,996 | Maximum dimensions for vector(n) float32 and vector(n, float16) |
| Vector index | 100 | Minimum non-NULL vectors before CREATE VECTOR INDEX (error 42266) |
| Vector search | 42274 | The error for TOP_N against a latest-version index |
| RRF | k = 60 | Conventional constant in 1 / (k + rank) |
| Chunking | CHUNK_SIZE in characters · OVERLAP 0–50% | AI_GENERATE_CHUNKS, CHUNK_TYPE = FIXED only |
sp_invoke_external_rest_endpoint | timeout 1–230 s (30) · retry 0–10 · return 0 for 2xx | Otherwise the return value is the HTTP status |
| Data API builder | page size 100 · max 100,000 · cache TTL 5 s | Defaults; $first=-1 asks for the maximum |
| CES | 256 KB (128–1,024) | Default and range of @max_message_size_kb |
| T-SQL | MAXRECURSION 100 · THROW ≥ 50000, severity 16 · TRY…CATCH 11–19 | Recursive CTE default limit; user errors; catchable severities |
| Indexes | 999 · 1,048,576 | Nonclustered indexes per table; rows per columnstore rowgroup |
| Regex | 2 MB · repetition ≤ 1,000 | LOB input limit; maximum counted repetition |
| Compatibility | 170 | Needed by REGEXP_LIKE, the regex TVFs and AI_GENERATE_CHUNKS |
| SQL database in Fabric | 32 vCores · 4 TB · 3 per trial capacity | Resource limits |
| Azure SQL Database CDC | S3 and above | Basic, S0, S1 and S2 don't support CDC |
GA versus preview, by platform
The features whose status differs by platform — the ones the study guide's "commonly used preview features" warning is about. The full matrix is on the Foundations page.
| Feature | SQL Server 2025 | Azure SQL DB | Azure SQL MI | Fabric SQL database |
|---|---|---|---|---|
Vector index, VECTOR_SEARCH | Preview | GA | GA AUTD · Preview 2025 | GA |
| Half-precision vectors | Preview | GA | GA | GA |
| Fuzzy string matching | Preview | Preview | Preview | Preview |
| Change event streaming | Preview | Preview | Preview | Preview |
| Full-text search | GA | GA | GA | Preview |
| Data virtualization (external tables) | GA | Preview | GA | Parquet and CSV from OneLake |
| External models and AI functions | GA | GA | GA AUTD only | GA |
| SDK-style SQL projects | GA in VS Code · preview in Visual Studio 2022 · not in Visual Studio 2026 · Fabric target platform in preview | |||
Renames and discrepancies
Where Learn modules, the study guide and the live documentation disagree, these notes follow the documentation. Know both versions so neither throws you.
| Topic | What you may read | What the documentation says now |
|---|---|---|
| Model platform name | Azure AI Foundry, "Azure OpenAI in Azure AI Foundry Models" | Microsoft Foundry (the study guide uses the new name; the old portal is "Foundry (classic)") |
| Fabric assistant name | "Fabric Copilot" (Learn module) | "Copilot in Microsoft Fabric" / "Microsoft Copilot in Fabric" (study guide) |
| Study guide "Get trained" link | Points to the DP-750 exam page | DP-800's training is course DP-800T00-A and its three learning paths |
| "authentication tables" | In the CI/CD pipeline-controls objective | No such GitHub feature; read it as pipeline authentication (OIDC, environment secrets) |
| Approximate search syntax | VECTOR_SEARCH(…, TOP_N = 10) | SELECT TOP (10) WITH APPROXIMATE … ORDER BY distance; TOP_N deprecated, error 42274 on new indexes |
| Vector index behaviour | Tables become read-only; filters applied after the search | Latest version: full DML and iterative filtering; GA in Azure SQL Database since September 2026 |
| DAB authentication provider | AzureAd, StaticWebApps | EntraId (old name still accepted); default is Unauthenticated in DAB 2.x |
| DAB entity metadata | mappings, source.key-fields, parameters as an object | A fields array (alias, primary-key, description) and parameters as an array — the old forms are deprecated |
| Prompt file inputs | {{procedureName}}, referenced with # (Learn) | ${input:procedureName}, run as /name or Chat: Run Prompt (VS Code) |
| CES protocol | AMQP examples | AMQP deprecated from 15 August 2026: AzureEventHubs (Azure SQL DB, Fabric) or AzureEventHubsApacheKafka (SQL Server 2025, MI) |
| Fabric encryption | Limitations page: TDE and customer-managed keys not supported | Feature table lists TDE, and the security page documents workspace customer-managed keys — expect "Fabric manages encryption at rest" |
| Fabric auditing | Feature table: SQL Server Auditing — No | Fabric has its own SQL auditing to OneLake; the "No" refers to SQL Server Audit objects |
| MI update policy | "MI supports it" | Often only with the SQL Server 2025 or Always-up-to-date policy; external models and AI functions need Always-up-to-date |
| Vector indexes in CI/CD | Deploy everything from the .dacpac | A DACPAC can skip the vector index (fewer than 100 rows) — create it in a post-deployment step after loading data |
Commonly confused pairs
| Pair | The difference that decides the answer |
|---|---|
| Always Encrypted vs dynamic data masking | AE hides data from the server and DBAs; DDM only changes what query results show to users without UNMASK |
| Always Encrypted vs column-level encryption vs TDE | Client-side keys · server-side keys you open in T-SQL · at-rest files and backups only |
| Deterministic vs randomized | Equality, joins and indexes vs stronger secrecy (operations need secure enclaves) |
| RLS filter vs block predicate | Hide rows from reads vs stop writes that break the rule |
| Change Tracking vs CDC vs CES | Which rows changed · every change with old and new values · each change pushed to a stream |
| Functions SQL trigger vs Logic Apps | Code, needs Change Tracking · low-code workflow, polls with identity or rowversion columns |
| RCSI vs SNAPSHOT | Statement-level versions, no code change · transaction-level versions, opt in per transaction, update conflicts possible |
| Blocking vs deadlock | A wait that ends · a cycle the engine breaks by killing a victim (1205) |
| Query Store vs Query Performance Insight | Engine feature on every platform · Azure SQL Database portal view over Query Store |
| DeployReport vs Script vs DriftReport | What publish would change (XML) · the T-SQL it would run · what changed outside the pipeline (registered databases) |
| Sequence vs IDENTITY | Shared across tables, value before insert, cycles · bound to one column |
| Inline vs multi-statement TVF | Expanded into the query and optimized · a black box filling a table variable |
| Temporal vs ledger | History you can query as of a time · tamper evidence you can verify |
| JSON_VALUE vs JSON_QUERY | Scalar (NULL for objects) · object or array |
| RANK vs DENSE_RANK vs ROW_NUMBER | Gaps after ties · no gaps · unique even for ties |
| NOT IN vs NOT EXISTS | No rows if the subquery returns a NULL · NULL-safe |
| THROW vs RAISERROR | Re-raises or raises severity 16, ends the batch · legacy, chosen severity, formatting |
| CONTAINS vs FREETEXT | Precise terms, phrases, NEAR, Boolean · natural-language meaning of the words |
| ENN vs ANN | VECTOR_DISTANCE over every row, exact · VECTOR_SEARCH + DiskANN, approximate and fast |
| Cosine vs dot vs Euclidean | Angle (default for text) · same ranking as cosine on normalized vectors · magnitude matters |
External model vs sp_invoke_external_rest_endpoint | Embeddings through AI_GENERATE_EMBEDDINGS · any HTTPS call, including chat completions |
@mssql chat vs inline completions | Schema-aware · not schema-aware |
copilot-instructions.md vs *.instructions.md vs *.prompt.md | Always · when applyTo matches · on demand as /name |
MCP stdio vs http | Local child process, Simulator auth, a fixed role · remote server URL with real authentication |
| SQL database vs Warehouse vs SQL analytics endpoint | Read-write OLTP · analytical warehouse · read-only T-SQL over Delta tables |
Scenario to answer
| The scenario says… | Choose |
|---|---|
| DBAs and the cloud operator must never see national IDs | Always Encrypted (deterministic if you must look them up) |
| Support staff see partial phone numbers; managers see all | Dynamic data masking + granular UNMASK for managers |
| Each tenant sees only its rows through a shared connection pool | RLS with a SESSION_CONTEXT predicate set read-only on connection open, plus a block predicate |
| The app on Azure Container Apps must connect without a password | Managed identity + contained Entra user FROM EXTERNAL PROVIDER |
| Embedding calls must not store an API key in the database | Database scoped credential with IDENTITY = 'Managed Identity' + RBAC role on the model resource |
| Prove to auditors that no row was altered after insert | Append-only ledger table with digests in immutable storage |
| Report prices as they were on any past date | System-versioned temporal table, FOR SYSTEM_TIME AS OF |
| Readers time out waiting for writers on an OLTP database | READ_COMMITTED_SNAPSHOT ON (and optimized locking) |
| A query regressed after a deployment; you can't change the app | Query Store: force the previous plan or add a Query Store hint |
| Two procedures deadlock updating the same two tables | Same access order in both, shorter transactions, supporting indexes, retry on 1205 |
| Find out whether someone hot-fixed production outside the pipeline | DriftReport (registered database) or a scheduled DeployReport |
| Status codes must be identical in every environment | Post-deployment MERGE script in the SQL project |
| Production deploys need DBA approval and no stored Azure secret | GitHub environment with required reviewers + OIDC azure/login |
| Expose tables and procedures as REST and GraphQL with no API code | Data API builder entities in dab-config.json |
| Let an AI agent read products but never delete them | SQL MCP Server with delete-record disabled and read-only entity permissions |
| A downstream system needs every change with before and after values | CDC (pull) or change event streaming (push) |
| Run code within seconds of a row change, outside the database | Azure Functions with the SQL trigger binding (Change Tracking on) |
| Users search by meaning and by exact part numbers | Hybrid: full-text + vector, fused with RRF |
| Semantic search over 20 million chunks with low latency | DiskANN vector index + SELECT TOP (N) WITH APPROXIMATE |
| Measure how accurate the approximate search is | Recall against an exact VECTOR_DISTANCE baseline |
| Documents exceed the embedding model's input limit | AI_GENERATE_CHUNKS with overlap, a chunk table, one embedding per chunk |
| Answer customer questions from the product table with a chat model | RAG: retrieve, FOR JSON context, sp_invoke_external_rest_endpoint, JSON_VALUE($.result.choices[0].message.content) |
| Copilot keeps ignoring the team's naming rules | .github/copilot-instructions.md (or a path-specific instructions file) |
| Copilot should see the live schema of the dev database | MSSQL extension (@mssql / agent mode) or SQL MCP Server with a read-only identity |
| Query a Fabric lakehouse from VS Code agent mode | Publish a Fabric data agent and add its MCP server URL |
| Find near-duplicate customer names with typos | JARO_WINKLER_SIMILARITY / EDIT_DISTANCE (preview), blocked by postcode first |
| Validate e-mail format on insert | A CHECK constraint with REGEXP_LIKE (compatibility level 170) |
Night-before checklist
- I can say, for vector indexes, regex, fuzzy matching, CES, external models and full-text, where each is GA and where it is preview.
- I know the three switches:
PREVIEW_FEATURES, compatibility level 170 andexternal rest endpoint enabled. - I can tell Always Encrypted, column encryption, TDE and dynamic data masking apart from a one-line requirement.
- I can write an RLS filter predicate and security policy, and explain why a block predicate is needed.
- I know the isolation levels' phenomena table and what RCSI and optimized locking change.
- I can read a Query Store fix:
sp_query_store_force_planversussp_query_store_set_hints. - I can read an SDK-style
.sqlproj, a publish profile and a GitHub Actions deploy job with OIDC and an environment gate. - I know what
DeployReport,Script,DriftReportandBlockOnPossibleDataLossdo. - I can read a
dab-config.json: provider, roles, field exclusions, policies, relationships, cache, paging, MCP tools. - I can choose between Change Tracking, CDC, CES, the Functions SQL trigger and Logic Apps from what the consumer needs.
- I can write
CREATE EXTERNAL MODEL, chunk withAI_GENERATE_CHUNKSand embed withAI_GENERATE_EMBEDDINGS. - I know the
VECTOR_SEARCHrules:WITH APPROXIMATE, order by distance ascending, noTOP_N, subquery for window functions. - I can compute an RRF score and say why ranks, not scores, are fused.
- I can find the answer in an
sp_invoke_external_rest_endpointresponse and handle a 429. - I know which Copilot instruction file applies when, and how to add an MCP server in
.vscode/mcp.json. - I have tried the exam sandbox and the practice assessment.
On the day
Read the platform and version in every scenario before the question itself. When two answers both work, prefer the one that is enforced by the engine over one that relies on the application, and the least-privilege option over the convenient one. Flag long case studies, answer the short items first, and come back.
Exam security
Don't share or look for live exam questions — it breaks Microsoft's exam agreement and the notes don't contain any. Everything here is drawn from the public study guide, the course and the documentation.