Question 50 A device event log needs subsecond timestamps with a supported modern SQL temporal type. Which implementation is the best fit? Use VARBINARY for arbitrary binary payloads Use NVARCHAR for multilingual free text Use DATETIME2 with an explicit fractional-second precision Use UNIQUEIDENTIFIER for externally generated UUID keys Explanation Correct Answer C:This approach records date-and-time values with configurable subsecond precision. Incorrect Answers A:That technique stores uninterpreted bytes without character conversion. This does not meet the need for a method that records date-and-time values with configurable subsecond precision. B:This choice stores Unicode text rather than code-page-limited character data. This does not meet the need for a method that records date-and-time values with configurable subsecond precision. D:This alternative stores globally assigned identifier values rather than sequential integers. This does not meet the need for a method that records date-and-time values with configurable subsecond precision.
Question 51 An appointment record must always have a customer identifier supplied. Which solution is most directly applicable? Make a required business value NOT NULL Add a UNIQUE constraint to the business identifier Create a PRIMARY KEY constraint on the record identifier Add a CHECK constraint defining the allowed value range Explanation Correct Answer A: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 B: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 disallows absent values rather than silently substituting a default. C:That technique enforces unique non-null row identity for referenced records. This does not meet the need for a method that disallows absent values rather than silently substituting a default. D:That technique enforces a domain rule at insert and update time. This does not meet the need for a method that disallows absent values rather than silently substituting a default.
Question 52 A workload repeatedly reads unprocessed outbox records from a mostly completed queue. What should the SQL developer implement? Use a filtered index for a small predictable row subset Use a ledger table to detect unauthorized tampering Use a clustered columnstore index for large analytical scans Use memory-optimized tables after workload validation Explanation Correct Answer A:This approach indexes only rows matching a specified filter predicate. Incorrect Answers B:This alternative supplies tamper-evident cryptographic verification of table changes. This does not meet the need for a method that indexes only rows matching a specified filter predicate. C:This choice compresses columns and supports vectorized batch analytics on suitable large fact datasets. This does not meet the need for a method that indexes only rows matching a specified filter predicate. D:This alternative can improve supported highly concurrent in-memory OLTP paths. This does not meet the need for a method that indexes only rows matching a specified filter predicate.
Question 53 A catalog demands unique manufacturer part numbers while retaining surrogate row IDs. What is the most appropriate design decision? Add a DEFAULT constraint for a missing optional input Create a PRIMARY KEY constraint on the record identifier Add a UNIQUE constraint to the business identifier Add a FOREIGN KEY from the child record to its parent Explanation Correct Answer C:Add a UNIQUE constraint to the business identifier fits because it rejects repeated non-key business values without making them the table's primary key. That addresses the stated technical constraint. Incorrect Answers A:This choice supplies a declared value when an insert omits the column. This does not meet the need for a method that rejects repeated non-key business values without making them the table's primary key. B:This choice enforces unique non-null row identity for referenced records. This does not meet the need for a method that rejects repeated non-key business values without making them the table's primary key. D:That technique prevents referencing a parent key that does not exist. This does not meet the need for a method that rejects repeated non-key business values without making them the table's primary key.
Question 54 Auditors must reconstruct a supplier's stored address at an earlier business date. Which solution best satisfies the stated constraint? Use a clustered columnstore index for large analytical scans Use a rowstore nonclustered index for a selective lookup Use an external table for supported data virtualization Use a system-versioned temporal table to retain prior row versions Explanation Correct Answer D:This choice maintains a history of row changes for point-in-time queries. Incorrect Answers A:This choice compresses columns and supports vectorized batch analytics on suitable large fact datasets. This does not meet the need for a method that maintains a history of row changes for point-in-time queries. B:This option accelerates supported predicates with ordered key entries. This does not meet the need for a method that maintains a history of row changes for point-in-time queries. C: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 maintains a history of row changes for point-in-time queries.
Question 55 A travel application receives booking instants from offices in different time zones and must retain offsets. What is the most appropriate design decision? Use BIGINT for the monotonically increasing row number Use BIT for a two-state flag Use UNIQUEIDENTIFIER for externally generated UUID keys Use DATETIMEOFFSET for timezone-aware instants Explanation Correct Answer D:This implementation retains an offset alongside a date and time value. Incorrect Answers A:That technique supplies a wide integral range without fractional storage. This does not meet the need for a method that retains an offset alongside a date and time value. B:This alternative represents a Boolean-like value with compact storage. This does not meet the need for a method that retains an offset alongside a date and time value. C:This alternative stores globally assigned identifier values rather than sequential integers. This does not meet the need for a method that retains an offset alongside a date and time value.
Question 56 A new Azure SQL design stores varying supplier attributes and expects supported JSON property search. Which change most directly meets the requirement? Persist a computed column derived from a JSON scalar and index it when supported Choose a composite index key that leads with the query's filtering columns Use a native JSON column and supported JSON indexing where available Use included non-key columns to cover a selective query Explanation Correct Answer C:This implementation applies platform-supported JSON storage and indexing capabilities. Incorrect Answers A:This alternative 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. B:This option 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. D:This choice avoids unnecessary key lookups when a predicate index misses returned columns. This does not meet the need for a method that applies platform-supported JSON storage and indexing capabilities.
Question 57 A reporting warehouse scans large fact ranges for grouped summaries rather than point updates. What should the SQL developer implement? Use a clustered columnstore index for large analytical scans Use a ledger table to detect unauthorized tampering Use graph node and edge tables for stored relationships Use memory-optimized tables after workload validation Explanation Correct Answer A:This choice compresses columns and supports vectorized batch analytics on suitable large fact datasets. Incorrect Answers B:That technique supplies tamper-evident cryptographic verification of table changes. This does not meet the need for a method that compresses columns and supports vectorized batch analytics on suitable large fact datasets. C:This choice 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:That technique 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 58 An enterprise catalog models products and suppliers connected through multiple relationship types. What is the most appropriate design decision? Use graph node and edge tables for stored relationships Use a system-versioned temporal table to retain prior row versions Use a clustered columnstore index for large analytical scans Use a rowstore nonclustered index for a selective lookup Explanation Correct Answer A:This implementation represents entities and their many-to-many connections in a graph model. Incorrect Answers B:That technique maintains a history of row changes for point-in-time queries. This does not meet the need for a method that represents entities and their many-to-many connections in a graph model. C:This alternative compresses columns and supports vectorized batch analytics on suitable large fact datasets. This does not meet the need for a method that represents entities and their many-to-many connections in a graph model. D:That technique accelerates supported predicates with ordered key entries. This does not meet the need for a method that represents entities and their many-to-many connections in a graph model.
Question 59 A supplier needs tamper-evident history for high-value certification data. Which solution best satisfies the stated constraint? Use a ledger table to detect unauthorized tampering Use a system-versioned temporal table to retain prior row versions Use a filtered index for a small predictable row subset Use memory-optimized tables after workload validation Explanation Correct Answer A:This implementation fits because it supplies tamper-evident cryptographic verification of table changes. That addresses the stated technical constraint. Incorrect Answers B:This choice maintains a history of row changes for point-in-time queries. This does not meet the need for a method that supplies tamper-evident cryptographic verification of table changes. C:This alternative indexes only rows matching a specified filter predicate. This does not meet the need for a method that supplies tamper-evident cryptographic verification of table changes. D:This option can improve supported highly concurrent in-memory OLTP paths. This does not meet the need for a method that supplies tamper-evident cryptographic verification of table changes.
Question 60 A measurement table must reject negative sample quantities. What should the engineer choose? Make a required business value NOT NULL Create a PRIMARY KEY constraint on the record identifier Add a CHECK constraint defining the allowed value range Add a UNIQUE constraint to the business identifier Explanation Correct Answer C:This implementation enforces a domain rule at insert and update time. Incorrect Answers A:This option disallows absent values rather than silently substituting a default. This does not meet the need for a method that enforces a domain rule at insert and update time. B:That technique enforces unique non-null row identity for referenced records. This does not meet the need for a method that enforces a domain rule at insert and update time. D:That technique 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 a domain rule at insert and update time.