100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 80
A large sales history should prune old monthly partitions. A test instead finds: All history partitions are scanned for one month. Which underlying issue best explains this finding?
- A global uniqueness rule omitted the tenant scope
- Rowstore layout is mismatched to large columnar aggregation scans
- Predicates or boundaries prevent partition elimination
- Staging table or indexes do not match partition structure
Explanation
Correct Answer
C:Predicates or boundaries prevent partition elimination. It accounts for the specific observed failure: all history partitions are scanned for one month. The intended solution is use partitioning by a suitable date key with the required setup.
Incorrect Answers
A:A global uniqueness rule omitted the tenant scope. This concerns tenant b cannot use an identifier already used by tenant a. Here, predicates or boundaries prevent partition elimination.
B:Rowstore layout is mismatched to large columnar aggregation scans. This concerns the reporting workload reads far more pages than budgeted. Here, predicates or boundaries prevent partition elimination.
D:Staging table or indexes do not match partition structure. This concerns partition switch fails with alignment error. Here, predicates or boundaries prevent partition elimination.
Question 81
An event ingestion workload needs memory-optimized high-concurrency access. Which database design or engineering approach most directly meets this requirement?
- Evaluate key lookup vs covering index cost
- Use a memory-optimized table for the supported workload
- Use DATETIME2 with an explicit UTC convention
- Use HIERARCHYID for tree paths on supported SQL editions
Explanation
Correct Answer
B:Use a memory-optimized table for the supported workload. The implementation is create memory-optimized filegroup and table settings where required; success can be checked by benchmark latch/contention under concurrent inserts. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Evaluate key lookup vs covering index cost. This is relevant when a customer table has high selectivity on postal code but returns many columns. Instead, use a memory-optimized table for the supported workload.
C:Use DATETIME2 with an explicit UTC convention. This concerns a service logs high-precision utc event times across regions. Instead, use a memory-optimized table for the supported workload.
D:Use HIERARCHYID for tree paths on supported SQL editions. This concerns hierarchy operations on organizational units. Instead, use a memory-optimized table for the supported workload.
Question 82
An event ingestion workload needs memory-optimized high-concurrency access. The team selects: Use a memory-optimized table for the supported workload. Which implementation action puts this design into practice?
- Create memory-optimized filegroup and table settings where required
- Store UTC timestamps and document their time zone semantics
- Create an index with WHERE Status='Open' and CustomerID as key
- Create columnstore index for scan-oriented measurements
Explanation
Correct Answer
A:Create memory-optimized filegroup and table settings where required. This is the concrete setup for use a memory-optimized table for the supported workload, and benchmark latch/contention under concurrent inserts supplies a direct confirmation.
Incorrect Answers
B:Store UTC timestamps and document their time zone semantics. This concerns a service logs high-precision utc event times across regions. Instead, create memory-optimized filegroup and table settings where required.
C:Create an index with WHERE Status='Open' and CustomerID as key. This concerns a compact access path excluding closed-order rows. Instead, create memory-optimized filegroup and table settings where required.
D:Create columnstore index for scan-oriented measurements. This concerns a fact table stores many categorical repetitive values. Instead, create memory-optimized filegroup and table settings where required.
Question 83
An event ingestion workload needs memory-optimized high-concurrency access. After implementing the design, which check most directly validates the expected behavior?
- Compare page splits and fragmentation before/after under same load
- Compare stored values with independently logged UTC instants
- Benchmark latch/contention under concurrent inserts
- Compare NULL-heavy storage and update cost
Explanation
Correct Answer
C:Benchmark latch/contention under concurrent inserts. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the deployed table is still disk-based rowstore without intended optimization.
Incorrect Answers
A:Compare page splits and fragmentation before/after under same load. This concerns a high-write table accumulates excessive page splits from random inserts. Here, benchmark latch/contention under concurrent inserts.
B:Compare stored values with independently logged UTC instants. This concerns a service logs high-precision utc event times across regions. Here, benchmark latch/contention under concurrent inserts.
D:Compare NULL-heavy storage and update cost. This concerns sparse optional attributes. Here, benchmark latch/contention under concurrent inserts.
Question 84
An event ingestion workload needs memory-optimized high-concurrency access. A test instead finds: Hot-row locking remains a throughput bottleneck. Which underlying issue best explains this finding?
- The deployed table is still disk-based rowstore without intended optimization
- Columnstore index was not deployed on the analytic workload
- Client-side key generation has no coordinated uniqueness guarantee
- Local times were stored without time zone normalization
Explanation
Correct Answer
A:The deployed table is still disk-based rowstore without intended optimization. It accounts for the specific observed failure: hot-row locking remains a throughput bottleneck. The intended solution is use a memory-optimized table for the supported workload with the required setup.
Incorrect Answers
B:Columnstore index was not deployed on the analytic workload. This concerns analytics still reads uncompressed rowstore pages. Here, the deployed table is still disk-based rowstore without intended optimization.
C:Client-side key generation has no coordinated uniqueness guarantee. This concerns application-generated keys collide during retries. Here, the deployed table is still disk-based rowstore without intended optimization.
D:Local times were stored without time zone normalization. This concerns events from different regions sort in an inconsistent order. Here, the deployed table is still disk-based rowstore without intended optimization.
Question 85
An operational queue needs a stable transaction identifier. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- Review clustered key choice and fill-factor tradeoffs
- Use IDENTITY for a table-local surrogate key
- Create an appropriate nonclustered index on token
- Define BIGINT IDENTITY and primary key constraints
- Enforce UNIQUE on natural business key in addition to surrogate PK
Explanation
Correct Answers
B:Use IDENTITY for a table-local surrogate key. The implementation is define bigint identity and primary key constraints; success can be checked by insert parallel rows and confirm key uniqueness. The method addresses the stated data and workload constraint.
D:This is the concrete configuration of the specified design: define bigint identity and primary key constraints. The choice makes the implementation testable in the given workload.
Incorrect Answers
A:Review clustered key choice and fill-factor tradeoffs. This concerns a high-write table accumulates excessive page splits from random inserts. Instead, use IDENTITY for a table-local surrogate key.
C:Create an appropriate nonclustered index on token. This concerns faster lookup by external transaction token. Instead, use IDENTITY for a table-local surrogate key.
E:Enforce UNIQUE on natural business key in addition to surrogate PK. This concerns protecting natural business uniqueness too. Instead, use IDENTITY for a table-local surrogate key.
Question 86
An operational queue needs a stable transaction identifier. The team selects: Use IDENTITY for a table-local surrogate key. Which implementation action puts this design into practice?
- Define BIGINT IDENTITY and primary key constraints
- Create an appropriate nonclustered or clustered key index
- Define append-only ledger behavior and archive digest policy
- Choose token as seek key and assess uniqueness
Explanation
Correct Answer
A:Define BIGINT IDENTITY and primary key constraints. This is the concrete setup for use identity for a table-local surrogate key, and insert parallel rows and confirm key uniqueness supplies a direct confirmation.
Incorrect Answers
B:Create an appropriate nonclustered or clustered key index. This concerns locating single primary-key rows quickly. Instead, define BIGINT IDENTITY and primary key constraints.
C:Define append-only ledger behavior and archive digest policy. This concerns an unchanging business document image. Instead, define BIGINT IDENTITY and primary key constraints.
D:Choose token as seek key and assess uniqueness. This concerns faster lookup by external transaction token. Instead, define BIGINT IDENTITY and primary key constraints.
Question 87
An operational queue needs a stable transaction identifier. After implementing the design, which check most directly validates the expected behavior?
- Check index seeks for single-key predicates
- Query FOR SYSTEM_TIME AS OF a prior timestamp
- Insert same reference for different tenants and reject within one tenant
- Insert parallel rows and confirm key uniqueness
Explanation
Correct Answer
D:Insert parallel rows and confirm key uniqueness. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that client-side key generation has no coordinated uniqueness guarantee.
Incorrect Answers
A:Check index seeks for single-key predicates. This concerns locating single primary-key rows quickly. Instead, insert parallel rows and confirm key uniqueness.
B:Query FOR SYSTEM_TIME AS OF a prior timestamp. This concerns querying yesterday's values after updates. Instead, insert parallel rows and confirm key uniqueness.
C:Insert same reference for different tenants and reject within one tenant. This concerns preserving uniqueness across tenant boundaries. Instead, insert parallel rows and confirm key uniqueness.
Question 88
An operational queue needs a stable transaction identifier. A test instead finds: Application-generated keys collide during retries. Which underlying issue best explains this finding?
- Client-side key generation has no coordinated uniqueness guarantee
- No useful index on transaction token
- A global uniqueness rule omitted the tenant scope
- The filtered predicate or query condition does not match
Explanation
Correct Answer
A:Client-side key generation has no coordinated uniqueness guarantee. It accounts for the specific observed failure: application-generated keys collide during retries. The intended solution is use identity for a table-local surrogate key with the required setup.
Incorrect Answers
B:No useful index on transaction token. This concerns payment token searches do full scans under load. Here, client-side key generation has no coordinated uniqueness guarantee.
C:A global uniqueness rule omitted the tenant scope. This concerns tenant b cannot use an identifier already used by tenant a. Here, client-side key generation has no coordinated uniqueness guarantee.
D:The filtered predicate or query condition does not match. This concerns the query still scans closed orders for every lookup. Here, client-side key generation has no coordinated uniqueness guarantee.
Question 89
A wide audit table queried by timestamp and actor must return several payload columns without repeated base-table key lookups. Which database design or engineering approach most directly meets this requirement?
- Use clustered columnstore storage for scan-heavy analytics
- Use a rowstore B-tree index aligned with point lookups
- Use a nonclustered covering index on the common filter keys
- Evaluate key lookup vs covering index cost
Explanation
Correct Answer
C:Use a nonclustered covering index on the common filter keys. The implementation is define appropriate (actorid, changedat) key columns and includes; success can be checked by compare plan estimates and actual reads on audit lookups. 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. Instead, use a nonclustered covering index on the common filter keys.
B:Use a rowstore B-tree index aligned with point lookups. This concerns locating single primary-key rows quickly. Instead, use a nonclustered covering index on the common filter keys.
D:Evaluate key lookup vs covering index cost. This is relevant when a customer table has high selectivity on postal code but returns many columns. Instead, use a nonclustered covering index on the common filter keys.
Question 90
A wide audit table queried by timestamp and actor must return several payload columns without repeated base-table key lookups. The team selects: Use a nonclustered covering index on the common filter keys. Which implementation action puts this design into practice?
- Create an appropriate nonclustered or clustered key index
- Define appropriate (ActorID, ChangedAt) key columns and includes
- Grant EXECUTE on a posting procedure rather than direct UPDATE
- Include common projected columns after measuring size and writes
Explanation
Correct Answer
B:Define appropriate (ActorID, ChangedAt) key columns and includes. This is the concrete setup for use a nonclustered covering index on the common filter keys, and compare plan estimates and actual reads on audit lookups supplies a direct confirmation.
Incorrect Answers
A:Create an appropriate nonclustered or clustered key index. This concerns locating single primary-key rows quickly. Instead, define appropriate (ActorID, ChangedAt) key columns and includes.
C:Grant EXECUTE on a posting procedure rather than direct UPDATE. This concerns preventing updating immutable posted rows. Instead, define appropriate (ActorID, ChangedAt) key columns and includes.
D:Include common projected columns after measuring size and writes. This is relevant when a customer table has high selectivity on postal code but returns many columns. Instead, define appropriate (ActorID, ChangedAt) key columns and includes.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests