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.
Question 101 Several reporting tools need the same permitted customer summary columns. What is the most appropriate design decision? 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 Create a view over approved tables for a reusable query projection Explanation Correct Answer D:This approach encapsulates a saved SELECT projection behind a stable name. Incorrect Answers A: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 a saved SELECT projection behind a stable name. B:This choice 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. C: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 102 A procedure references Orders from a connection whose default schema varies by login. What is the most appropriate next design step? Grant EXECUTE on an approved procedure instead of broad base-table writes 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 Use schema-qualified object names in routines Explanation Correct Answer D:This choice removes ambiguity and supports predictable name resolution. Incorrect Answers A: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. B:This choice prevents unintended repeated trigger execution. This does not meet the need for a method that removes ambiguity and supports predictable name resolution. C:This option 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.
Question 103 An insert procedure must return the assigned row identifier to its caller. Which technical choice addresses this need? Use THROW to propagate a caught SQL error Add explicit transaction boundaries around interdependent writes Use OUTPUT parameters for a small scalar result from a procedure Use a parameterized stored procedure instead of SQL string concatenation Explanation Correct Answer C:This choice returns one status or generated value through a documented output argument. 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 returns one status or generated value through a documented output argument. B:This alternative 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. D:This choice 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 104 An approved view presents combined data but inserts must be translated into base-table changes. What is the most appropriate next design step? 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 an inline table-valued function for a parameterized rowset Use an INSTEAD OF trigger for custom DML behavior on a view Explanation Correct Answer D:This implementation fits because it substitutes validated logic for the attempted operation. That addresses the stated technical constraint. Incorrect Answers A:This choice 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. B:That technique 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:This alternative 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.
Question 105 A multi-tenant deployment uses explicit dbo-qualified procedure and table names in production code. Which change most directly meets the requirement? Use schema-qualified object names in routines Avoid side-effecting trigger recursion by guarding the trigger path 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 A:This choice removes ambiguity and supports predictable name resolution. Incorrect Answers B:That technique prevents unintended repeated trigger execution. This does not meet the need for a method that removes ambiguity and supports predictable name resolution. C: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 removes ambiguity and supports predictable name resolution. D:That technique 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 106 A validation stored procedure needs to report a short status code alongside its normal execution. Which solution is most directly applicable? Add explicit transaction boundaries around interdependent writes Use OUTPUT parameters for a small scalar result from a procedure Use TRY...CATCH around database statements that can fail Use a parameterized stored procedure instead of SQL string concatenation Explanation Correct Answer B:This implementation fits because it returns one status or generated value through a documented output argument. That addresses the stated technical constraint. Incorrect Answers A:This choice 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. C:This alternative 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. D:That technique 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 107 A support operator needs one diagnostic routine but not unrestricted SELECT on confidential data. Which approach would you recommend? 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 Grant EXECUTE on an approved procedure instead of broad base-table writes Use schema-qualified object names in routines Explanation Correct Answer C: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:This choice prevents unintended repeated trigger execution. 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. D:This option removes ambiguity and supports predictable name resolution. This does not meet the need for a method that constrains the exposed write operation to reviewed logic.
Question 108 A migration script must not leave a partially committed multi-statement unit after a supported error. Which solution is most directly applicable? 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 Use THROW to propagate a caught SQL error Explanation Correct Answer C:This implementation makes many statement errors abort and roll back an explicit transaction. Incorrect Answers A:This alternative 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. B:That technique 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. D:This alternative 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.
Question 109 An order creation must write its header and reserved inventory atomically. What is the most appropriate next design step? Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Use THROW to propagate a caught SQL error Use TRY...CATCH around database statements that can fail Add explicit transaction boundaries around interdependent writes Explanation Correct Answer D:This approach commits related updates together or rolls back consistently. Incorrect Answers A:That technique 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. B:This option 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. C:This option 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 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.