100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 70
A product catalog stores variable structured attributes in JSON. The team selects: Use SQL JSON storage with typed extraction strategy. Which implementation action puts this design into practice?
- Define JSON_OBJECT keys and typed values in a controlled projection
- Store validated JSON and create appropriate JSON indexes where supported
- Choose token as seek key and assess uniqueness
- Create an appropriate unique index or constraint
Explanation
Correct Answer
B:Store validated JSON and create appropriate JSON indexes where supported. This is the concrete setup for use sql json storage with typed extraction strategy, and query json_value for a typed documented attribute supplies a direct confirmation.
Incorrect Answers
A:Define JSON_OBJECT keys and typed values in a controlled projection. This concerns sharing an external json schema with downstream systems. Instead, store validated JSON and create appropriate JSON indexes where supported.
C:Choose token as seek key and assess uniqueness. This concerns faster lookup by external transaction token. Instead, store validated JSON and create appropriate JSON indexes where supported.
D:Create an appropriate unique index or constraint. This concerns not allow two accounts with the same email address. Instead, store validated JSON and create appropriate JSON indexes where supported.
Question 71
A product catalog stores variable structured attributes in JSON. After implementing the design, which check most directly validates the expected behavior?
- Execute a MATCH graph traversal with expected edges
- Query JSON_VALUE for a typed documented attribute
- Compare NULL-heavy storage and update cost
- Query FOR SYSTEM_TIME AS OF a prior timestamp
Explanation
Correct Answer
B:Query JSON_VALUE for a typed documented attribute. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that indexing or typed extraction does not match the json path.
Incorrect Answers
A:Execute a MATCH graph traversal with expected edges. This concerns an application models relationships between people and events. Instead, query JSON_VALUE for a typed documented attribute.
C:Compare NULL-heavy storage and update cost. This concerns sparse optional attributes. Instead, query JSON_VALUE for a typed documented attribute.
D:Query FOR SYSTEM_TIME AS OF a prior timestamp. This concerns querying yesterday's values after updates. Instead, query JSON_VALUE for a typed documented attribute.
Question 72
A product catalog stores variable structured attributes in JSON. A test instead finds: Property lookups require parsing every JSON document. Which underlying issue best explains this finding?
- Indexing or typed extraction does not match the JSON path
- JSON contract and projected field names were not stabilized
- The filtered predicate or query condition does not match
- The necessary search-key index is missing or unusable
Explanation
Correct Answer
A:Indexing or typed extraction does not match the JSON path. It accounts for the specific observed failure: property lookups require parsing every json document. The intended solution is use sql json storage with typed extraction strategy with the required setup.
Incorrect Answers
B:JSON contract and projected field names were not stabilized. This concerns a client breaks after column rename changes payload. Here, indexing or typed extraction does not match the JSON path.
C:The filtered predicate or query condition does not match. This concerns the query still scans closed orders for every lookup. Here, indexing or typed extraction does not match the JSON path.
D:The necessary search-key index is missing or unusable. This concerns point lookups scan an entire large rowset. Here, indexing or typed extraction does not match the JSON path.
Question 73
A reporting system reads files outside its SQL database. Which database design or engineering approach most directly meets this requirement?
- Use an EXTERNAL TABLE over the external data location
- Use a composite unique constraint including TenantID
- Use explicit JSON shape in API and deterministic SQL projection
- Create an appropriate nonclustered index on token
Explanation
Correct Answer
A:Use an EXTERNAL TABLE over the external data location. The implementation is configure external data source, format and table definition; success can be checked by select expected rows from the external location. 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 an EXTERNAL TABLE over the external data location.
C:Use explicit JSON shape in API and deterministic SQL projection. This concerns sharing an external json schema with downstream systems. Instead, use an EXTERNAL TABLE over the external data location.
D:Create an appropriate nonclustered index on token. This concerns faster lookup by external transaction token. Instead, use an EXTERNAL TABLE over the external data location.
Question 74
A reporting system reads files outside its SQL database. The team selects: Use an EXTERNAL TABLE over the external data location. Which implementation action puts this design into practice?
- Choose token as seek key and assess uniqueness
- Configure external data source, format and table definition
- Create node/edge objects and appropriate relationships
- Configure period columns and SYSTEM_VERSIONING=ON
Explanation
Correct Answer
B:Configure external data source, format and table definition. This is the concrete setup for use an external table over the external data location, and select expected rows from the external location supplies a direct confirmation.
Incorrect Answers
A:Choose token as seek key and assess uniqueness. This concerns faster lookup by external transaction token. Instead, configure external data source, format and table definition.
C:Create node/edge objects and appropriate relationships. This concerns an application models relationships between people and events. Instead, configure external data source, format and table definition.
D:Configure period columns and SYSTEM_VERSIONING=ON. This concerns querying yesterday's values after updates. Instead, configure external data source, format and table definition.
Question 75
A reporting system reads files outside its SQL database. After implementing the design, which check most directly validates the expected behavior?
- Query FOR SYSTEM_TIME AS OF a prior timestamp
- SELECT expected rows from the external location
- Execute a MATCH graph traversal with expected edges
- Attempt an insert with an absent customer ID and expect a constraint error
Explanation
Correct Answer
B:SELECT expected rows from the external location. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that external data source credentials or file format are misconfigured.
Incorrect Answers
A:Query FOR SYSTEM_TIME AS OF a prior timestamp. This concerns querying yesterday's values after updates. The relevant SQL or system operation is SELECT expected rows from the external location.
C:Execute a MATCH graph traversal with expected edges. This concerns an application models relationships between people and events. The relevant SQL or system operation is SELECT expected rows from the external location.
D:Attempt an insert with an absent customer ID and expect a constraint error. This concerns rejecting orders pointing to nonexistent customers. The relevant SQL or system operation is SELECT expected rows from the external location.
Question 76
A reporting system reads files outside its SQL database. A test instead finds: Query fails although internal tables work. Which underlying issue best explains this finding?
- External data source credentials or file format are misconfigured
- No temporal history was retained for updates
- A non-Unicode column or conversion path lost characters
- Identifiers were stored as unconstrained character data
Explanation
Correct Answer
A:External data source credentials or file format are misconfigured. It accounts for the specific observed failure: query fails although internal tables work. The intended solution is use an external table over the external data location with the required setup.
Incorrect Answers
B:No temporal history was retained for updates. This concerns yesterday's price cannot be reconstructed. Here, external data source credentials or file format are misconfigured.
C:A non-Unicode column or conversion path lost characters. This concerns names are replaced with question marks on insertion. Here, external data source credentials or file format are misconfigured.
D:Identifiers were stored as unconstrained character data. This concerns string-form guid casts inflate joins and permit malformed values. Here, external data source credentials or file format are misconfigured.
Question 77
A large sales history should prune old monthly partitions. Which database design or engineering approach most directly meets this requirement?
- Use partitioning by a suitable date key
- Put equality-filter CustomerID before the date range in a composite index
- Use aligned partition switching into a staging table
- Choose GEOGRAPHY for earth-surface spatial computations
Explanation
Correct Answer
A:Use partitioning by a suitable date key. The implementation is create partition function and scheme with aligned boundaries; success can be checked by inspect touched partitions for a bounded date filter. The method addresses the stated data and workload constraint.
Incorrect Answers
B: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 partitioning by a suitable date key.
C:Use aligned partition switching into a staging table. This concerns rapid removal of a completed month. Instead, use partitioning by a suitable date key.
D:Choose GEOGRAPHY for earth-surface spatial computations. This concerns a geography service stores exact longitude and latitude coordinates. Instead, use partitioning by a suitable date key.
Question 78
A large sales history should prune old monthly partitions. The team selects: Use partitioning by a suitable date key. Which implementation action puts this design into practice?
- Declare a bounded DECIMAL precision and scale
- Create partition function and scheme with aligned boundaries
- Align indexes and CHECK constraints before ALTER TABLE SWITCH
- Create a composite index on (CustomerID, SaleDate)
Explanation
Correct Answer
B:Create partition function and scheme with aligned boundaries. This is the concrete setup for use partitioning by a suitable date key, and inspect touched partitions for a bounded date filter supplies a direct confirmation.
Incorrect Answers
A:Declare a bounded DECIMAL precision and scale. This concerns a SaaS order table stores exact prices used in invoicing. Instead, create partition function and scheme with aligned boundaries.
C:Align indexes and CHECK constraints before ALTER TABLE SWITCH. This concerns rapid removal of a completed month. Instead, create partition function and scheme with aligned boundaries.
D:Create a composite index on (CustomerID, SaleDate). This concerns a report selects sales by customerid and then date. Instead, create partition function and scheme with aligned boundaries.
Question 79
A large sales history should prune old monthly partitions. After implementing the design, which check most directly validates the expected behavior?
- Check plan avoids a large Sort operator
- Inspect seeks and logical reads for the target predicate
- Inspect touched partitions for a bounded date filter
- Measure metadata-only switch and row counts
Explanation
Correct Answer
C:Inspect touched partitions for a bounded date filter. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that predicates or boundaries prevent partition elimination.
Incorrect Answers
A:Check plan avoids a large Sort operator. This concerns a billing query sorts by lastmodified within a customer. Instead, inspect touched partitions for a bounded date filter.
B:Inspect seeks and logical reads for the target predicate. This concerns a report selects sales by customerid and then date. Instead, inspect touched partitions for a bounded date filter.
D:Measure metadata-only switch and row counts. This concerns rapid removal of a completed month. Instead, inspect touched partitions for a bounded date filter.
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.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests