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.
Question 41 A product description accepts accented European and Asian characters in one column. Which solution is most directly applicable? Use BIT for a two-state flag Use BIGINT for the monotonically increasing row number Use NVARCHAR for multilingual free text Use DATETIME2 with an explicit fractional-second precision Explanation Correct Answer C:This implementation fits because it stores Unicode text rather than code-page-limited character data. That addresses the stated technical constraint. Incorrect Answers A:That technique represents a Boolean-like value with compact storage. This does not meet the need for a method that stores Unicode text rather than code-page-limited character data. B:This alternative supplies a wide integral range without fractional storage. This does not meet the need for a method that stores Unicode text rather than code-page-limited character data. D:This choice 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.
Question 42 A retail event table will exceed two billion integer identifiers and must remain unique. Which implementation is the best fit? Use DATETIME2 with an explicit fractional-second precision Use BIGINT for the monotonically increasing row number Use DECIMAL(19,4) for the amount column Use UNIQUEIDENTIFIER for externally generated UUID keys Explanation Correct Answer B:This implementation fits because it supplies a wide integral range without fractional storage. That addresses the stated technical constraint. Incorrect Answers A:This alternative records date-and-time values with configurable subsecond precision. This does not meet the need for a method that supplies a wide integral range without fractional storage. C:This option stores fixed-point financial values without binary floating-point rounding. This does not meet the need for a method that supplies a wide integral range without fractional storage. D:This alternative stores globally assigned identifier values rather than sequential integers. This does not meet the need for a method that supplies a wide integral range without fractional storage.
Question 43 A large fact table's partition-switch process fails because an index has incompatible partition boundaries. What should the engineer choose? Use partition-aligned indexes with a partitioned table Choose a composite index key that leads with the query's filtering columns Use a native JSON column and supported JSON indexing where available Create a table partition function and scheme on a date key Explanation Correct Answer A:This implementation fits because it keeps supporting indexes consistent with table partition boundaries. That addresses the stated technical constraint. Incorrect Answers B:This choice supports seek and range access for the recurring predicate. This does not meet the need for a method that keeps supporting indexes consistent with table partition boundaries. C:That technique applies platform-supported JSON storage and indexing capabilities. This does not meet the need for a method that keeps supporting indexes consistent with table partition boundaries. D:This alternative separates large rows into predictable date-based partitions for maintenance. This does not meet the need for a method that keeps supporting indexes consistent with table partition boundaries.
Question 44 An internal queue assigns numbers automatically as new queue rows arrive. What is the most appropriate next design step? Use a SEQUENCE object for IDs shared by independent tables Add a DEFAULT constraint for a missing optional input Use an IDENTITY column for automatically generated row keys Add a CHECK constraint defining the allowed value range Explanation Correct Answer C:This implementation derives a numeric value as each row is inserted into a particular table. Incorrect Answers A:This choice generates numeric values independently of one table's identity column. This does not meet the need for a method that derives a numeric value as each row is inserted into a particular table. B:This choice supplies a declared value when an insert omits the column. This does not meet the need for a method that derives a numeric value as each row is inserted into a particular table. D:This choice enforces a domain rule at insert and update time. This does not meet the need for a method that derives a numeric value as each row is inserted into a particular table.
Question 45 A tested application has a very high rate of small transactional lookups and evaluates memory-optimized OLTP. Which implementation is the best fit? Use a filtered index for a small predictable row subset Use an external table for supported data virtualization Use graph node and edge tables for stored relationships Use memory-optimized tables after workload validation Explanation Correct Answer D:This approach can improve supported highly concurrent in-memory OLTP paths. Incorrect Answers A:This alternative 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. B:That technique queries external data through a mapped schema without copying every source row first. This does not meet the need for a method that can improve supported highly concurrent in-memory OLTP paths. 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 can improve supported highly concurrent in-memory OLTP paths.
Question 46 A customer list uses an indexed region key but requires two nonfiltering display fields. What is the most appropriate next design step? Choose a composite index key that leads with the query's filtering columns Use partition-aligned indexes with a partitioned table Use a native JSON column and supported JSON indexing where available Use included non-key columns to cover a selective query Explanation Correct Answer D:This choice avoids unnecessary key lookups when a predicate index misses returned columns. Incorrect Answers A:This alternative 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:That technique 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. C:This alternative applies platform-supported JSON storage and indexing capabilities. This does not meet the need for a method that avoids unnecessary key lookups when a predicate index misses returned columns.
Question 47 Two regional systems independently generate record identifiers that will later be merged. Which solution best satisfies the stated constraint? Use DATETIME2 with an explicit fractional-second precision Use UNIQUEIDENTIFIER for externally generated UUID keys Use DECIMAL(19,4) for the amount column Use VARBINARY for arbitrary binary payloads Explanation Correct Answer B:This approach stores globally assigned identifier values rather than sequential integers. Incorrect Answers A:That technique records date-and-time values with configurable subsecond precision. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers. C:This alternative stores fixed-point financial values without binary floating-point rounding. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers. D:That technique stores uninterpreted bytes without character conversion. This does not meet the need for a method that stores globally assigned identifier values rather than sequential integers.
Question 48 New inventory rows should begin with zero reserved units when the caller omits the field. What should the SQL developer implement? Use a SEQUENCE object for IDs shared by independent tables Add a FOREIGN KEY from the child record to its parent Add a UNIQUE constraint to the business identifier Add a DEFAULT constraint for a missing optional input Explanation Correct Answer D:Add a DEFAULT constraint for a missing optional input fits because it supplies a declared value when an insert omits the column. That addresses the stated technical constraint. Incorrect Answers A:This choice generates numeric values independently of one table's identity column. This does not meet the need for a method that supplies a declared value when an insert omits the column. B:This choice prevents referencing a parent key that does not exist. This does not meet the need for a method that supplies a declared value when an insert omits the 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 supplies a declared value when an insert omits the column.
Question 49 Multiple operational tables need a common number allocator before inserting rows. What is the most appropriate design decision? Add a DEFAULT constraint for a missing optional input Add a UNIQUE constraint to the business identifier Make a required business value NOT NULL Use a SEQUENCE object for IDs shared by independent tables Explanation Correct Answer D:This approach generates numeric values independently of one table's identity column. 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 generates numeric values independently of one table's identity column. B: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. C: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 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.