Question 30 A signature service stores a compact binary cryptographic digest. What is the most appropriate design decision? Use DECIMAL(19,4) for the amount column Use VARBINARY for arbitrary binary payloads Use NVARCHAR for multilingual free text Use UNIQUEIDENTIFIER for externally generated UUID keys Explanation Correct Answer B:This implementation fits because it stores uninterpreted bytes without character conversion. That addresses the stated technical constraint. Incorrect Answers A:That technique stores fixed-point financial values without binary floating-point rounding. This does not meet the need for a method that stores uninterpreted bytes without character conversion. C:This option stores Unicode text rather than code-page-limited character data. This does not meet the need for a method that stores uninterpreted bytes without character conversion. D:This alternative 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.
Question 31 A product review must belong to a registered product row. Which change most directly meets the requirement? Create a PRIMARY KEY constraint on the record identifier Add a FOREIGN KEY from the child record to its parent Add a UNIQUE constraint to the business identifier Make a required business value NOT NULL Explanation Correct Answer B:This approach prevents referencing a parent key that does not exist. Incorrect Answers A:This option enforces unique non-null row identity for referenced records. This does not meet the need for a method that prevents referencing a parent key that does not exist. C: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 prevents referencing a parent key that does not exist. D:That technique 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.
Question 32 A workflow table stores whether a task is currently active as a two-state value. Which approach would you recommend? Use NVARCHAR for multilingual free text Use DATETIMEOFFSET for timezone-aware instants Use VARBINARY for arbitrary binary payloads Use BIT for a two-state flag Explanation Correct Answer D:This implementation fits because it represents a Boolean-like value with compact storage. That addresses the stated technical constraint. Incorrect Answers A:This option stores Unicode text rather than code-page-limited character data. This does not meet the need for a method that represents a Boolean-like value with compact storage. B:This option retains an offset alongside a date and time value. This does not meet the need for a method that represents a Boolean-like value with compact storage. C:That technique stores uninterpreted bytes without character conversion. This does not meet the need for a method that represents a Boolean-like value with compact storage.
Question 33 An integration table holds compressed certificate data as bytes rather than as rendered text. Which solution best satisfies the stated constraint? Use UNIQUEIDENTIFIER for externally generated UUID keys Use VARBINARY for arbitrary binary payloads Use DECIMAL(19,4) for the amount column Use BIT for a two-state flag Explanation Correct Answer B:This implementation stores uninterpreted bytes without character conversion. Incorrect Answers A:This alternative 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 choice stores fixed-point financial values without binary floating-point rounding. This does not meet the need for a method that stores uninterpreted bytes without character conversion. D:This alternative represents a Boolean-like value with compact storage. This does not meet the need for a method that stores uninterpreted bytes without character conversion.
Question 34 A customer table needs an enforced stable key for each individual customer. Which solution best satisfies the stated constraint? Add a CHECK constraint defining the allowed value range Add a FOREIGN KEY from the child record to its parent Create a PRIMARY KEY constraint on the record identifier Add a DEFAULT constraint for a missing optional input Explanation Correct Answer C:This approach enforces unique non-null row identity for referenced records. Incorrect Answers A:That technique 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. B:This alternative prevents referencing a parent key that does not exist. This does not meet the need for a method that enforces unique non-null row identity for referenced records. D:This choice 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.
Question 35 An integration solution needs to query external object-backed data without maintaining a full local copy. Which change most directly meets the requirement? Use a rowstore nonclustered index for a selective lookup Use a clustered columnstore index for large analytical scans Use an external table for supported data virtualization Use memory-optimized tables after workload validation Explanation Correct Answer C:This choice queries external data through a mapped schema without copying every source row first. Incorrect Answers A:This option accelerates supported predicates with ordered key entries. This does not meet the need for a method that queries external data through a mapped schema without copying every source row first. B:This alternative compresses columns and supports vectorized batch analytics on suitable large fact datasets. 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 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.
Question 36 A service queries a narrow customer-status subset from a large OLTP table. Which implementation is the best fit? Use a system-versioned temporal table to retain prior row versions Use a rowstore nonclustered index for a selective lookup Use memory-optimized tables after workload validation Use an external table for supported data virtualization Explanation Correct Answer B:This choice accelerates supported predicates with ordered key entries. 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 accelerates supported predicates with ordered key entries. C:This option can improve supported highly concurrent in-memory OLTP paths. This does not meet the need for a method that accelerates supported predicates with ordered key entries. D:This choice queries external data through a mapped schema without copying every source row first. This does not meet the need for a method that accelerates supported predicates with ordered key entries.
Question 37 A reporting database moves last year's data using carefully aligned date partitions. What is the most appropriate next design step? Use included non-key columns to cover a selective query 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 D:This approach separates large rows into predictable date-based partitions for maintenance. Incorrect Answers A:This alternative 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. 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 option 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.
Question 38 A document ingest table repeatedly filters on a JSON customerId scalar with high selectivity. Which solution is most directly applicable? Create a table partition function and scheme on a date key Use included non-key columns to cover a selective query Persist a computed column derived from a JSON scalar and index it when supported Use partition-aligned indexes with a partitioned table Explanation Correct Answer C:This approach exposes an often-filtered JSON property as an indexable typed expression. Incorrect Answers A:This alternative 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. B:This choice avoids unnecessary key lookups when a predicate index misses returned columns. This does not meet the need for a method that exposes an often-filtered JSON property as an indexable typed expression. D:This option 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.
Question 39 A rental platform finds bookings by PropertyId within a date range under frequent reads. What is the most appropriate next design step? Choose a composite index key that leads with the query's filtering columns Persist a computed column derived from a JSON scalar and index it when supported Use included non-key columns to cover a selective query Use partition-aligned indexes with a partitioned table Explanation Correct Answer A:Choose a composite index key that leads with the query's filtering columns fits because it supports seek and range access for the recurring predicate. That addresses the stated technical constraint. Incorrect Answers B: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. C:This alternative 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 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.
Question 40 A payroll ledger needs exact fractional-currency values that can be reconciled to the cent. What should the SQL developer implement? Use DECIMAL(19,4) for the amount column Use BIT for a two-state flag Use BIGINT for the monotonically increasing row number Use NVARCHAR for multilingual free text Explanation Correct Answer A:This approach stores fixed-point financial values without binary floating-point rounding. Incorrect Answers B:That technique represents a Boolean-like value with compact storage. This does not meet the need for a method that stores fixed-point financial values without binary floating-point rounding. C:This alternative supplies a wide integral range without fractional storage. This does not meet the need for a method that stores fixed-point financial values without binary floating-point rounding. D:This choice stores Unicode text rather than code-page-limited character data. This does not meet the need for a method that stores fixed-point financial values without binary floating-point rounding.