How to use this post
Eight original DP-800: Developing AI-Enabled Database Solutions scenario questions, spread across the three domains. Answer each one before reading its explanation. Every explanation covers why each wrong answer is wrong.
DP-800 is the exam for people who work where T-SQL meets AI: vector search inside the database, RAG that calls a model from a stored procedure, and the work of securing and shipping all of it. Passing it earns Microsoft Certified: SQL AI Developer Associate.
These eight questions are written against Microsoft's skills outline as of 19 October 2026. They are not real exam questions, and anyone selling "real DP-800 questions" is offering content that breaks Microsoft's exam policies and can get a certification revoked.
DP-800 at a glance
| Certification | Microsoft Certified: SQL AI Developer Associate |
|---|---|
| Passing score | 700 on Microsoft's scaled score. It isn't a percentage. |
| Platforms | SQL Server 2025, Azure SQL, and SQL databases in Microsoft Fabric |
| Renewal | Yearly, through a free online assessment on Microsoft Learn |
| Domain | Weight |
|---|---|
| Design and develop database solutions | 35–40% |
| Secure, optimize, and deploy database solutions | 35–40% |
| Implement AI capabilities in database solutions | 25–30% |
Source: the official DP-800 study guide. Check it close to your exam date.
Domain 1: Design and develop database solutions
Question 1: Storing and querying semi-structured data
Your e-commerce database stores product attributes that vary by category: laptops have CPU specs, shirts have sizes. Attributes arrive as JSON, queries often filter on specific attribute values, and performance is degrading as the catalogue grows. You're on SQL Server 2025. What's the best design?
- A. Store the payload in an
nvarchar(max)column and parse it withOPENJSONin every query - B. Use the native
jsondata type with a JSON index - C. Normalise every attribute into an entity-attribute-value (EAV) table
- D. Store the JSON as
varbinary(max)to reduce storage
Correct answer: B
SQL Server 2025 has a native json type and CREATE JSON INDEX, both generally available. A JSON index covers the paths inside the document, so one index can serve many filters that use JSON_VALUE, JSON_PATH_EXISTS or JSON_CONTAINS. You keep the flexible schema and get index-supported filtering. "Design and implement JSON columns and indexes" is on the outline.
Why A is wrong: It works, but shredding unindexed text in every query scans every row: the exact problem in the scenario.
Why C is wrong: EAV turns one product lookup into many joins and weakens integrity. It's the anti-pattern the JSON type exists to avoid.
Why D is wrong: varbinary makes the JSON opaque: no JSON functions, no indexing, no validation.
Question 2: Choosing a specialised table type
A financial services client must prove to a regulator that historical account records haven't been altered, including by DBAs with full server access. Auditors want cryptographic proof of integrity, not just a change history. Which table type fits?
- A. Temporal tables
- B. Ledger tables
- C. In-memory OLTP tables
- D. A regular table with an AFTER UPDATE trigger writing to an audit table
Correct answer: B
Ledger tables chain row versions into cryptographically verifiable hashes, with database digests you can store outside the database. They exist for tamper evidence against anyone, including administrators.
Why A is wrong: Temporal tables record a full change history, but a privileged user can still alter it. History isn't tamper evidence, a favourite exam distinction.
Why C is wrong: In-memory tables are a performance feature with no audit value.
Why D is wrong: Triggers can be disabled and audit tables edited by anyone with enough privilege.
Question 3: Validating data with regular expressions
You need to reject malformed email addresses at the database layer on SQL Server 2025, without triggers or application changes. What's the cleanest implementation?
- A. A CHECK constraint using
LIKE '%@%.%' - B. A CHECK constraint using
REGEXP_LIKEwith an email pattern - C. An INSTEAD OF INSERT trigger using
PATINDEX - D. Validate only in the application tier
Correct answer: B
SQL Server 2025 added native regular-expression functions, and Microsoft's own documentation shows REGEXP_LIKE enforcing email and phone formats in CHECK constraints. It's declarative and applies to every writer. One detail worth knowing: REGEXP_LIKE needs database compatibility level 170.
Why A is wrong: LIKE has no real pattern language, so values like a@b. pass.
Why C is wrong: A trigger is procedural overhead for what a constraint states declaratively, and PATINDEX shares LIKE's weak pattern syntax.
Why D is wrong: Application-only checks miss bulk loads, integration jobs and ad-hoc fixes, and the scenario asks for the database layer.
Domain 2: Secure, optimize, and deploy database solutions
Question 4: Protecting sensitive data from privileged users
Compliance requires that database administrators never see plaintext national ID numbers: not in query results, not in memory, not in backups. Which feature satisfies this?
- A. Transparent Data Encryption (TDE)
- B. Dynamic Data Masking (DDM)
- C. Always Encrypted
- D. Row-Level Security (RLS)
Correct answer: C
Always Encrypted encrypts data on the client, with keys held by the application, so the database engine and its administrators only ever handle ciphertext. It's the only option here that protects data from high-privilege database users.
Why A is wrong: TDE encrypts files and backups at rest. Anyone who can run a query, including every DBA, sees plaintext.
Why B is wrong: DDM only changes what's displayed. The data is stored in plaintext, privileged users can be granted UNMASK, and values can be inferred through predicates.
Why D is wrong: RLS controls which rows a user sees, not what's visible within a column.
Question 5: Diagnosing a sudden performance regression
A stored procedure that ran in 200 ms now takes 30 seconds after last night's data load. Nothing was deployed. You suspect the optimiser chose a new plan. What's the fastest safe way to confirm and fix it?
- A. Rebuild every index on the tables involved
- B. Use Query Store to compare plans over time and force the last known good plan
- C. Restart the instance to clear the plan cache
- D. Scale the database up and monitor
Correct answer: B
This is Query Store's signature scenario. It keeps plan history, shows the regression, and lets you force the previous plan while you fix the cause, which is often stale statistics after a big load.
Why A is wrong: Hours of maintenance for a problem you haven't diagnosed.
Why C is wrong: A restart throws away every plan, hurts every workload, and the optimiser may compile the same bad plan again.
Why D is wrong: Paying more to run a bad plan isn't a fix, and on-premises SQL Server has no tier to scale.
Question 6: Catching schema drift before deployment
Your team deploys through SQL Database Projects in a GitHub pipeline. Last week a hotfix was applied directly to production and never reached source control. You want the pipeline to detect this kind of divergence before future deployments overwrite it. What do you use?
- A. Schema drift detection with SQL Database Projects, comparing the project model with the target before deploying
- B. Query Store
- C. An Extended Events session capturing DDL
- D. A nightly job that scripts the database for someone to diff by hand
Correct answer: A
A SQL Database Project is a declarative model of the intended schema. Comparing it with the live target surfaces drift as a pipeline gate, before anything is overwritten. "Detect schema drift by using SQL Database Projects" is on the outline word for word.
Why B is wrong: Query Store tracks query performance, not schema state.
Why C is wrong: It records future DDL while it runs, but can't show the current net difference, and last week's change is already missed.
Why D is wrong: It recreates by hand what the project tooling does natively. "Manually" is the tell in a CI/CD question.
Domain 3: Implement AI capabilities in database solutions
Question 7: Choosing a vector search strategy at scale
Your product-search table holds 12 million embeddings. Users need semantically relevant results in under a second, and slightly approximate results are acceptable. What's the right approach?
- A. Exact k-nearest-neighbour (KNN) search:
ORDER BY VECTOR_DISTANCE(...)over every row - B. A DiskANN vector index queried with
VECTOR_SEARCHfor approximate nearest neighbour (ANN) results - C. Precompute all pairwise distances into a lookup table
- D. Use full-text search on the product descriptions instead
Correct answer: B
At 12 million vectors, scanning every row per query won't be sub-second. An approximate (ANN) search over a DiskANN vector index trades a small, controlled amount of recall for far lower latency: exactly the trade the scenario allows. "Choose between using ANN and KNN for vector search" is on the outline.
Why A is wrong: Exact KNN computes the distance to every row. It's right for small tables, or when you need exact results to measure your ANN recall, but too slow here. Note that VECTOR_DISTANCE never uses a vector index.
Why C is wrong: 12 million squared is about 144 trillion distances, out of date the moment a product is added.
Why D is wrong: Full-text matches keywords, not meaning. Hybrid search, combining full-text and vector results with reciprocal rank fusion (RRF), is a strong pattern, but full-text alone drops the semantic requirement.
Question 8: Designing RAG inside the database
You're building a support assistant that must answer from current customer contract data in Azure SQL. Answers must reflect changes made minutes ago, and the model must never receive data the requesting agent isn't allowed to see. What's the correct architecture?
- A. Fine-tune the model on a nightly export of the contracts table
- B. Give the model's agent direct read access to the database and let it query freely
- C. RAG: run a permission-filtered vector or hybrid search for relevant rows, convert them to JSON, and send them as grounding context with
sp_invoke_external_rest_endpoint - D. Paste the entire contracts table into the prompt for every question
Correct answer: C
Retrieval-augmented generation is the outline's pattern: retrieve only the relevant rows the requester may see (Row-Level Security and object permissions apply to the retrieval query), shape them as JSON, and include them in the model call through sp_invoke_external_rest_endpoint. Freshness comes free, because retrieval happens when the question is asked.
Why A is wrong: Fine-tuning bakes in data as of the export, stale by morning, and can repeat data to users who shouldn't see it.
Why B is wrong: An unconstrained model with direct database access is a security anti-pattern. The outline has a whole section on securing model and MCP endpoints.
Why D is wrong: It won't fit in a context window, costs explode, and every customer's data goes out with every request.
How to study for DP-800
Match your hours to the weights. Roughly 40% on core SQL development: the json type and JSON indexes, specialised tables (especially temporal vs. ledger), window functions, CTEs, the regular-expression and fuzzy-matching functions, and Copilot and MCP configuration. 35% on security, performance and CI/CD: the Always Encrypted, DDM, RLS and TDE decision matrix, Query Store, SQL Database Projects and Data API builder. 25% on AI features: external models and embeddings, vector types and functions, ANN vs. KNN, hybrid search with RRF, and RAG through sp_invoke_external_rest_endpoint.
Three habits help most. Work in a real environment: SQL Server 2025 Developer edition is free, so you can practise the AI features locally. Learn where SQL Server, Azure SQL and SQL database in Fabric differ, because questions expect it. And drill the decision pairs (temporal vs. ledger, DDM vs. Always Encrypted, ANN vs. KNN, CDC vs. Change Tracking), because many scenario questions are one of these forks in disguise.
Try 20 more, free
→ Take the free DP-800 practice test: 20 questions, no signup, and your score as soon as you submit. Give your email at the end to see every answer explained.
Full DP-800 practice tests
853 questions across 15 full-length practice tests, each with an explanation for every answer. See the DP-800 course →
This article is not affiliated with or endorsed by Microsoft. All questions above are original, written from Microsoft's published DP-800 skills outline.