100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 210
A row-level change should record old and new salaries. The team selects: Use inserted and deleted pseudo-tables in AFTER UPDATE trigger. Which implementation action puts this design into practice?
- Join inserted to deleted on key and write before/after values
- Aggregate inserted rows by key and update once per group
- Read deleted pseudo-table and insert appropriate audit row
- Define SELECT with GROUP BY and typed return columns
Explanation
Correct Answer
A:Join inserted to deleted on key and write before/after values. This is the concrete setup for use inserted and deleted pseudo-tables in after update trigger, and update multiple rows and inspect both versions supplies a direct confirmation.
Incorrect Answers
B:Aggregate inserted rows by key and update once per group. This concerns a trigger maintains an aggregate after bulk inserts. Here, join inserted to deleted on key and write before/after values.
C:Read deleted pseudo-table and insert appropriate audit row. This concerns capturing an employee delete event. Here, join inserted to deleted on key and write before/after values.
D:Define SELECT with GROUP BY and typed return columns. This concerns a reporting function returns one aggregated row per team. Here, join inserted to deleted on key and write before/after values.
Question 211
A row-level change should record old and new salaries. After implementing the design, which check most directly validates the expected behavior?
- Force failure after first update and confirm rollback
- Delete two rows and confirm two events
- Update multiple rows and inspect both versions
- Force a runtime error and inspect durable rows
Explanation
Correct Answer
C:Update multiple rows and inspect both versions. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that trigger omitted deleted pseudo-table.
Incorrect Answers
A:Force failure after first update and confirm rollback. This concerns an inventory transfer debits and credits two records. Instead, update multiple rows and inspect both versions.
B:Delete two rows and confirm two events. This concerns capturing an employee delete event. Instead, update multiple rows and inspect both versions.
D:Force a runtime error and inspect durable rows. This concerns all steps to roll back on error. Instead, update multiple rows and inspect both versions.
Question 212
A row-level change should record old and new salaries. A test instead finds: Audit has new values but loses prior values. Which underlying issue best explains this finding?
- Trigger does not process deleted as a set
- The input contract used fragile delimited text
- Trigger omitted deleted pseudo-table
- The trigger treated inserted as a single row
Explanation
Correct Answer
C:Trigger omitted deleted pseudo-table. It accounts for the specific observed failure: audit has new values but loses prior values. The intended solution is use inserted and deleted pseudo-tables in after update trigger with the required setup.
Incorrect Answers
A:Trigger does not process deleted as a set. This concerns bulk delete produces one audit entry. Here, trigger omitted deleted pseudo-table.
B:The input contract used fragile delimited text. This is relevant when an overly long comma-separated string is parsed incorrectly. Here, trigger omitted deleted pseudo-table.
D:The trigger treated inserted as a single row. This concerns totals reflect only the last inserted row. Here, trigger omitted deleted pseudo-table.
Question 213
An audit procedure can throw a constraint exception midway. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- Enforce the rule with a constraint where expressible
- In CATCH, test XACT_STATE and rollback when required
- Use TRY...CATCH with XACT_STATE-aware rollback
- Use stable ORDER BY plus OFFSET/FETCH
- Use a stable view contract with least-privilege access
Explanation
Correct Answers
B:This is the concrete configuration of the specified design: in catch, test xact_state and rollback when required. The choice makes the implementation testable in the given workload.
C:Use TRY...CATCH with XACT_STATE-aware rollback. The implementation is in catch, test xact_state and rollback when required; success can be checked by inject a constraint failure and verify transaction state. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Enforce the rule with a constraint where expressible. This concerns rejecting a prohibited change consistently. Here, in CATCH, test XACT_STATE and rollback when required.
D:Use stable ORDER BY plus OFFSET/FETCH. This concerns exposing pagination to API callers. Here, in CATCH, test XACT_STATE and rollback when required.
E:Use a stable view contract with least-privilege access. This concerns mask administrative implementation complexity. Here, in CATCH, test XACT_STATE and rollback when required.
Question 214
An audit procedure can throw a constraint exception midway. The team selects: Use TRY...CATCH with XACT_STATE-aware rollback. Which implementation action puts this design into practice?
- Combine XACT_ABORT with TRY CATCH and rollback
- BEGIN TRAN and COMMIT only after both operations succeed
- Read deleted pseudo-table and insert appropriate audit row
- In CATCH, test XACT_STATE and rollback when required
Explanation
Correct Answer
D:In CATCH, test XACT_STATE and rollback when required. This is the concrete setup for use try...catch with xact_state-aware rollback, and inject a constraint failure and verify transaction state supplies a direct confirmation.
Incorrect Answers
A:Combine XACT_ABORT with TRY CATCH and rollback. This concerns all steps to roll back on error. Here, in CATCH, test XACT_STATE and rollback when required.
B:BEGIN TRAN and COMMIT only after both operations succeed. This concerns an inventory transfer debits and credits two records. Here, in CATCH, test XACT_STATE and rollback when required.
C:Read deleted pseudo-table and insert appropriate audit row. This concerns capturing an employee delete event. Here, in CATCH, test XACT_STATE and rollback when required.
Question 215
An audit procedure can throw a constraint exception midway. After implementing the design, which check most directly validates the expected behavior?
- Force failure after first update and confirm rollback
- Inject a constraint failure and verify transaction state
- Execute with multiple IDs and verify set-based operation
- Compare rowsets for eligible and ineligible customers
Explanation
Correct Answer
B:Inject a constraint failure and verify transaction state. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that catch failed to handle transaction state.
Incorrect Answers
A:Force failure after first update and confirm rollback. This concerns an inventory transfer debits and credits two records. Here, inject a constraint failure and verify transaction state.
C:Execute with multiple IDs and verify set-based operation. This concerns an application submits a list of ids for one batch operation. Here, inject a constraint failure and verify transaction state.
D:Compare rowsets for eligible and ineligible customers. This concerns returning a set of eligible orders for one customer. Here, inject a constraint failure and verify transaction state.
Question 216
An audit procedure can throw a constraint exception midway. A test instead finds: The connection retains an uncommittable transaction. Which underlying issue best explains this finding?
- DDL was written without idempotency or migration state
- Work was not enclosed in the intended atomic transaction
- CATCH failed to handle transaction state
- Transaction handling was incomplete
Explanation
Correct Answer
C:CATCH failed to handle transaction state. It accounts for the specific observed failure: the connection retains an uncommittable transaction. The intended solution is use try...catch with xact_state-aware rollback with the required setup.
Incorrect Answers
A:DDL was written without idempotency or migration state. This concerns second deployment fails on object already existing. The relevant SQL or system operation is CATCH failed to handle transaction state.
B:Work was not enclosed in the intended atomic transaction. This concerns half of a posting batch persists after failure. The relevant SQL or system operation is CATCH failed to handle transaction state.
D:Transaction handling was incomplete. This concerns early statements commit despite later failure. The relevant SQL or system operation is CATCH failed to handle transaction state.
Question 217
A stored procedure must return a status to caller. Which database design or engineering approach most directly meets this requirement?
- Use an inline table-valued function when suitable
- Wrap both updates in one explicit transaction
- Use stable ORDER BY plus OFFSET/FETCH
- Use output parameters or return status explicitly
Explanation
Correct Answer
D:Use output parameters or return status explicitly. The implementation is declare @status output and set it before completion; success can be checked by invoke with output and inspect returned status. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use an inline table-valued function when suitable. This concerns returning a set of eligible orders for one customer. Instead, use output parameters or return status explicitly.
B:Wrap both updates in one explicit transaction. This concerns an inventory transfer debits and credits two records. Instead, use output parameters or return status explicitly.
C:Use stable ORDER BY plus OFFSET/FETCH. This concerns exposing pagination to API callers. Instead, use output parameters or return status explicitly.
Question 218
A stored procedure must return a status to caller. The team selects: Use output parameters or return status explicitly. Which implementation action puts this design into practice?
- Accept page number and rows, compute OFFSET safely
- Declare @Status OUTPUT and set it before completion
- CREATE FUNCTION returning one parameterized SELECT
- Declare typed procedure parameters and avoid concatenated dynamic SQL
Explanation
Correct Answer
B:Declare @Status OUTPUT and set it before completion. This is the concrete setup for use output parameters or return status explicitly, and invoke with output and inspect returned status supplies a direct confirmation.
Incorrect Answers
A:Accept page number and rows, compute OFFSET safely. This concerns exposing pagination to API callers. Here, declare @Status OUTPUT and set it before completion.
C:CREATE FUNCTION returning one parameterized SELECT. This concerns returning a set of eligible orders for one customer. Here, declare @Status OUTPUT and set it before completion.
D:Declare typed procedure parameters and avoid concatenated dynamic SQL. This concerns a stored procedure reads user input in a search filter. Here, declare @Status OUTPUT and set it before completion.
Question 219
A stored procedure must return a status to caller. After implementing the design, which check most directly validates the expected behavior?
- Fetch adjacent pages and test for overlap
- Invoke with OUTPUT and inspect returned status
- Compare rowsets for eligible and ineligible customers
- Try unexpected sort identifier and confirm rejection
Explanation
Correct Answer
B:Invoke with OUTPUT and inspect returned status. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that parameter was not declared or bound as output.
Incorrect Answers
A:Fetch adjacent pages and test for overlap. This concerns exposing pagination to API callers. Here, invoke with OUTPUT and inspect returned status.
C:Compare rowsets for eligible and ineligible customers. This concerns returning a set of eligible orders for one customer. Here, invoke with OUTPUT and inspect returned status.
D:Try unexpected sort identifier and confirm rejection. This concerns a stored procedure filters by dynamically supplied sort direction. Here, invoke with OUTPUT and inspect returned status.
Question 220
A stored procedure must return a status to caller. A test instead finds: Caller never receives the computed result. Which underlying issue best explains this finding?
- Aggregation was applied after a many-to-many join
- The query lacks a deterministic ordering key
- Dynamic SQL identifiers are unvalidated
- Parameter was not declared or bound as OUTPUT
Explanation
Correct Answer
D:Parameter was not declared or bound as OUTPUT. It accounts for the specific observed failure: caller never receives the computed result. The intended solution is use output parameters or return status explicitly with the required setup.
Incorrect Answers
A:Aggregation was applied after a many-to-many join. This concerns totals are inflated by duplicated join rows. Here, parameter was not declared or bound as OUTPUT.
B:The query lacks a deterministic ordering key. This concerns rows repeat between pages unpredictably. Here, parameter was not declared or bound as OUTPUT.
C:Dynamic SQL identifiers are unvalidated. This concerns user input becomes executable order by SQL. Here, parameter was not declared or bound as OUTPUT.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests