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 50

Question 490

A rolling retention process must remove the oldest table partition with minimal data movement. Which implementation decision BEST addresses this requirement?

  1. Manage the partition function with properly planned SPLIT RANGE boundaries.
  2. Use compatible staging tables and ALTER TABLE SWITCH after checking alignment and constraints.
  3. Review SEQUENCE data type, MAXVALUE and cycle policy before changing generation.
  4. Replace FLOAT money arithmetic with appropriately sized DECIMAL precision and scale.

Explanation

Correct Answer

B:

Use compatible staging tables and ALTER TABLE SWITCH after checking alignment and constraints. This targets the primary issue because metadata-based partition switching requires strict schema and partition alignment. Verification: A tested switch moves the intended range without orphaning rows. Prevent recurrence: Use transactional change control and rehearse rollback.

Incorrect Answers

A:

Missing future boundaries force incoming values into the existing terminal partition.

C:

Finite ranges exhaust and enabling CYCLE without safety checks may repeat identifiers.

D:

Binary floating-point approximations cannot represent every decimal currency value exactly.

Question 491

An analyst wants the latest event per sensor without deleting older events. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. Repeated execution assigns stable row numbers to equal-time events.
  2. Valid test codes match and invalid or truncated codes do not.
  3. The query returns one expected latest event for each sensor.
  4. Rows matching the expected category are returned in a controlled test.

Explanation

Correct Answer

C:

The query returns one expected latest event for each sensor. This is the decisive observable evidence after implementing use ROW_NUMBER() OVER(PARTITION BY SensorId ORDER BY EventTime DESC, EventId DESC) and filter rn=1. It tests the underlying requirement: Ranking functions identify one top row per group with an explicit order.

Incorrect Answers

A:

Repeated execution assigns stable row numbers to equal-time events. However, that result would not establish the required outcome: the query returns one expected latest event for each sensor.

B:

Valid test codes match and invalid or truncated codes do not. However, that result would not establish the required outcome: the query returns one expected latest event for each sensor.

D:

Rows matching the expected category are returned in a controlled test. However, that result would not establish the required outcome: the query returns one expected latest event for each sensor.

Question 492

A batch transaction updates thousands of rows in one lock-intensive statement with escalating contention. Which technical reason BEST explains the proposed correction?

  1. Opposite lock acquisition sequences can form a cyclic wait.
  2. Uncontrolled parallelism can increase coordination overhead for short queries.
  3. Database-level settings can affect many workloads and engine versions differently.
  4. Large transactions increase lock duration, log pressure and potential escalation.

Explanation

Correct Answer

D:

Large transactions increase lock duration, log pressure and potential escalation. This explains why the corrective action is to process safe bounded batches or adjust indexes and isolation as measured. Confirm the diagnosis when batch tests preserve atomic business requirements with lower p95 waits.

Incorrect Answers

A:

Opposite lock acquisition sequences can form a cyclic wait. The actual technical constraint is that large transactions increase lock duration, log pressure and potential escalation.

B:

Uncontrolled parallelism can increase coordination overhead for short queries. The actual technical constraint is that large transactions increase lock duration, log pressure and potential escalation.

C:

Database-level settings can affect many workloads and engine versions differently. The actual technical constraint is that large transactions increase lock duration, log pressure and potential escalation.

Question 493

A SQL application must summarize customer orders but presently concatenates column values into ambiguous text. What maintenance or governance control BEST reduces recurrence of this problem?

  1. Exclude sensitive data and validate schema before external transmission.
  2. Enforce outbound allowlists and unit tests for fields.
  3. Restrict execute permission and log outbound destination.
  4. Version and validate provider schemas.

Explanation

Correct Answer

A:

Exclude sensitive data and validate schema before external transmission. This guards against recurrence because structured JSON preserves field boundaries and escaping for downstream model prompts. The direct remediation is: Serialize selected relational fields with FOR JSON or appropriate JSON_OBJECT support.

Incorrect Answers

B:

Enforce outbound allowlists and unit tests for fields. The current task instead requires this action: exclude sensitive data and validate schema before external transmission.

C:

Restrict execute permission and log outbound destination. The current task instead requires this action: exclude sensitive data and validate schema before external transmission.

D:

Version and validate provider schemas. The relevant technical condition instead is that exclude sensitive data and validate schema before external transmission.

Question 494

An application enables SERIALIZABLE for every read and sees growing lock contention. Which implementation decision BEST addresses this requirement?

  1. Require an expected rowversion predicate and verify rows affected before confirming success.
  2. Choose the lowest isolation that meets correctness and benchmark concurrency behavior.
  3. Evaluate tempdb file sizing and allocation configuration appropriate to SQL engine/version.
  4. Standardize resource update order and review deadlock graph evidence.

Explanation

Correct Answer

B:

Choose the lowest isolation that meets correctness and benchmark concurrency behavior. This targets the primary issue because serializable range protection can reduce throughput and increase blocking. Verification: Correctness tests pass without excessive lock waits under the selected isolation. Prevent recurrence: Document isolation assumptions in critical transaction code.

Incorrect Answers

A:

Optimistic concurrency only works if conflicts are detected, not silently overwritten.

C:

Tempdb contention can impede transient sorting, hashing and version-store activity.

D:

Standardize resource update order and review deadlock graph evidence. The relevant technical condition instead is that choose the lowest isolation that meets correctness and benchmark concurrency behavior.

Question 495

A team chooses an ANN index solely by query latency without checking write costs. Which measurable outcome BEST verifies that the requirement is satisfied?

  1. Recall improves on a holdout set while latency remains acceptable.
  2. A controlled CONTAINS query succeeds after population completes.
  3. A representative mixed read/write load meets agreed SLOs.
  4. Known phrase and inflection tests return expected articles.

Explanation

Correct Answer

C:

A representative mixed read/write load meets agreed SLOs. This is the decisive observable evidence after implementing compare index build time, insert cost, storage and recall alongside query latency. It tests the underlying requirement: Index choice affects ingestion and maintenance as well as retrieval speed.

Incorrect Answers

A:

Recall improves on a holdout set while latency remains acceptable. However, that result would not establish the required outcome: a representative mixed read/write load meets agreed SLOs.

B:

A controlled CONTAINS query succeeds after population completes. However, that result would not establish the required outcome: a representative mixed read/write load meets agreed SLOs.

D:

Known phrase and inflection tests return expected articles. However, that result would not establish the required outcome: a representative mixed read/write load meets agreed SLOs.

Question 496

An inventory query compares each item's price to its category's average price. Which technical reason BEST explains the proposed correction?

  1. NULL in a NOT IN subquery can yield UNKNOWN and unexpectedly filter all rows.
  2. The graph pattern engine expects node/edge structures, not arbitrary string lists.
  3. Transient retries can replay committed work even after client-side timeouts.
  4. An outer reference lets the inner aggregate vary by each row's category.

Explanation

Correct Answer

D:

An outer reference lets the inner aggregate vary by each row's category. This explains why the corrective action is to use a correlated aggregate subquery or window function scoped by category. Confirm the diagnosis when an item is flagged only when above its own category average.

Incorrect Answers

A:

NULL in a NOT IN subquery can yield UNKNOWN and unexpectedly filter all rows. The actual technical constraint is that an outer reference lets the inner aggregate vary by each row's category.

B:

The graph pattern engine expects node/edge structures, not arbitrary string lists. The actual technical constraint is that an outer reference lets the inner aggregate vary by each row's category.

C:

Transient retries can replay committed work even after client-side timeouts. The actual technical constraint is that an outer reference lets the inner aggregate vary by each row's category.

Question 497

A read-only reporting view must be exposed to partners without direct table edit access. Which preventive practice BEST addresses the underlying risk over time?

  1. Review view changes and role grants together.
  2. Validate input parameters and avoid excessive execution privileges.
  3. Monitor connection failures and restrict ingress.
  4. Use telemetry and abuse alerting across API and SQL.

Explanation

Correct Answer

A:

Review view changes and role grants together. This guards against recurrence because mapping a view can create a governed projection with narrower permissions than base tables. The direct remediation is: Map the database view as a DAB entity and restrict permitted operations.

Incorrect Answers

B:

Validate input parameters and avoid excessive execution privileges. The current task instead requires this action: review view changes and role grants together.

C:

Monitor connection failures and restrict ingress. The current task instead requires this action: review view changes and role grants together.

D:

Use telemetry and abuse alerting across API and SQL. The current task instead requires this action: review view changes and role grants together.

Question 498

A developer assumes SQL graph MATCH automatically finds shortest paths in every query. What should the SQL developer do FIRST to address the specific issue?

  1. Normalize relationships into graph edge tables before using MATCH.
  2. Choose supported shortest-path syntax when needed rather than ordinary MATCH alone.
  3. Use REGEXP_LIKE with an anchored validated pattern where supported.
  4. Use fuzzy scoring only for candidate generation and validate matches with stable IDs.

Explanation

Correct Answer

B:

Choose supported shortest-path syntax when needed rather than ordinary MATCH alone. This targets the primary issue because a path existence predicate does not necessarily compute minimal hop count. Verification: A sample network returns the known minimal path when explicitly requested. Prevent recurrence: Guard against cyclic paths and unsupported graph features.

Incorrect Answers

A:

The graph pattern engine expects node/edge structures, not arbitrary string lists.

C:

A regex predicate evaluates pattern matching rather than fixed substring equality.

D:

String resemblance cannot guarantee two records represent the same person.

Question 499

A SQL Server catalog needs inflection-aware keyword search in support articles. Which acceptance check is MOST directly related to this scenario?

  1. The quoted phrase test excludes documents without that phrase.
  2. Recall improves on a holdout set while latency remains acceptable.
  3. Known phrase and inflection tests return expected articles.
  4. The corrected query executes and returns expected similar records.

Explanation

Correct Answer

C:

Known phrase and inflection tests return expected articles. This is the decisive observable evidence after implementing configure a full-text catalog/index and query with CONTAINS or FREETEXT as appropriate. It tests the underlying requirement: Full-text indexes support linguistic tokenization and retrieval beyond simple LIKE scanning.

Incorrect Answers

A:

The quoted phrase test excludes documents without that phrase. However, that result would not establish the required outcome: known phrase and inflection tests return expected articles.

B:

Recall improves on a holdout set while latency remains acceptable. However, that result would not establish the required outcome: known phrase and inflection tests return expected articles.

D:

The corrected query executes and returns expected similar records. However, that result would not establish the required outcome: known phrase and inflection tests return expected articles.

Question 500

A compliance team needs cryptographically verifiable evidence that ledger entries were not silently altered. Which technical property or constraint MOST directly matters here?

  1. Temporal history captures prior row versions when system versioning is enabled.
  2. A missing selective access path forces large table or clustered-index scans.
  3. Column-oriented compression and batch processing can accelerate broad aggregations.
  4. Ledger tamper evidence supports verification; it does not prevent every privileged misuse.

Explanation

Correct Answer

D:

Ledger tamper evidence supports verification; it does not prevent every privileged misuse. This explains why the corrective action is to assess SQL ledger tables with digest storage and verification controls. Confirm the diagnosis when verification detects a controlled tampering attempt against the ledger history.

Incorrect Answers

A:

Temporal history captures prior row versions when system versioning is enabled. The actual technical constraint is that ledger tamper evidence supports verification; it does not prevent every privileged misuse.

B:

A missing selective access path forces large table or clustered-index scans. The actual technical constraint is that ledger tamper evidence supports verification; it does not prevent every privileged misuse.

C:

Column-oriented compression and batch processing can accelerate broad aggregations. The actual technical constraint is that ledger tamper evidence supports verification; it does not prevent every privileged misuse.

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