Microsoft DP-800 Exam Dumps, Practice Test Questions

100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!

Microsoft DP-800  Premium File
$54.99
$49.99

DP-800 Premium File

  • Premium File: 101 Questions & Answers. Last update: Nov 9, 2026
  • Latest Questions
  • 100% Accurate Answers
  • Fast Exam Updates

DP-800 Premium File

Microsoft DP-800  Premium File
  • Premium File: 101 Questions & Answers. Last update: Nov 9, 2026
  • Latest Questions
  • 100% Accurate Answers
  • Fast Exam Updates
$54.99
$49.99

Microsoft DP-800 Practice Test 34

Question 330

A transaction routine needs to rethrow original error after cleanup. The team selects: Use THROW in CATCH after rollback. Which implementation action puts this design into practice?

  1. SUM(Amount) OVER(ORDER BY SaleDate ROWS UNBOUNDED PRECEDING)
  2. Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID)
  3. Write WITH cte AS (anchor UNION ALL recursive query)
  4. BEGIN CATCH ROLLBACK; THROW; END CATCH

Explanation

Correct Answer

D:

BEGIN CATCH ROLLBACK; THROW; END CATCH. This is the concrete setup for use throw in catch after rollback, and test caller receives sql error after failure supplies a direct confirmation.

Incorrect Answers

A:

SUM(Amount) OVER(ORDER BY SaleDate ROWS UNBOUNDED PRECEDING). This concerns cumulative revenue by day. The relevant SQL or system operation is BEGIN CATCH ROLLBACK; THROW; END CATCH.

B:

Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID). This concerns top three sales orders per salesperson. The relevant SQL or system operation is BEGIN CATCH ROLLBACK; THROW; END CATCH.

C:

Write WITH cte AS (anchor UNION ALL recursive query). This concerns a hierarchy report expands an organization's manager chain. The relevant SQL or system operation is BEGIN CATCH ROLLBACK; THROW; END CATCH.

Question 331

A transaction routine needs to rethrow original error after cleanup. After implementing the design, which check most directly validates the expected behavior?

  1. Parse output and verify key types
  2. Check each group has row numbers 1 to 3
  3. Test caller receives SQL error after failure
  4. Test customers with no payments retain outer row

Explanation

Correct Answer

C:

Test caller receives SQL error after failure. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that error was swallowed in a catch block.

Incorrect Answers

A:

Parse output and verify key types. This concerns a developer creates json object from named SQL expressions. Instead, test caller receives SQL error after failure.

B:

Check each group has row numbers 1 to 3. This concerns top three sales orders per salesperson. Instead, test caller receives SQL error after failure.

D:

Test customers with no payments retain outer row. This concerns each order with customer's most recent payment. Instead, test caller receives SQL error after failure.

Question 332

A transaction routine needs to rethrow original error after cleanup. A test instead finds: App treats an aborted transaction as success. Which underlying issue best explains this finding?

  1. Window ordering lacks a deterministic tie-breaker
  2. Error was swallowed in a CATCH block
  3. GROUP BY collapsed the original rows
  4. Raw edit distance confused with normalized similarity

Explanation

Correct Answer

B:

Error was swallowed in a CATCH block. It accounts for the specific observed failure: app treats an aborted transaction as success. The intended solution is use throw in catch after rollback with the required setup.

Incorrect Answers

A:

Window ordering lacks a deterministic tie-breaker. This concerns top ranks drift across executions on ties. Here, error was swallowed in a CATCH block.

C:

GROUP BY collapsed the original rows. This concerns group detail disappears from report. Here, error was swallowed in a CATCH block.

D:

Raw edit distance confused with normalized similarity. This concerns distance scale mistakenly treated as 0 to 100 score. Here, error was swallowed in a CATCH block.

Question 333

A JSON array aggregation produces product IDs per customer. Which database design or engineering approach most directly meets this requirement?

  1. Use JSON_ARRAYAGG where supported
  2. Use OPENJSON WITH to shred the array
  3. Use ROW_NUMBER by customer and descending OrderDate
  4. Use JARO_WINKLER_DISTANCE on supported edition

Explanation

Correct Answer

A:

Use JSON_ARRAYAGG where supported. The implementation is group by customer and call json_arrayagg(productid); success can be checked by validate output array content for sample orders. The method addresses the stated data and workload constraint.

Incorrect Answers

B:

Use OPENJSON WITH to shred the array. This concerns a json payload includes an order array to become SQL rows. Instead, use JSON_ARRAYAGG where supported.

C:

Use ROW_NUMBER by customer and descending OrderDate. This concerns pick latest order per customer. Instead, use JSON_ARRAYAGG where supported.

D:

Use JARO_WINKLER_DISTANCE on supported edition. This concerns typo-resistant matching on customer surnames with prefix-sensitive scores. Instead, use JSON_ARRAYAGG where supported.

Question 334

A JSON array aggregation produces product IDs per customer. The team selects: Use JSON_ARRAYAGG where supported. Which implementation action puts this design into practice?

  1. JSON_VALUE(Payload,'$.customer.id') with appropriate cast
  2. Group by customer and call JSON_ARRAYAGG(ProductID)
  3. OPENJSON(@payload,'$.orders') WITH typed properties
  4. PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Duration) OVER(PARTITION BY Region)

Explanation

Correct Answer

B:

Group by customer and call JSON_ARRAYAGG(ProductID). This is the concrete setup for use json_arrayagg where supported, and validate output array content for sample orders supplies a direct confirmation.

Incorrect Answers

A:

JSON_VALUE(Payload,'$.customer.id') with appropriate cast. This concerns reading one scalar value from json document. Here, group by customer and call JSON_ARRAYAGG(ProductID).

C:

OPENJSON(@payload,'$.orders') WITH typed properties. This concerns a json payload includes an order array to become SQL rows. Here, group by customer and call JSON_ARRAYAGG(ProductID).

D:

PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Duration) OVER(PARTITION BY Region). This concerns returning median of response times by region. Here, group by customer and call JSON_ARRAYAGG(ProductID).

Question 335

A JSON array aggregation produces product IDs per customer. After implementing the design, which check most directly validates the expected behavior?

  1. Validate serialized output against API contract
  2. Validate output array content for sample orders
  3. Compare resulting rows and typed amounts
  4. Test extraction against known example payload

Explanation

Correct Answer

B:

Validate output array content for sample orders. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that string_agg used instead of required json array.

Incorrect Answers

A:

Validate serialized output against API contract. This concerns a service sends SQL query results as nested json objects. Here, validate output array content for sample orders.

C:

Compare resulting rows and typed amounts. This concerns a json payload includes an order array to become SQL rows. Here, validate output array content for sample orders.

D:

Test extraction against known example payload. This concerns reading one scalar value from json document. Here, validate output array content for sample orders.

Question 336

A JSON array aggregation produces product IDs per customer. A test instead finds: Aggregated values become comma-separated plain text. Which underlying issue best explains this finding?

  1. Similarity was interpreted without a validated threshold
  2. JSON path is wrong or payload shape differs
  3. JSON path or case-sensitive property names do not match
  4. STRING_AGG used instead of required JSON array

Explanation

Correct Answer

D:

STRING_AGG used instead of required JSON array. It accounts for the specific observed failure: aggregated values become comma-separated plain text. The intended solution is use json_arrayagg where supported with the required setup.

Incorrect Answers

A:

Similarity was interpreted without a validated threshold. This concerns names with same prefix falsely merge. The relevant SQL or system operation is STRING_AGG used instead of required JSON array.

B:

JSON path is wrong or payload shape differs. This concerns returned value is null for present nested field. The relevant SQL or system operation is STRING_AGG used instead of required JSON array.

C:

JSON path or case-sensitive property names do not match. This concerns nested array values are null in projection. The relevant SQL or system operation is STRING_AGG used instead of required JSON array.

Question 337

A text cleansing query replaces matching substrings using regex. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)

  1. Use REGEXP_SUBSTR where supported
  2. Use JARO_WINKLER_DISTANCE on supported edition
  3. Specify tested pattern and replacement with REGEXP_REPLACE
  4. Use recursive CTE with explicit anchor and recursive member
  5. Use REGEXP_REPLACE on supported engine

Explanation

Correct Answers

C:

This is the concrete configuration of the specified design: specify tested pattern and replacement with regexp_replace. The choice makes the implementation testable in the given workload.

E:

Use REGEXP_REPLACE on supported engine. The implementation is specify tested pattern and replacement with regexp_replace; success can be checked by compare expected cleaned output for complex cases. The method addresses the stated data and workload constraint.

Incorrect Answers

A:

Use REGEXP_SUBSTR where supported. This concerns a log-cleaning query extracts the first matched token. Here, specify tested pattern and replacement with REGEXP_REPLACE.

B:

Use JARO_WINKLER_DISTANCE on supported edition. This concerns typo-resistant matching on customer surnames with prefix-sensitive scores. Here, specify tested pattern and replacement with REGEXP_REPLACE.

D:

Use recursive CTE with explicit anchor and recursive member. This concerns a hierarchy report expands an organization's manager chain. Here, specify tested pattern and replacement with REGEXP_REPLACE.

Question 338

A text cleansing query replaces matching substrings using regex. The team selects: Use REGEXP_REPLACE on supported engine. Which implementation action puts this design into practice?

  1. Specify tested pattern and replacement with REGEXP_REPLACE
  2. BEGIN CATCH ROLLBACK; THROW; END CATCH
  3. REGEXP_LIKE(ColumnName, approved_pattern) in WHERE
  4. Call REGEXP_SUBSTR with tested expression

Explanation

Correct Answer

A:

Specify tested pattern and replacement with REGEXP_REPLACE. This is the concrete setup for use regexp_replace on supported engine, and compare expected cleaned output for complex cases supplies a direct confirmation.

Incorrect Answers

B:

BEGIN CATCH ROLLBACK; THROW; END CATCH. This concerns rethrow original error after cleanup. Here, specify tested pattern and replacement with REGEXP_REPLACE.

C:

REGEXP_LIKE(ColumnName, approved_pattern) in WHERE. This concerns identifying strings matching a supported regular expression. Here, specify tested pattern and replacement with REGEXP_REPLACE.

D:

Call REGEXP_SUBSTR with tested expression. This concerns a log-cleaning query extracts the first matched token. Here, specify tested pattern and replacement with REGEXP_REPLACE.

Question 339

A text cleansing query replaces matching substrings using regex. After implementing the design, which check most directly validates the expected behavior?

  1. Compare expected cleaned output for complex cases
  2. Test customers with no payments retain outer row
  3. Compare extraction with known log text
  4. Test expected two-hop results

Explanation

Correct Answer

A:

Compare expected cleaned output for complex cases. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that replace used where a pattern was required.

Incorrect Answers

B:

Test customers with no payments retain outer row. This concerns each order with customer's most recent payment. Instead, compare expected cleaned output for complex cases.

C:

Compare extraction with known log text. This concerns a log-cleaning query extracts the first matched token. Instead, compare expected cleaned output for complex cases.

D:

Test expected two-hop results. This concerns a graph database query asks for friends of friends. Instead, compare expected cleaned output for complex cases.

Question 340

A text cleansing query replaces matching substrings using regex. A test instead finds: Only literal substring is replaced. Which underlying issue best explains this finding?

  1. Pattern matching lacks required regex structure
  2. CROSS APPLY used where OUTER APPLY was required
  3. JSON path or case-sensitive property names do not match
  4. REPLACE used where a pattern was required

Explanation

Correct Answer

D:

REPLACE used where a pattern was required. It accounts for the specific observed failure: only literal substring is replaced. The intended solution is use regexp_replace on supported engine with the required setup.

Incorrect Answers

A:

Pattern matching lacks required regex structure. This concerns ordinary like incorrectly accepts malformed ids. The relevant SQL or system operation is REPLACE used where a pattern was required.

B:

CROSS APPLY used where OUTER APPLY was required. This concerns orders vanish when payment row is absent. The relevant SQL or system operation is REPLACE used where a pattern was required.

C:

JSON path or case-sensitive property names do not match. This concerns nested array values are null in projection. The relevant SQL or system operation is REPLACE used where a pattern was required.

Developing AI-Enabled Database Solutions (DP-800) Practice Tests

UP

SPECIAL OFFER: GET 10% OFF

This is ONE TIME OFFER

ExamSnap Discount Offer
Enter Your Email Address to Receive Your 10% Off Discount Code

A confirmation link will be sent to this email address to verify your login. *We value your privacy. We will not rent or sell your email address.

Download Free Demo of VCE Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.

Simply submit your e-mail address below to get started with our interactive software demo of your free trial.

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.