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 5

Question 40

A product catalog stores optional nested specifications that vary by category but still needs relational constraints. Which ongoing control is MOST relevant after the issue is fixed?

  1. Reject unknown fields and bound payload size before committing.
  2. Measure cardinality and maintain customer-selective indexes.
  3. Audit index alignment after every schema release.
  4. Document a schema contract and version changes to payload format.

Explanation

Correct Answer

D:

Document a schema contract and version changes to payload format. This guards against recurrence because JSON storage allows flexible attributes without abandoning structured identity and integrity. The direct remediation is: Store appropriate semi-structured attributes in a validated JSON column alongside relational keys.

Incorrect Answers

A:

Reject unknown fields and bound payload size before committing. The current task instead requires this action: document a schema contract and version changes to payload format.

B:

Measure cardinality and maintain customer-selective indexes. The current task instead requires this action: document a schema contract and version changes to payload format.

C:

Audit index alignment after every schema release. The current task instead requires this action: document a schema contract and version changes to payload format.

Question 41

Two vectors must be compared exactly using cosine distance without building an ANN index. What is the MOST appropriate immediate corrective approach?

  1. Use VECTOR_DISTANCE with supported metric and compatible vectors.
  2. Use supported VECTORPROPERTY to obtain relevant vector metadata.
  3. Combine full-text/lexical matching with semantic vector retrieval before ranking.
  4. Generate query embeddings with the same approved model and dimension as the column.

Explanation

Correct Answer

A:

Use VECTOR_DISTANCE with supported metric and compatible vectors. This targets the primary issue because exact distance calculation compares actual embeddings rather than requiring an approximate index. Verification: Small reference vectors yield known expected distances. Prevent recurrence: Verify metric choice and dimension equality.

Incorrect Answers

B:

Vector metadata functions describe representation without parsing serialized JSON arrays.

C:

Combine full-text/lexical matching with semantic vector retrieval before ranking. The relevant technical condition instead is that use VECTOR_DISTANCE with supported metric and compatible vectors.

D:

Generate query embeddings with the same approved model and dimension as the column. The relevant technical condition instead is that use VECTOR_DISTANCE with supported metric and compatible vectors.

Question 42

An instruction file tells Copilot to use production credentials in example scripts. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. SQL file suggestions follow database style while other files retain appropriate conventions.
  2. Generated examples show placeholders and approved secret references.
  3. The session lists the current intended tools and returns valid metadata.
  4. The final query respects RLS while returning the caller's permitted rows.

Explanation

Correct Answer

B:

Generated examples show placeholders and approved secret references. This is the decisive observable evidence after implementing remove secret references from instructions and require environment-injected credentials. It tests the underlying requirement: Instructions are prompt context and can unintentionally expose confidential values.

Incorrect Answers

A:

SQL file suggestions follow database style while other files retain appropriate conventions. However, that result would not establish the required outcome: generated examples show placeholders and approved secret references.

C:

The session lists the current intended tools and returns valid metadata. However, that result would not establish the required outcome: generated examples show placeholders and approved secret references.

D:

The final query respects RLS while returning the caller's permitted rows. However, that result would not establish the required outcome: generated examples show placeholders and approved secret references.

Question 43

A query suddenly slows after an index change and the team has no baseline execution plan. Which technical reason BEST explains the proposed correction?

  1. Platform-native query insight tools expose historical workload statistics for managed instances.
  2. Tempdb contention can impede transient sorting, hashing and version-store activity.
  3. Access path and estimated cardinality changes can explain runtime degradation.
  4. A read-then-write race can oversell without a concurrency constraint.

Explanation

Correct Answer

C:

Access path and estimated cardinality changes can explain runtime degradation. This explains why the corrective action is to compare actual execution plans and Query Store history to identify plan regressions. Confirm the diagnosis when a forced or corrected plan improves tested workload without wrong results.

Incorrect Answers

A:

Platform-native query insight tools expose historical workload statistics for managed instances. The actual technical constraint is that access path and estimated cardinality changes can explain runtime degradation.

B:

Tempdb contention can impede transient sorting, hashing and version-store activity. The actual technical constraint is that access path and estimated cardinality changes can explain runtime degradation.

D:

A read-then-write race can oversell without a concurrency constraint. The actual technical constraint is that access path and estimated cardinality changes can explain runtime degradation.

Question 44

A deployment warns about potential data loss from a changed column type but pipeline ignores it. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Reconcile sanctioned hotfixes into source control promptly.
  2. Review seed-data changes like schema changes.
  3. Audit pipeline changes and approval records.
  4. Keep backups and verify postdeployment data counts.

Explanation

Correct Answer

D:

Keep backups and verify postdeployment data counts. This guards against recurrence because schema reconciliation can propose destructive changes that threaten existing records. The direct remediation is: Review deployment drift and data-loss warnings with explicit owner approval.

Incorrect Answers

A:

Reconcile sanctioned hotfixes into source control promptly. The current task instead requires this action: keep backups and verify postdeployment data counts.

B:

Review seed-data changes like schema changes. The current task instead requires this action: keep backups and verify postdeployment data counts.

C:

Audit pipeline changes and approval records. The current task instead requires this action: keep backups and verify postdeployment data counts.

Question 45

A nightly batch must assign a contiguous range of sequence values efficiently. Which implementation decision BEST addresses this requirement?

  1. Evaluate sp_sequence_get_range when available to reserve a set of sequence values.
  2. Revise the design to tolerate gaps rather than using sequence numbers as legal receipt counters.
  3. Define and test a FOREIGN KEY and explicitly select appropriate delete behavior.
  4. Use OPENJSON with a typed WITH schema and transactional parent-child inserts.

Explanation

Correct Answer

A:

Evaluate sp_sequence_get_range when available to reserve a set of sequence values. This targets the primary issue because bulk reservation reduces repeated allocation calls but values remain consumable and nontransactional. Verification: A test reserves the requested count and avoids collisions. Prevent recurrence: Monitor exhaustion and limit allocation rights.

Incorrect Answers

B:

Sequence numbers can be consumed even when transactions roll back or cache is lost.

C:

Referential integrity prevents orphan children unless cascade or other actions are deliberately configured.

D:

Parsing nested arrays into typed rows reduces fragile manual string extraction.

Question 46

A migration is merged after accidentally excluding a new stored procedure from the project file. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. A clean clone builds the same schema model.
  2. DACPAC inspection contains the new approved procedure.
  3. A staging publish preserves expected historical data and references.
  4. A billing SQL PR requires a finance-code-owner approval before merge.

Explanation

Correct Answer

B:

DACPAC inspection contains the new approved procedure. This is the decisive observable evidence after implementing review project inclusion and validate the built model against intended objects. It tests the underlying requirement: Source presence does not guarantee every file enters the SQL project model.

Incorrect Answers

A:

A clean clone builds the same schema model. However, that result would not establish the required outcome: dACPAC inspection contains the new approved procedure.

C:

A staging publish preserves expected historical data and references. However, that result would not establish the required outcome: dACPAC inspection contains the new approved procedure.

D:

A billing SQL PR requires a finance-code-owner approval before merge. However, that result would not establish the required outcome: dACPAC inspection contains the new approved procedure.

Question 47

A reporting app needs nested order and line-item fields in one client query. Which technical reason BEST explains the proposed correction?

  1. The two interfaces expose related data but have different query syntax and operation models.
  2. Syntactic JSON validity alone does not prove a mapped SQL object exists.
  3. GraphQL can request related fields through a typed schema rather than repeated round trips.
  4. Runtime containers need compatible DAB binaries, configuration and connection settings.

Explanation

Correct Answer

C:

GraphQL can request related fields through a typed schema rather than repeated round trips. This explains why the corrective action is to configure a GraphQL endpoint and modeled relationships for approved entities. Confirm the diagnosis when a nested query returns only permitted order lines for the signed-in user.

Incorrect Answers

A:

The two interfaces expose related data but have different query syntax and operation models. The actual technical constraint is that graphQL can request related fields through a typed schema rather than repeated round trips.

B:

Syntactic JSON validity alone does not prove a mapped SQL object exists. The actual technical constraint is that graphQL can request related fields through a typed schema rather than repeated round trips.

D:

Runtime containers need compatible DAB binaries, configuration and connection settings. The actual technical constraint is that graphQL can request related fields through a typed schema rather than repeated round trips.

Question 48

A multistatement table-valued function causes bad row estimates and inefficient joins. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Add compile-time signature review and CI tests requiring a table-returning function and expected columns when callers need multirow results..
  2. Implement transactional outbox or supported change notifications.
  3. Verify behavior and compatibility-level differences before broad replacement.
  4. Keep table cardinality and version-specific improvements in review.

Explanation

Correct Answer

D:

Keep table cardinality and version-specific improvements in review. This guards against recurrence because optimizer visibility can differ between inline and multistatement TVFs. The direct remediation is: Consider converting eligible logic into an inline TVF and compare plans.

Incorrect Answers

A:

Add compile-time signature review and CI tests requiring a table-returning function and expected columns when callers need multirow results.

B:

Implement transactional outbox or supported change notifications. The current task instead requires this action: keep table cardinality and version-specific improvements in review.

C:

Verify behavior and compatibility-level differences before broad replacement. The current task instead requires this action: keep table cardinality and version-specific improvements in review.

Question 49

A sales report needs customers who have at least one unpaid order. What should the SQL developer do FIRST to address the specific issue?

  1. Use a correlated EXISTS subquery filtered by each outer customer's key.
  2. Use a correlated aggregate subquery or window function scoped by category.
  3. Include an explicit window ORDER BY with unique tie-breaker columns.
  4. Use JSON_ARRAYAGG grouped by the order identifier where supported.

Explanation

Correct Answer

A:

Use a correlated EXISTS subquery filtered by each outer customer's key. This targets the primary issue because EXISTS is a semijoin-style test of matching rows without duplicating the outer customer. Verification: One row per qualified customer appears in a test dataset. Prevent recurrence: Use selective indexes on the correlated join key.

Incorrect Answers

B:

An outer reference lets the inner aggregate vary by each row's category.

C:

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

D:

JSON_ARRAYAGG can collect multiple row values into a JSON array.

Question 50

A pricing table admits negative unit prices despite a documented positive-price rule. Which acceptance check is MOST directly related to this scenario?

  1. Orphan inserts fail and approved delete scenarios behave as specified.
  2. Direct SQL inserts with negative prices fail without affecting valid rows.
  3. Reconciled payment totals match control calculations to the cent.
  4. A controlled report benchmark shows lower elapsed time and logical IO.

Explanation

Correct Answer

B:

Direct SQL inserts with negative prices fail without affecting valid rows. This is the decisive observable evidence after implementing use a CHECK constraint expressing the supported business condition. It tests the underlying requirement: Application-layer validation alone can be bypassed by other writers.

Incorrect Answers

A:

Orphan inserts fail and approved delete scenarios behave as specified. However, that result would not establish the required outcome: direct SQL inserts with negative prices fail without affecting valid rows.

C:

Reconciled payment totals match control calculations to the cent. However, that result would not establish the required outcome: direct SQL inserts with negative prices fail without affecting valid rows.

D:

A controlled report benchmark shows lower elapsed time and logical IO. However, that result would not establish the required outcome: direct SQL inserts with negative prices fail without affecting valid rows.

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