100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 110
A payment table needs faster lookup by external transaction token. The team selects: Create an appropriate nonclustered index on token. Which implementation action puts this design into practice?
- Choose token as seek key and assess uniqueness
- Define BIGINT IDENTITY and primary key constraints
- Define DEFAULT ('Pending') on the Status column
- Grant EXECUTE on a posting procedure rather than direct UPDATE
Explanation
Correct Answer
A:Choose token as seek key and assess uniqueness. This is the concrete setup for create an appropriate nonclustered index on token, and observe seeks on token equality search supplies a direct confirmation.
Incorrect Answers
B:Define BIGINT IDENTITY and primary key constraints. This concerns a stable transaction identifier. Instead, choose token as seek key and assess uniqueness.
C:Define DEFAULT ('Pending') on the Status column. This concerns a default status when applications omit it. Instead, choose token as seek key and assess uniqueness.
D:Grant EXECUTE on a posting procedure rather than direct UPDATE. This concerns preventing updating immutable posted rows. Instead, choose token as seek key and assess uniqueness.
Question 111
A payment table needs faster lookup by external transaction token. After implementing the design, which check most directly validates the expected behavior?
- Attempt direct UPDATE from application principal and confirm denial
- Insert parallel rows and confirm key uniqueness
- Observe seeks on token equality search
- Attempt an insert with an absent customer ID and expect a constraint error
Explanation
Correct Answer
C:Observe seeks on token equality search. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that no useful index on transaction token.
Incorrect Answers
A:Attempt direct UPDATE from application principal and confirm denial. This concerns preventing updating immutable posted rows. Instead, observe seeks on token equality search.
B:Insert parallel rows and confirm key uniqueness. This concerns a stable transaction identifier. Instead, observe seeks on token equality search.
D:Attempt an insert with an absent customer ID and expect a constraint error. This concerns rejecting orders pointing to nonexistent customers. Instead, observe seeks on token equality search.
Question 112
A payment table needs faster lookup by external transaction token. A test instead finds: Payment token searches do full scans under load. Which underlying issue best explains this finding?
- Client-side key generation has no coordinated uniqueness guarantee
- Direct table write permissions bypass business logic
- No useful index on transaction token
- Immutable cryptographic ledger verification was not implemented
Explanation
Correct Answer
C:No useful index on transaction token. It accounts for the specific observed failure: payment token searches do full scans under load. The intended solution is create an appropriate nonclustered index on token with the required setup.
Incorrect Answers
A:Client-side key generation has no coordinated uniqueness guarantee. This concerns application-generated keys collide during retries. Here, no useful index on transaction token.
B:Direct table write permissions bypass business logic. This concerns a low-privilege account edits posted transactions. Here, no useful index on transaction token.
D:Immutable cryptographic ledger verification was not implemented. This concerns an insider edits audit records without detectable evidence. Here, no useful index on transaction token.
Question 113
A tenant app needs fast reads only for active subscriptions. Which TWO listed actions form the most appropriate database design and its concrete implementation? (Choose TWO.)
- Build filtered index on TenantID with active status predicate
- Use a rowstore B-tree index aligned with point lookups
- Use append-only ledger table for immutable entries where appropriate
- Use a filtered index limited to active rows
- Use a composite unique constraint including TenantID
Explanation
Correct Answers
A:This is the concrete configuration of the specified design: build filtered index on tenantid with active status predicate. The choice makes the implementation testable in the given workload.
D:Use a filtered index limited to active rows. The implementation is build filtered index on tenantid with active status predicate; success can be checked by check plan uses the filtered index on active-only queries. The method addresses the stated data and workload constraint.
Incorrect Answers
B:Use a rowstore B-tree index aligned with point lookups. This concerns locating single primary-key rows quickly. Instead, build filtered index on TenantID with active status predicate.
C:Use append-only ledger table for immutable entries where appropriate. This concerns an unchanging business document image. Instead, build filtered index on TenantID with active status predicate.
E:Use a composite unique constraint including TenantID. This concerns preserving uniqueness across tenant boundaries. Instead, build filtered index on TenantID with active status predicate.
Question 114
A tenant app needs fast reads only for active subscriptions. The team selects: Use a filtered index limited to active rows. Which implementation action puts this design into practice?
- Build filtered index on TenantID with active status predicate
- Define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef
- Define appropriate (ActorID, ChangedAt) key columns and includes
- Define DEFAULT ('Pending') on the Status column
Explanation
Correct Answer
A:Build filtered index on TenantID with active status predicate. This is the concrete setup for use a filtered index limited to active rows, and check plan uses the filtered index on active-only queries supplies a direct confirmation.
Incorrect Answers
B:Define UNIQUE(TenantID, ExternalRef) rather than global ExternalRef. This concerns preserving uniqueness across tenant boundaries. Instead, build filtered index on TenantID with active status predicate.
C:Define appropriate (ActorID, ChangedAt) key columns and includes. This concerns returning several payload columns without repeated base-table key lookups. Instead, build filtered index on TenantID with active status predicate.
D:Define DEFAULT ('Pending') on the Status column. This concerns a default status when applications omit it. Instead, build filtered index on TenantID with active status predicate.
Question 115
A tenant app needs fast reads only for active subscriptions. After implementing the design, which check most directly validates the expected behavior?
- Measure batch-mode scan and compression benefits
- Check plan uses the filtered index on active-only queries
- Insert same reference for different tenants and reject within one tenant
- Check plan avoids a large Sort operator
Explanation
Correct Answer
B:Check plan uses the filtered index on active-only queries. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that queries do not qualify for the filtered index.
Incorrect Answers
A:Measure batch-mode scan and compression benefits. This concerns an analytical table scans billions of order-line measures. Instead, check plan uses the filtered index on active-only queries.
C:Insert same reference for different tenants and reject within one tenant. This concerns preserving uniqueness across tenant boundaries. Instead, check plan uses the filtered index on active-only queries.
D:Check plan avoids a large Sort operator. This concerns a billing query sorts by lastmodified within a customer. Instead, check plan uses the filtered index on active-only queries.
Question 116
A tenant app needs fast reads only for active subscriptions. A test instead finds: Inactive subscriptions dominate I/O. Which underlying issue best explains this finding?
- A global uniqueness rule omitted the tenant scope
- External data source credentials or file format are misconfigured
- Queries do not qualify for the filtered index
- Referential integrity was left to application code only
Explanation
Correct Answer
C:Queries do not qualify for the filtered index. It accounts for the specific observed failure: inactive subscriptions dominate i/o. The intended solution is use a filtered index limited to active rows 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, queries do not qualify for the filtered index.
B:External data source credentials or file format are misconfigured. This concerns querying fails although internal tables work. Here, queries do not qualify for the filtered index.
D:Referential integrity was left to application code only. This concerns orphan orders appear after application validation is bypassed. Here, queries do not qualify for the filtered index.
Question 117
A geography service stores exact longitude and latitude coordinates. Which database design or engineering approach most directly meets this requirement?
- Index a deterministic persisted computed column when supported
- Use DECIMAL(19,4) for currency calculations
- Choose GEOGRAPHY for earth-surface spatial computations
- Evaluate columnstore dictionary compression
Explanation
Correct Answer
C:Choose GEOGRAPHY for earth-surface spatial computations. The implementation is populate geography points with documented srid; success can be checked by test spatial distance results against known locations. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Index a deterministic persisted computed column when supported. This concerns a query filters by a computed normalized email address. Instead, choose GEOGRAPHY for earth-surface spatial computations.
B:Use DECIMAL(19,4) for currency calculations. This concerns a SaaS order table stores exact prices used in invoicing. Instead, choose GEOGRAPHY for earth-surface spatial computations.
D:Evaluate columnstore dictionary compression. This concerns a fact table stores many categorical repetitive values. Instead, choose GEOGRAPHY for earth-surface spatial computations.
Question 118
A geography service stores exact longitude and latitude coordinates. The team selects: Choose GEOGRAPHY for earth-surface spatial computations. Which implementation action puts this design into practice?
- Create columnstore index for scan-oriented measurements
- Declare a bounded DECIMAL precision and scale
- Index ProductID INCLUDE (ProductName)
- Populate geography points with documented SRID
Explanation
Correct Answer
D:Populate geography points with documented SRID. This is the concrete setup for choose geography for earth-surface spatial computations, and test spatial distance results against known locations supplies a direct confirmation.
Incorrect Answers
A:Create columnstore index for scan-oriented measurements. This concerns a fact table stores many categorical repetitive values. Here, populate geography points with documented SRID.
B:Declare a bounded DECIMAL precision and scale. This concerns a SaaS order table stores exact prices used in invoicing. Here, populate geography points with documented SRID.
C:Index ProductID INCLUDE (ProductName). This concerns productname in output but filters on productid. Here, populate geography points with documented SRID.
Question 119
A geography service stores exact longitude and latitude coordinates. After implementing the design, which check most directly validates the expected behavior?
- Validate output against consumer schema
- Attempt a second account with the same normalized address
- Test spatial distance results against known locations
- Compare SUM totals to exact invoice amounts
Explanation
Correct Answer
C:Test spatial distance results against known locations. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that planar geometry or an incorrect srid was used for geodetic coordinates.
Incorrect Answers
A:Validate output against consumer schema. This concerns sharing an external json schema with downstream systems. Instead, test spatial distance results against known locations.
B:Attempt a second account with the same normalized address. This concerns not allow two accounts with the same email address. Instead, test spatial distance results against known locations.
D:Compare SUM totals to exact invoice amounts. This concerns a SaaS order table stores exact prices used in invoicing. Instead, test spatial distance results against known locations.
Question 120
A geography service stores exact longitude and latitude coordinates. A test instead finds: Distances are nonsensical across world regions. Which underlying issue best explains this finding?
- Columnstore index was not deployed on the analytic workload
- Approximate floating-point representation was chosen for currency
- Planar geometry or an incorrect SRID was used for geodetic coordinates
- No database CHECK constraint enforces the valid range
Explanation
Correct Answer
C:Planar geometry or an incorrect SRID was used for geodetic coordinates. It accounts for the specific observed failure: distances are nonsensical across world regions. The intended solution is choose geography for earth-surface spatial computations with the required setup.
Incorrect Answers
A:Columnstore index was not deployed on the analytic workload. This concerns analytics still reads uncompressed rowstore pages. Here, planar geometry or an incorrect SRID was used for geodetic coordinates.
B:Approximate floating-point representation was chosen for currency. This concerns accumulated invoice totals vary after repeated arithmetic. Here, planar geometry or an incorrect SRID was used for geodetic coordinates.
D:No database CHECK constraint enforces the valid range. This concerns a direct bulk import accepts negative package weights. Here, planar geometry or an incorrect SRID was used for geodetic coordinates.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests