A stored procedure processes a JSON payload that contains multiple records. Some records may violate a check constraint when inserted. You need to insert all valid records and collect the error messages for invalid ones, without aborting the entire transaction. Which approach should you use?

Use a single INSERT...SELECT with OPENJSON and let the entire statement fail if any row violates a constraint

Use OPENJSON to shred the payload into a table variable, then use a TRY...CATCH around each insert to capture errors

Use a cursor to loop through the JSON array and use RAISERROR to log errors

Use JSON_MODIFY to remove invalid records before inserting

A web application sends JSON payloads that include a nested array of line items. You need to insert each line item into a relational table LineItems, while also storing the header information in an Orders table. Which T-SQL approach should you use?

Use JSON_MODIFY to add the line items to an existing table

Use JSON_ARRAY to create a new array and then use a cursor to insert each element

Use JSON_QUERY to get the array and then use STRING_SPLIT to break it into rows

Use OPENJSON to shred the JSON and insert into LineItems, and use JSON_VALUE to extract header fields for Orders

You are writing a script that processes a batch of records. If a specific error occurs (e.g., a foreign key violation), you want to raise a custom error message and roll back the entire transaction. Which error handling approach should you use?

Use THROW without a TRY...CATCH block to raise the error.

Use TRY...CATCH and inside the CATCH block use THROW to raise a custom error after rolling back the transaction.

Use RAISERROR before the transaction to check for potential violations.

Use a single TRY...CATCH block without a rollback and let the transaction continue.

Microsoft Certified:SQL AI Developer Associate

Domain 1Objective 3

Write Advanced T-SQL Code DP-800 Practice Questions (Page 3)

Part of the Design and develop database solutions domain, which accounts for 35–40% of the DP-800 exam. Microsoft does not publish an official question count, but from its 120-minute exam (~50–80 total, ~18–32 in this domain), expect 5–8 from this objective — we provide 31 practice questions to prepare you well beyond it. (estimate)

31questions here

7free pages

8concepts

35–40%of the exam

Questions 11–15

11expert · hard
A stored procedure processes a JSON payload that contains multiple records. Some records may violate a check constraint when inserted. You need to insert all valid records and collect the error messages for invalid ones, without aborting the entire transaction. Which approach should you use?
Select an answer first
12foundation · easy
You need to calculate a running total of sales amounts ordered by date, while still returning each individual sales row. Which type of function is designed to perform calculations across a set of rows related to the current row without collapsing the rows into a single output?
Select an answer first
13application · medium
A web application sends JSON payloads that include a nested array of line items. You need to insert each line item into a relational table LineItems, while also storing the header information in an Orders table. Which T-SQL approach should you use?
Select an answer first
14application · medium
You are writing a script that processes a batch of records. If a specific error occurs (e.g., a foreign key violation), you want to raise a custom error message and roll back the entire transaction. Which error handling approach should you use?
Select an answer first
15foundation · easy
Which clause is required to define the window (set of rows) for a window function in T-SQL?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-800

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-800” is a trademark of its owner, used for identification only.

Write Advanced T-SQL Code DP-800 Practice Questions (Page 3)

Questions 11–15

A stored procedure processes a JSON payload that contains multiple records. Some records may violate a check constraint when inserted. You need to insert all valid records and collect the error messages for invalid ones, without aborting the entire transaction. Which approach should you use?

You need to calculate a running total of sales amounts ordered by date, while still returning each individual sales row. Which type of function is designed to perform calculations across a set of rows related to the current row without collapsing the rows into a single output?

A web application sends JSON payloads that include a nested array of line items. You need to insert each line item into a relational table LineItems, while also storing the header information in an Orders table. Which T-SQL approach should you use?

You are writing a script that processes a batch of records. If a specific error occurs (e.g., a foreign key violation), you want to raise a custom error message and roll back the entire transaction. Which error handling approach should you use?

Which clause is required to define the window (set of rows) for a window function in T-SQL?

Keep studying — Design and develop database solutions