Microsoft DP-800 Practice Test 4
Question 30
Brookfield Labs is responsible for an Azure SQL or Microsoft SQL workload. An organization needs cryptographic evidence when transaction records change. Operational evidence: Ordinary audit timestamps are not tamper-evident. What operational condition would provide the earliest useful regression warning?
- Track digest storage, verification failures, and feature compatibility
- Track fragmentation and write amplification over time
- Monitor truncation failures and unexpected growth
- Track index usage and modification overhead after rollout
Explanation
Correct Answer
A:
Ordinary audit timestamps are not tamper-evident. Track digest storage, verification failures, and feature compatibility.
Incorrect Answers
B:
B-tree fill factor is a page-maintenance tuning choice. Reducing page splits cannot make an audit timestamp or record history tamper-evident.
C:
“Monitor truncation failures and unexpected growth” concerns Fixed CHAR allocation wastes space on short product names; the reported problem instead is Ordinary audit timestamps are not tamper-evident. Track digest storage, verification failures, and feature compatibility.
D:
“Track index usage and modification overhead after rollout” concerns Table scans occur despite highly selective filters; the reported problem instead is Ordinary audit timestamps are not tamper-evident. Track digest storage, verification failures, and feature compatibility.
Question 31
At Cascade Manufacturing, an app stores variable product attributes next to relational keys. The observed constraint is: A rigid collection of nullable columns changes with every product type. What initial check best narrows the technical cause without altering business data?
- Identify which attributes require typed relational filtering
- Compare contention waits and transactional feature limitations
- Compare duplicates and identify the true unique business key
- Identify cross-table allocation and gap-tolerance requirements
Explanation
Correct Answer
A:
A rigid collection of nullable columns changes with every product type. Identify which attributes require typed relational filtering.
Incorrect Answers
B:
“Compare contention waits and transactional feature limitations” concerns A small set of hot rows is updated at high frequency; the reported problem instead is A rigid collection of nullable columns changes with every product type. Identify which attributes require typed relational filtering.
C:
“Compare duplicates and identify the true unique business key” concerns Application-level duplicate checks race under concurrency; the reported problem instead is A rigid collection of nullable columns changes with every product type. Identify which attributes require typed relational filtering.
D:
“Identify cross-table allocation and gap-tolerance requirements” concerns IDENTITY on one table cannot coordinate numbering across tables; the reported problem instead is A rigid collection of nullable columns changes with every product type. Identify which attributes require typed relational filtering.
Question 32
A database engineer at Clearwater Utilities is investigating this case: An app stores variable product attributes next to relational keys. The relevant finding is: A rigid collection of nullable columns changes with every product type. Which proposed change is the most suitable response to the observed constraint?
- Store flexible attributes in JSON with appropriate validation and access patterns
- Create supported computed-column or JSON index strategy for the hot path
- Evaluate fill factor or a less-random clustering key when evidence supports it
- Evaluate a memory-optimized table only when workload and supported features fit
Explanation
Correct Answer
A:
A rigid collection of nullable columns changes with every product type. Store flexible attributes in JSON with appropriate validation and access patterns.
Incorrect Answers
B:
“Create supported computed-column or JSON index strategy for the hot path” concerns Repeated JSON_VALUE evaluation produces expensive scans; the reported problem instead is A rigid collection of nullable columns changes with every product type. Store flexible attributes in JSON with appropriate validation and access patterns.
C:
Fill factor can reduce fragmentation from inserts or updates, but does not solve schema churn caused by product categories requiring different attributes.
D:
“Evaluate a memory-optimized table only when workload and supported features fit” concerns A small set of hot rows is updated at high frequency; the reported problem instead is A rigid collection of nullable columns changes with every product type. Store flexible attributes in JSON with appropriate validation and access patterns.
Question 33
Cedarbridge Health is responsible for an Azure SQL or Microsoft SQL workload. An app stores variable product attributes next to relational keys. Operational evidence: A rigid collection of nullable columns changes with every product type. What isolated pilot would provide the strongest evidence before rollout?
- Insert synthetic transfer edges and run a MATCH graph query
- Insert and extract synthetic nested product properties
- Insert sample rows omitting and specifying a timestamp
- Benchmark representative insert sequences with alternative designs
Explanation
Correct Answer
B:
A rigid collection of nullable columns changes with every product type. Insert and extract synthetic nested product properties.
Incorrect Answers
A:
Graph edges model relationships among entities. They do not by themselves give a flexible representation of different product attributes with evolving shapes.
C:
An insertion timestamp records an event time, not the changing set of optional product attributes that drives repeated nullable-column additions.
D:
Fill factor can reduce fragmentation from inserts or updates, but does not solve schema churn caused by product categories requiring different attributes.
Question 34
At Ashwood Solutions, an app stores variable product attributes next to relational keys. The observed constraint is: A rigid collection of nullable columns changes with every product type. Which acceptance result most directly demonstrates the requirement has been satisfied?
- JSON parsing and indexed queries return correct typed values
- Missing values receive the documented default without overwriting explicit values
- Active lookups improve and archived rows remain correct
- Queries filter correctly and use the intended supported index
Explanation
Correct Answer
A:
A rigid collection of nullable columns changes with every product type. JSON parsing and indexed queries return correct typed values.
Incorrect Answers
B:
An insertion timestamp records an event time, not the changing set of optional product attributes that drives repeated nullable-column additions.
C:
“Active lookups improve and archived rows remain correct” concerns A full index is large and mostly unused; the reported problem instead is A rigid collection of nullable columns changes with every product type. JSON parsing and indexed queries return correct typed values.
D:
“Queries filter correctly and use the intended supported index” concerns Repeated JSON_VALUE evaluation produces expensive scans; the reported problem instead is A rigid collection of nullable columns changes with every product type. JSON parsing and indexed queries return correct typed values.
Question 35
A database engineer at Seabrook University is investigating this case: An app stores variable product attributes next to relational keys. The relevant finding is: A rigid collection of nullable columns changes with every product type. What operational condition would provide the earliest useful regression warning?
- Monitor JSON validation failures and query cost
- Monitor truncation failures and unexpected growth
- Monitor edge integrity and query cost
- Monitor connector latency and remote access failures
Explanation
Correct Answer
A:
A rigid collection of nullable columns changes with every product type. Monitor JSON validation failures and query cost.
Incorrect Answers
B:
“Monitor truncation failures and unexpected growth” concerns Fixed CHAR allocation wastes space on short product names; the reported problem instead is A rigid collection of nullable columns changes with every product type. Monitor JSON validation failures and query cost.
C:
Graph edges model relationships among entities. They do not by themselves give a flexible representation of different product attributes with evolving shapes.
D:
“Monitor connector latency and remote access failures” concerns Copying all external data hourly is costly; the reported problem instead is A rigid collection of nullable columns changes with every product type. Monitor JSON validation failures and query cost.
Question 36
Atlas Publishing is responsible for an Azure SQL or Microsoft SQL workload. A large orders table filters frequently on a scalar JSON customer segment. Operational evidence: Repeated JSON_VALUE evaluation produces expensive scans. What initial check best narrows the technical cause without altering business data?
- Inspect actual execution plans and filter selectivity for CustomerId and OrderDate
- Inspect whether system time and change history must be queryable
- Inspect repeated JSON paths and execution-plan predicates
- Compare columnstore suitability with row modification frequency and query plan
Explanation
Correct Answer
C:
Repeated JSON_VALUE evaluation produces expensive scans. Inspect repeated JSON paths and execution-plan predicates.
Incorrect Answers
A:
A general rowstore index can help ordinary relational predicates but need not make expressions over JSON_VALUE sargable. Indexing an appropriate computed JSON path targets the repeated extraction.
B:
Temporal history retains past row versions; it does not avoid repeated JSON_VALUE evaluation inside current queries or provide a selective access path to extracted JSON values.
D:
Columnstore is intended for broad analytic scans and compression, not selective predicates that repeatedly compute a JSON property from stored documents.
Question 37
At Mountainview Realty, a large orders table filters frequently on a scalar JSON customer segment. The observed constraint is: Repeated JSON_VALUE evaluation produces expensive scans. Which proposed change is the most suitable response to the observed constraint?
- Create a filtered index matching the stable active-status predicate
- Create supported computed-column or JSON index strategy for the hot path
- Create a suitably ordered nonclustered covering index after evaluating write overhead
- Store flexible attributes in JSON with appropriate validation and access patterns
Explanation
Correct Answer
B:
Repeated JSON_VALUE evaluation produces expensive scans. Create supported computed-column or JSON index strategy for the hot path.
Incorrect Answers
A:
“Create a filtered index matching the stable active-status predicate” concerns A full index is large and mostly unused; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Create supported computed-column or JSON index strategy for the hot path.
C:
A general rowstore index can help ordinary relational predicates but need not make expressions over JSON_VALUE sargable. Indexing an appropriate computed JSON path targets the repeated extraction.
D:
“Store flexible attributes in JSON with appropriate validation and access patterns” concerns A rigid collection of nullable columns changes with every product type; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Create supported computed-column or JSON index strategy for the hot path.
Question 38
A database engineer at Willow Creek Finance is investigating this case: A large orders table filters frequently on a scalar JSON customer segment. The relevant finding is: Repeated JSON_VALUE evaluation produces expensive scans. What isolated pilot would provide the strongest evidence before rollout?
- Compare query plans before and after indexing in a test database
- Update synthetic contracts and query an earlier AS OF timestamp
- Query a small approved external dataset in isolation
- Replay a representative workload against an indexed test copy
Explanation
Correct Answer
A:
Repeated JSON_VALUE evaluation produces expensive scans. Compare query plans before and after indexing in a test database.
Incorrect Answers
B:
Temporal history retains past row versions; it does not avoid repeated JSON_VALUE evaluation inside current queries or provide a selective access path to extracted JSON values.
C:
“Query a small approved external dataset in isolation” concerns Copying all external data hourly is costly; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Compare query plans before and after indexing in a test database.
D:
A general rowstore index can help ordinary relational predicates but need not make expressions over JSON_VALUE sargable. Indexing an appropriate computed JSON path targets the repeated extraction.
Question 39
Granite Ridge Technology is responsible for an Azure SQL or Microsoft SQL workload. A large orders table filters frequently on a scalar JSON customer segment. Operational evidence: Repeated JSON_VALUE evaluation produces expensive scans. Which acceptance result most directly demonstrates the requirement has been satisfied?
- Queries prune dates appropriately and retention moves only intended records
- Selective reads use index seeks and write latency stays acceptable
- Queries filter correctly and use the intended supported index
- JSON parsing and indexed queries return correct typed values
Explanation
Correct Answer
C:
Repeated JSON_VALUE evaluation produces expensive scans. Queries filter correctly and use the intended supported index.
Incorrect Answers
A:
“Queries prune dates appropriately and retention moves only intended records” concerns Large deletes cause long log usage and blocking; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Queries filter correctly and use the intended supported index.
B:
A general rowstore index can help ordinary relational predicates but need not make expressions over JSON_VALUE sargable. Indexing an appropriate computed JSON path targets the repeated extraction.
D:
“JSON parsing and indexed queries return correct typed values” concerns A rigid collection of nullable columns changes with every product type; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Queries filter correctly and use the intended supported index.
Question 40
At Pinecrest Operations, a large orders table filters frequently on a scalar JSON customer segment. The observed constraint is: Repeated JSON_VALUE evaluation produces expensive scans. What operational condition would provide the earliest useful regression warning?
- Monitor plan regressions and index maintenance
- Track index usage and modification overhead after rollout
- Monitor filtered-index eligibility and plan reuse
- Watch partition boundary maintenance and skew
Explanation
Correct Answer
A:
Repeated JSON_VALUE evaluation produces expensive scans. Monitor plan regressions and index maintenance.
Incorrect Answers
B:
A general rowstore index can help ordinary relational predicates but need not make expressions over JSON_VALUE sargable. Indexing an appropriate computed JSON path targets the repeated extraction.
C:
“Monitor filtered-index eligibility and plan reuse” concerns A full index is large and mostly unused; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Monitor plan regressions and index maintenance.
D:
“Watch partition boundary maintenance and skew” concerns Large deletes cause long log usage and blocking; the reported problem instead is Repeated JSON_VALUE evaluation produces expensive scans. Monitor plan regressions and index maintenance.
