100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
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?
- SUM(Amount) OVER(ORDER BY SaleDate ROWS UNBOUNDED PRECEDING)
- Define ROW_NUMBER() OVER(PARTITION BY SalespersonID ORDER BY Amount DESC, OrderID)
- Write WITH cte AS (anchor UNION ALL recursive query)
- 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?
- Parse output and verify key types
- Check each group has row numbers 1 to 3
- Test caller receives SQL error after failure
- 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?
- Window ordering lacks a deterministic tie-breaker
- Error was swallowed in a CATCH block
- GROUP BY collapsed the original rows
- 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?
- Use JSON_ARRAYAGG where supported
- Use OPENJSON WITH to shred the array
- Use ROW_NUMBER by customer and descending OrderDate
- 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?
- JSON_VALUE(Payload,'$.customer.id') with appropriate cast
- Group by customer and call JSON_ARRAYAGG(ProductID)
- OPENJSON(@payload,'$.orders') WITH typed properties
- 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?
- Validate serialized output against API contract
- Validate output array content for sample orders
- Compare resulting rows and typed amounts
- 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?
- Similarity was interpreted without a validated threshold
- JSON path is wrong or payload shape differs
- JSON path or case-sensitive property names do not match
- 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.)
- Use REGEXP_SUBSTR where supported
- Use JARO_WINKLER_DISTANCE on supported edition
- Specify tested pattern and replacement with REGEXP_REPLACE
- Use recursive CTE with explicit anchor and recursive member
- 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?
- Specify tested pattern and replacement with REGEXP_REPLACE
- BEGIN CATCH ROLLBACK; THROW; END CATCH
- REGEXP_LIKE(ColumnName, approved_pattern) in WHERE
- 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?
- Compare expected cleaned output for complex cases
- Test customers with no payments retain outer row
- Compare extraction with known log text
- 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?
- Pattern matching lacks required regex structure
- CROSS APPLY used where OUTER APPLY was required
- JSON path or case-sensitive property names do not match
- 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