McAfee Secure

Exam Code: DP-800

Exam Name: Developing AI-Enabled Database Solutions

Certification Provider: Microsoft

Corresponding Certification: Microsoft Certified: SQL AI Developer Associate

Questions & Answers

Microsoft DP-800 Questions & Answers

Study with Up-To-Date REAL Exam Questions and Answers from the ACTUAL Test

Questions & Answers

101 Questions & Answers with Testing Engine

"Developing AI-Enabled Database Solutions Exam", also known as DP-800 exam, is a Microsoft certification exam.

Pass your tests with the always up-to-date DP-800 Exam Engine. Your DP-800 training materials keep you at the head of the pack!

Satisfaction Guaranteed

Money Back Guarantee

Testking provides hassle-free money back guarantee with our products. That is because we have 100% trust in the abilities of our professional and experience product team, and our record is a proof of that.

99.6% PASS RATE
Was: $137.49
Now: $124.99

Microsoft DP-800 Practice Test 33

Question 320

A reporting transaction under READ COMMITTED sees changing totals between consecutive statements. What is the MOST relevant technical distinction in this situation?

  1. Optimistic concurrency only works if conflicts are detected, not silently overwritten.
  2. Lock duration and scope influence writer blocking under concurrency.
  3. Large transactions increase lock duration, log pressure and potential escalation.
  4. Isolation choices define which committed versions are visible across statements.

Explanation

Correct Answer

D:

Isolation choices define which committed versions are visible across statements. This explains why the corrective action is to evaluate SNAPSHOT isolation or appropriate transaction design for repeatable point-in-time reads. Confirm the diagnosis when repeated statements in one snapshot transaction give a consistent view.

Incorrect Answers

A:

Optimistic concurrency only works if conflicts are detected, not silently overwritten. The actual technical constraint is that isolation choices define which committed versions are visible across statements.

B:

Lock duration and scope influence writer blocking under concurrency. The actual technical constraint is that isolation choices define which committed versions are visible across statements.

C:

Large transactions increase lock duration, log pressure and potential escalation. The actual technical constraint is that isolation choices define which committed versions are visible across statements.

Question 321

An analyst wants to use ordinary LIKE predicates against randomized Always Encrypted ciphertext. Which ongoing control is MOST relevant after the issue is fixed?

  1. Document leakage tradeoffs before enabling secure enclaves.
  2. Rehearse key recovery and avoid distributing keys in scripts.
  3. Avoid broad database role grants to fix one dynamic query.
  4. Rotate any exposed keys and protect repository history.

Explanation

Correct Answer

A:

Document leakage tradeoffs before enabling secure enclaves. This guards against recurrence because randomized encryption does not support general plaintext pattern matching inside the engine. The direct remediation is: Redesign the query or use an explicitly supported encryption/enclave pattern with documented tradeoffs.

Incorrect Answers

B:

Rehearse key recovery and avoid distributing keys in scripts. The current task instead requires this action: document leakage tradeoffs before enabling secure enclaves.

C:

Avoid broad database role grants to fix one dynamic query.

D:

Rotate any exposed keys and protect repository history. The current task instead requires this action: document leakage tradeoffs before enabling secure enclaves.

Question 322

A SQL service's identity can call a model but lacks permission to read a required secure search index. Which implementation decision BEST addresses this requirement?

  1. Use a supported managed identity or securely retrieved credential mechanism for the endpoint.
  2. Configure distinct least-privilege grants on model and search resources.
  3. Perform supported key rotation and client configuration changes as a coordinated migration.
  4. Configure a suitable masking function on the email column and scope UNMASK.

Explanation

Correct Answer

B:

Configure distinct least-privilege grants on model and search resources. This targets the primary issue because identity permission at one Azure service does not imply access to adjacent resources. Verification: Model calls and retrieval calls each succeed only with required scopes. Prevent recurrence: Review grants separately when components are replaced.

Incorrect Answers

A:

Embedding static credentials in SQL code can expose model access and billing.

C:

Column key hierarchy and client metadata must agree for encrypted column access.

D:

Dynamic masking is evaluated at query output rather than permanently altering storage.

Question 323

A recursive organization-chart query runs indefinitely after cyclic parent links are introduced. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. A small hierarchy returns all reachable descendants once under expected rules.
  2. The test input produces distinct reviewer rows and correct identifiers.
  3. A cyclic test input is detected rather than consuming unbounded resources.
  4. Valid test codes match and invalid or truncated codes do not.

Explanation

Correct Answer

C:

A cyclic test input is detected rather than consuming unbounded resources. This is the decisive observable evidence after implementing validate acyclic parent relationships and bound recursion depth. It tests the underlying requirement: A cycle can repeatedly regenerate the same nodes in a recursive CTE.

Incorrect Answers

A:

A small hierarchy returns all reachable descendants once under expected rules. However, that result would not establish the required outcome: a cyclic test input is detected rather than consuming unbounded resources.

B:

The test input produces distinct reviewer rows and correct identifiers. However, that result would not establish the required outcome: a cyclic test input is detected rather than consuming unbounded resources.

D:

Valid test codes match and invalid or truncated codes do not. However, that result would not establish the required outcome: a cyclic test input is detected rather than consuming unbounded resources.

Question 324

A dev wants to enable all query optimization flags globally because one report is slow. Which technical reason BEST explains the proposed correction?

  1. Resource limits can create bottlenecks independent of query syntax.
  2. Large transactions increase lock duration, log pressure and potential escalation.
  3. Isolation choices define which committed versions are visible across statements.
  4. Database-level settings can affect many workloads and engine versions differently.

Explanation

Correct Answer

D:

Database-level settings can affect many workloads and engine versions differently. This explains why the corrective action is to use targeted measured configuration changes and a rollback plan rather than blindly enabling trace flags. Confirm the diagnosis when the report improves without regression in representative unrelated queries.

Incorrect Answers

A:

Resource limits can create bottlenecks independent of query syntax. The actual technical constraint is that database-level settings can affect many workloads and engine versions differently.

B:

Large transactions increase lock duration, log pressure and potential escalation. The actual technical constraint is that database-level settings can affect many workloads and engine versions differently.

C:

Isolation choices define which committed versions are visible across statements. The actual technical constraint is that database-level settings can affect many workloads and engine versions differently.

Question 325

A query uses vectors with unequal magnitudes and wants unit-length normalized comparisons. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Do not assume normalization is right for every model/metric.
  2. Validate runtime-specific function availability.
  3. Choose exact versus linguistic search deliberately.
  4. Tune an ANN search index without checking the embedding model’s normalization and distance assumptions.

Explanation

Correct Answer

A:

Do not assume normalization is right for every model/metric. This guards against recurrence because normalizing vector length separates direction from magnitude for suitable similarity metrics. The direct remediation is: Use VECTOR_NORMALIZE where supported before the intended comparison workflow.

Incorrect Answers

B:

Validating runtime-specific function availability addresses a different database behavior; the relevant distinction is that do not assume normalization is right for every model/metric.

C:

Choose exact versus linguistic search deliberately. The current task instead requires this action: do not assume normalization is right for every model/metric.

D:

Candidate-search tuning cannot establish whether unit-length normalization is mathematically appropriate for the selected model and distance metric.

Question 326

A salesperson needs to compare each month's sales with the prior month's result. Which implementation decision BEST addresses this requirement?

  1. Include an explicit window ORDER BY with unique tie-breaker columns.
  2. Use LAG(SalesAmount) OVER(PARTITION BY Region ORDER BY SalesMonth).
  3. Use a correlated EXISTS subquery filtered by each outer customer's key.
  4. Use EDIT_DISTANCE_SIMILARITY and calibrate thresholds with labeled records where supported.

Explanation

Correct Answer

B:

Use LAG(SalesAmount) OVER(PARTITION BY Region ORDER BY SalesMonth). This targets the primary issue because LAG references a preceding ordered row without self-joining every record. Verification: Month-over-month differences match a reference table. Prevent recurrence: Specify missing predecessor behavior explicitly.

Incorrect Answers

A:

Window ranking order is defined by its OVER clause, not the outer result sort.

C:

EXISTS is a semijoin-style test of matching rows without duplicating the outer customer.

D:

Similarity normalization makes scores easier to compare but is not proof of identity.

Question 327

A service trusts an unsigned client-supplied user role in a REST header. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. An anonymous request is denied and an approved role sees only permitted fields.
  2. A mismatched department INSERT or UPDATE fails under the test role.
  3. A forged admin header fails while valid signed identities receive correct rights.
  4. A test UPDATE appears in protected audit storage with correct identity.

Explanation

Correct Answer

C:

A forged admin header fails while valid signed identities receive correct rights. This is the decisive observable evidence after implementing validate provider-issued identity tokens and map claims to authorized application roles. It tests the underlying requirement: Untrusted headers cannot be treated as authenticated authorization claims.

Incorrect Answers

A:

An anonymous request is denied and an approved role sees only permitted fields. However, that result would not establish the required outcome: a forged admin header fails while valid signed identities receive correct rights.

B:

A mismatched department INSERT or UPDATE fails under the test role. However, that result would not establish the required outcome: a forged admin header fails while valid signed identities receive correct rights.

D:

A test UPDATE appears in protected audit storage with correct identity. However, that result would not establish the required outcome: a forged admin header fails while valid signed identities receive correct rights.

Question 328

A developer tries to use CONTAINS on a text column without a full-text index. Which technical reason BEST explains the proposed correction?

  1. FREETEXT expands linguistic meaning and is not equivalent to exact phrase matching.
  2. Index choice affects ingestion and maintenance as well as retrieval speed.
  3. Embedding storage and ANN index structures grow with vector size and document count.
  4. Full-text predicates require a configured index and supported column types.

Explanation

Correct Answer

D:

Full-text predicates require a configured index and supported column types. This explains why the corrective action is to create a supported full-text index and required unique key on the searchable table. Confirm the diagnosis when a controlled CONTAINS query succeeds after population completes.

Incorrect Answers

A:

FREETEXT expands linguistic meaning and is not equivalent to exact phrase matching. The actual technical constraint is that full-text predicates require a configured index and supported column types.

B:

Index choice affects ingestion and maintenance as well as retrieval speed. The actual technical constraint is that full-text predicates require a configured index and supported column types.

C:

Embedding storage and ANN index structures grow with vector size and document count. The actual technical constraint is that full-text predicates require a configured index and supported column types.

Question 329

An auditor sees database logs kept on the same instance a compromised admin can delete. Which preventive practice BEST addresses the underlying risk over time?

  1. Use monitored retention and independent security custody.
  2. Retain logs immutably where required and limit audit administrators.
  3. Rate-limit expensive query shapes and monitor access logs.
  4. Rotate signing keys and validate audiences and issuers.

Explanation

Correct Answer

A:

Use monitored retention and independent security custody. This guards against recurrence because tamper-resistant off-host retention is stronger evidence than only local logs. The direct remediation is: Send audit output to appropriately protected centralized storage with restricted permissions.

Incorrect Answers

B:

Retain logs immutably where required and limit audit administrators. The current task instead requires this action: use monitored retention and independent security custody.

C:

Rate-limit expensive query shapes and monitor access logs. The current task instead requires this action: use monitored retention and independent security custody.

D:

Rotate signing keys and validate audiences and issuers. The current task instead requires this action: use monitored retention and independent security custody.

Question 330

A finance application needs deterministic machine-readable response fields from model inference. What should the SQL developer do FIRST to address the specific issue?

  1. Benchmark smaller models and route complex cases only when quality needs justify it.
  2. Prefer a model and API mode supporting validated structured output and enforce schema checks.
  3. Plan a new compatible vector column/index and re-embed the corpus using the new model.
  4. Include semantically meaningful title and content fields, with identifiers retained as metadata.

Explanation

Correct Answer

B:

Prefer a model and API mode supporting validated structured output and enforce schema checks. This targets the primary issue because unconstrained prose is fragile to parse and can omit required keys. Verification: Sample calls produce valid JSON schema instances or explicit errors. Prevent recurrence: Reject unexpected fields and never trust generated totals without validation.

Incorrect Answers

A:

Model size increases cost and latency without guaranteeing proportional quality gains.

C:

Vector dimensionality is fixed by the column and must match the query vector.

D:

IDs alone lack the natural-language signal needed for semantic retrieval.

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