100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
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.
Question 91
A wide audit table queried by timestamp and actor must return several payload columns without repeated base-table key lookups. After implementing the design, which check most directly validates the expected behavior?
- Compare key lookups in the execution plan
- Compare logical reads and lookup operators
- Compare plan estimates and actual reads on audit lookups
- Test spatial distance results against known locations
Explanation
Correct Answer
C:Compare plan estimates and actual reads on audit lookups. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that audit query lacks an index matching its where and output pattern.
Incorrect Answers
A:Compare key lookups in the execution plan. This concerns productname in output but filters on productid. Instead, compare plan estimates and actual reads on audit lookups.
B:Compare logical reads and lookup operators. This is relevant when a customer table has high selectivity on postal code but returns many columns. Instead, compare plan estimates and actual reads on audit lookups.
D:Test spatial distance results against known locations. This concerns a geography service stores exact longitude and latitude coordinates. Instead, compare plan estimates and actual reads on audit lookups.
Question 92
A wide audit table queried by timestamp and actor must return several payload columns without repeated base-table key lookups. A test instead finds: Investigators wait on full audit-table scans. Which underlying issue best explains this finding?
- Predicates or boundaries prevent partition elimination
- The covering index omitted a required output column
- A narrow index omits frequently requested payload columns
- Audit query lacks an index matching its WHERE and output pattern
Explanation
Correct Answer
D:Audit query lacks an index matching its WHERE and output pattern. It accounts for the specific observed failure: investigators wait on full audit-table scans. The intended solution is use a nonclustered covering index on the common filter keys with the required setup.
Incorrect Answers
A:Predicates or boundaries prevent partition elimination. This concerns all history partitions are scanned for one month. Instead, audit query lacks an index matching its WHERE and output pattern.
B:The covering index omitted a required output column. This concerns thousands of key lookups inflate report i/o. Instead, audit query lacks an index matching its WHERE and output pattern.
C:A narrow index omits frequently requested payload columns. This concerns a selective index seek causes excessive bookmark lookups. Instead, audit query lacks an index matching its WHERE and output pattern.
Question 93
A schema evolution must preserve uniqueness across tenant boundaries. Which database design or engineering approach most directly meets this requirement?
- Use controlled DML permissions or protected stored procedures
- Use a UNIQUE constraint on normalized email keys
- Use a composite unique constraint including TenantID
- Use a composite index supporting filter and order
Explanation
Correct Answer
C:Use a composite unique constraint including TenantID. The implementation is define unique(tenantid, externalref) rather than global externalref; success can be checked by insert same reference for different tenants and reject within one tenant. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use controlled DML permissions or protected stored procedures. This concerns preventing updating immutable posted rows. Instead, use a composite unique constraint including TenantID.
B:Use a UNIQUE constraint on normalized email keys. This concerns not allow two accounts with the same email address. Instead, use a composite unique constraint including TenantID.
D:Use a composite index supporting filter and order. This concerns a billing query sorts by lastmodified within a customer. Instead, use a composite unique constraint including TenantID.
Question 94
A schema evolution must preserve uniqueness across tenant boundaries. The team selects: Use a composite unique constraint including TenantID. Which implementation action puts this design into practice?
- Build filtered index on TenantID with active status predicate
- Create separate unique constraint on normalized BusinessCode
- Enable an appropriate ledger table and persist digests externally
- Define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef
Explanation
Correct Answer
D:Define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef. This is the concrete setup for use a composite unique constraint including tenantid, and insert same reference for different tenants and reject within one tenant supplies a direct confirmation.
Incorrect Answers
A:Build filtered index on TenantID with active status predicate. This concerns fast reads only for active subscriptions. Instead, define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef.
B:Create separate unique constraint on normalized BusinessCode. This concerns protecting natural business uniqueness too. Instead, define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef.
C:Enable an appropriate ledger table and persist digests externally. This concerns reveal tampering with historical rows. Instead, define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef.
Question 95
A schema evolution must preserve uniqueness across tenant boundaries. After implementing the design, which check most directly validates the expected behavior?
- Check plan uses the filtered index on active-only queries
- Validate output against consumer schema
- Insert same reference for different tenants and reject within one tenant
- Measure batch-mode scan and compression benefits
Explanation
Correct Answer
C:Insert same reference for different tenants and reject within one tenant. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that a global uniqueness rule omitted the tenant scope.
Incorrect Answers
A:Check plan uses the filtered index on active-only queries. This concerns fast reads only for active subscriptions. Instead, insert same reference for different tenants and reject within one tenant.
B:Validate output against consumer schema. This concerns sharing an external json schema with downstream systems. Instead, insert same reference for different tenants and reject within one tenant.
D:Measure batch-mode scan and compression benefits. This concerns an analytical table scans billions of order-line measures. Instead, insert same reference for different tenants and reject within one tenant.
Question 96
A schema evolution must preserve uniqueness across tenant boundaries. A test instead finds: Tenant B cannot use an identifier already used by Tenant A. Which underlying issue best explains this finding?
- No database CHECK constraint enforces the valid range
- Only the surrogate primary key is constrained
- A global uniqueness rule omitted the tenant scope
- Queries do not qualify for the filtered index
Explanation
Correct Answer
C:A global uniqueness rule omitted the tenant scope. It accounts for the specific observed failure: tenant b cannot use an identifier already used by tenant a. The intended solution is use a composite unique constraint including tenantid with the required setup.
Incorrect Answers
A:No database CHECK constraint enforces the valid range. This concerns a direct bulk import accepts negative package weights. Here, a global uniqueness rule omitted the tenant scope.
B:Only the surrogate primary key is constrained. This concerns two records share a supposedly unique real-world identifier. Here, a global uniqueness rule omitted the tenant scope.
D:Queries do not qualify for the filtered index. This concerns inactive subscriptions dominate i/o. Here, a global uniqueness rule omitted the tenant scope.
Question 97
A data warehouse records GUID request identifiers from clients. Which database design or engineering approach most directly meets this requirement?
- Use a UNIQUE constraint on normalized email keys
- Use minimally sufficient data types and explicit NOT NULL where needed
- Store GUIDs using UNIQUEIDENTIFIER rather than text
- Use an EXTERNAL TABLE over the external data location
Explanation
Correct Answer
C:Store GUIDs using UNIQUEIDENTIFIER rather than text. The implementation is define uniqueidentifier columns and validate guid input; success can be checked by compare typed guid joins without string conversion. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Use a UNIQUE constraint on normalized email keys. This concerns not allow two accounts with the same email address. Instead, store GUIDs using UNIQUEIDENTIFIER rather than text.
B:Use minimally sufficient data types and explicit NOT NULL where needed. This concerns an etl process bulk loads records with a reliable fixed set of values. Instead, store GUIDs using UNIQUEIDENTIFIER rather than text.
D:Use an EXTERNAL TABLE over the external data location. This concerns a reporting system reads files outside its SQL database. Instead, store GUIDs using UNIQUEIDENTIFIER rather than text.
Question 98
A data warehouse records GUID request identifiers from clients. The team selects: Store GUIDs using UNIQUEIDENTIFIER rather than text. Which implementation action puts this design into practice?
- Declare NOT NULL for mandatory columns
- Create an appropriate unique index or constraint
- Define appropriate (ActorID, ChangedAt) key columns and includes
- Define UNIQUEIDENTIFIER columns and validate GUID input
Explanation
Correct Answer
D:Define UNIQUEIDENTIFIER columns and validate GUID input. This is the concrete setup for store guids using uniqueidentifier rather than text, and compare typed guid joins without string conversion 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, define UNIQUEIDENTIFIER columns and validate GUID input.
B:Create an appropriate unique index or constraint. This concerns not allow two accounts with the same email address. Instead, define UNIQUEIDENTIFIER columns and validate GUID input.
C:Define appropriate (ActorID, ChangedAt) key columns and includes. This concerns returning several payload columns without repeated base-table key lookups. Instead, define UNIQUEIDENTIFIER columns and validate GUID input.
Question 99
A data warehouse records GUID request identifiers from clients. After implementing the design, which check most directly validates the expected behavior?
- Try duplicate business code with a different ID
- Query JSON_VALUE for a typed documented attribute
- Compare typed GUID joins without string conversion
- Verify distinct increasing allocated values across both tables
Explanation
Correct Answer
C:Compare typed GUID joins without string conversion. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that identifiers were stored as unconstrained character data.
Incorrect Answers
A:Try duplicate business code with a different ID. This concerns protecting natural business uniqueness too. Instead, compare typed GUID joins without string conversion.
B:Query JSON_VALUE for a typed documented attribute. This concerns a product catalog stores variable structured attributes in json. Instead, compare typed GUID joins without string conversion.
D:Verify distinct increasing allocated values across both tables. This concerns an automatically generated numeric key shared by several tables. Instead, compare typed GUID joins without string conversion.
Question 100
A data warehouse records GUID request identifiers from clients. A test instead finds: String-form GUID casts inflate joins and permit malformed values. Which underlying issue best explains this finding?
- Nullable schema contradicts a mandatory business attribute
- Identifiers were stored as unconstrained character data
- Only the surrogate primary key is constrained
- External data source credentials or file format are misconfigured
Explanation
Correct Answer
B:Identifiers were stored as unconstrained character data. It accounts for the specific observed failure: string-form guid casts inflate joins and permit malformed values. The intended solution is store guids using uniqueidentifier rather than text with the required setup.
Incorrect Answers
A:Nullable schema contradicts a mandatory business attribute. This concerns incomplete records enter critical report aggregates. Here, identifiers were stored as unconstrained character data.
C:Only the surrogate primary key is constrained. This concerns two records share a supposedly unique real-world identifier. Here, identifiers were stored as unconstrained character data.
D:External data source credentials or file format are misconfigured. This concerns querying fails although internal tables work. Here, identifiers were stored as unconstrained character data.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests