Question 80 A data engineer wants a governed SQL schema over an external source supported by the selected platform. What is the most appropriate next design step? Use a system-versioned temporal table to retain prior row versions Use an external table for supported data virtualization Use memory-optimized tables after workload validation Use graph node and edge tables for stored relationships Explanation Correct Answer B:This choice queries external data through a mapped schema without copying every source row first. Incorrect Answers A:This choice maintains a history of row changes for point-in-time queries. This does not meet the need for a method that queries external data through a mapped schema without copying every source row first. C:This option can improve supported highly concurrent in-memory OLTP paths. This does not meet the need for a method that queries external data through a mapped schema without copying every source row first. D:This choice represents entities and their many-to-many connections in a graph model. This does not meet the need for a method that queries external data through a mapped schema without copying every source row first.
Question 81 An application needs to search a JSON property efficiently without shredding the whole document on each query. What should the SQL developer implement? Use a native JSON column and supported JSON indexing where available Use partition-aligned indexes with a partitioned table Persist a computed column derived from a JSON scalar and index it when supported Create a table partition function and scheme on a date key Explanation Correct Answer C:Persist a computed column derived from a JSON scalar and index it when supported fits because it exposes an often-filtered JSON property as an indexable typed expression. That addresses the stated technical constraint. Incorrect Answers A:That technique applies platform-supported JSON storage and indexing capabilities. This does not meet the need for a method that exposes an often-filtered JSON property as an indexable typed expression. B:This alternative keeps supporting indexes consistent with table partition boundaries. This does not meet the need for a method that exposes an often-filtered JSON property as an indexable typed expression. D:This choice separates large rows into predictable date-based partitions for maintenance. This does not meet the need for a method that exposes an often-filtered JSON property as an indexable typed expression.
Question 82 A prototype targets microsecond-scale in-memory processing for small hot operational rows. What should the SQL developer implement? Use a rowstore nonclustered index for a selective lookup Use memory-optimized tables after workload validation Use a filtered index for a small predictable row subset Use graph node and edge tables for stored relationships Explanation Correct Answer B:This implementation fits because it can improve supported highly concurrent in-memory OLTP paths. That addresses the stated technical constraint. Incorrect Answers A:This choice accelerates supported predicates with ordered key entries. This does not meet the need for a method that can improve supported highly concurrent in-memory OLTP paths. C:This option indexes only rows matching a specified filter predicate. This does not meet the need for a method that can improve supported highly concurrent in-memory OLTP paths. D:This alternative represents entities and their many-to-many connections in a graph model. This does not meet the need for a method that can improve supported highly concurrent in-memory OLTP paths.
Question 83 A message archive needs lossless storage of binary attachments in a column. Which solution is most directly applicable? Use VARBINARY for arbitrary binary payloads Use UNIQUEIDENTIFIER for externally generated UUID keys Use BIGINT for the monotonically increasing row number Use DATETIME2 with an explicit fractional-second precision Explanation Correct Answer A:This implementation fits because it stores uninterpreted bytes without character conversion. That addresses the stated technical constraint. Incorrect Answers B:This option stores globally assigned identifier values rather than sequential integers. This does not meet the need for a method that stores uninterpreted bytes without character conversion. C:This option supplies a wide integral range without fractional storage. This does not meet the need for a method that stores uninterpreted bytes without character conversion. D:This alternative records date-and-time values with configurable subsecond precision. This does not meet the need for a method that stores uninterpreted bytes without character conversion.
Question 84 An offline mobile sync layer imports UUID primary identifiers created outside SQL Server. What should the engineer choose? Use BIT for a two-state flag Use UNIQUEIDENTIFIER for externally generated UUID keys Use DATETIMEOFFSET for timezone-aware instants Use NVARCHAR for multilingual free text Explanation Correct Answer B:This choice stores globally assigned identifier values rather than sequential integers. Incorrect Answers A:This alternative represents a Boolean-like value with compact storage. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers. C:This alternative retains an offset alongside a date and time value. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers. D:This choice stores Unicode text rather than code-page-limited character data. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers.
Question 85 A case-management system filters a branch first and then a case-opened date interval. What is the most appropriate design decision? Use partition-aligned indexes with a partitioned table Choose a composite index key that leads with the query's filtering columns Use included non-key columns to cover a selective query Persist a computed column derived from a JSON scalar and index it when supported Explanation Correct Answer B:This implementation supports seek and range access for the recurring predicate. Incorrect Answers A:This option keeps supporting indexes consistent with table partition boundaries. This does not meet the need for a method that supports seek and range access for the recurring predicate. C:This choice avoids unnecessary key lookups when a predicate index misses returned columns. This does not meet the need for a method that supports seek and range access for the recurring predicate. D:This alternative exposes an often-filtered JSON property as an indexable typed expression. This does not meet the need for a method that supports seek and range access for the recurring predicate.
Question 86 A supported function is needed to normalize one SKU label expression within queries. Which implementation is the best fit? Use a scalar user-defined function for reusable scalar logic where appropriate Use an INSTEAD OF trigger for custom DML behavior on a view 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 A:This implementation returns one computed value rather than a relation or side-effecting workflow. Incorrect Answers B:This choice 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. C:This choice 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. D:That technique 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.
Question 87 A procedure performs a permitted table operation without granting every caller direct table rights. Which solution is most directly applicable? 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 C:This choice scopes privilege escalation through explicitly defined module security. Incorrect Answers A:This choice 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. B: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 scopes privilege escalation through explicitly defined module security. D:That technique prevents unintended repeated trigger execution. This does not meet the need for a method that scopes privilege escalation through explicitly defined module security.
Question 88 An audit rule requires recording a row change when a sensitive table is updated. Which approach would you recommend? 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 a scalar user-defined function for reusable scalar logic where appropriate Use a stored procedure for a validated multi-step database operation Explanation Correct Answer B:This choice runs a constrained response after a supported table DML event. Incorrect Answers A:This option substitutes validated logic for the attempted operation. This does not meet the need for a method that runs a constrained response after a supported table DML event. C:This alternative 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:This option 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 89 A stored procedure must capture a duplicate-key failure without obscuring the caller's error. What is the most appropriate next design step? 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 Use SET XACT_ABORT ON in a transactionally sensitive procedure where appropriate Explanation Correct Answer C:This implementation fits because it routes runtime SQL errors into a defined recovery path. That addresses the stated technical constraint. Incorrect Answers A:That technique 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. 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 routes runtime SQL errors into a defined recovery path. D:This alternative 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.
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.