100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 50
A hot OLTP table must locate single primary-key rows quickly. The team selects: Use a rowstore B-tree index aligned with point lookups. Which implementation action puts this design into practice?
- Create an appropriate unique index or constraint
- Define JSON_OBJECT keys and typed values in a controlled projection
- Define BIGINT IDENTITY and primary key constraints
- Create an appropriate nonclustered or clustered key index
Explanation
Correct Answer
D:Create an appropriate nonclustered or clustered key index. This is the concrete setup for use a rowstore b-tree index aligned with point lookups, and check index seeks for single-key predicates supplies a direct confirmation.
Incorrect Answers
A:Create an appropriate unique index or constraint. This concerns not allow two accounts with the same email address. Instead, create an appropriate nonclustered or clustered key index.
B:Define JSON_OBJECT keys and typed values in a controlled projection. This concerns sharing an external json schema with downstream systems. Instead, create an appropriate nonclustered or clustered key index.
C:Define BIGINT IDENTITY and primary key constraints. This concerns a stable transaction identifier. Instead, create an appropriate nonclustered or clustered key index.
Question 51
A hot OLTP table must locate single primary-key rows quickly. After implementing the design, which check most directly validates the expected behavior?
- Check plan uses the filtered index on active-only queries
- Query JSON_VALUE for a typed documented attribute
- Insert parallel rows and confirm key uniqueness
- Check index seeks for single-key predicates
Explanation
Correct Answer
D:Check index seeks for single-key predicates. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the necessary search-key index is missing or unusable.
Incorrect Answers
A:Check plan uses the filtered index on active-only queries. This concerns fast reads only for active subscriptions. Instead, check index seeks for single-key predicates.
B:Query JSON_VALUE for a typed documented attribute. This concerns a product catalog stores variable structured attributes in json. Instead, check index seeks for single-key predicates.
C:Insert parallel rows and confirm key uniqueness. This concerns a stable transaction identifier. Instead, check index seeks for single-key predicates.
Question 52
A hot OLTP table must locate single primary-key rows quickly. A test instead finds: Point lookups scan an entire large rowset. Which underlying issue best explains this finding?
- The indexed computed column or matching expression is absent
- The index key order does not match filter and ordering
- Only the surrogate primary key is constrained
- The necessary search-key index is missing or unusable
Explanation
Correct Answer
D:The necessary search-key index is missing or unusable. It accounts for the specific observed failure: point lookups scan an entire large rowset. The intended solution is use a rowstore b-tree index aligned with point lookups with the required setup.
Incorrect Answers
A:The indexed computed column or matching expression is absent. This concerns the optimizer evaluates the function across every row. Here, the necessary search-key index is missing or unusable.
B:The index key order does not match filter and ordering. This concerns every customer inquiry triggers an expensive sort. Here, the necessary search-key index is missing or unusable.
C:Only the surrogate primary key is constrained. This concerns two records share a supposedly unique real-world identifier. Here, the necessary search-key index is missing or unusable.
Question 53
A query filters by a computed normalized email address. Which database design or engineering approach most directly meets this requirement?
- Use clustered columnstore storage for scan-heavy analytics
- Index a deterministic persisted computed column when supported
- Use a UNIQUE constraint on normalized email keys
- Use an INCLUDE column in a nonclustered index
Explanation
Correct Answer
B:Index a deterministic persisted computed column when supported. The implementation is define the deterministic expression and build an index on it; success can be checked by verify a predicate can seek using the computed expression. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use clustered columnstore storage for scan-heavy analytics. This concerns an analytical table scans billions of order-line measures. Here, index a deterministic persisted computed column when supported.
C:Use a UNIQUE constraint on normalized email keys. This concerns not allow two accounts with the same email address. Here, index a deterministic persisted computed column when supported.
D:Use an INCLUDE column in a nonclustered index. This concerns productname in output but filters on productid. Here, index a deterministic persisted computed column when supported.
Question 54
A query filters by a computed normalized email address. The team selects: Index a deterministic persisted computed column when supported. Which implementation action puts this design into practice?
- Index ProductID INCLUDE (ProductName)
- Define the deterministic expression and build an index on it
- Create an appropriate unique index or constraint
- Create SEQUENCE and request NEXT VALUE FOR on insert
Explanation
Correct Answer
B:Define the deterministic expression and build an index on it. This is the concrete setup for index a deterministic persisted computed column when supported, and verify a predicate can seek using the computed expression supplies a direct confirmation.
Incorrect Answers
A:Index ProductID INCLUDE (ProductName). This concerns productname in output but filters on productid. Instead, define the deterministic expression and build an index on it.
C:Create an appropriate unique index or constraint. This concerns not allow two accounts with the same email address. Instead, define the deterministic expression and build an index on it.
D:Create SEQUENCE and request NEXT VALUE FOR on insert. This concerns an automatically generated numeric key shared by several tables. Instead, define the deterministic expression and build an index on it.
Question 55
A query filters by a computed normalized email address. After implementing the design, which check most directly validates the expected behavior?
- Attempt a second account with the same normalized address
- Compare SUM totals to exact invoice amounts
- Compare key lookups in the execution plan
- Verify a predicate can seek using the computed expression
Explanation
Correct Answer
D:Verify a predicate can seek using the computed expression. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the indexed computed column or matching expression is absent.
Incorrect Answers
A:Attempt a second account with the same normalized address. This concerns not allow two accounts with the same email address. Instead, verify a predicate can seek using the computed expression.
B:Compare SUM totals to exact invoice amounts. This concerns a SaaS order table stores exact prices used in invoicing. Instead, verify a predicate can seek using the computed expression.
C:Compare key lookups in the execution plan. This concerns productname in output but filters on productid. Instead, verify a predicate can seek using the computed expression.
Question 56
A query filters by a computed normalized email address. A test instead finds: The optimizer evaluates the function across every row. Which underlying issue best explains this finding?
- The covering index omitted a required output column
- The indexed computed column or matching expression is absent
- Uniqueness is enforced only by a race-prone application check
- Audit query lacks an index matching its WHERE and output pattern
Explanation
Correct Answer
B:The indexed computed column or matching expression is absent. It accounts for the specific observed failure: the optimizer evaluates the function across every row. The intended solution is index a deterministic persisted computed column when supported with the required setup.
Incorrect Answers
A:The covering index omitted a required output column. This concerns thousands of key lookups inflate report i/o. Here, the indexed computed column or matching expression is absent.
C:Uniqueness is enforced only by a race-prone application check. This concerns duplicate login identities appear after concurrent requests. Here, the indexed computed column or matching expression is absent.
D:Audit query lacks an index matching its WHERE and output pattern. This concerns investigators wait on full audit-table scans. Here, the indexed computed column or matching expression is absent.
Question 57
A database needs to query yesterday's values after updates. Which database design or engineering approach most directly meets this requirement?
- Use an INCLUDE column in a nonclustered index
- Use a system-versioned temporal table
- Use an EXTERNAL TABLE over the external data location
- Evaluate columnstore dictionary compression
Explanation
Correct Answer
B:Use a system-versioned temporal table. The implementation is configure period columns and system_versioning=on; success can be checked by query for system_time as of a prior timestamp. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use an INCLUDE column in a nonclustered index. This concerns productname in output but filters on productid. Instead, use a system-versioned temporal table.
C:Use an EXTERNAL TABLE over the external data location. This concerns a reporting system reads files outside its SQL database. Instead, use a system-versioned temporal table.
D:Evaluate columnstore dictionary compression. This concerns a fact table stores many categorical repetitive values. Instead, use a system-versioned temporal table.
Question 58
A database needs to query yesterday's values after updates. The team selects: Use a system-versioned temporal table. Which implementation action puts this design into practice?
- Declare NOT NULL for mandatory columns
- Configure external data source, format and table definition
- Configure period columns and SYSTEM_VERSIONING=ON
- Index ProductID INCLUDE (ProductName)
Explanation
Correct Answer
C:Configure period columns and SYSTEM_VERSIONING=ON. This is the concrete setup for use a system-versioned temporal table, and query for system_time as of a prior timestamp supplies a direct confirmation.
Incorrect Answers
A:Declare NOT NULL for mandatory columns. This concerns an etl process bulk loads records with a reliable fixed set of values. Instead, configure period columns and SYSTEM_VERSIONING=ON.
B:Configure external data source, format and table definition. This concerns a reporting system reads files outside its SQL database. Instead, configure period columns and SYSTEM_VERSIONING=ON.
D:Index ProductID INCLUDE (ProductName). This concerns productname in output but filters on productid. Instead, configure period columns and SYSTEM_VERSIONING=ON.
Question 59
A database needs to query yesterday's values after updates. After implementing the design, which check most directly validates the expected behavior?
- Query descendants with IsDescendantOf
- Verify a predicate can seek using the computed expression
- Query FOR SYSTEM_TIME AS OF a prior timestamp
- Compare key lookups in the execution plan
Explanation
Correct Answer
C:Query FOR SYSTEM_TIME AS OF a prior timestamp. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that no temporal history was retained for updates.
Incorrect Answers
A:Query descendants with IsDescendantOf. This concerns hierarchy operations on organizational units. Instead, query FOR SYSTEM_TIME AS OF a prior timestamp.
B:Verify a predicate can seek using the computed expression. This concerns a query filters by a computed normalized email address. Instead, query FOR SYSTEM_TIME AS OF a prior timestamp.
D:Compare key lookups in the execution plan. This concerns productname in output but filters on productid. Instead, query FOR SYSTEM_TIME AS OF a prior timestamp.
Question 60
A database needs to query yesterday's values after updates. A test instead finds: Yesterday's price cannot be reconstructed. Which underlying issue best explains this finding?
- The indexed computed column or matching expression is absent
- Staging table or indexes do not match partition structure
- Columnstore index was not deployed on the analytic workload
- No temporal history was retained for updates
Explanation
Correct Answer
D:No temporal history was retained for updates. It accounts for the specific observed failure: yesterday's price cannot be reconstructed. The intended solution is use a system-versioned temporal table with the required setup.
Incorrect Answers
A:The indexed computed column or matching expression is absent. This concerns the optimizer evaluates the function across every row. Here, no temporal history was retained for updates.
B:Staging table or indexes do not match partition structure. This concerns partition switch fails with alignment error. Here, no temporal history was retained for updates.
C:Columnstore index was not deployed on the analytic workload. This concerns analytics still reads uncompressed rowstore pages. Here, no temporal history was retained for updates.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests