100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 140
A table with unique identifiers must protect natural business uniqueness too. A test instead finds: Two records share a supposedly unique real-world identifier. Which underlying issue best explains this finding?
- The necessary search-key index is missing or unusable
- Identifiers were stored as unconstrained character data
- No temporal history was retained for updates
- Only the surrogate primary key is constrained
Explanation
Correct Answer
D:Only the surrogate primary key is constrained. It accounts for the specific observed failure: two records share a supposedly unique real-world identifier. The intended solution is enforce unique on natural business key in addition to surrogate pk with the required setup.
Incorrect Answers
A:The necessary search-key index is missing or unusable. This concerns point lookups scan an entire large rowset. Here, only the surrogate primary key is constrained.
B:Identifiers were stored as unconstrained character data. This concerns string-form guid casts inflate joins and permit malformed values. Here, only the surrogate primary key is constrained.
C:No temporal history was retained for updates. This concerns yesterday's price cannot be reconstructed. Here, only the surrogate primary key is constrained.
Question 141
A billing query sorts by LastModified within a customer. Which database design or engineering approach most directly meets this requirement?
- Use a composite index supporting filter and order
- Use a composite unique constraint including TenantID
- Review clustered key choice and fill-factor tradeoffs
- Put equality-filter CustomerID before the date range in a composite index
Explanation
Correct Answer
A:Use a composite index supporting filter and order. The implementation is index (customerid, lastmodified desc); success can be checked by check plan avoids a large sort operator. The method addresses the stated data and workload constraint.
Incorrect Answers
B:Use a composite unique constraint including TenantID. This concerns preserving uniqueness across tenant boundaries. Instead, use a composite index supporting filter and order.
C:Review clustered key choice and fill-factor tradeoffs. This concerns a high-write table accumulates excessive page splits from random inserts. Instead, use a composite index supporting filter and order.
D:Put equality-filter CustomerID before the date range in a composite index. This concerns a report selects sales by customerid and then date. Instead, use a composite index supporting filter and order.
Question 142
A billing query sorts by LastModified within a customer. The team selects: Use a composite index supporting filter and order. Which implementation action puts this design into practice?
- Index (CustomerID, LastModified DESC)
- Declare the foreign key between Orders.CustomerID and Customers.CustomerID
- Create an index with WHERE Status='Open' and CustomerID as key
- Create a composite index on (CustomerID, SaleDate)
Explanation
Correct Answer
A:Index (CustomerID, LastModified DESC). This is the concrete setup for use a composite index supporting filter and order, and check plan avoids a large sort operator supplies a direct confirmation.
Incorrect Answers
B:Declare the foreign key between Orders.CustomerID and Customers.CustomerID. This concerns rejecting orders pointing to nonexistent customers. Here, index (CustomerID, LastModified DESC).
C:Create an index with WHERE Status='Open' and CustomerID as key. This concerns a compact access path excluding closed-order rows. Here, index (CustomerID, LastModified DESC).
D:Create a composite index on (CustomerID, SaleDate). This concerns a report selects sales by customerid and then date. Here, index (CustomerID, LastModified DESC).
Question 143
A billing query sorts by LastModified within a customer. After implementing the design, which check most directly validates the expected behavior?
- Inspect a representative query plan for filtered index eligibility
- Check plan uses the filtered index on active-only queries
- Query FOR SYSTEM_TIME AS OF a prior timestamp
- Check plan avoids a large Sort operator
Explanation
Correct Answer
D:Check plan avoids a large Sort operator. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the index key order does not match filter and ordering.
Incorrect Answers
A:Inspect a representative query plan for filtered index eligibility. This concerns a compact access path excluding closed-order rows. Instead, check plan avoids a large Sort operator.
B:Check plan uses the filtered index on active-only queries. This concerns fast reads only for active subscriptions. Instead, check plan avoids a large Sort operator.
C:Query FOR SYSTEM_TIME AS OF a prior timestamp. This concerns querying yesterday's values after updates. Instead, check plan avoids a large Sort operator.
Question 144
A billing query sorts by LastModified within a customer. A test instead finds: Every customer inquiry triggers an expensive sort. Which underlying issue best explains this finding?
- Composite key order does not support equality then date range
- The index key order does not match filter and ordering
- A non-Unicode column or conversion path lost characters
- The filtered predicate or query condition does not match
Explanation
Correct Answer
B:The index key order does not match filter and ordering. It accounts for the specific observed failure: every customer inquiry triggers an expensive sort. The intended solution is use a composite index supporting filter and order with the required setup.
Incorrect Answers
A:Composite key order does not support equality then date range. This concerns the engine performs a broad range scan for every customer. Here, the index key order does not match filter and ordering.
C:A non-Unicode column or conversion path lost characters. This concerns names are replaced with question marks on insertion. Here, the index key order does not match filter and ordering.
D:The filtered predicate or query condition does not match. This concerns the query still scans closed orders for every lookup. Here, the index key order does not match filter and ordering.
Question 145
A customer table has high selectivity on postal code but returns many columns. Which database design or engineering approach most directly meets this requirement?
- Use a nonclustered covering index on the common filter keys
- Use a DEFAULT constraint for the status column
- Evaluate key lookup vs covering index cost
- Use NVARCHAR for Unicode names
Explanation
Correct Answer
C:Evaluate key lookup vs covering index cost. The implementation is include common projected columns after measuring size and writes; success can be checked by compare logical reads and lookup operators. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use a nonclustered covering index on the common filter keys. This concerns returning several payload columns without repeated base-table key lookups. Instead, evaluate key lookup vs covering index cost.
B:Use a DEFAULT constraint for the status column. This concerns a default status when applications omit it. Instead, evaluate key lookup vs covering index cost.
D:Use NVARCHAR for Unicode names. This concerns retaining multilingual names. Instead, evaluate key lookup vs covering index cost.
Question 146
A customer table has high selectivity on postal code but returns many columns. The team selects: Evaluate key lookup vs covering index cost. Which implementation action puts this design into practice?
- Size the NVARCHAR column for expected characters
- Configure period columns and SYSTEM_VERSIONING=ON
- Include common projected columns after measuring size and writes
- Define appropriate (ActorID, ChangedAt) key columns and includes
Explanation
Correct Answer
C:Include common projected columns after measuring size and writes. This is the concrete setup for evaluate key lookup vs covering index cost, and compare logical reads and lookup operators supplies a direct confirmation.
Incorrect Answers
A:Size the NVARCHAR column for expected characters. This concerns retaining multilingual names. Here, include common projected columns after measuring size and writes.
B:Configure period columns and SYSTEM_VERSIONING=ON. This concerns querying yesterday's values after updates. Here, include common projected columns after measuring size and writes.
D:Define appropriate (ActorID, ChangedAt) key columns and includes. This concerns returning several payload columns without repeated base-table key lookups. Here, include common projected columns after measuring size and writes.
Question 147
A customer table has high selectivity on postal code but returns many columns. After implementing the design, which check most directly validates the expected behavior?
- Compare logical reads and lookup operators
- Try duplicate business code with a different ID
- Round-trip Arabic and Chinese customer names
- Inspect seeks and logical reads for the target predicate
Explanation
Correct Answer
A:Compare logical reads and lookup operators. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that a narrow index omits frequently requested payload columns.
Incorrect Answers
B:Try duplicate business code with a different ID. This concerns protecting natural business uniqueness too. Instead, compare logical reads and lookup operators.
C:Round-trip Arabic and Chinese customer names. This concerns retaining multilingual names. Instead, compare logical reads and lookup operators.
D:Inspect seeks and logical reads for the target predicate. This concerns a report selects sales by customerid and then date. Instead, compare logical reads and lookup operators.
Question 148
A customer table has high selectivity on postal code but returns many columns. A test instead finds: A selective index seek causes excessive bookmark lookups. Which underlying issue best explains this finding?
- A narrow index omits frequently requested payload columns
- A non-Unicode column or conversion path lost characters
- No useful index on transaction token
- Audit query lacks an index matching its WHERE and output pattern
Explanation
Correct Answer
A:A narrow index omits frequently requested payload columns. It accounts for the specific observed failure: a selective index seek causes excessive bookmark lookups. The intended solution is evaluate key lookup vs covering index cost with the required setup.
Incorrect Answers
B:A non-Unicode column or conversion path lost characters. This concerns names are replaced with question marks on insertion. Here, a narrow index omits frequently requested payload columns.
C:No useful index on transaction token. This concerns payment token searches do full scans under load. Here, a narrow index omits frequently requested payload columns.
D:Audit query lacks an index matching its WHERE and output pattern. This concerns investigators wait on full audit-table scans. Here, a narrow index omits frequently requested payload columns.
Question 149
An archive system requires an unchanging business document image. Which database design or engineering approach most directly meets this requirement?
- Use a nonclustered covering index on the common filter keys
- Use append-only ledger table for immutable entries where appropriate
- Enforce UNIQUE on natural business key in addition to surrogate PK
- Use controlled DML permissions or protected stored procedures
Explanation
Correct Answer
B:Use append-only ledger table for immutable entries where appropriate. The implementation is define append-only ledger behavior and archive digest policy; success can be checked by try update on committed entries and verify rejection. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use a nonclustered covering index on the common filter keys. This concerns returning several payload columns without repeated base-table key lookups. Instead, use append-only ledger table for immutable entries where appropriate.
C:Enforce UNIQUE on natural business key in addition to surrogate PK. This concerns protecting natural business uniqueness too. Instead, use append-only ledger table for immutable entries where appropriate.
D:Use controlled DML permissions or protected stored procedures. This concerns preventing updating immutable posted rows. Instead, use append-only ledger table for immutable entries where appropriate.
Question 150
An archive system requires an unchanging business document image. The team selects: Use append-only ledger table for immutable entries where appropriate. Which implementation action puts this design into practice?
- Define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef
- Create separate unique constraint on normalized BusinessCode
- Store UTC timestamps and document their time zone semantics
- Define append-only ledger behavior and archive digest policy
Explanation
Correct Answer
D:Define append-only ledger behavior and archive digest policy. This is the concrete setup for use append-only ledger table for immutable entries where appropriate, and try update on committed entries and verify rejection supplies a direct confirmation.
Incorrect Answers
A:Define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef. This concerns preserving uniqueness across tenant boundaries. Instead, define append-only ledger behavior and archive digest policy.
B:Create separate unique constraint on normalized BusinessCode. This concerns protecting natural business uniqueness too. Instead, define append-only ledger behavior and archive digest policy.
C:Store UTC timestamps and document their time zone semantics. This concerns a service logs high-precision utc event times across regions. Instead, define append-only ledger behavior and archive digest policy.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests