Free DP-800 sample questions with answers
Here are five free DP-800 sample questions covering the three skill areas, with the answer and the reasoning behind it. Try each one before opening the answer. These are original scenario questions written against the official skills outline, not questions from the real exam.
Question 1. An Orders table stores each order's lines as a JSON array in a column named Lines, for example [{'sku':'A1','qty':2},{'sku':'B7','qty':1}]. A report needs one row per line, with sku as a string and qty as an integer. Which approach produces that result?
- A. Call JSON_VALUE on the Lines column for each element index in a UNION query
- B. Call JSON_QUERY on the Lines column and cast the result to int
- C. Use CROSS APPLY OPENJSON(Lines) WITH (sku varchar(20), qty int)
- D. Filter the table with WHERE ISJSON(Lines) = 1
Show answer
Answer: C
OPENJSON with a WITH clause, applied to the column with CROSS APPLY, turns each array element into a row and lets you declare the sku and qty column types. JSON_VALUE returns a single scalar from one path, so it cannot expand an array into rows. JSON_QUERY returns an object or array fragment as text, not typed columns. ISJSON only validates that the text is JSON.
Want more questions like this? Full DP-800 practice tests →
Question 2. A sales database holds one table with rows for all regions. Each sales representative connects through a shared application account, and the application sets the representative's ID with SESSION_CONTEXT at the start of each session. Representatives must see only their own rows and must not be able to insert or update rows so that they belong to someone else. What should you implement?
- A. Dynamic Data Masking on the representative ID column
- B. A security policy with a filter predicate and a block predicate based on SESSION_CONTEXT
- C. Always Encrypted on the representative ID column
- D. DENY SELECT on the table for the application account
Show answer
Answer: B
A row-level security policy with a filter predicate hides rows from reads, updates and deletes, and a block predicate stops writes that would violate the rule, such as inserting a row for another representative. The predicate can read SESSION_CONTEXT, which suits a shared application account. Dynamic Data Masking changes how column values are displayed, not which rows are returned. Always Encrypted protects column contents from the server and does not filter rows. Denying SELECT on the table blocks everyone, including the owner of the data.
Want more questions like this? Full DP-800 practice tests →
Question 3. Intermittent deadlocks occur between two stored procedures. One updates the Customers table and then the Invoices table; the other updates Invoices and then Customers, inside separate transactions. Which change most directly removes the deadlock?
- A. Change both procedures to update the tables in the same order
- B. Add the NOLOCK hint to the UPDATE statements
- C. Increase the lock timeout for the sessions
- D. Move the database to a larger service tier
Show answer
Answer: A
The deadlock is a lock cycle caused by two transactions taking the same locks in opposite order. Making both procedures access the tables in the same order prevents the cycle. NOLOCK is a read hint and does not change write locks, and it brings dirty-read risk. A longer lock timeout only makes sessions wait longer, and a deadlock is resolved by the engine picking a victim rather than by timing out. A bigger service tier adds resources but does not change the lock order.
Want more questions like this? Full DP-800 practice tests →
Question 4. A table holds 20 million product embeddings. A search endpoint must return the ten most similar products in well under a second, and slightly imperfect recall is acceptable. Which design fits?
- A. ORDER BY VECTOR_DISTANCE over all rows with TOP (10)
- B. Full-text search on the product description column
- C. Store embeddings as JSON strings and compare them in the application
- D. Create a vector index and query with VECTOR_SEARCH
Show answer
Answer: D
Approximate nearest neighbour search over a vector index trades a little recall for much lower latency, and VECTOR_SEARCH is the T-SQL function that uses such an index. Computing VECTOR_DISTANCE for every row and sorting is exact nearest neighbour search, which scans all 20 million rows. Full-text search matches words, not meaning, so it cannot rank by embedding similarity. Storing vectors as strings and parsing them at query time is slower and gives up the vector type and index.
Want more questions like this? Full DP-800 practice tests →
Question 5. Product descriptions change a few thousand times a day. Embeddings must be refreshed soon after each change, but application transactions must not wait for a call to an external model endpoint, and the solution should not rescan the whole table. What should you use?
- A. An AFTER UPDATE trigger that calls the embedding endpoint synchronously
- B. A nightly job that regenerates every embedding
- C. Change Tracking with an Azure Function that uses a SQL trigger binding
- D. Generate the embedding for each row during every search query
Show answer
Answer: C
Change Tracking records which rows changed, and an Azure Function with a SQL trigger binding can pick those rows up asynchronously, call the model and write the new embedding, so transactions are not slowed. A synchronous AFTER UPDATE trigger that calls an endpoint makes every transaction wait for the model and can fail the write. A nightly full regeneration leaves embeddings stale for hours and wastes model calls on unchanged rows. Embedding at query time adds model latency to every search and repeats the work for each query.
Want more questions like this? Full DP-800 practice tests →
Where to go next
If you got questions 1 or 3 wrong, read design and develop explained or secure, optimize and deploy explained. If 4 or 5 caught you out, start with AI capabilities explained. Then try the 20-question practice test.