Question 1 A ten-year event table is archived by calendar month and needs partition-aware lifecycle operations. Which technical choice addresses this need? Persist a computed column derived from a JSON scalar and index it when supported Use partition-aligned indexes with a partitioned table Use included non-key columns to cover a selective query Create a table partition function and scheme on a date key Explanation Correct Answer D:This approach separates large rows into predictable date-based partitions for maintenance. Incorrect Answers A:That technique exposes an often-filtered JSON property as an indexable typed expression. This does not meet the need for a method that separates large rows into predictable date-based partitions for maintenance. B:This alternative keeps supporting indexes consistent with table partition boundaries. This does not meet the need for a method that separates large rows into predictable date-based partitions for maintenance. C:This choice avoids unnecessary key lookups when a predicate index misses returned columns. This does not meet the need for a method that separates large rows into predictable date-based partitions for maintenance.
Question 2 A reservation service records local event timestamps together with each location's UTC offset. Which implementation is the best fit? Use DATETIME2 with an explicit fractional-second precision Use VARBINARY for arbitrary binary payloads Use DATETIMEOFFSET for timezone-aware instants Use NVARCHAR for multilingual free text Explanation Correct Answer C:This approach retains an offset alongside a date and time value. Incorrect Answers A:This alternative records date-and-time values with configurable subsecond precision. This does not meet the need for a method that retains an offset alongside a date and time value. B:This choice stores uninterpreted bytes without character conversion. This does not meet the need for a method that retains an offset alongside a date and time value. D:This alternative stores Unicode text rather than code-page-limited character data. This does not meet the need for a method that retains an offset alongside a date and time value.
Question 3 A current platform supports native JSON and needs flexible semi-structured item metadata with property lookups. What is the most appropriate next design step? Choose a composite index key that leads with the query's filtering columns Create a table partition function and scheme on a date key Use a native JSON column and supported JSON indexing where available Persist a computed column derived from a JSON scalar and index it when supported Explanation Correct Answer C:This choice applies platform-supported JSON storage and indexing capabilities. Incorrect Answers A:That technique supports seek and range access for the recurring predicate. This does not meet the need for a method that applies platform-supported JSON storage and indexing capabilities. B:This choice separates large rows into predictable date-based partitions for maintenance. This does not meet the need for a method that applies platform-supported JSON storage and indexing capabilities. D:This choice exposes an often-filtered JSON property as an indexable typed expression. This does not meet the need for a method that applies platform-supported JSON storage and indexing capabilities.
Question 4 A support dashboard repeatedly retrieves one order by its external order number. Which approach would you recommend? Use a rowstore nonclustered index for a selective lookup Use graph node and edge tables for stored relationships Use a filtered index for a small predictable row subset Use a system-versioned temporal table to retain prior row versions Explanation Correct Answer A:This approach accelerates supported predicates with ordered key entries. Incorrect Answers B:This alternative represents entities and their many-to-many connections in a graph model. This does not meet the need for a method that accelerates supported predicates with ordered key entries. C:That technique indexes only rows matching a specified filter predicate. This does not meet the need for a method that accelerates supported predicates with ordered key entries. D:That technique maintains a history of row changes for point-in-time queries. This does not meet the need for a method that accelerates supported predicates with ordered key entries.
Question 5 A disconnected service assigns UUIDs before records arrive at a central database. Which implementation is the best fit? Use BIT for a two-state flag Use UNIQUEIDENTIFIER for externally generated UUID keys Use DATETIMEOFFSET for timezone-aware instants Use BIGINT for the monotonically increasing row number Explanation Correct Answer B:This implementation stores globally assigned identifier values rather than sequential integers. Incorrect Answers A:That technique 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:That technique 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 supplies a wide integral range without fractional storage. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers.
Question 6 A payment row cannot be created without a currency code. What should the SQL developer implement? Use an IDENTITY column for automatically generated row keys Make a required business value NOT NULL Use a SEQUENCE object for IDs shared by independent tables Add a FOREIGN KEY from the child record to its parent Explanation Correct Answer B:Make a required business value NOT NULL fits because it disallows absent values rather than silently substituting a default. That addresses the stated technical constraint. Incorrect Answers A:That technique derives a numeric value as each row is inserted into a particular table. This does not meet the need for a method that disallows absent values rather than silently substituting a default. C:This choice generates numeric values independently of one table's identity column. This does not meet the need for a method that disallows absent values rather than silently substituting a default. D:This choice prevents referencing a parent key that does not exist. This does not meet the need for a method that disallows absent values rather than silently substituting a default.
Question 7 An API filters by OrderId but repeatedly retrieves Status and DueDate from the base row. Which approach would you recommend? Choose a composite index key that leads with the query's filtering columns Create a table partition function and scheme on a date key Use partition-aligned indexes with a partitioned table Use included non-key columns to cover a selective query Explanation Correct Answer D:This implementation avoids unnecessary key lookups when a predicate index misses returned columns. Incorrect Answers A:That technique supports seek and range access for the recurring predicate. This does not meet the need for a method that avoids unnecessary key lookups when a predicate index misses returned columns. B:This choice separates large rows into predictable date-based partitions for maintenance. This does not meet the need for a method that avoids unnecessary key lookups when a predicate index misses returned columns. C:This option keeps supporting indexes consistent with table partition boundaries. This does not meet the need for a method that avoids unnecessary key lookups when a predicate index misses returned columns.
Question 8 An analytics table holds hundreds of millions of rows for aggregation-heavy reports. Which solution best satisfies the stated constraint? Use an external table for supported data virtualization Use graph node and edge tables for stored relationships Use a clustered columnstore index for large analytical scans Use memory-optimized tables after workload validation Explanation Correct Answer C:This implementation compresses columns and supports vectorized batch analytics on suitable large fact datasets. Incorrect Answers A:This option queries external data through a mapped schema without copying every source row first. This does not meet the need for a method that compresses columns and supports vectorized batch analytics on suitable large fact datasets. B:This alternative represents entities and their many-to-many connections in a graph model. This does not meet the need for a method that compresses columns and supports vectorized batch analytics on suitable large fact datasets. D:This alternative can improve supported highly concurrent in-memory OLTP paths. This does not meet the need for a method that compresses columns and supports vectorized batch analytics on suitable large fact datasets.
Question 9 Every invoice line must reference an existing invoice header. Which solution best satisfies the stated constraint? Make a required business value NOT NULL Add a FOREIGN KEY from the child record to its parent Use an IDENTITY column for automatically generated row keys Add a UNIQUE constraint to the business identifier Explanation Correct Answer B:This implementation prevents referencing a parent key that does not exist. Incorrect Answers A:This alternative disallows absent values rather than silently substituting a default. This does not meet the need for a method that prevents referencing a parent key that does not exist. C:This choice derives a numeric value as each row is inserted into a particular table. This does not meet the need for a method that prevents referencing a parent key that does not exist. D:This choice rejects repeated non-key business values without making them the table's primary key. This does not meet the need for a method that prevents referencing a parent key that does not exist.
Question 10 An order table must prevent two rows sharing the same OrderId used by child line items. Which solution best satisfies the stated constraint? Create a PRIMARY KEY constraint on the record identifier Add a CHECK constraint defining the allowed value range Add a DEFAULT constraint for a missing optional input Add a UNIQUE constraint to the business identifier Explanation Correct Answer A:This implementation enforces unique non-null row identity for referenced records. Incorrect Answers B:This choice enforces a domain rule at insert and update time. This does not meet the need for a method that enforces unique non-null row identity for referenced records. C:That technique supplies a declared value when an insert omits the column. This does not meet the need for a method that enforces unique non-null row identity for referenced records. D:This option rejects repeated non-key business values without making them the table's primary key. This does not meet the need for a method that enforces unique non-null row identity for referenced records.