Launch Deal · 50% off 1st month ·
← Exam practice

DP-800

SQL AI Developer Associate

22 lessons · 134 questions · Recently updated

DP-800 practice test questions in the style of the real exam, a free 10-question mock test you can take without an account, and a full 50-question timed mock exam. Every answer comes with an explanation.

📖 Free study guide

Full practice exam

50 questions · 120 minutes · timed, scored by domain · drawn from a bank of 112

Lessons by domain

Every lesson ends with a practice scenario on what you just read, with an explanation for every answer.

Free DP-800 sample questions

5 questions from the DP-800 practice test, with the answer and an explanation for each. They are original scenarios written from the public exam outline, not real exam questions.

  1. Question 1

    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?

    1. A.A memory-optimized copy of the table
    2. B.A nonclustered columnstore index on the columns the aggregations use
    3. C.A clustered columnstore index that replaces the clustered primary key
    4. D.A filtered nonclustered index for each finance query
    Show answer and explanation

    Correct answer: B

    A nonclustered columnstore index on a rowstore table supports operational analytics (HTAP). The OLTP lookups keep using the B-tree, while the aggregations scan the compressed columnstore in batch mode.

    Replacing the clustered key with a clustered columnstore hurts single-row lookups. Filtered indexes help narrow seeks, not full aggregations. A memory-optimized copy adds data duplication and synchronization work without being designed for scans.

    From the lesson Design Tables, Data Types and Indexes.

  2. Question 2

    Auditors require cryptographic proof that rows in a payments table haven't been altered, including by database administrators, while normal updates must remain possible. Which table design meets the requirement?

    1. A.A system-versioned temporal table
    2. B.A memory-optimized table with SCHEMA_AND_DATA durability
    3. C.A trigger that copies every change to an audit table
    4. D.An updatable ledger table (LEDGER = ON), with database digests stored outside the database
    Show answer and explanation

    Correct answer: D

    Ledger tables chain row versions with cryptographic hashes. Database digests stored externally allow verification with sp_verify_database_ledger, so tampering by anyone, administrators included, can be detected. Updatable ledger tables still allow normal DML.

    Temporal tables keep history but give no cryptographic tamper evidence, and privileged users can change history. Memory-optimized tables are about performance. A trigger-based audit table can itself be altered.

    From the lesson Specialized Tables: In-Memory, Temporal, External, Ledger and Graph.

  3. Question 3

    The json column Payload contains an items array, for example [{"sku":"A1","qty":2},{"sku":"B7","qty":1}]. You need one result row per array element, with typed sku and qty columns, next to the OrderID. Which approach should you use?

    1. A.SELECT ... FOR JSON PATH
    2. B.CROSS APPLY OPENJSON(Payload, '$.items') WITH (sku varchar(20) '$.sku', qty int '$.qty')
    3. C.JSON_VALUE(Payload, '$.items')
    4. D.JSON_ARRAYAGG(Payload)
    Show answer and explanation

    Correct answer: B

    OPENJSON shreds a JSON array into rows, and its WITH clause defines typed columns from each element's properties. CROSS APPLY joins the rows to each order.

    JSON_VALUE returns scalars only, so it returns NULL for an array. JSON_ARRAYAGG builds JSON from rows, which is the opposite direction. FOR JSON turns a result set into JSON text.

    From the lesson JSON Columns, JSON Indexes and JSON Functions.

  4. Question 4

    Invoices and credit notes are stored in two different tables, but they must share one continuous document number series. The application needs the number before it inserts the row, so it can print it on a PDF. What should you use?

    1. A.An IDENTITY column in each table
    2. B.SCOPE_IDENTITY() after each insert
    3. C.MAX(DocumentNumber) + 1 read inside each insert
    4. D.A SEQUENCE object, with NEXT VALUE FOR used by both tables
    Show answer and explanation

    Correct answer: D

    A sequence is a standalone object that several tables can draw from, and NEXT VALUE FOR returns a value before any insert happens.

    IDENTITY columns create a separate series per table. SCOPE_IDENTITY returns a value only after the insert. MAX + 1 causes duplicates under concurrency.

    From the lesson Constraints, Sequences and Partitioning.

  5. Question 5

    A stored procedure updates three tables. If any statement fails, nothing must be left half done, the failure must be logged, and the caller must still receive the original error. Which pattern should you use?

    1. A.A CATCH block that commits whatever succeeded
    2. B.SET XACT_ABORT ON, a transaction in TRY, and in CATCH: ROLLBACK if @@TRANCOUNT > 0, log the error, then THROW
    3. C.A TRY block with no transaction, and PRINT ERROR_MESSAGE() in CATCH
    4. D.Three separate transactions, one per UPDATE
    Show answer and explanation

    Correct answer: B

    With XACT_ABORT ON and the work in one transaction, any runtime error dooms the whole transaction. CATCH rolls it back, writes the log entry, and THROW without arguments re-raises the original error to the caller.

    Without a transaction, earlier updates stay committed, and PRINT hides the error from the caller. Separate transactions allow partial results. Committing in CATCH saves exactly the partial work you must avoid.

    From the lesson Views, Functions, Stored Procedures, Triggers and Error Handling.