100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 30
A user directory must not allow two accounts with the same email address. The team selects: Use a UNIQUE constraint on normalized email keys. Which implementation action puts this design into practice?
- Define the deterministic expression and build an index on it
- Create an appropriate unique index or constraint
- Configure period columns and SYSTEM_VERSIONING=ON
- Create separate unique constraint on normalized BusinessCode
Explanation
Correct Answer
B:Create an appropriate unique index or constraint. This is the concrete setup for use a unique constraint on normalized email keys, and attempt a second account with the same normalized address supplies a direct confirmation.
Incorrect Answers
A:Define the deterministic expression and build an index on it. This concerns a query filters by a computed normalized email address. Instead, create an appropriate unique index or constraint.
C:Configure period columns and SYSTEM_VERSIONING=ON. This concerns querying yesterday's values after updates. Instead, create an appropriate unique index or constraint.
D:Create separate unique constraint on normalized BusinessCode. This concerns protecting natural business uniqueness too. Instead, create an appropriate unique index or constraint.
Question 31
A user directory must not allow two accounts with the same email address. After implementing the design, which check most directly validates the expected behavior?
- Attempt a second account with the same normalized address
- Verify a predicate can seek using the computed expression
- Attempt an insert with an absent customer ID and expect a constraint error
- Verify distinct increasing allocated values across both tables
Explanation
Correct Answer
A:Attempt a second account with the same normalized address. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that uniqueness is enforced only by a race-prone application check.
Incorrect Answers
B:Verify a predicate can seek using the computed expression. This concerns a query filters by a computed normalized email address. Here, attempt a second account with the same normalized address.
C:Attempt an insert with an absent customer ID and expect a constraint error. This concerns rejecting orders pointing to nonexistent customers. Here, attempt a second account with the same normalized address.
D:Verify distinct increasing allocated values across both tables. This concerns an automatically generated numeric key shared by several tables. Here, attempt a second account with the same normalized address.
Question 32
A user directory must not allow two accounts with the same email address. A test instead finds: Duplicate login identities appear after concurrent requests. Which underlying issue best explains this finding?
- A global uniqueness rule omitted the tenant scope
- Approximate floating-point representation was chosen for currency
- The indexed computed column or matching expression is absent
- Uniqueness is enforced only by a race-prone application check
Explanation
Correct Answer
D:Uniqueness is enforced only by a race-prone application check. It accounts for the specific observed failure: duplicate login identities appear after concurrent requests. The intended solution is use a unique constraint on normalized email keys 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, uniqueness is enforced only by a race-prone application check.
B:Approximate floating-point representation was chosen for currency. This concerns accumulated invoice totals vary after repeated arithmetic. Here, uniqueness is enforced only by a race-prone application check.
C:The indexed computed column or matching expression is absent. This concerns the optimizer evaluates the function across every row. Here, uniqueness is enforced only by a race-prone application check.
Question 33
An application reads only open orders for each customer and needs a compact access path excluding closed-order rows. Which database design or engineering approach most directly meets this requirement?
- Use an INCLUDE column in a nonclustered index
- Use a filtered nonclustered index on the active subset
- Create an appropriate nonclustered index on token
- Store GUIDs using UNIQUEIDENTIFIER rather than text
Explanation
Correct Answer
B:Use a filtered nonclustered index on the active subset. The implementation is create an index with where status='open' and customerid as key; success can be checked by inspect a representative query plan for filtered index eligibility. 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 filtered nonclustered index on the active subset.
C:Create an appropriate nonclustered index on token. This concerns faster lookup by external transaction token. Instead, use a filtered nonclustered index on the active subset.
D:Store GUIDs using UNIQUEIDENTIFIER rather than text. This concerns a data warehouse records guid request identifiers from clients. Instead, use a filtered nonclustered index on the active subset.
Question 34
An application reads only open orders for each customer and needs a compact access path excluding closed-order rows. The team selects: Use a filtered nonclustered index on the active subset. Which implementation action puts this design into practice?
- Create an appropriate nonclustered or clustered key index
- Create an index with WHERE Status='Open' and CustomerID as key
- Declare the foreign key between Orders.CustomerID and Customers.CustomerID
- Create a composite index on (CustomerID, SaleDate)
Explanation
Correct Answer
B:Create an index with WHERE Status='Open' and CustomerID as key. This is the concrete setup for use a filtered nonclustered index on the active subset, and inspect a representative query plan for filtered index eligibility supplies a direct confirmation.
Incorrect Answers
A:Create an appropriate nonclustered or clustered key index. This concerns locating single primary-key rows quickly. Instead, create an index with WHERE Status='Open' and CustomerID as key.
C:Declare the foreign key between Orders.CustomerID and Customers.CustomerID. This concerns rejecting orders pointing to nonexistent customers. Instead, create an index with WHERE Status='Open' and CustomerID as key.
D:Create a composite index on (CustomerID, SaleDate). This concerns a report selects sales by customerid and then date. Instead, create an index with WHERE Status='Open' and CustomerID as key.
Question 35
An application reads only open orders for each customer and needs a compact access path excluding closed-order rows. After implementing the design, which check most directly validates the expected behavior?
- Inspect a representative query plan for filtered index eligibility
- Check plan avoids a large Sort operator
- Attempt direct UPDATE from application principal and confirm denial
- Query descendants with IsDescendantOf
Explanation
Correct Answer
A:Inspect a representative query plan for filtered index eligibility. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that the filtered predicate or query condition does not match.
Incorrect Answers
B:Check plan avoids a large Sort operator. This concerns a billing query sorts by lastmodified within a customer. Instead, inspect a representative query plan for filtered index eligibility.
C:Attempt direct UPDATE from application principal and confirm denial. This concerns preventing updating immutable posted rows. Instead, inspect a representative query plan for filtered index eligibility.
D:Query descendants with IsDescendantOf. This concerns hierarchy operations on organizational units. Instead, inspect a representative query plan for filtered index eligibility.
Question 36
An application reads only open orders for each customer and needs a compact access path excluding closed-order rows. A test instead finds: The query still scans closed orders for every lookup. Which underlying issue best explains this finding?
- Indexing or typed extraction does not match the JSON path
- The index key order does not match filter and ordering
- The filtered predicate or query condition does not match
- The hierarchy is stored as unvalidated arbitrary text
Explanation
Correct Answer
C:The filtered predicate or query condition does not match. It accounts for the specific observed failure: the query still scans closed orders for every lookup. The intended solution is use a filtered nonclustered index on the active subset with the required setup.
Incorrect Answers
A:Indexing or typed extraction does not match the JSON path. This concerns property lookups require parsing every json document. Here, the filtered predicate or query condition does not match.
B:The index key order does not match filter and ordering. This concerns every customer inquiry triggers an expensive sort. Here, the filtered predicate or query condition does not match.
D:The hierarchy is stored as unvalidated arbitrary text. This concerns string-prefix guesswork. Here, the filtered predicate or query condition does not match.
Question 37
A report selects sales by CustomerID and then Date. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- Put equality-filter CustomerID before the date range in a composite index
- Use an INCLUDE column in a nonclustered index
- Use partitioning by a suitable date key
- Create a composite index on (CustomerID, SaleDate)
- Create an index on SaleDate alone without leading CustomerID
Explanation
Correct Answers
A:Put equality-filter CustomerID before the date range in a composite index. The implementation is create a composite index on (customerid, saledate); success can be checked by inspect seeks and logical reads for the target predicate. The method addresses the stated data and workload constraint.
D:This is the concrete configuration of the specified design: create a composite index on (customerid, saledate). The choice makes the implementation testable in the given workload.
Incorrect Answers
B:Use an INCLUDE column in a nonclustered index. This concerns productname in output but filters on productid. Here, put equality-filter CustomerID before the date range in a composite index.
C:Use partitioning by a suitable date key. This concerns prune old monthly partitions. Here, put equality-filter CustomerID before the date range in a composite index.
E:An index keyed only on SaleDate omits the required leading equality filter on CustomerID, so it cannot provide the sought CustomerID-then-date access pattern.
Question 38
A report selects sales by CustomerID and then Date. The team selects: Put equality-filter CustomerID before the date range in a composite index. Which implementation action puts this design into practice?
- Define the deterministic expression and build an index on it
- Index (CustomerID, LastModified DESC)
- Declare the foreign key between Orders.CustomerID and Customers.CustomerID
- Create a composite index on (CustomerID, SaleDate)
Explanation
Correct Answer
D:Create a composite index on (CustomerID, SaleDate). This is the concrete setup for put equality-filter customerid before the date range in a composite index, and inspect seeks and logical reads for the target predicate supplies a direct confirmation.
Incorrect Answers
A:Define the deterministic expression and build an index on it. This concerns a query filters by a computed normalized email address. Instead, create a composite index on (CustomerID, SaleDate).
B:Index (CustomerID, LastModified DESC). This concerns a billing query sorts by lastmodified within a customer. Instead, create a composite index on (CustomerID, SaleDate).
C:Declare the foreign key between Orders.CustomerID and Customers.CustomerID. This concerns rejecting orders pointing to nonexistent customers. Instead, create a composite index on (CustomerID, SaleDate).
Question 39
A report selects sales by CustomerID and then Date. After implementing the design, which check most directly validates the expected behavior?
- Inspect touched partitions for a bounded date filter
- Inspect seeks and logical reads for the target predicate
- Compare plan estimates and actual reads on audit lookups
- Compare logical reads and lookup operators
Explanation
Correct Answer
B:Inspect seeks and logical reads for the target predicate. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that composite key order does not support equality then date range.
Incorrect Answers
A:Inspect touched partitions for a bounded date filter. This concerns prune old monthly partitions. Instead, inspect seeks and logical reads for the target predicate.
C:Compare plan estimates and actual reads on audit lookups. This concerns returning several payload columns without repeated base-table key lookups. Instead, inspect seeks and logical reads for the target predicate.
D:Compare logical reads and lookup operators. This is relevant when a customer table has high selectivity on postal code but returns many columns. Instead, inspect seeks and logical reads for the target predicate.
Question 40
A report selects sales by CustomerID and then Date. A test instead finds: The engine performs a broad range scan for every customer. Which underlying issue best explains this finding?
- The filtered predicate or query condition does not match
- Composite key order does not support equality then date range
- JSON contract and projected field names were not stabilized
- The index key order does not match filter and ordering
Explanation
Correct Answer
B:Composite key order does not support equality then date range. It accounts for the specific observed failure: the engine performs a broad range scan for every customer. The intended solution is put equality-filter customerid before the date range in a composite index with the required setup.
Incorrect Answers
A:The filtered predicate or query condition does not match. This concerns the query still scans closed orders for every lookup. Here, composite key order does not support equality then date range.
C:JSON contract and projected field names were not stabilized. This concerns a client breaks after column rename changes payload. Here, composite key order does not support equality then date range.
D:The index key order does not match filter and ordering. This concerns every customer inquiry triggers an expensive sort. Here, composite key order does not support equality then date range.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests