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 15

Question 140

Security needs SQL audit data for an extended period but storage cleanup deletes logs after a week. The team is considering this step: Configure a governed audit sink and retention/alerting policies with access separation. What evidence should they review before proceeding?

  1. Inspect the number of SQL audit records currently available but omit the mandated retention period and deletion schedule.
  2. Check the audit sink's storage tier but not whether a privileged operator can erase its historical events.
  3. Check retention obligations, storage lifecycle rules and immutable storage support.
  4. Measure real-time audit ingestion latency without testing historical event retrieval or expiry handling.

Explanation

Correct Answer

C:

Check retention obligations, storage lifecycle rules and immutable storage support. Audit retention must align with legal, threat-detection and tamper-resistance requirements. Audit events remain queryable for the required duration after simulated cleanup.

Incorrect Answers

A:

A present SQL audit count cannot prove records survive the required years. Review retention obligations and actual storage lifecycle rules.

B:

An audit sink that can be silently deleted may fail tamper-resistance obligations. Inspect immutable retention and access separation.

D:

Fast ingestion does not establish long-term audit access. Verify queries against events older than the current cleanup interval and monitor expiry policy.

Question 141

A pricing procedure passes compile checks but calculates tax incorrectly at zero quantity and high precision. The team implemented this change: Add isolated tests for rounding, negative inputs and expected failures. Which result would best confirm the change worked?

  1. Confirm the new tests execute successfully without inserting a known defective zero-quantity calculation.
  2. Confirm the SQL pricing procedure's normal invoice total, but leave zero-quantity tax rounding, decimal overflow and failed-transaction rollback untested.
  3. Tests catch the incorrect calculation before merge and pass after correction.
  4. Measure how many SQL pricing unit-test assertions execute, without comparing expected high-precision tax amounts or cleaning failed fixtures.

Explanation

Correct Answer

C:

Tests catch the incorrect calculation before merge and pass after correction. SQL unit tests should exercise business invariants and boundary behavior beyond compilation. Unit tests cannot replace realistic integration and performance tests.

Incorrect Answers

A:

A green run without a negative control may mean the tests never exercise the original boundary defect.

B:

A normal invoice total may pass while the pricing procedure still miscalculates zero quantity, decimal precision or rollback-on-error behavior.

D:

Assertion count cannot prove accurate oracle values or recoverability after test errors.

Question 142

A database team wants near-real-time embeddings but proposes calling the external model directly inside every OLTP trigger. Which implementation step most directly addresses this requirement?

  1. Call the embedding model synchronously in the SQL row trigger before allowing the business transaction to commit.
  2. Use a minimal change trigger or outbox plus asynchronous embedding worker.
  3. Launch an embedding request from every changed row with no durable idempotency key or replay handling.
  4. Remove the embedding worker's queue checkpoint so each trigger delivery always starts from the latest text snapshot.

Explanation

Correct Answer

B:

Triggering a durable work queue is usually safer than blocking transactions on remote model latency. Use a minimal change trigger or outbox plus asynchronous embedding worker. Database writes remain fast and eventually yield current embedding rows.

Incorrect Answers

A:

Synchronous SQL trigger model calls hold transactions open during network latency. Queue a minimal change event for an async worker.

C:

Per-row model requests can duplicate after retry and lose updates. Use durable keys, retries and checkpointed processing.

D:

Discarding the queue checkpoint can skip or replay changes. A durable outbox/queue with versioned correlation is needed.

Question 143

A SQL-backed app needs REST access to a public product view but not underlying staff salary tables. The team implemented this change: Expose only the product view with minimal REST operations and roles. Which result would best confirm the change worked?

  1. Verify only that GET /products succeeds, without probing direct /salaries and update routes.
  2. Confirm that the product page shows no salary values without examining raw API responses.
  3. Allowed GET succeeds while prohibited updates and salary reads fail.
  4. Approve when authorized users can read products without checking that unauthenticated mutations fail.

Explanation

Correct Answer

C:

Allowed GET succeeds while prohibited updates and salary reads fail. Data API builder exposes declared entities and operations according to configuration and roles. Public endpoints still need rate controls and appropriate data classification.

Incorrect Answers

A:

DAB entities and SQL grants must be explicitly scoped; successful public GET does not prove private tables are hidden.

B:

Authorization and SQL projection must be server-side; client presentation cannot protect confidential fields.

D:

Explicit read actions and least-privilege backend roles are needed; successful GET must coexist with denied mutations.

Question 144

A SQL query becomes five times slower after an application release despite unchanged results. Initial testing shows: Median and tail latency recover over a controlled workload. Which limitation should the team still consider?

  1. Forced plans may become stale as schema and data change.
  2. A forced Query Store plan remains optimal for a report even after its SQL schema and input distribution change substantially.
  3. Selecting a historically fast Query Store plan prevents application-side errors and new parameter skew from affecting performance.
  4. Identifying one slow execution plan in Query Store proves the release is the sole root cause of all API timeouts.

Explanation

Correct Answer

A:

Forced plans may become stale as schema and data change. Query Store can compare historical plans and runtimes to identify regressions.

Incorrect Answers

B:

Forced plans can become stale as schema or cardinality changes. Monitor regressions and keep reversal criteria.

C:

Historical plan speed does not guarantee future query behavior under different parameters. Test representative skew.

D:

Query Store helps identify correlations, not unquestionable causation. Compare runtime, plan, traffic and dependency evidence.

Question 145

A retrieved supplier PDF contains instructions telling the AI assistant to email confidential SQL records to an attacker. The team proposes this approach: Separate retrieved quotes from tool policy and deny unsolicited outbound data actions. Which technical principle supports this approach?

  1. Text inside a retrieved supplier document has the same authority as a user-approved tool request.
  2. A prompt telling the assistant to ignore hostile instructions is sufficient to prevent exfiltration with unrestricted tools.
  3. If one benign PDF produced a helpful summary, the retrieval pipeline cannot be redirected by another document.
  4. Retrieved RAG content is untrusted task data and must not override authorized instructions.

Explanation

Correct Answer

D:

Retrieved RAG content is untrusted task data and must not override authorized instructions. Detection by prompt text alone cannot replace least-privilege tools.

Incorrect Answers

A:

Retrieved materials are untrusted task data; they cannot grant authority for new SQL or email actions.

B:

Prompt defenses are fallible; least-privilege database roles, approval gates and outbound restrictions also matter.

C:

Test adversarial retrieved text, tool-call logs and exfiltration attempts; benign functional tests are insufficient.

Question 146

A fast reporting query suddenly chooses a scan after data distribution shifts during a bulk load. The team proposes this approach: Refresh relevant stats or update query design after sampling tests. Which technical principle supports this approach?

  1. The SQL optimizer always prefers the newest query plan after a bulk load even when its statistics describe older row distributions.
  2. Stale or unrepresentative statistics can mislead the query optimizer.
  3. A table's B-tree index fragmentation is the sole cause of a newly chosen full scan after a highly skewed data import.
  4. Query Store captures historical runtimes by itself and therefore forces the cardinality estimator to use accurate current histograms.

Explanation

Correct Answer

B:

Stale or unrepresentative statistics can mislead the query optimizer. Overfrequent full-scan statistics can consume resources.

Incorrect Answers

A:

Optimizer plan selection depends on cardinality estimates; stale SQL statistics can still favor scans after distribution shifts.

C:

Fragmentation and cardinality estimation are separate factors. Bulk-load distribution shifts can make statistics stale and distort plan selection.

D:

Query Store evidence does not automatically refresh SQL histograms. Cardinality estimates require suitable current statistics.

Question 147

A human-resources query traverses manager relationships with potentially deep reporting chains. Which implementation step most directly addresses this requirement?

  1. Build a recursion-limited CTE with cycle protection and trusted root selection.
  2. Run recursive traversal without cycle safeguards or a reviewed maximum-depth policy.
  3. Start traversal from every employee and treat duplicate ancestry paths as unique results.
  4. Replace a scoped hierarchy query with unbounded recursive traversal of every employee in the company.

Explanation

Correct Answer

A:

A recursive CTE can walk parent-child hierarchies with anchor and recursive members. Build a recursion-limited CTE with cycle protection and trusted root selection. Sample chain traversal returns correct levels without infinite loops.

Incorrect Answers

B:

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

C:

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

D:

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

Question 148

A support database stores one JSON metadata blob per ticket and must filter by a scalar priority property. Initial testing shows: Priority filters match expected tickets including null/missing edge cases. Which limitation should the team still consider?

  1. JSON_VALUE always succeeds and returns a meaningful value for every JSON path.
  2. Text collation and type conversion cannot change scalar JSON filter semantics.
  3. JSON_VALUE is not intended to return an entire nested JSON object.
  4. Correct JSON extraction guarantees a selective access path on every SQL platform.

Explanation

Correct Answer

C:

JSON_VALUE is not intended to return an entire nested JSON object. JSON_VALUE extracts a scalar property from valid JSON for relational predicates.

Incorrect Answers

A:

JSON_VALUE extracts scalar data with null/path limits; validate missing and nested cases.

B:

Use explicit conversion and actual collation/format rules for scalar predicates.

D:

Correctness and indexing are different; choose supported computed/expression indexes after measurement. The genuine residual limitation is: JSON_VALUE is not intended to return an entire nested JSON object.

Question 149

A team wants to block insecure GRANT statements in database scripts before deployment. The team proposes this approach: Add a reviewed SQL policy gate and require exception approvals. Which technical principle supports this approach?

  1. Compiling a database project inherently rejects every risky permission statement.
  2. A branch named secure ensures that all embedded grants are least-privilege.
  3. An unrestricted GRANT EXECUTE on privileged procedures cannot enlarge data access.
  4. Static analysis and policy-as-code can scan SQL project changes for risky permission patterns.

Explanation

Correct Answer

D:

Static analysis and policy-as-code can scan SQL project changes for risky permission patterns. Static rules may miss dynamically constructed SQL executed at runtime.

Incorrect Answers

A:

Compilation alone does not enforce permission policy; explicit static rules and runtime controls are needed for broader grants.

B:

Branch names and unconditional exceptions do not prove least-privilege; tests must show prohibited grants are blocked with traceable review.

C:

Privileges can be conveyed through procedures and other objects; policy coverage must include relevant GRANT syntax and negative fixtures.

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.

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