McAfee Secure

Exam Code: DP-800

Exam Name: Developing AI-Enabled Database Solutions

Certification Provider: Microsoft

Corresponding Certification: Microsoft Certified: SQL AI Developer Associate

Microsoft DP-800 Questions & Answers

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

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 19

Question 180

A SQL AI integration needs a managed definition for an approved text embedding endpoint. Which ongoing control is MOST relevant after the issue is fixed?

  1. Inspect invocation logs and rate limits.
  2. Check supported modalities, retention and safety policy.
  3. Alert on per-document cost and failed queue age.
  4. Version endpoint changes and restrict model creation rights.

Explanation

Correct Answer

D:

Version endpoint changes and restrict model creation rights. This guards against recurrence because a database model definition binds SQL AI operations to a specific external provider configuration. The direct remediation is: Create an external model object with supported endpoint, model identifiers and secure credential strategy.

Incorrect Answers

A:

Inspect invocation logs and rate limits. The current task instead requires this action: version endpoint changes and restrict model creation rights.

B:

Check supported modalities, retention and safety policy. The current task instead requires this action: version endpoint changes and restrict model creation rights.

C:

Alert on per-document cost and failed queue age. The current task instead requires this action: version endpoint changes and restrict model creation rights.

Question 181

A reporting warehouse scans hundreds of millions of narrow fact rows for aggregations. What is the MOST appropriate immediate corrective approach?

  1. Evaluate a clustered columnstore index for large analytic scans with batch-mode benefits.
  2. Select BIGINT after modeling value range, foreign keys and migration effects.
  3. Assess external tables and supported connector or virtualization capabilities for the deployed engine.
  4. Assess SQL ledger tables with digest storage and verification controls.

Explanation

Correct Answer

A:

Evaluate a clustered columnstore index for large analytic scans with batch-mode benefits. This targets the primary issue because column-oriented compression and batch processing can accelerate broad aggregations. Verification: A controlled report benchmark shows lower elapsed time and logical IO. Prevent recurrence: Avoid replacing lookup-heavy OLTP rowstore access without workload tests.

Incorrect Answers

B:

Selecting BIGINT after modeling value range, foreign keys and migration effects would not correct a reporting warehouse scans hundreds of millions of narrow fact rows for aggregations. Evaluate a clustered columnstore index for large analytic scans with batch-mode benefits.

C:

External tables abstract remote data but do not automatically remove network or predicate-pushdown costs.

D:

Ledger tamper evidence supports verification; it does not prevent every privileged misuse.

Question 182

A support knowledgebase contains title and body but only opaque GUIDs are embedded. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. Stock changes do not regenerate descriptive vectors yet filters use current quantities.
  2. Known user phrases recover the correct article chunk.
  3. All new embeddings insert without dimension errors and search quality is rechecked.
  4. A gold query set ranks known relevant chunks consistently after alignment.

Explanation

Correct Answer

B:

Known user phrases recover the correct article chunk. This is the decisive observable evidence after implementing include semantically meaningful title and content fields, with identifiers retained as metadata. It tests the underlying requirement: IDs alone lack the natural-language signal needed for semantic retrieval.

Incorrect Answers

A:

Stock changes do not regenerate descriptive vectors yet filters use current quantities. However, that result would not establish the required outcome: known user phrases recover the correct article chunk.

C:

All new embeddings insert without dimension errors and search quality is rechecked. However, that result would not establish the required outcome: known user phrases recover the correct article chunk.

D:

A gold query set ranks known relevant chunks consistently after alignment. However, that result would not establish the required outcome: known user phrases recover the correct article chunk.

Question 183

A support system needs to redact variable-length ticket numbers in a free-text column. Which technical reason BEST explains the proposed correction?

  1. Regex substring extraction returns the pattern occurrence, not a tokenized table.
  2. Levenshtein-style distance measures character edits, not verified real-world identity.
  3. Regex replacement can transform all matching substrings without fixed positions.
  4. Transient retries can replay committed work even after client-side timeouts.

Explanation

Correct Answer

C:

Regex replacement can transform all matching substrings without fixed positions. This explains why the corrective action is to use REGEXP_REPLACE with an appropriately tested pattern on supported SQL versions. Confirm the diagnosis when mixed-case sample descriptions preserve nonmatching text.

Incorrect Answers

A:

Regex substring extraction returns the pattern occurrence, not a tokenized table. The actual technical constraint is that regex replacement can transform all matching substrings without fixed positions.

B:

Levenshtein-style distance measures character edits, not verified real-world identity. The actual technical constraint is that regex replacement can transform all matching substrings without fixed positions.

D:

Transient retries can replay committed work even after client-side timeouts. The actual technical constraint is that regex replacement can transform all matching substrings without fixed positions.

Question 184

A query plan shows estimated one row but actual one million rows after skewed parameter values. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Combine query data with Azure Monitor and service limits.
  2. Avoid universally forcing MAXDOP 1 without workload evaluation.
  3. Monitor p95 waits and adjust scaling only from measured evidence.
  4. Measure regressions before using hints or recompilation globally.

Explanation

Correct Answer

D:

Measure regressions before using hints or recompilation globally. This guards against recurrence because a poor cardinality estimate can induce unsuitable joins and memory grants. The direct remediation is: Investigate parameter sensitivity, statistics and alternative plan strategies.

Incorrect Answers

A:

Combine query data with Azure Monitor and service limits. The current task instead requires this action: measure regressions before using hints or recompilation globally.

B:

Avoid universally forcing MAXDOP 1 without workload evaluation. The current task instead requires this action: measure regressions before using hints or recompilation globally.

C:

Monitor p95 waits and adjust scaling only from measured evidence.

Question 185

A hybrid search service applies tenant filters after merging candidate lists and sometimes leaks IDs in results. Which implementation decision BEST addresses this requirement?

  1. Enforce authorization in every retrieval branch before fusion and presentation.
  2. Combine full-text/lexical matching with semantic vector retrieval before ranking.
  3. Use supported VECTORPROPERTY to obtain relevant vector metadata.
  4. Align metric and model embedding guidance across index creation and query evaluation.

Explanation

Correct Answer

A:

Enforce authorization in every retrieval branch before fusion and presentation. This targets the primary issue because postfusion filtering can leave side-channel exposure and ranking distortion. Verification: Cross-tenant queries expose no IDs or snippets from denied records. Prevent recurrence: Test RLS and filter boundaries on lexical and vector paths.

Incorrect Answers

B:

Combine full-text/lexical matching with semantic vector retrieval before ranking. The relevant technical condition instead is that enforce authorization in every retrieval branch before fusion and presentation.

C:

Vector metadata functions describe representation without parsing serialized JSON arrays.

D:

Aligning metric and model embedding guidance across index creation and query evaluation would not correct a hybrid search service applies tenant filters after merging candidate lists and sometimes leaks IDs in results. Enforce authorization in every retrieval branch before fusion and presentation.

Question 186

A stored procedure's second UPDATE fails but first UPDATE remains committed. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. The same request ID produces one ledger effect across repeated calls.
  2. Injected errors leave balances unchanged and propagate a useful error.
  3. Candidate rankings improve for the known transpositions without false merges.
  4. A sample network returns the known minimal path when explicitly requested.

Explanation

Correct Answer

B:

Injected errors leave balances unchanged and propagate a useful error. This is the decisive observable evidence after implementing use TRY/CATCH with an explicit transaction, rollback, and THROW for failure handling. It tests the underlying requirement: Without coordinated error handling, partial writes can violate business invariants.

Incorrect Answers

A:

The same request ID produces one ledger effect across repeated calls. However, that result would not establish the required outcome: injected errors leave balances unchanged and propagate a useful error.

C:

Candidate rankings improve for the known transpositions without false merges. However, that result would not establish the required outcome: injected errors leave balances unchanged and propagate a useful error.

D:

A sample network returns the known minimal path when explicitly requested. However, that result would not establish the required outcome: injected errors leave balances unchanged and propagate a useful error.

Question 187

A database project changes a NOT NULL column without backfilling existing NULL rows. Which technical reason BEST explains the proposed correction?

  1. Changing project source alone does not apply DDL to the deployed database.
  2. Path ownership helps route high-impact DDL for accountable review.
  3. Existing data can prevent otherwise valid stricter schema changes.
  4. Reference data is usually broadly readable to repository collaborators.

Explanation

Correct Answer

C:

Existing data can prevent otherwise valid stricter schema changes. This explains why the corrective action is to include an appropriate staged data migration and backfill before constraint enforcement. Confirm the diagnosis when a staging upgrade preserves rows and enforces the new constraint.

Incorrect Answers

A:

Changing project source alone does not apply DDL to the deployed database. The actual technical constraint is that existing data can prevent otherwise valid stricter schema changes.

B:

Path ownership helps route high-impact DDL for accountable review. The actual technical constraint is that existing data can prevent otherwise valid stricter schema changes.

D:

Reference data is usually broadly readable to repository collaborators. The actual technical constraint is that existing data can prevent otherwise valid stricter schema changes.

Question 188

A CI pipeline stores a SQL administrative password as plain text in a workflow YAML file. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Audit assignments and revoke stale principals.
  2. Test migrations with production-shaped data volumes.
  3. Require branch protection and code review for production.
  4. Restrict secret access by environment and role.

Explanation

Correct Answer

D:

Restrict secret access by environment and role. This guards against recurrence because repository and job logs can leak plaintext secrets. The direct remediation is: Move secrets into a managed secret store or approved OIDC-based identity and rotate exposed credentials.

Incorrect Answers

A:

Audit assignments and revoke stale principals. The current task instead requires this action: restrict secret access by environment and role.

B:

Test migrations with production-shaped data volumes. The current task instead requires this action: restrict secret access by environment and role.

C:

Require branch protection and code review for production. The current task instead requires this action: restrict secret access by environment and role.

Question 189

An ETL pipeline treats a fuzzy match score as a UNIQUE customer identifier. What should the SQL developer do FIRST to address the specific issue?

  1. Use fuzzy scoring only for candidate generation and validate matches with stable IDs.
  2. Evaluate EDIT_DISTANCE to quantify edit operations before human review.
  3. Use a recursive CTE with an anchor member and a terminating recursive member.
  4. Use CATCH to log safely and rethrow meaningful failures rather than masking them.

Explanation

Correct Answer

A:

Use fuzzy scoring only for candidate generation and validate matches with stable IDs. This targets the primary issue because string resemblance cannot guarantee two records represent the same person. Verification: A deliberate near-name collision remains as separate customers. Prevent recurrence: Keep review logs and a reversible merge mechanism.

Incorrect Answers

B:

Evaluate EDIT_DISTANCE to quantify edit operations before human review. The relevant technical condition instead is that use fuzzy scoring only for candidate generation and validate matches with stable IDs.

C:

Using a recursive CTE with an anchor member and a terminating recursive member would not correct an ETL pipeline treats a fuzzy match score as a UNIQUE customer identifier. Use fuzzy scoring only for candidate generation and validate matches with stable IDs.

D:

Using CATCH to log safely and rethrow meaningful failures rather than masking them would not correct an ETL pipeline treats a fuzzy match score as a UNIQUE customer identifier. Use fuzzy scoring only for candidate generation and validate matches with stable IDs.

Question 190

A GraphQL endpoint permits nested queries that return customer records from unauthorized tenants. Which acceptance check is MOST directly related to this scenario?

  1. An anonymous request is denied and an approved role sees only permitted fields.
  2. Cross-tenant nested queries return no unauthorized records in negative tests.
  3. Model calls and retrieval calls each succeed only with required scopes.
  4. The verified pipeline deploys to staging but fails from an untrusted branch.

Explanation

Correct Answer

B:

Cross-tenant nested queries return no unauthorized records in negative tests. This is the decisive observable evidence after implementing enforce tenant-aware authorization, bounded query complexity and back-end SQL row security. It tests the underlying requirement: GraphQL relationship traversal can increase data exposure if access is too broad.

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: cross-tenant nested queries return no unauthorized records in negative tests.

C:

Model calls and retrieval calls each succeed only with required scopes. However, that result would not establish the required outcome: cross-tenant nested queries return no unauthorized records in negative tests.

D:

The verified pipeline deploys to staging but fails from an untrusted branch. However, that result would not establish the required outcome: cross-tenant nested queries return no unauthorized records in negative tests.

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