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.
Question 131 An application identity should call a constrained stored procedure instead of receiving db_owner. Which approach would you recommend? Use EXECUTE AS with a reviewed execution context only where necessary 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 D:This implementation constrains the exposed write operation to reviewed logic. Incorrect Answers A:This alternative 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. B:This option 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. C:This alternative 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 132 A refactor must avoid accidentally resolving an object from a different caller default schema. What is the most appropriate design decision? Prefer an inline table-valued function to a multi-statement TVF when composability and estimates matter Use a table-valued parameter to pass an approved set of rows to a procedure Grant EXECUTE on an approved procedure instead of broad base-table writes Use schema-qualified object names in routines Explanation Correct Answer D:This implementation fits because it removes ambiguity and supports predictable name resolution. That addresses the stated technical constraint. Incorrect Answers A: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 removes ambiguity and supports predictable name resolution. B:This alternative safely passes multiple structured rows in one call. This does not meet the need for a method that removes ambiguity and supports predictable name resolution. C:This option constrains the exposed write operation to reviewed logic. This does not meet the need for a method that removes ambiguity and supports predictable name resolution.
Question 133 A financial procedure must detect a failed write and roll back its transaction. What should the SQL developer implement? Use TRY...CATCH around database statements that can fail Use a parameterized stored procedure instead of SQL string concatenation Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate 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 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. C:This option makes many statement errors abort and roll back an explicit transaction. 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 134 An import service needs its parent job to see the caught database failure. What is the most appropriate design decision? Use OUTPUT parameters for a small scalar result from a procedure Use TRY...CATCH around database statements that can fail Add explicit transaction boundaries around interdependent writes Use THROW to propagate a caught SQL error Explanation Correct Answer D:This choice returns a SQL exception to the caller instead of silently swallowing it. Incorrect Answers A: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. B:This option 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. C: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.
Question 135 A lookup query reuses a compact scalar transformation after reviewing performance impact. What should the engineer choose? 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 Use an inline table-valued function for a parameterized rowset Explanation Correct Answer A:This implementation fits because it returns one computed value rather than a relation or side-effecting workflow. That addresses the stated technical constraint. Incorrect Answers B:This alternative 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:This alternative returns an optimizable relational result parameterized by inputs. This does not meet the need for a method that returns one computed value rather than a relation or side-effecting workflow.
Question 136 An integration routine needs predictable transaction abortion for supported run-time failures. Which solution best satisfies the stated constraint? Use a parameterized stored procedure instead of SQL string concatenation Use THROW to propagate a caught SQL error 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:This alternative separates untrusted values from query instructions. This does not meet the need for a method that makes many statement errors abort and roll back an explicit transaction. B:This choice 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. 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 137 A controlled abstraction intercepts a delete request to apply custom database-side behavior. Which change most directly meets the requirement? Use an inline table-valued function for a parameterized rowset 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 an INSTEAD OF trigger for custom DML behavior on a view Explanation Correct Answer D:This approach substitutes validated logic for the attempted operation. Incorrect Answers A:This option returns an optimizable relational result parameterized by inputs. This does not meet the need for a method that substitutes validated logic for the attempted operation. B:This choice 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. C:That technique returns one computed value rather than a relation or side-effecting workflow. This does not meet the need for a method that substitutes validated logic for the attempted operation.
Question 138 A dashboard must list customers even when their latest activity TVF returns nothing. What is the most appropriate next design step? Use OUTER APPLY to preserve left rows without matches Use NOT EXISTS for an anti-join condition Use COALESCE to select the first non-null expression Use CROSS APPLY for a correlated table expression that must match Explanation Correct Answer A:This implementation extends a correlated table expression while retaining unmatched outer rows. Incorrect Answers B:This alternative keeps rows with no matching counterpart. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows. C:This alternative supplies a fallback among ordered nullable values. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows. D:This choice evaluates a right-hand rowset per left row and excludes unmatched rows. This does not meet the need for a method that extends a correlated table expression while retaining unmatched outer rows.
Question 139 A salary report shows each role's average alongside individual compensation rows. Which solution best satisfies the stated constraint? Use SUM(amount) OVER(PARTITION BY ... ORDER BY ...) with an appropriate frame Use AVG() OVER(PARTITION BY ...) for a group statistic on each detail row Use DENSE_RANK() for tied rankings without numeric gaps Use NTILE(4) OVER to divide ordered rows into four buckets Explanation Correct Answer B:This approach preserves each input row while exposing its peer-group average. Incorrect Answers A:This alternative calculates a running total while retaining detail rows. This does not meet the need for a method that preserves each input row while exposing its peer-group average. C:This alternative assigns equal ranks for ties and consecutive distinct ranks. This does not meet the need for a method that preserves each input row while exposing its peer-group average. D:This alternative assigns approximate quantile groups by ordered position. This does not meet the need for a method that preserves each input row while exposing its peer-group average.
Question 140 A customer matching study handles swapped or slightly misspelled surnames. The target SQL engine supports the named T-SQL feature. Which implementation is the best fit? Use a graph MATCH query over node and edge tables Use REGEXP_REPLACE to replace portions matching a regular expression Use JARO_WINKLER_DISTANCE for name-oriented fuzzy comparison Use REGEXP_SUBSTR to extract a regex-matched fragment Explanation Correct Answer C:This implementation measures likeness of string pairs especially near their prefixes. Incorrect Answers A:That technique follows graph-pattern relationships stored in graph tables. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes. B:That technique transforms substrings identified by a regex. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes. D:This alternative returns a portion of text matching a pattern. This does not meet the need for a method that measures likeness of string pairs especially near their prefixes.