100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 170
An API must return a set of eligible orders for one customer. The team selects: Use an inline table-valued function when suitable. Which implementation action puts this design into practice?
- CREATE FUNCTION returning one parameterized SELECT
- CREATE FUNCTION with typed scalar input and return
- Configure a controlled execution context and avoid blanket role grants
- Define SELECT with GROUP BY and typed return columns
Explanation
Correct Answer
A:CREATE FUNCTION returning one parameterized SELECT. This is the concrete setup for use an inline table-valued function when suitable, and compare rowsets for eligible and ineligible customers supplies a direct confirmation.
Incorrect Answers
B:CREATE FUNCTION with typed scalar input and return. This concerns a finance analyst repeatedly calculates tax for one amount. The relevant SQL or system operation is CREATE FUNCTION returning one parameterized SELECT.
C:Configure a controlled execution context and avoid blanket role grants. This concerns securely run a privileged operation for app callers. The relevant SQL or system operation is CREATE FUNCTION returning one parameterized SELECT.
D:Define SELECT with GROUP BY and typed return columns. This concerns a reporting function returns one aggregated row per team. The relevant SQL or system operation is CREATE FUNCTION returning one parameterized SELECT.
Question 171
An API must return a set of eligible orders for one customer. After implementing the design, which check most directly validates the expected behavior?
- Compare rowsets for eligible and ineligible customers
- Delete two rows and confirm two events
- Invoke with OUTPUT and inspect returned status
- Fetch adjacent pages and test for overlap
Explanation
Correct Answer
A:Compare rowsets for eligible and ineligible customers. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that wrong programmable object type was chosen.
Incorrect Answers
B:Delete two rows and confirm two events. This concerns capturing an employee delete event. Instead, compare rowsets for eligible and ineligible customers.
C:Invoke with OUTPUT and inspect returned status. This concerns returning a status to caller. Instead, compare rowsets for eligible and ineligible customers.
D:Fetch adjacent pages and test for overlap. This concerns exposing pagination to API callers. Instead, compare rowsets for eligible and ineligible customers.
Question 172
An API must return a set of eligible orders for one customer. A test instead finds: A scalar function is used where a rowset is required. Which underlying issue best explains this finding?
- Parameter was not declared or bound as OUTPUT
- Wrong programmable object type was chosen
- Trigger does not process deleted as a set
- The trigger treated inserted as a single row
Explanation
Correct Answer
B:Wrong programmable object type was chosen. It accounts for the specific observed failure: a scalar function is used where a rowset is required. The intended solution is use an inline table-valued function when suitable with the required setup.
Incorrect Answers
A:Parameter was not declared or bound as OUTPUT. This concerns caller never receives the computed result. Here, wrong programmable object type was chosen.
C:Trigger does not process deleted as a set. This concerns bulk delete produces one audit entry. Here, wrong programmable object type was chosen.
D:The trigger treated inserted as a single row. This concerns totals reflect only the last inserted row. Here, wrong programmable object type was chosen.
Question 173
A monthly closing process performs several SQL steps as one operation. Which database design or engineering approach most directly meets this requirement?
- Use an inline table-valued function when suitable
- Use a stored procedure with explicit transaction behavior
- Wrap both updates in one explicit transaction
- Use parameterized SQL to avoid injection
Explanation
Correct Answer
B:Use a stored procedure with explicit transaction behavior. The implementation is create procedure and manage transactions and errors; success can be checked by run success and rollback integration tests. 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 a stored procedure with explicit transaction behavior.
C:Wrap both updates in one explicit transaction. This concerns an inventory transfer debits and credits two records. Instead, use a stored procedure with explicit transaction behavior.
D:Use parameterized SQL to avoid injection. This concerns a stored procedure reads user input in a search filter. Instead, use a stored procedure with explicit transaction behavior.
Question 174
A monthly closing process performs several SQL steps as one operation. The team selects: Use a stored procedure with explicit transaction behavior. Which implementation action puts this design into practice?
- Declare typed procedure parameters and avoid concatenated dynamic SQL
- Create user-defined table type and READONLY proc parameter
- Expose only approved columns through a named VIEW
- CREATE PROCEDURE and manage transactions and errors
Explanation
Correct Answer
D:CREATE PROCEDURE and manage transactions and errors. This is the concrete setup for use a stored procedure with explicit transaction behavior, and run success and rollback integration tests supplies a direct confirmation.
Incorrect Answers
A:Declare typed procedure parameters and avoid concatenated dynamic SQL. This concerns a stored procedure reads user input in a search filter. The relevant SQL or system operation is CREATE PROCEDURE and manage transactions and errors.
B:Create user-defined table type and READONLY proc parameter. This concerns an application submits a list of ids for one batch operation. The relevant SQL or system operation is CREATE PROCEDURE and manage transactions and errors.
C:Expose only approved columns through a named VIEW. This concerns mask administrative implementation complexity. The relevant SQL or system operation is CREATE PROCEDURE and manage transactions and errors.
Question 175
A monthly closing process performs several SQL steps as one operation. After implementing the design, which check most directly validates the expected behavior?
- Execute with multiple IDs and verify set-based operation
- Run success and rollback integration tests
- Update multiple employees and inspect history rows
- Check caller can execute proc but cannot modify unrelated tables
Explanation
Correct Answer
B:Run success and rollback integration tests. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that work was not enclosed in the intended atomic transaction.
Incorrect Answers
A:Execute with multiple IDs and verify set-based operation. This concerns an application submits a list of ids for one batch operation. Instead, run success and rollback integration tests.
C:Update multiple employees and inspect history rows. This concerns creating a history event. Instead, run success and rollback integration tests.
D:Check caller can execute proc but cannot modify unrelated tables. This concerns securely run a privileged operation for app callers. Instead, run success and rollback integration tests.
Question 176
A monthly closing process performs several SQL steps as one operation. A test instead finds: Half of a posting batch persists after failure. Which underlying issue best explains this finding?
- Transaction handling was incomplete
- Work was not enclosed in the intended atomic transaction
- The view uses fragile SELECT * or outdated assumptions
- Operations were not atomic
Explanation
Correct Answer
B:Work was not enclosed in the intended atomic transaction. It accounts for the specific observed failure: half of a posting batch persists after failure. The intended solution is use a stored procedure with explicit transaction behavior with the required setup.
Incorrect Answers
A:Transaction handling was incomplete. This concerns early statements commit despite later failure. Here, work was not enclosed in the intended atomic transaction.
C:The view uses fragile SELECT * or outdated assumptions. This concerns reports disagree after underlying schema changes. Here, work was not enclosed in the intended atomic transaction.
D:Operations were not atomic. This concerns total stock changes after interrupted transfer. Here, work was not enclosed in the intended atomic transaction.
Question 177
A change to an employee row must create a history event. Which database design or engineering approach most directly meets this requirement?
- Use stored procedures as the approved access boundary
- Use inline TVF or grouped view depending on parameters
- Use AFTER DELETE trigger on targeted table
- Use a DML trigger only for the specified side effect
Explanation
Correct Answer
D:Use a DML trigger only for the specified side effect. The implementation is create after update trigger handling inserted and deleted sets; success can be checked by update multiple employees and inspect history rows. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use stored procedures as the approved access boundary. This concerns business operations but not table access. Instead, use a DML trigger only for the specified side effect.
B:Use inline TVF or grouped view depending on parameters. This concerns a reporting function returns one aggregated row per team. Instead, use a DML trigger only for the specified side effect.
C:Use AFTER DELETE trigger on targeted table. This concerns capturing an employee delete event. Instead, use a DML trigger only for the specified side effect.
Question 178
A change to an employee row must create a history event. The team selects: Use a DML trigger only for the specified side effect. Which implementation action puts this design into practice?
- Aggregate inserted rows by key and update once per group
- Read deleted pseudo-table and insert appropriate audit row
- Expose only approved columns through a named VIEW
- CREATE AFTER UPDATE trigger handling inserted and deleted sets
Explanation
Correct Answer
D:CREATE AFTER UPDATE trigger handling inserted and deleted sets. This is the concrete setup for use a dml trigger only for the specified side effect, and update multiple employees and inspect history rows supplies a direct confirmation.
Incorrect Answers
A:Aggregate inserted rows by key and update once per group. This concerns a trigger maintains an aggregate after bulk inserts. The relevant SQL or system operation is CREATE AFTER UPDATE trigger handling inserted and deleted sets.
B:Read deleted pseudo-table and insert appropriate audit row. This concerns capturing an employee delete event. The relevant SQL or system operation is CREATE AFTER UPDATE trigger handling inserted and deleted sets.
C:Expose only approved columns through a named VIEW. This concerns mask administrative implementation complexity. The relevant SQL or system operation is CREATE AFTER UPDATE trigger handling inserted and deleted sets.
Question 179
A change to an employee row must create a history event. After implementing the design, which check most directly validates the expected behavior?
- Test quote-containing input and check plan parameters
- Delete two rows and confirm two events
- Update multiple employees and inspect history rows
- Force a runtime error and inspect durable rows
Explanation
Correct Answer
C:Update multiple employees and inspect history rows. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the trigger incorrectly assumes one affected row.
Incorrect Answers
A:Test quote-containing input and check plan parameters. This concerns a stored procedure reads user input in a search filter. Instead, update multiple employees and inspect history rows.
B:Delete two rows and confirm two events. This concerns capturing an employee delete event. Instead, update multiple employees and inspect history rows.
D:Force a runtime error and inspect durable rows. This concerns all steps to roll back on error. Instead, update multiple employees and inspect history rows.
Question 180
A change to an employee row must create a history event. A test instead finds: Only one of several changed employees is audited. Which underlying issue best explains this finding?
- Independent copied tax expressions diverge
- The trigger incorrectly assumes one affected row
- Trigger does not process deleted as a set
- The trigger treated inserted as a single row
Explanation
Correct Answer
B:The trigger incorrectly assumes one affected row. It accounts for the specific observed failure: only one of several changed employees is audited. The intended solution is use a dml trigger only for the specified side effect with the required setup.
Incorrect Answers
A:Independent copied tax expressions diverge. This concerns reports disagree on rounding behavior. Here, the trigger incorrectly assumes one affected row.
C:Trigger does not process deleted as a set. This concerns bulk delete produces one audit entry. Here, the trigger incorrectly assumes one affected row.
D:The trigger treated inserted as a single row. This concerns totals reflect only the last inserted row. Here, the trigger incorrectly assumes one affected row.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests