Question 90 A checkout API submits dozens of order lines to one validation procedure. Which technical choice addresses this need? Use schema-qualified object names in routines Use a table-valued parameter to pass an approved set of rows to a procedure Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter Grant EXECUTE on an approved procedure instead of broad base-table writes Explanation Correct Answer B:This implementation fits because it safely passes multiple structured rows in one call. That addresses the stated technical constraint. Incorrect Answers A:This option removes ambiguity and supports predictable name resolution. This does not meet the need for a method that safely passes multiple structured rows in one call. C:This option exposes a relational expression that is easier for the optimizer to reason about. This does not meet the need for a method that safely passes multiple structured rows in one call. D:This choice constrains the exposed write operation to reviewed logic. This does not meet the need for a method that safely passes multiple structured rows in one call.
Question 91 A dashboard experiences poor estimates after joining a multi-statement function returning many orders. Which implementation is the best fit? Use a table-valued parameter to pass an approved set of rows to a procedure Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter Use EXECUTE AS with a reviewed execution context only where necessary Avoid side-effecting trigger recursion by guarding the trigger path Explanation Correct Answer B:This approach exposes a relational expression that is easier for the optimizer to reason about. Incorrect Answers A:This choice safely passes multiple structured rows in one call. This does not meet the need for a method that exposes a relational expression that is easier for the optimizer to reason about. C:This choice scopes privilege escalation through explicitly defined module security. This does not meet the need for a method that exposes a relational expression that is easier for the optimizer to reason about. D:That technique prevents unintended repeated trigger execution. This does not meet the need for a method that exposes a relational expression that is easier for the optimizer to reason about.
Question 92 A partner service must invoke a vetted payment workflow without general UPDATE permission on its tables. What should the engineer choose? Use EXECUTE AS with a reviewed execution context only where necessary Grant EXECUTE on an approved procedure instead of broad base-table writes Use a table-valued parameter to pass an approved set of rows to a procedure Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter Explanation Correct Answer B:Grant EXECUTE on an approved procedure instead of broad base-table writes is the appropriate choice: it constrains the exposed write operation to reviewed logic. The deployment should be tested with representative data. Incorrect Answers A:That technique scopes privilege escalation through explicitly defined module security. This does not meet the need for a method that constrains the exposed write operation to reviewed logic. C:That technique safely passes multiple structured rows in one call. This does not meet the need for a method that constrains the exposed write operation to reviewed logic. D:This choice exposes a relational expression that is easier for the optimizer to reason about. This does not meet the need for a method that constrains the exposed write operation to reviewed logic.
Question 93 A writable reporting view needs its update routed to underlying normalized tables. Which implementation is the best fit? Use a stored procedure for a validated multi-step database operation Create a view over approved tables for a reusable query projection Use an INSTEAD OF trigger for custom DML behavior on a view Use an AFTER trigger for an explicitly required DML-follow-up action Explanation Correct Answer C:This implementation fits because it substitutes validated logic for the attempted operation. That addresses the stated technical constraint. Incorrect Answers A:That technique encapsulates authorized statements and parameter validation. This does not meet the need for a method that substitutes validated logic for the attempted operation. B:This alternative encapsulates a saved SELECT projection behind a stable name. This does not meet the need for a method that substitutes validated logic for the attempted operation. D:This alternative runs a constrained response after a supported table DML event. This does not meet the need for a method that substitutes validated logic for the attempted operation.
Question 94 A dashboard needs a reusable tenant-filtered set of orders as a table expression. Which implementation is the best fit? Use an inline table-valued function for a parameterized rowset Create a view over approved tables for a reusable query projection Use a stored procedure for a validated multi-step database operation Use an INSTEAD OF trigger for custom DML behavior on a view Explanation Correct Answer A:This implementation returns an optimizable relational result parameterized by inputs. Incorrect Answers B:That technique encapsulates a saved SELECT projection behind a stable name. This does not meet the need for a method that returns an optimizable relational result parameterized by inputs. C:This alternative encapsulates authorized statements and parameter validation. This does not meet the need for a method that returns an optimizable relational result parameterized by inputs. D:This alternative substitutes validated logic for the attempted operation. This does not meet the need for a method that returns an optimizable relational result parameterized by inputs.
Question 95 An accounts workflow must insert a header and related rows transactionally through one database entry point. What is the most appropriate design decision? Use a stored procedure for a validated multi-step database operation Create a view over approved tables for a reusable query projection Use an INSTEAD OF trigger for custom DML behavior on a view Use an AFTER trigger for an explicitly required DML-follow-up action Explanation Correct Answer A:This implementation fits because it encapsulates authorized statements and parameter validation. That addresses the stated technical constraint. Incorrect Answers B:This choice encapsulates a saved SELECT projection behind a stable name. This does not meet the need for a method that encapsulates authorized statements and parameter validation. C:This alternative substitutes validated logic for the attempted operation. This does not meet the need for a method that encapsulates authorized statements and parameter validation. D:This choice runs a constrained response after a supported table DML event. This does not meet the need for a method that encapsulates authorized statements and parameter validation.
Question 96 A bulk update requires a fail-fast transaction policy when a runtime statement fails. Which change most directly meets the requirement? Use THROW to propagate a caught SQL error Use TRY...CATCH around database statements that can fail Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Use OUTPUT parameters for a small scalar result from a procedure Explanation Correct Answer C:This implementation makes many statement errors abort and roll back an explicit transaction. Incorrect Answers A:That technique returns a SQL exception to the caller instead of silently swallowing it. This does not meet the need for a method that makes many statement errors abort and roll back an explicit transaction. B:That technique routes runtime SQL errors into a defined recovery path. This does not meet the need for a method that makes many statement errors abort and roll back an explicit transaction. D:This option returns one status or generated value through a documented output argument. This does not meet the need for a method that makes many statement errors abort and roll back an explicit transaction.
Question 97 A transfer debits one account and credits another as one logical operation. What is the most appropriate design decision? Use TRY...CATCH around database statements that can fail Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Add explicit transaction boundaries around interdependent writes Use a parameterized stored procedure instead of SQL string concatenation Explanation Correct Answer C:Add explicit transaction boundaries around interdependent writes fits because it commits related updates together or rolls back consistently. That addresses the stated technical constraint. Incorrect Answers A:That technique routes runtime SQL errors into a defined recovery path. This does not meet the need for a method that commits related updates together or rolls back consistently. B:This choice makes many statement errors abort and roll back an explicit transaction. This does not meet the need for a method that commits related updates together or rolls back consistently. D:This alternative separates untrusted values from query instructions. This does not meet the need for a method that commits related updates together or rolls back consistently.
Question 98 A trigger updates the same table and causes repeated change callbacks. What is the most appropriate design decision? Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter Use schema-qualified object names in routines Grant EXECUTE on an approved procedure instead of broad base-table writes Avoid side-effecting trigger recursion by guarding the trigger path Explanation Correct Answer D:Avoid side-effecting trigger recursion by guarding the trigger path is the appropriate choice: it prevents unintended repeated trigger execution. The deployment should be tested with representative data. Incorrect Answers A:That technique exposes a relational expression that is easier for the optimizer to reason about. This does not meet the need for a method that prevents unintended repeated trigger execution. B:This alternative removes ambiguity and supports predictable name resolution. This does not meet the need for a method that prevents unintended repeated trigger execution. C:That technique constrains the exposed write operation to reviewed logic. This does not meet the need for a method that prevents unintended repeated trigger execution.
Question 99 An error handler currently logs a failure but incorrectly reports successful checkout. Which approach would you recommend? Use THROW to propagate a caught SQL error Add explicit transaction boundaries around interdependent writes Use a parameterized stored procedure instead of SQL string concatenation Use OUTPUT parameters for a small scalar result from a procedure Explanation Correct Answer A:This implementation fits because it returns a SQL exception to the caller instead of silently swallowing it. That addresses the stated technical constraint. Incorrect Answers B:This alternative commits related updates together or rolls back consistently. This does not meet the need for a method that returns a SQL exception to the caller instead of silently swallowing it. C:That technique separates untrusted values from query instructions. This does not meet the need for a method that returns a SQL exception to the caller instead of silently swallowing it. D:This choice returns one status or generated value through a documented output argument. This does not meet the need for a method that returns a SQL exception to the caller instead of silently swallowing it.
Question 100 A web API passes a customer search term that could contain quote characters. Which change most directly meets the requirement? Use a parameterized stored procedure instead of SQL string concatenation Add explicit transaction boundaries around interdependent writes Use OUTPUT parameters for a small scalar result from a procedure Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Explanation Correct Answer A:This approach separates untrusted values from query instructions. Incorrect Answers B:That technique commits related updates together or rolls back consistently. This does not meet the need for a method that separates untrusted values from query instructions. C:This choice returns one status or generated value through a documented output argument. This does not meet the need for a method that separates untrusted values from query instructions. D:This option makes many statement errors abort and roll back an explicit transaction. This does not meet the need for a method that separates untrusted values from query instructions.