100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 230
A database needs to capture an employee delete event. The team selects: Use AFTER DELETE trigger on targeted table. Which implementation action puts this design into practice?
- Read deleted pseudo-table and insert appropriate audit row
- Grant EXECUTE on procedures and restrict base-table DML
- CREATE AFTER UPDATE trigger handling inserted and deleted sets
- CREATE FUNCTION with typed scalar input and return
Explanation
Correct Answer
A:Read deleted pseudo-table and insert appropriate audit row. This is the concrete setup for use after delete trigger on targeted table, and delete two rows and confirm two events supplies a direct confirmation.
Incorrect Answers
B:Grant EXECUTE on procedures and restrict base-table DML. This concerns business operations but not table access. Instead, read deleted pseudo-table and insert appropriate audit row.
C:CREATE AFTER UPDATE trigger handling inserted and deleted sets. This concerns creating a history event. Instead, read deleted pseudo-table and insert appropriate audit row.
D:CREATE FUNCTION with typed scalar input and return. This concerns a finance analyst repeatedly calculates tax for one amount. Instead, read deleted pseudo-table and insert appropriate audit row.
Question 231
A database needs to capture an employee delete event. After implementing the design, which check most directly validates the expected behavior?
- Force a runtime error and inspect durable rows
- Delete two rows and confirm two events
- Attempt direct table writes from app role and confirm denial
- Update multiple employees and inspect history rows
Explanation
Correct Answer
B:Delete two rows and confirm two events. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that trigger does not process deleted as a set.
Incorrect Answers
A:Force a runtime error and inspect durable rows. This concerns all steps to roll back on error. Here, delete two rows and confirm two events.
C:Attempt direct table writes from app role and confirm denial. This concerns business operations but not table access. Here, delete two rows and confirm two events.
D:Update multiple employees and inspect history rows. This concerns creating a history event. Here, delete two rows and confirm two events.
Question 232
A database needs to capture an employee delete event. A test instead finds: Bulk delete produces one audit entry. Which underlying issue best explains this finding?
- Transaction handling was incomplete
- Work was not enclosed in the intended atomic transaction
- The trigger incorrectly assumes one affected row
- Trigger does not process deleted as a set
Explanation
Correct Answer
D:Trigger does not process deleted as a set. It accounts for the specific observed failure: bulk delete produces one audit entry. The intended solution is use after delete trigger on targeted table with the required setup.
Incorrect Answers
A:Transaction handling was incomplete. This concerns early statements commit despite later failure. Here, trigger does not process deleted as a set.
B:Work was not enclosed in the intended atomic transaction. This concerns half of a posting batch persists after failure. Here, trigger does not process deleted as a set.
C:The trigger incorrectly assumes one affected row. This concerns only one of several changed employees is audited. Here, trigger does not process deleted as a set.
Question 233
A stored procedure must expose pagination to API callers. Which database design or engineering approach most directly meets this requirement?
- Use module signing or EXECUTE AS with scoped permission
- Use stable ORDER BY plus OFFSET/FETCH
- Use a stored procedure with explicit transaction behavior
- Use a stable view contract with least-privilege access
Explanation
Correct Answer
B:Use stable ORDER BY plus OFFSET/FETCH. The implementation is accept page number and rows, compute offset safely; success can be checked by fetch adjacent pages and test for overlap. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use module signing or EXECUTE AS with scoped permission. This concerns securely run a privileged operation for app callers. Instead, use stable ORDER BY plus OFFSET/FETCH.
C:Use a stored procedure with explicit transaction behavior. This concerns a monthly closing process performs several SQL steps as one operation. Instead, use stable ORDER BY plus OFFSET/FETCH.
D:Use a stable view contract with least-privilege access. This concerns mask administrative implementation complexity. Instead, use stable ORDER BY plus OFFSET/FETCH.
Question 234
A stored procedure must expose pagination to API callers. The team selects: Use stable ORDER BY plus OFFSET/FETCH. Which implementation action puts this design into practice?
- Declare typed procedure parameters and avoid concatenated dynamic SQL
- Accept page number and rows, compute OFFSET safely
- Aggregate inserted rows by key and update once per group
- Declare @Status OUTPUT and set it before completion
Explanation
Correct Answer
B:Accept page number and rows, compute OFFSET safely. This is the concrete setup for use stable order by plus offset/fetch, and fetch adjacent pages and test for overlap 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. Here, accept page number and rows, compute OFFSET safely.
C:Aggregate inserted rows by key and update once per group. This concerns a trigger maintains an aggregate after bulk inserts. Here, accept page number and rows, compute OFFSET safely.
D:Declare @Status OUTPUT and set it before completion. This concerns returning a status to caller. Here, accept page number and rows, compute OFFSET safely.
Question 235
A stored procedure must expose pagination to API callers. After implementing the design, which check most directly validates the expected behavior?
- Invoke with OUTPUT and inspect returned status
- Fetch adjacent pages and test for overlap
- Force a runtime error and inspect durable rows
- Test quote-containing input and check plan parameters
Explanation
Correct Answer
B:Fetch adjacent pages and test for overlap. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the query lacks a deterministic ordering key.
Incorrect Answers
A:Invoke with OUTPUT and inspect returned status. This concerns returning a status to caller. Here, fetch adjacent pages and test for overlap.
C:Force a runtime error and inspect durable rows. This concerns all steps to roll back on error. Here, fetch adjacent pages and test for overlap.
D:Test quote-containing input and check plan parameters. This concerns a stored procedure reads user input in a search filter. Here, fetch adjacent pages and test for overlap.
Question 236
A stored procedure must expose pagination to API callers. A test instead finds: Rows repeat between pages unpredictably. Which underlying issue best explains this finding?
- Trigger does not process deleted as a set
- Dynamic SQL identifiers are unvalidated
- The query lacks a deterministic ordering key
- Parameter was not declared or bound as OUTPUT
Explanation
Correct Answer
C:The query lacks a deterministic ordering key. It accounts for the specific observed failure: rows repeat between pages unpredictably. The intended solution is use stable order by plus offset/fetch with the required setup.
Incorrect Answers
A:Trigger does not process deleted as a set. This concerns bulk delete produces one audit entry. Here, the query lacks a deterministic ordering key.
B:Dynamic SQL identifiers are unvalidated. This concerns user input becomes executable order by SQL. Here, the query lacks a deterministic ordering key.
D:Parameter was not declared or bound as OUTPUT. This concerns caller never receives the computed result. Here, the query lacks a deterministic ordering key.
Question 237
An application submits a list of IDs for one batch operation. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- Use an inline table-valued function when suitable
- Use parameterized SQL to avoid injection
- Use a table-valued parameter to pass structured rows
- Use a stored procedure with explicit transaction behavior
- Create user-defined table type and READONLY proc parameter
Explanation
Correct Answers
C:Use a table-valued parameter to pass structured rows. The implementation is create user-defined table type and readonly proc parameter; success can be checked by execute with multiple ids and verify set-based operation. The method addresses the stated data and workload constraint.
E:This is the concrete configuration of the specified design: create user-defined table type and readonly proc parameter. The choice makes the implementation testable in the given workload.
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 table-valued parameter to pass structured rows.
B:Use parameterized SQL to avoid injection. This concerns a stored procedure reads user input in a search filter. Instead, use a table-valued parameter to pass structured rows.
D:Use a stored procedure with explicit transaction behavior. This concerns a monthly closing process performs several SQL steps as one operation. Instead, use a table-valued parameter to pass structured rows.
Question 238
An application submits a list of IDs for one batch operation. The team selects: Use a table-valued parameter to pass structured rows. Which implementation action puts this design into practice?
- Validate choice and use controlled CASE or QUOTENAME pattern
- Create user-defined table type and READONLY proc parameter
- CREATE PROCEDURE and manage transactions and errors
- Grant EXECUTE on procedures and restrict base-table DML
Explanation
Correct Answer
B:Create user-defined table type and READONLY proc parameter. This is the concrete setup for use a table-valued parameter to pass structured rows, and execute with multiple ids and verify set-based operation supplies a direct confirmation.
Incorrect Answers
A:Validate choice and use controlled CASE or QUOTENAME pattern. This concerns a stored procedure filters by dynamically supplied sort direction. Instead, create user-defined table type and READONLY proc parameter.
C:CREATE PROCEDURE and manage transactions and errors. This concerns a monthly closing process performs several SQL steps as one operation. Instead, create user-defined table type and READONLY proc parameter.
D:Grant EXECUTE on procedures and restrict base-table DML. This concerns business operations but not table access. Instead, create user-defined table type and READONLY proc parameter.
Question 239
An application submits a list of IDs for one batch operation. After implementing the design, which check most directly validates the expected behavior?
- Invoke with OUTPUT and inspect returned status
- Execute with multiple IDs and verify set-based operation
- Check caller can execute proc but cannot modify unrelated tables
- Compare output across two department IDs
Explanation
Correct Answer
B:Execute with multiple IDs and verify set-based operation. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the input contract used fragile delimited text.
Incorrect Answers
A:Invoke with OUTPUT and inspect returned status. This concerns returning a status to caller. Instead, execute with multiple IDs and verify set-based operation.
C:Check caller can execute proc but cannot modify unrelated tables. This concerns securely run a privileged operation for app callers. Instead, execute with multiple IDs and verify set-based operation.
D:Compare output across two department IDs. This concerns a query returns top-priced products for a department. Instead, execute with multiple IDs and verify set-based operation.
Question 240
An application submits a list of IDs for one batch operation. A test instead finds: An overly long comma-separated string is parsed incorrectly. Which underlying issue best explains this finding?
- Untrusted data was concatenated into executable SQL
- Business rule exists only in one application tier
- Operations were not atomic
- The input contract used fragile delimited text
Explanation
Correct Answer
D:The input contract used fragile delimited text. It accounts for the specific observed failure: an overly long comma-separated string is parsed incorrectly. The intended solution is use a table-valued parameter to pass structured rows with the required setup.
Incorrect Answers
A:Untrusted data was concatenated into executable SQL. This concerns a string input changes SQL command structure. Here, the input contract used fragile delimited text.
B:Business rule exists only in one application tier. This concerns an alternate application bypasses the validation. Here, the input contract used fragile delimited text.
C:Operations were not atomic. This concerns total stock changes after interrupted transfer. Here, the input contract used fragile delimited text.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests