mcAfee Secure Website
exam =5
exam =6

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

Reliable & Actual Study Materials for DP-800 Exam Success

101 Questions & Answers with Testing Engine

"DP-800: Developing AI-Enabled Database Solutions" Testing Engine covers all the knowledge points of the real Microsoft DP-800 exam.

The latest actual DP-800 Questions & Answers from Pass4sure. Everything you need to prepare and get best score at DP-800 exam easily and quickly.

exam =7
Guarantee

Satisfaction Guaranteed

Pass4sure has a remarkable Microsoft Candidate Success record. We're confident of our products and provide no hassle product exchange. That's how confident we are!

99.3% Pass Rate
Was: $137.49
Now: $124.99

Microsoft DP-800 Practice Test 16

Question 150

A multi-region SQL analytics app must prevent staff from seeing rows assigned to another region. Which implementation step most directly addresses this requirement?

  1. Filter regional rows in the client UI after a database query has returned the full multi-region dataset.
  2. Implement a schema-bound security predicate backed by trustworthy identity attributes.
  3. Use a session-provided region string as the RLS identity without validating whether the caller can forge that value.
  4. Grant the regional reporting login db_owner so its own application code can decide which other regions to hide.

Explanation

Correct Answer

B:

Row-Level Security predicates enforce row access in the database based on a validated execution context. Implement a schema-bound security predicate backed by trustworthy identity attributes. Cross-region query tests return no forbidden records for low-privileged users.

Incorrect Answers

A:

Client-side filtering happens after sensitive rows have already left SQL. Enforce regional RLS in the database.

C:

A spoofable region attribute can bypass RLS. Derive row policy from a trustworthy authenticated SQL execution context.

D:

Database ownership defeats least privilege; apply schema-bound RLS and restricted grants to regional staff access.

Question 151

A supported Azure SQL deployment must invoke an external language model from a controlled stored procedure. The team implemented this change: Configure an approved endpoint and least-privilege procedure call with timeouts. Which result would best confirm the change worked?

  1. A test prompt receives a validated response and audit identifies the invocation.
  2. Approve the external SQL model invocation when a request succeeds using an administrator credential but the intended procedure role is denied.
  3. Treat an HTTP 200 response as proof of a valid language model result without checking JSON structure, timeout or audit tracing.
  4. Verify the stored procedure's external endpoint call only under normal latency and ignore throttling, retry and timeout failures.

Explanation

Correct Answer

A:

A test prompt receives a validated response and audit identifies the invocation. Sp_invoke_external_rest_endpoint can call permitted HTTPS endpoints with configured credentials where supported. External calls introduce latency, cost and failure modes to SQL workloads.

Incorrect Answers

B:

Administrative success is not least-privilege verification. Test the actual application procedure identity and its grants.

C:

HTTP success alone cannot prove usable model output. Validate response schema and traceable request identity.

D:

A production SQL model call must handle external failures safely. Test latency spikes, throttling and expected timeouts.

Question 152

An Azure SQL database experiences intermittent latency and operations wants top resource-consuming queries over time. The team implemented this change: Analyze top statements and correlate plan/runtime changes with traffic. Which result would best confirm the change worked?

  1. Report success because Azure SQL Query Store is enabled, even though P95 request latency and CPU peaks are unchanged.
  2. Optimized statements reduce resource peaks under repeated workload.
  3. Use a faster single query execution as proof that the intermittent SQL production regression is permanently resolved.
  4. Accept lower average CPU despite a new execution plan that doubles timeout rates for the most affected API call.

Explanation

Correct Answer

B:

Optimized statements reduce resource peaks under repeated workload. Azure SQL query insights and Query Store can reveal resource-heavy query trends. Monitoring metrics do not by themselves identify causal application behavior.

Incorrect Answers

A:

Enabling Query Store produces telemetry; improvement must be measured with repeat workloads and actual latency/CPU reductions.

C:

One fast query does not capture intermittent regressions. Compare workload distributions, tail latency and resource usage over time.

D:

An average CPU reduction does not compensate for worse timeout/tail behavior. Verify service impact under comparable load.

Question 153

A role has GRANT SELECT but one user receives access denied due to another inherited DENY. The team implemented this change: Remove conflicting unneeded role membership or redesign least-privilege grants safely. Which result would best confirm the change worked?

  1. Accept the permission repair when the intended principal can SELECT the approved table but can also read unrelated sensitive objects.
  2. Intended principal reads approved objects while unrelated access remains denied.
  3. Test SELECT access as a db_owner and infer that the target user's inherited DENY conflict was resolved correctly.
  4. Treat successful SELECT after removing an entire DENY role as correct even though other inherited GRANTs now expose private rows.

Explanation

Correct Answer

B:

Intended principal reads approved objects while unrelated access remains denied. Effective SQL permissions combine principals and may be affected by explicit DENY. Broad removal of DENY can expose data through other inherited grants.

Incorrect Answers

A:

A successful approved SELECT does not establish least privilege. Test unrelated table access denial too.

C:

An administrator's result does not show the target user's effective GRANT/DENY memberships. Test the actual principal.

D:

A broad DENY removal can unintentionally expand access. Verify intended table SELECT and negative tests for forbidden rows.

Question 154

A development team needs a portable modern build pipeline for SQL schema validation and deployment artifacts. The team implemented this change: Configure SDK-style project and run repeatable builds in CI. Which result would best confirm the change worked?

  1. Accept the SDK-style SQL project because it compiles on one developer laptop with local dependencies not installed in CI.
  2. Clean agents produce equivalent artifacts and expected schema validation results.
  3. Verify that the SQL project produces a DACPAC but never check that its schema targets the intended SQL engine version.
  4. Treat a green source restore step as a complete SQL schema validation even when the build emits unresolved references.

Explanation

Correct Answer

B:

Clean agents produce equivalent artifacts and expected schema validation results. SDK-style SQL projects support project-based builds and consistent CI model validation where supported. SDK features and compatible targets evolve across versions.

Incorrect Answers

A:

A local compilation does not establish portable SQL builds. Verify on a clean CI agent with pinned SDK and project references.

C:

A DACPAC artifact alone does not prove target compatibility. Validate the declared platform and its object support.

D:

Package restore does not validate the database model. Inspect SQL project compilation and high-risk unresolved-reference diagnostics.

Question 155

A human-resources query traverses manager relationships with potentially deep reporting chains. The team implemented this change: Build a recursion-limited CTE with cycle protection and trusted root selection. Which result would best confirm the change worked?

  1. Pass a shallow three-person hierarchy test and ignore a fixture where A manages B and B manages A.
  2. Count returned rows while ignoring whether managers are assigned to the correct depth and root.
  3. Accept quick execution on a ten-row fixture as proof of scalability for millions of edges.
  4. Sample chain traversal returns correct levels without infinite loops.

Explanation

Correct Answer

D:

Sample chain traversal returns correct levels without infinite loops. A recursive CTE can walk parent-child hierarchies with anchor and recursive members. Recursive query cost can grow quickly with malformed cyclic data.

Incorrect Answers

A:

Cycles and malformed hierarchy links can repeat indefinitely; bound recursion and test cycle handling explicitly.

B:

Correct recursive output depends on trusted root selection, termination condition and validated hierarchy relationships.

C:

Hierarchical recursion can expand rapidly; profile realistic depth, fan-out, indexes and platform recursion controls.

Question 156

A service filters an indexed customer code using LEFT(code,3) and scans the whole table. The team implemented this change: Rewrite to a precise supported prefix range or index an appropriate computed column. Which result would best confirm the change worked?

  1. Accept the rewritten customer-code query because SQL uses an index seek even though several boundary prefixes are missing.
  2. The plan uses an efficient access path and returns the same codes.
  3. Test only an ASCII uppercase customer-code sample and infer the prefix predicate preserves all case-sensitive collation behavior.
  4. Treat lower estimated cost as success although the rewritten prefix range returns extra customer codes outside the requested prefix.

Explanation

Correct Answer

B:

The plan uses an efficient access path and returns the same codes. Applying a function to an indexed predicate can prevent efficient seeking.

Incorrect Answers

A:

An index seek is not enough when collation or prefix boundaries omit valid codes. Compare original and new result sets.

C:

Different collations can change prefix ordering and case behavior. Test realistic customer codes across edge boundaries.

D:

A cheaper plan with incorrect customer codes violates correctness. Verify exact prefix membership and access path together.

Question 157

A pricing report calls a scalar function per row and CPU increases with report size. Initial testing shows: CPU cost per thousand rows decreases with the same calculated prices. Which limitation should the team still consider?

  1. An inline rewrite can never change numeric precision or NULL handling in business calculations.
  2. Rewriting business logic can introduce subtle null/rounding differences.
  3. Connection pooling eliminates function CPU and therefore no calculation regression tests are necessary.
  4. The only possible regression after set-based conversion is a slower disk cache warm-up.

Explanation

Correct Answer

B:

Rewriting business logic can introduce subtle null/rounding differences. Non-inlineable scalar UDF execution can make row-by-row evaluation expensive.

Incorrect Answers

A:

Row-at-a-time scalar execution can be expensive; eligible inlining and semantic equivalence both need measurement.

C:

More connections do not remove per-row computation; performance must be compared with unchanged amounts and boundary tests.

D:

Cached data does not eliminate expensive function invocations, and a rewrite can alter calculation semantics beyond disk IO.

Question 158

A data sync trigger sends duplicate change entries when an update modifies values without changing the monitored column. The team proposes this approach: Write a set-based change trigger that emits only actual relevant column transitions. Which technical principle supports this approach?

  1. UPDATE(column) returns true only when a monitored column’s value changes in every affected row.
  2. An SQL Server DML trigger is invoked separately for every updated row and can safely read one scalar inserted value.
  3. Comparing inserted and deleted values avoids treating unchanged data as meaningful changes.
  4. Updating an audit timestamp proves the monitored business property has changed for each affected row.

Explanation

Correct Answer

C:

Comparing inserted and deleted values avoids treating unchanged data as meaningful changes. Triggers should not synchronously depend on an unavailable external network service.

Incorrect Answers

A:

A column can appear in a SET clause without changing its value; compare inserted and deleted per row.

B:

DML triggers are statement-scoped; inserted and deleted are rowsets and require set-based comparison and NULL-safe handling.

D:

A timestamp may change for unrelated reasons; only a checked business-column transition can justify the event.

Question 159

A chat agent includes internal legal memoranda in context for a general staff user. Initial testing shows: General users cannot receive or infer restricted legal text. Which limitation should the team still consider?

  1. Prompt instructions replace retrieval-time document authorization.
  2. String metadata filters automatically enforce identity-based access policies.
  3. A final-answer redaction prevents cross-user context-cache or telemetry leaks.
  4. Prompt-level refusal is not a reliable substitute for retrieval access control.

Explanation

Correct Answer

D:

Prompt-level refusal is not a reliable substitute for retrieval access control. RAG must enforce document authorization before retrieved text reaches the model.

Incorrect Answers

A:

Authorize chunks before retrieval/context assembly; do not give the model forbidden documents.

B:

Use trusted identity and document ACLs rather than unvalidated prompt or request parameters.

C:

Authorize and partition retrieval, caches and logs, not merely the final answer. The genuine residual limitation is: Prompt-level refusal is not a reliable substitute for retrieval access control.

Question 160

A search procedure compares one query vector against stored vectors with a selected distance metric. The team is considering this step: Use VECTOR_DISTANCE in a scoped evaluated query with stable ordering. What evidence should they review before proceeding?

  1. Check that the query vector exists but omit its dimensions and whether the stored vectors share the chosen distance metric.
  2. Use a B-tree index on a vector column as evidence that VECTOR_DISTANCE must perform approximate ANN retrieval.
  3. Inspect distance scores from mixed embedding model versions without identifying which model generated query and stored vectors.
  4. Confirm input dimensions, metric syntax, value validity and row filters.

Explanation

Correct Answer

D:

Confirm input dimensions, metric syntax, value validity and row filters. VECTOR_DISTANCE calculates a metric between compatible vectors in supported SQL engines.

Incorrect Answers

A:

Vector existence alone is not metric compatibility. Check dimensions, valid values and cosine/euclidean/dot metric requirements.

B:

A B-tree is not a vector index, and VECTOR_DISTANCE is exact scalar computation; it does not use ANN indexes.

C:

Distance values between incompatible model spaces may be meaningless. Verify version, dimension and metric agreement.

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