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.
Question 121 A synchronization service passes a batch of approved keys without building a dynamic IN string. Which approach would you recommend? Use a table-valued parameter to pass an approved set of rows to a procedure Avoid side-effecting trigger recursion by guarding the trigger path Use schema-qualified object names in routines Grant EXECUTE on an approved procedure instead of broad base-table writes Explanation Correct Answer A:This choice safely passes multiple structured rows in one call. Incorrect Answers B:That technique prevents unintended repeated trigger execution. This does not meet the need for a method that safely passes multiple structured rows in one call. C:This alternative 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. D:That technique 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 122 An automation routine needs privilege isolation and an audited effective execution identity. What should the engineer choose? Grant EXECUTE on an approved procedure instead of broad base-table writes 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 Use schema-qualified object names in routines Explanation Correct Answer C:This approach scopes privilege escalation through explicitly defined module security. Incorrect Answers A:This option constrains the exposed write operation to reviewed logic. This does not meet the need for a method that scopes privilege escalation through explicitly defined module security. 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 scopes privilege escalation through explicitly defined module security. D:This choice removes ambiguity and supports predictable name resolution. This does not meet the need for a method that scopes privilege escalation through explicitly defined module security.
Question 123 A settlement job updates payment state and ledger totals with all-or-nothing semantics. What is the most appropriate next design step? Use THROW to propagate a caught SQL error Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Use TRY...CATCH around database statements that can fail Add explicit transaction boundaries around interdependent writes Explanation Correct Answer D:This implementation commits related updates together or rolls back consistently. Incorrect Answers A:This alternative returns a SQL exception to the caller instead of silently swallowing it. This does not meet the need for a method that commits related updates together or rolls back consistently. B:This alternative 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. C: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.
Question 124 An administrator schedules a controlled set of parameterized maintenance commands as one unit. Which implementation is the best fit? Use an INSTEAD OF trigger for custom DML behavior on a view 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 inline table-valued function for a parameterized rowset Explanation Correct Answer C:This implementation encapsulates authorized statements and parameter validation. Incorrect Answers A:This option substitutes validated logic for the attempted operation. This does not meet the need for a method that encapsulates authorized statements and parameter validation. B:This option 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 returns an optimizable relational result parameterized by inputs. This does not meet the need for a method that encapsulates authorized statements and parameter validation.
Question 125 An analytical query is repeatedly reused with stable joins but must not materialize results separately. Which technical choice addresses this need? Use a stored procedure for a validated multi-step database operation Use an INSTEAD OF trigger for custom DML behavior on a view Create a view over approved tables for a reusable query projection Use an inline table-valued function for a parameterized rowset Explanation Correct Answer C:Create a view over approved tables for a reusable query projection fits because it encapsulates a saved SELECT projection behind a stable name. 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 encapsulates a saved SELECT projection behind a stable name. B:That technique 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. D:This choice 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.
Question 126 An integration endpoint accepts a user-entered key that must not change the SQL command structure. Which solution is most directly applicable? Use a parameterized stored procedure instead of SQL string concatenation Use OUTPUT parameters for a small scalar result from a procedure Use THROW to propagate a caught SQL error Use TRY...CATCH around database statements that can fail Explanation Correct Answer A:This implementation separates untrusted values from query instructions. Incorrect Answers B:This option 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. C:That technique 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:That technique routes runtime SQL errors into a defined recovery path. This does not meet the need for a method that separates untrusted values from query instructions.
Question 127 A service needs a composable parameterized collection of eligible products. Which technical choice addresses this need? Use an inline table-valued function for a parameterized rowset Use an INSTEAD OF trigger for custom DML behavior on a view Use a stored procedure for a validated multi-step database operation Use a scalar user-defined function for reusable scalar logic where appropriate Explanation Correct Answer A:This implementation fits because it returns an optimizable relational result parameterized by inputs. That addresses the stated technical constraint. Incorrect Answers B:This option 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:That technique 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 returns one computed value rather than a relation or side-effecting workflow. This does not meet the need for a method that returns an optimizable relational result parameterized by inputs.
Question 128 A logging trigger requires an explicit guard against repeated recursive invocation. What should the engineer choose? Use a table-valued parameter to pass an approved set of rows to a procedure 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 fits because it prevents unintended repeated trigger execution. That addresses the stated technical constraint. Incorrect Answers A:This option safely passes multiple structured rows in one call. This does not meet the need for a method that prevents unintended repeated trigger execution. B:This choice removes ambiguity and supports predictable name resolution. This does not meet the need for a method that prevents unintended repeated trigger execution. C:This option constrains the exposed write operation to reviewed logic. This does not meet the need for a method that prevents unintended repeated trigger execution.
Question 129 A maintenance job returns the count of changed rows as an explicit output parameter. What should the SQL developer implement? Add explicit transaction boundaries around interdependent writes Use TRY...CATCH around database statements that can fail Use a parameterized stored procedure instead of SQL string concatenation Use OUTPUT parameters for a small scalar result from a procedure Explanation Correct Answer D:This approach returns one status or generated value through a documented output argument. Incorrect Answers A:That technique commits related updates together or rolls back consistently. This does not meet the need for a method that returns one status or generated value through a documented output argument. B:This choice routes runtime SQL errors into a defined recovery path. This does not meet the need for a method that returns one status or generated value through a documented output argument. C:This option separates untrusted values from query instructions. This does not meet the need for a method that returns one status or generated value through a documented output argument.
Question 130 A regulatory system explicitly requires table-level after-delete change capture under reviewed constraints. Which change most directly meets the requirement? Use a stored procedure for a validated multi-step database operation Use a scalar user-defined function for reusable scalar logic where appropriate Use an inline table-valued function for a parameterized rowset Use an AFTER trigger for an explicitly required DML-follow-up action Explanation Correct Answer D:This choice runs a constrained response after a supported table DML event. Incorrect Answers A:This choice 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. 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. C:This choice returns an optimizable relational result parameterized by inputs. This does not meet the need for a method that runs a constrained response after a supported table DML event.