Microsoft DP-800 Practice Test 3 - Prepaway
Practice Exams:

Pass Microsoft DP-800 Exam in First Attempt Guaranteed!

Get 100% Latest Exam Questions, Accurate & Verified Answers!

30 Days Free Updates, Instant Download!

Verified By Experts
Premium File
$69.99 $76.99

DP-800 Premium ETE File


  • Premium File 101 Questions & Answers. Last Update: Nov 06, 2026
Whats Included:
  • Latest Questions
  • 100% Accurate Answers
  • Fast Exam Updates

Microsoft DP-800 Practice Test 3

Question 20

A frequently executed dashboard query filters a narrow index but retrieves many extra columns through repeated key lookups. What technical finding would substantiate this root cause?

  1. Query Store shows the same query hash shifted to a more expensive plan after deployment.
  2. A blocked request waits behind a long-running transaction but no deadlock victim is recorded.
  3. A simultaneous test creates two confirmed bookings for one remaining seat.
  4. The plan includes hundreds of thousands of lookups for columns absent from the nonclustered index.

Explanation

Correct Answer

D:

The plan includes hundreds of thousands of lookups for columns absent from the nonclustered index. Covering access paths may avoid repeated lookups but extra index width has costs.

Incorrect Answers

A:

Query Store shows the same query hash shifted to a more expensive plan after deployment. Covering access paths may avoid repeated lookups but extra index width has costs. The plan includes hundreds of thousands of lookups for columns absent from the nonclustered index.

B:

A blocked request waits behind a long-running transaction but no deadlock victim is recorded. Covering access paths may avoid repeated lookups but extra index width has costs. The plan includes hundreds of thousands of lookups for columns absent from the nonclustered index.

C:

A simultaneous test creates two confirmed bookings for one remaining seat. Covering access paths may avoid repeated lookups but extra index width has costs. The plan includes hundreds of thousands of lookups for columns absent from the nonclustered index.

Question 21

A billing service reports connection timeouts and the team labels every wait a deadlock without inspecting lock ownership. What should the database developer do FIRST within approved procedures?

  1. Choose transactional locking or isolation that enforces the reservation invariant under concurrency.
  2. Measure actual fragmentation, page density, workload plans, and maintenance impact before a rebuild policy.
  3. Evaluate row-versioning-based read committed behavior with its storage and consistency requirements.
  4. Use live DMVs, wait information, and deadlock evidence to distinguish blocking chains from actual cyclic deadlocks.

Explanation

Correct Answer

D:

Use live DMVs, wait information, and deadlock evidence to distinguish blocking chains from actual cyclic deadlocks. Blocking is waiting for locks; a deadlock involves a cycle that SQL Server must resolve.

Incorrect Answers

A:

Choose transactional locking or isolation that enforces the reservation invariant under concurrency. Blocking is waiting for locks; a deadlock involves a cycle that SQL Server must resolve. Use live DMVs, wait information, and deadlock evidence to distinguish blocking chains from actual cyclic deadlocks.

B:

Measure actual fragmentation, page density, workload plans, and maintenance impact before a rebuild policy. Blocking is waiting for locks; a deadlock involves a cycle that SQL Server must resolve. Use live DMVs, wait information, and deadlock evidence to distinguish blocking chains from actual cyclic deadlocks.

C:

Evaluate row-versioning-based read committed behavior with its storage and consistency requirements. Blocking is waiting for locks; a deadlock involves a cycle that SQL Server must resolve. Use live DMVs, wait information, and deadlock evidence to distinguish blocking chains from actual cyclic deadlocks.

Question 22

An IoT staging table includes duplicate device readings and analysts need the latest reading per device without deleting historical evidence. How should the SQL engineering team confirm the issue is truly resolved?

  1. A reviewed query handles absent or malformed values and computes numerical results accurately.
  2. Repeated runs produce the expected ranking and tie handling for the same data.
  3. The query selects exactly one deterministic latest record per device and keeps history intact.
  4. The graph query returns the verified two-hop relationships without inventing direct account links.

Explanation

Correct Answer

C:

The query selects exactly one deterministic latest record per device and keeps history intact. An IoT staging table includes duplicate device readings and analysts need the latest reading per device without deleting historical evidence.

Incorrect Answers

A:

A reviewed query handles absent or malformed values and computes numerical results accurately. An IoT staging table includes duplicate device readings and analysts need the latest reading per device without deleting historical evidence. The query selects exactly one deterministic latest record per device and keeps history intact.

B:

Repeated runs produce the expected ranking and tie handling for the same data. An IoT staging table includes duplicate device readings and analysts need the latest reading per device without deleting historical evidence. The query selects exactly one deterministic latest record per device and keeps history intact.

D:

The graph query returns the verified two-hop relationships without inventing direct account links. An IoT staging table includes duplicate device readings and analysts need the latest reading per device without deleting historical evidence. The query selects exactly one deterministic latest record per device and keeps history intact.

Question 23

A global incident system includes support notes in six languages and its English-only embedding model retrieves poor results for non-English tickets. What would be a valid post-database-change verification?

  1. A replay-safe feed propagates additions, updates, and deletions without a full rebuild.
  2. A test query retrieves complete relevant clause context and the answer cites the exception.
  3. A held-out multilingual search set meets retrieval quality targets across required languages.
  4. Search uses the approved description text and protected identifiers do not appear in vector inputs.

Explanation

Correct Answer

C:

A held-out multilingual search set meets retrieval quality targets across required languages. The operating rule is: Embedding model suitability depends on supported languages and measured retrieval performance.

Incorrect Answers

A:

A replay-safe feed propagates additions, updates, and deletions without a full rebuild. The operating rule is: Embedding model suitability depends on supported languages and measured retrieval performance. A held-out multilingual search set meets retrieval quality targets across required languages.

B:

A test query retrieves complete relevant clause context and the answer cites the exception. The operating rule is: Embedding model suitability depends on supported languages and measured retrieval performance. A held-out multilingual search set meets retrieval quality targets across required languages.

D:

Search uses the approved description text and protected identifiers do not appear in vector inputs. The operating rule is: Embedding model suitability depends on supported languages and measured retrieval performance. A held-out multilingual search set meets retrieval quality targets across required languages.

Question 24

A team switches to SDK-style SQL Database Projects but assumes copying .sql files without a project definition creates a validated deployable model. Which TWO options together specify a corrective step and a meaningful test?

  1. Deployment preserves required values or uses a documented approved transformation without silent truncation.
  2. CI produces a valid model and catches an intentional missing-object reference.
  3. Enforce branch policies, required reviewers, and successful SQL project validation before merge.
  4. Add end-to-end integration tests using compatible staged SQL schema and actual API configuration.
  5. Configure the supported SDK-style project, dependencies, and build validation in CI.

Explanation

Correct Answers

B:

CI produces a valid model and catches an intentional missing-object reference. A team switches to SDK-style SQL Database Projects but assumes copying.sql files without a project definition creates a validated deployable model.

E:

Configure the supported SDK-style project, dependencies, and build validation in CI. A team switches to SDK-style SQL Database Projects but assumes copying.sql files without a project definition creates a validated deployable model.

Incorrect Answers

A:

Deployment preserves required values or uses a documented approved transformation without silent truncation. A team switches to SDK-style SQL Database Projects but assumes copying.sql files without a project definition creates a validated deployable model. CI produces a valid model and catches an intentional missing-object reference.

C:

Enforce branch policies, required reviewers, and successful SQL project validation before merge. A team switches to SDK-style SQL Database Projects but assumes copying.sql files without a project definition creates a validated deployable model. Configure the supported SDK-style project, dependencies, and build validation in CI.

D:

Add end-to-end integration tests using compatible staged SQL schema and actual API configuration. A team switches to SDK-style SQL Database Projects but assumes copying.sql files without a project definition creates a validated deployable model. CI produces a valid model and catches an intentional missing-object reference.

Question 25

A support role sees masked card numbers but can execute broad aggregate and comparison queries that might reveal protected details. Which clue best distinguishes this issue from similar incidents?

  1. The compromised application identity reads plaintext fields from normal SQL queries despite encrypted storage.
  2. A test role derives confidential values through unrestricted filters despite seeing masked display columns.
  3. A database support login can read every salary directly in plaintext despite lacking a payroll role.
  4. Application credential inspection shows ALTER and DROP privileges over unrelated schema objects.

Explanation

Correct Answer

B:

A test role derives confidential values through unrestricted filters despite seeing masked display columns. Data masking is a presentation-level control and inference exposure needs separate review.

Incorrect Answers

A:

The compromised application identity reads plaintext fields from normal SQL queries despite encrypted storage. Data masking is a presentation-level control and inference exposure needs separate review. A test role derives confidential values through unrestricted filters despite seeing masked display columns.

C:

A database support login can read every salary directly in plaintext despite lacking a payroll role. Data masking is a presentation-level control and inference exposure needs separate review. A test role derives confidential values through unrestricted filters despite seeing masked display columns.

D:

Application credential inspection shows ALTER and DROP privileges over unrelated schema objects. Data masking is a presentation-level control and inference exposure needs separate review. A test role derives confidential values through unrestricted filters despite seeing masked display columns.

Question 26

An SQL-based RAG procedure sends hundreds of long retrieved chunks to a model and becomes expensive while often omitting the most relevant clauses due to context limits. What would be a valid post-database-change verification?

  1. The request sends only approved fields and the model receives sufficient task context.
  2. The workflow meets token and latency budgets while citing the correct current clauses.
  3. Every material policy claim cites a current accessible source or is marked unsupported.
  4. Test queries without evidence return a clearly marked gap and a human review route.

Explanation

Correct Answer

B:

The workflow meets token and latency budgets while citing the correct current clauses.

Incorrect Answers

A:

The request sends only approved fields and the model receives sufficient task context. The workflow meets token and latency budgets while citing the correct current clauses.

C:

Every material policy claim cites a current accessible source or is marked unsupported. The workflow meets token and latency budgets while citing the correct current clauses.

D:

Test queries without evidence return a clearly marked gap and a human review route. The workflow meets token and latency budgets while citing the correct current clauses.

Question 27

A shared function retrieves customer contract records using a tenant identifier supplied by an untrusted caller. How should the SQL engineering team confirm the issue is truly resolved?

  1. A cross-tenant negative test returns no forbidden records while authorized calls still work.
  2. Tests return the same correctly scoped rows for all clients without a substantial plan regression.
  3. Running the same deployment twice produces the expected reference rows without duplication.
  4. Unauthorized selects are blocked by permissions and legitimate data changes produce reliable audit records.

Explanation

Correct Answer

A:

A cross-tenant negative test returns no forbidden records while authorized calls still work.

Incorrect Answers

B:

Tests return the same correctly scoped rows for all clients without a substantial plan regression. A cross-tenant negative test returns no forbidden records while authorized calls still work.

C:

Running the same deployment twice produces the expected reference rows without duplication. A cross-tenant negative test returns no forbidden records while authorized calls still work.

D:

Unauthorized selects are blocked by permissions and legitimate data changes produce reliable audit records. A cross-tenant negative test returns no forbidden records while authorized calls still work.

Question 28

A multilingual knowledge base stores English-only embeddings and assumes a translated query produces equivalent relevance in all supported locales. What would be a valid post-database-change verification?

  1. A test accepts valid vectors, rejects inconsistent dimensions, and reduces conversion work.
  2. Exact product-code queries retrieve their matching rows while conceptual requests remain useful.
  3. Language-sliced tests retrieve authorized relevant records with accepted coverage.
  4. Benchmark ordering matches a validated metric-specific reference calculation.

Explanation

Correct Answer

C:

Language-sliced tests retrieve authorized relevant records with accepted coverage.

Incorrect Answers

A:

A test accepts valid vectors, rejects inconsistent dimensions, and reduces conversion work. Language-sliced tests retrieve authorized relevant records with accepted coverage.

B:

Exact product-code queries retrieve their matching rows while conceptual requests remain useful. Language-sliced tests retrieve authorized relevant records with accepted coverage.

D:

Benchmark ordering matches a validated metric-specific reference calculation. Language-sliced tests retrieve authorized relevant records with accepted coverage.

Question 29

An anti-fraud application frequently traverses accounts, devices, and shared identifiers through variable-length relationships that are cumbersome in ordinary joins. Which outcome shows the requirement is met without introducing a new risk?

  1. Orphan inserts fail while legitimate import ordering and transactions succeed.
  2. A representative graph traversal returns validated connections at acceptable cost.
  3. Checkout transaction latency remains stable while reporting queries use purpose-built structures.
  4. A load test shows lower contention without unacceptable loss of required durable records.

Explanation

Correct Answer

B:

A representative graph traversal returns validated connections at acceptable cost.

Incorrect Answers

A:

Orphan inserts fail while legitimate import ordering and transactions succeed. A representative graph traversal returns validated connections at acceptable cost.

C:

Checkout transaction latency remains stable while reporting queries use purpose-built structures. A representative graph traversal returns validated connections at acceptable cost.

D:

A load test shows lower contention without unacceptable loss of required durable records. A representative graph traversal returns validated connections at acceptable cost.

Question 30

A team deploys DAB behind a gateway and assumes the backend is safe because the gateway normally authenticates users. Choose TWO statements: a suitable intervention and evidence of its success.

  1. A bounded query or optimized mapping meets latency targets under representative load.
  2. Require and test appropriate service-level authentication and network restrictions at every reachable endpoint.
  3. Direct unauthenticated calls fail while gateway-routed authorized traffic succeeds.
  4. Configure appropriate pagination, result limits, and role-level filtering in DAB.
  5. Configure the supported DAB GraphQL relationship mapping and test nested authorization across entities.

Explanation

Correct Answers

B:

Require and test appropriate service-level authentication and network restrictions at every reachable endpoint.

C:

Direct unauthenticated calls fail while gateway-routed authorized traffic succeeds. Require and test appropriate service-level authentication and network restrictions at every reachable endpoint.

Incorrect Answers

A:

A bounded query or optimized mapping meets latency targets under representative load. Require and test appropriate service-level authentication and network restrictions at every reachable endpoint.

D:

Configure appropriate pagination, result limits, and role-level filtering in DAB. Require and test appropriate service-level authentication and network restrictions at every reachable endpoint.

E:

Configure the supported DAB GraphQL relationship mapping and test nested authorization across entities. Require and test appropriate service-level authentication and network restrictions at every reachable endpoint.

Developing AI-Enabled Database Solutions (DP-800) Practice Tests