Question 110 A support system exposes a permission-scoped routine for a multi-table status transition. 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 a scalar user-defined function for reusable scalar logic where appropriate Use an AFTER trigger for an explicitly required DML-follow-up action Explanation Correct Answer A:This implementation encapsulates authorized statements and parameter validation. Incorrect Answers B:That technique 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 choice returns one computed value rather than a relation or side-effecting workflow. This does not meet the need for a method that encapsulates authorized statements and parameter validation. D:That technique 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 111 A query composes parameterized rowsets and needs plan visibility rather than an opaque result table. Which change most directly meets the requirement? Use EXECUTE AS with a reviewed execution context only where necessary Use schema-qualified object names in routines 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 C:Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter is the appropriate choice: it exposes a relational expression that is easier for the optimizer to reason about. The deployment should be tested with representative data. Incorrect Answers A:This alternative 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. B:That technique removes ambiguity and supports predictable name resolution. This does not meet the need for a method that exposes a relational expression that is easier for the optimizer to reason about. D:This option constrains the exposed write operation to reviewed logic. This does not meet the need for a method that exposes a relational expression that is easier for the optimizer to reason about.
Question 112 A planning report calls a reusable date-range rowset from a larger SELECT. Which solution is most directly applicable? Use a stored procedure for a validated multi-step database operation Use an INSTEAD OF trigger for custom DML behavior on a view Use an AFTER trigger for an explicitly required DML-follow-up action Use an inline table-valued function for a parameterized rowset Explanation Correct Answer D:This implementation fits because it returns an optimizable relational result parameterized by inputs. That addresses the stated technical constraint. Incorrect Answers A: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. B: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. C:This choice runs a constrained response after a supported table DML event. This does not meet the need for a method that returns an optimizable relational result parameterized by inputs.
Question 113 An account lookup must handle apostrophes in names without creating dynamic executable text. Which technical choice addresses this need? Use a parameterized stored procedure instead of SQL string concatenation Add explicit transaction boundaries around interdependent writes Use THROW to propagate a caught SQL error Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Explanation Correct Answer A:This choice separates untrusted values from query instructions. Incorrect Answers B:This choice 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 alternative returns a SQL exception to the caller instead of silently swallowing it. This does not meet the need for a method that separates untrusted values from query instructions. D:This alternative 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.
Question 114 A legacy integration needs a database-side post-insert event after application DML. Which solution is most directly applicable? Create a view over approved tables for a reusable query projection Use a scalar user-defined function for reusable scalar logic where appropriate Use an AFTER trigger for an explicitly required DML-follow-up action Use a stored procedure for a validated multi-step database operation Explanation Correct Answer C:This approach runs a constrained response after a supported table DML event. Incorrect Answers A:This alternative encapsulates a saved SELECT projection behind a stable name. This does not meet the need for a method that runs a constrained response after a supported table DML event. B:That technique returns one computed value rather than a relation or side-effecting workflow. This does not meet the need for a method that runs a constrained response after a supported table DML event. D:That technique encapsulates authorized statements and parameter validation. This does not meet the need for a method that runs a constrained response after a supported table DML event.
Question 115 An application needs a deterministic formatting calculation that returns one scalar value. Which technical choice addresses this need? Use an AFTER trigger for an explicitly required DML-follow-up action Use a scalar user-defined function for reusable scalar logic where appropriate 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 B:This approach returns one computed value rather than a relation or side-effecting workflow. Incorrect Answers A:This option runs a constrained response after a supported table DML event. This does not meet the need for a method that returns one computed value rather than a relation or side-effecting workflow. C:That technique encapsulates authorized statements and parameter validation. This does not meet the need for a method that returns one computed value rather than a relation or side-effecting workflow. D:That technique substitutes validated logic for the attempted operation. This does not meet the need for a method that returns one computed value rather than a relation or side-effecting workflow.
Question 116 A signed and reviewed module requires a stable security context independent of the invoking login. What is the most appropriate design decision? Use EXECUTE AS with a reviewed execution context only where necessary Avoid side-effecting trigger recursion by guarding the trigger path 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 A:This approach scopes privilege escalation through explicitly defined module security. Incorrect Answers B:This alternative prevents unintended repeated trigger execution. This does not meet the need for a method that scopes privilege escalation through explicitly defined module security. C:That technique safely passes multiple structured rows in one call. This does not meet the need for a method that scopes privilege escalation through explicitly defined module security. 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 scopes privilege escalation through explicitly defined module security.
Question 117 A multi-step import needs to log and rethrow unexpected statement failures. What should the engineer choose? Use TRY...CATCH around database statements that can fail Use OUTPUT parameters for a small scalar result from a procedure Use a parameterized stored procedure instead of SQL string concatenation Add explicit transaction boundaries around interdependent writes Explanation Correct Answer A:This choice routes runtime SQL errors into a defined recovery path. Incorrect Answers B:This choice returns one status or generated value through a documented output argument. This does not meet the need for a method that routes runtime SQL errors into a defined recovery path. C:This option separates untrusted values from query instructions. This does not meet the need for a method that routes runtime SQL errors into a defined recovery path. D:This alternative commits related updates together or rolls back consistently. This does not meet the need for a method that routes runtime SQL errors into a defined recovery path.
Question 118 A post-update hook unexpectedly fires again when it modifies its own source rows. Which technical choice addresses this need? Use EXECUTE AS with a reviewed execution context only where necessary Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter Use schema-qualified object names in routines 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:This option scopes privilege escalation through explicitly defined module security. This does not meet the need for a method that prevents unintended repeated trigger execution. B: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 prevents unintended repeated trigger execution. C:That technique removes ambiguity and supports predictable name resolution. This does not meet the need for a method that prevents unintended repeated trigger execution.
Question 119 A validation routine must notify the API when a transaction fails. Which solution best satisfies the stated constraint? Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Use THROW to propagate a caught SQL error Add explicit transaction boundaries around interdependent writes Use TRY...CATCH around database statements that can fail Explanation Correct Answer B:This approach returns a SQL exception to the caller instead of silently swallowing it. Incorrect Answers A:This option makes many statement errors abort and roll back an explicit transaction. 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 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. D:This choice routes runtime SQL errors into a defined recovery path. This does not meet the need for a method that returns a SQL exception to the caller instead of silently swallowing it.
Question 120 A service must expose a consistent non-sensitive projection while the base joins evolve. Which solution is most directly applicable? Create a view over approved tables for a reusable query projection Use an AFTER trigger for an explicitly required DML-follow-up action Use an inline table-valued function for a parameterized rowset Use an INSTEAD OF trigger for custom DML behavior on a view Explanation Correct Answer A:This approach encapsulates a saved SELECT projection behind a stable name. Incorrect Answers B:That technique runs a constrained response after a supported table DML event. This does not meet the need for a method that encapsulates a saved SELECT projection behind a stable name. C:This alternative returns an optimizable relational result parameterized by inputs. This does not meet the need for a method that encapsulates a saved SELECT projection behind a stable name. D:This choice substitutes validated logic for the attempted operation. This does not meet the need for a method that encapsulates a saved SELECT projection behind a stable name.