Question 70 An asset registry needs distinct serial-number values in addition to its internal record keys. Which approach would you recommend? Add a UNIQUE constraint to the business identifier Add a DEFAULT constraint for a missing optional input Make a required business value NOT NULL Use a SEQUENCE object for IDs shared by independent tables Explanation Correct Answer A:This implementation rejects repeated non-key business values without making them the table's primary key. Incorrect Answers B:This alternative 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. C:This option disallows absent values rather than silently substituting a default. 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:This option generates numeric values independently of one table's identity 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.
Question 71 A tax filing row must never omit its reporting period. Which implementation is the best fit? Make a required business value NOT NULL 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 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 option 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. C: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. D:This choice 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.
Question 72 A migration tool needs a next-value generator outside any particular table insert. Which solution best satisfies the stated constraint? Add a CHECK constraint defining the allowed value range Use a SEQUENCE object for IDs shared by independent tables Add a UNIQUE constraint to the business identifier Make a required business value NOT NULL Explanation Correct Answer B:This choice generates numeric values independently of one table's identity column. Incorrect Answers A:This option enforces a domain rule at insert and update time. This does not meet the need for a method that generates numeric values independently of one table's identity column. C:This alternative rejects repeated non-key business values without making them the table's primary key. This does not meet the need for a method that generates numeric values independently of one table's identity column. D:This choice disallows absent values rather than silently substituting a default. This does not meet the need for a method that generates numeric values independently of one table's identity column.
Question 73 A platform-qualified Fabric SQL workload needs JSON documents with validated search-path performance. 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 Persist a computed column derived from a JSON scalar and index it when supported Use a native JSON column and supported JSON indexing where available Explanation Correct Answer D:This approach applies platform-supported JSON storage and indexing capabilities. Incorrect Answers A:This option keeps supporting indexes consistent with table partition boundaries. This does not meet the need for a method that applies platform-supported JSON storage and indexing capabilities. B:This alternative 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. C:This option 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 74 A rolling retention workload must manage complete time slices instead of deleting individual old rows. Which implementation is the best fit? Choose a composite index key that leads with the query's filtering columns Create a table partition function and scheme on a date key Persist a computed column derived from a JSON scalar and index it when supported Use included non-key columns to cover a selective query Explanation Correct Answer B:Create a table partition function and scheme on a date key fits because it separates large rows into predictable date-based partitions for maintenance. That addresses the stated technical constraint. Incorrect Answers A:This alternative supports seek and range access for the recurring predicate. This does not meet the need for a method that separates large rows into predictable date-based partitions for maintenance. C:This alternative 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. D: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.
Question 75 A laboratory sample table must store precise capture times without relying on obsolete DATETIME rounding. What should the engineer choose? Use NVARCHAR for multilingual free text Use DECIMAL(19,4) for the amount column Use BIGINT for the monotonically increasing row number Use DATETIME2 with an explicit fractional-second precision Explanation Correct Answer D:This implementation records date-and-time values with configurable subsecond precision. Incorrect Answers A: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. B:This option stores fixed-point financial values without binary floating-point rounding. This does not meet the need for a method that records date-and-time values with configurable subsecond precision. C:This option supplies a wide integral range without fractional storage. This does not meet the need for a method that records date-and-time values with configurable subsecond precision.
Question 76 A new audit entry should automatically receive the current creation time when none is supplied. What is the most appropriate next design step? Add a UNIQUE constraint to the business identifier Add a CHECK constraint defining the allowed value range Add a DEFAULT constraint for a missing optional input Create a PRIMARY KEY constraint on the record identifier Explanation Correct Answer C:This implementation supplies a declared value when an insert omits the column. Incorrect Answers A: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 supplies a declared value when an insert omits the column. B:This alternative enforces a domain rule at insert and update time. This does not meet the need for a method that supplies a declared value when an insert omits the column. D:This option enforces unique non-null row identity for referenced records. This does not meet the need for a method that supplies a declared value when an insert omits the column.
Question 77 A telemetry archive needs compressed column-oriented scans for time-window aggregates. What is the most appropriate design decision? Use a ledger table to detect unauthorized tampering Use a filtered index for a small predictable row subset Use a clustered columnstore index for large analytical scans Use graph node and edge tables for stored relationships Explanation Correct Answer C:This implementation fits because it compresses columns and supports vectorized batch analytics on suitable large fact datasets. That addresses the stated technical constraint. Incorrect Answers A:This alternative 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. B:That technique indexes only rows matching a specified filter predicate. This does not meet the need for a method that compresses columns and supports vectorized batch analytics on suitable large fact datasets. D:This option 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.
Question 78 A public-sector application stores multilingual citizen comments without losing Unicode characters. What is the most appropriate next design step? Use UNIQUEIDENTIFIER for externally generated UUID keys Use DATETIME2 with an explicit fractional-second precision Use DATETIMEOFFSET for timezone-aware instants Use NVARCHAR for multilingual free text Explanation Correct Answer D:This choice stores Unicode text rather than code-page-limited character data. Incorrect Answers A:That technique stores globally assigned identifier values rather than sequential integers. This does not meet the need for a method that stores Unicode text rather than code-page-limited character data. B:That technique records date-and-time values with configurable subsecond precision. This does not meet the need for a method that stores Unicode text rather than code-page-limited character data. C:This choice retains an offset alongside a date and time value. This does not meet the need for a method that stores Unicode text rather than code-page-limited character data.
Question 79 A warehouse shipment item must reference a valid dispatch record. Which implementation is the best fit? Add a UNIQUE constraint to the business identifier Add a FOREIGN KEY from the child record to its parent Use a SEQUENCE object for IDs shared by independent tables 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: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 prevents referencing a parent key that does not exist. C:This option generates numeric values independently of one table's identity column. 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 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.