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 94

Question 930

A SQL AI integration needs a managed definition for an approved text embedding endpoint. Which technical principle BEST explains the relevant design decision?

  1. Model invocation can export inputs and incur charges, so object privileges matter.
  2. A database model definition binds SQL AI operations to a specific external provider configuration.
  3. A larger model is not automatically the best fit for cost, speed or multilingual accuracy.
  4. Configure CDC to refresh rows after source data changes without defining an external model object.

Explanation

Correct Answer

B:

A database model definition binds SQL AI operations to a specific external provider configuration. This explains why the corrective action is to create an external model object with supported endpoint, model identifiers and secure credential strategy. Confirm the diagnosis when an authorized test generates an embedding with the expected size.

Incorrect Answers

A:

Modeling invocation can export inputs and incur charges, so object privileges matter concerns a different mechanism; the relevant technical point is that a database model definition binds SQL AI operations to a specific external provider configuration.

C:

A larger model is not automatically the best fit for cost, speed or multilingual accuracy. The actual technical constraint is that a database model definition binds SQL AI operations to a specific external provider configuration.

D:

CDC identifies changed data but does not create the authenticated model configuration that SQL AI operations need to call the approved endpoint.

Question 931

A reporting warehouse scans hundreds of millions of narrow fact rows for aggregations. Which ongoing control is MOST relevant after the issue is fixed?

  1. Validate client parameter types and index-size effects before deployment.
  2. Monitor source availability and credential rotation.
  3. Avoid replacing lookup-heavy OLTP rowstore access without workload tests.
  4. Protect digests independently and audit administrative access.

Explanation

Correct Answer

C:

Avoid replacing lookup-heavy OLTP rowstore access without workload tests. This guards against recurrence because column-oriented compression and batch processing can accelerate broad aggregations. The direct remediation is: Evaluate a clustered columnstore index for large analytic scans with batch-mode benefits.

Incorrect Answers

A:

Validate client parameter types and index-size effects before deployment. The current task instead requires this action: avoid replacing lookup-heavy OLTP rowstore access without workload tests.

B:

Monitor source availability and credential rotation. The current task instead requires this action: avoid replacing lookup-heavy OLTP rowstore access without workload tests.

D:

Protect digests independently and audit administrative access. The current task instead requires this action: avoid replacing lookup-heavy OLTP rowstore access without workload tests.

Question 932

A support knowledgebase contains title and body but only opaque GUIDs are embedded. What is the MOST appropriate immediate corrective approach?

  1. Keep rapidly changing structured quantities as filterable columns outside the descriptive embedding text.
  2. Use Change Tracking to refresh embeddings for every new GUID without including document text.
  3. Benchmark smaller models and route complex cases only when quality needs justify it.
  4. Include semantically meaningful title and content fields, with identifiers retained as metadata.

Explanation

Correct Answer

D:

Include semantically meaningful title and content fields, with identifiers retained as metadata. This targets the primary issue because IDs alone lack the natural-language signal needed for semantic retrieval. Verification: Known user phrases recover the correct article chunk. Prevent recurrence: Keep source citations and access metadata attached.

Incorrect Answers

A:

Frequent numeric fields add update cost and can weaken semantic relevance.

B:

A fresher index of opaque identifiers is still semantically weak. Embed meaningful title and content fields, then retain GUIDs for identity and filtering.

C:

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

Question 933

A support system needs to redact variable-length ticket numbers in a free-text column. What observed evidence would BEST confirm successful remediation?

  1. Mixed-case sample descriptions preserve nonmatching text.
  2. Test values return the intended code or an explicit missing result.
  3. Injected errors leave balances unchanged and propagate a useful error.
  4. Known two-hop links are retrieved without unrelated reverse links.

Explanation

Correct Answer

A:

Mixed-case sample descriptions preserve nonmatching text. This is the decisive observable evidence after implementing use REGEXP_REPLACE with an appropriately tested pattern on supported SQL versions. It tests the underlying requirement: Regex replacement can transform all matching substrings without fixed positions.

Incorrect Answers

B:

Test values return the intended code or an explicit missing result. However, that result would not establish the required outcome: mixed-case sample descriptions preserve nonmatching text.

C:

Injected errors leave balances unchanged and propagate a useful error. However, that result would not establish the required outcome: mixed-case sample descriptions preserve nonmatching text.

D:

Known two-hop links are retrieved without unrelated reverse links. However, that result would not establish the required outcome: mixed-case sample descriptions preserve nonmatching text.

Question 934

A query plan shows estimated one row but actual one million rows after skewed parameter values. What is the MOST relevant technical distinction in this situation?

  1. Platform-native query insight tools expose historical workload statistics for managed instances.
  2. A poor cardinality estimate can induce unsuitable joins and memory grants.
  3. Tempdb contention can impede transient sorting, hashing and version-store activity.
  4. A read-then-write race can oversell without a concurrency constraint.

Explanation

Correct Answer

B:

A poor cardinality estimate can induce unsuitable joins and memory grants. This explains why the corrective action is to investigate parameter sensitivity, statistics and alternative plan strategies. Confirm the diagnosis when different representative parameter values meet latency targets after tuning.

Incorrect Answers

A:

Platform-native query insight tools expose historical workload statistics for managed instances. The actual technical constraint is that a poor cardinality estimate can induce unsuitable joins and memory grants.

C:

Tempdb contention can impede transient sorting, hashing and version-store activity. The actual technical constraint is that a poor cardinality estimate can induce unsuitable joins and memory grants.

D:

A read-then-write race can oversell without a concurrency constraint. The actual technical constraint is that a poor cardinality estimate can induce unsuitable joins and memory grants.

Question 935

A hybrid search service applies tenant filters after merging candidate lists and sometimes leaks IDs in results. Which ongoing control is MOST relevant after the issue is fixed?

  1. Tune weighting using labeled relevance judgments.
  2. Choose exact versus linguistic search deliberately.
  3. Test RLS and filter boundaries on lexical and vector paths.
  4. Verify platform, dimension limit and client compatibility.

Explanation

Correct Answer

C:

Test RLS and filter boundaries on lexical and vector paths. This guards against recurrence because postfusion filtering can leave side-channel exposure and ranking distortion. The direct remediation is: Enforce authorization in every retrieval branch before fusion and presentation.

Incorrect Answers

A:

Tune weighting using labeled relevance judgments. The current task instead requires this action: test RLS and filter boundaries on lexical and vector paths.

B:

Choose exact versus linguistic search deliberately. The current task instead requires this action: test RLS and filter boundaries on lexical and vector paths.

D:

Verify platform, dimension limit and client compatibility. The current task instead requires this action: test RLS and filter boundaries on lexical and vector paths.

Question 936

A stored procedure's second UPDATE fails but first UPDATE remains committed. What is the MOST appropriate immediate corrective approach?

  1. Make transaction operations idempotent using a stable request ID and uniqueness constraints.
  2. Use a correlated aggregate subquery or window function scoped by category.
  3. Use OPENJSON on the reviewer array with a typed WITH clause.
  4. Use TRY/CATCH with an explicit transaction, rollback, and THROW for failure handling.

Explanation

Correct Answer

D:

Use TRY/CATCH with an explicit transaction, rollback, and THROW for failure handling. This targets the primary issue because without coordinated error handling, partial writes can violate business invariants. Verification: Injected errors leave balances unchanged and propagate a useful error. Prevent recurrence: Use XACT_STATE appropriately before commit or rollback.

Incorrect Answers

A:

Transient retries can replay committed work even after client-side timeouts.

B:

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

C:

OPENJSON expands array elements into tabular rows for joins and validation.

Question 937

A database project changes a NOT NULL column without backfilling existing NULL rows. What observed evidence would BEST confirm successful remediation?

  1. A staging upgrade preserves rows and enforces the new constraint.
  2. After publish, the target contains the intended index and tested workload improves.
  3. The project builds on a clean agent and generates the expected DACPAC.
  4. Drift detection flags the unexpected column change before publish.

Explanation

Correct Answer

A:

A staging upgrade preserves rows and enforces the new constraint. This is the decisive observable evidence after implementing include an appropriate staged data migration and backfill before constraint enforcement. It tests the underlying requirement: Existing data can prevent otherwise valid stricter schema changes.

Incorrect Answers

B:

After publish, the target contains the intended index and tested workload improves. However, that result would not establish the required outcome: a staging upgrade preserves rows and enforces the new constraint.

C:

The project builds on a clean agent and generates the expected DACPAC. However, that result would not establish the required outcome: a staging upgrade preserves rows and enforces the new constraint.

D:

Drift detection flags the unexpected column change before publish. However, that result would not establish the required outcome: a staging upgrade preserves rows and enforces the new constraint.

Question 938

A CI pipeline stores a SQL administrative password as plain text in a workflow YAML file. Which technical reason BEST explains the proposed correction?

  1. Shared overly privileged credentials increase blast radius across systems.
  2. Repository and job logs can leak plaintext secrets.
  3. Schema compilation alone does not prove business rules are correct.
  4. Cross-system contracts can fail even when individual SQL procedures are correct.

Explanation

Correct Answer

B:

Repository and job logs can leak plaintext secrets. This explains why the corrective action is to move secrets into a managed secret store or approved OIDC-based identity and rotate exposed credentials. Confirm the diagnosis when scans find no credentials and a staging deployment authenticates securely.

Incorrect Answers

A:

Shared overly privileged credentials increase blast radius across systems. The actual technical constraint is that repository and job logs can leak plaintext secrets.

C:

Schema compilation alone does not prove business rules are correct. The actual technical constraint is that repository and job logs can leak plaintext secrets.

D:

Cross-system contracts can fail even when individual SQL procedures are correct. The actual technical constraint is that repository and job logs can leak plaintext secrets.

Question 939

An ETL pipeline treats a fuzzy match score as a UNIQUE customer identifier. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Use supplemental identifiers and human approval for merges.
  2. Inspect plans when a CTE is referenced repeatedly.
  3. Keep review logs and a reversible merge mechanism.
  4. Check engine support and trim blank tokens before inserting.

Explanation

Correct Answer

C:

Keep review logs and a reversible merge mechanism. This guards against recurrence because string resemblance cannot guarantee two records represent the same person. The direct remediation is: Use fuzzy scoring only for candidate generation and validate matches with stable IDs.

Incorrect Answers

A:

Use supplemental identifiers and human approval for merges. The current task instead requires this action: keep review logs and a reversible merge mechanism.

B:

Inspect plans when a CTE is referenced repeatedly. The current task instead requires this action: keep review logs and a reversible merge mechanism.

D:

Check engine support and trim blank tokens before inserting. The current task instead requires this action: keep review logs and a reversible merge mechanism.

Question 940

A GraphQL endpoint permits nested queries that return customer records from unauthorized tenants. Which implementation decision BEST addresses this requirement?

  1. Configure authentication and per-entity role/operation permissions rather than public wildcard actions.
  2. Require encrypted transport and a verified principal alongside network access restrictions.
  3. Grant SELECT on the approved view and validate ownership-chain and execution context behavior.
  4. Enforce tenant-aware authorization, bounded query complexity and back-end SQL row security.

Explanation

Correct Answer

D:

Enforce tenant-aware authorization, bounded query complexity and back-end SQL row security. This targets the primary issue because graphQL relationship traversal can increase data exposure if access is too broad. Verification: Cross-tenant nested queries return no unauthorized records in negative tests. Prevent recurrence: Rate-limit expensive query shapes and monitor access logs.

Incorrect Answers

A:

API generation does not automatically imply that exposed data is appropriately authorized.

B:

Network allowlisting does not establish user identity or object authorization.

C:

Exposing a constrained view can limit data surface without broad base-table rights.

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