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 3

Question 20

A payroll SQL table contains salaries that database administrators should not read in plaintext. The team proposes this approach: Encrypt payroll columns using managed client-side keys and test authorized application access. Which technical principle supports this approach?

  1. Transparent Data Encryption hides salary plaintext from an administrator issuing SELECT on the running database.
  2. Dynamic Data Masking cryptographically hides payroll amounts from all database administrators.
  3. Always Encrypted can keep supported sensitive column values encrypted at the client and protected from server-side plaintext access.
  4. Moving column keys to a vault automatically prevents a DBA from seeing previously stored plaintext values.

Explanation

Correct Answer

C:

Always Encrypted can keep supported sensitive column values encrypted at the client and protected from server-side plaintext access. Unsupported operations and key rotation require careful application design.

Incorrect Answers

A:

TDE protects files at rest, not SELECT plaintext for DBAs; client-side column encryption needs supported drivers and operation testing.

B:

Presentation masks are not encryption; the stated threat model requires DBA-inaccessible plaintext via client-held keys.

D:

Keys must be used by a supported encrypted-column design; vault inventory alone does not protect data or remove operational limits.

Question 21

A checkout service must insert payment authorization and order records atomically. Initial testing shows: Fault-injection leaves no partially committed paid order. Which limitation should the team still consider?

  1. An external payment provider always commits and rolls back with the local SQL database transaction.
  2. TRY/CATCH automatically turns every network retry into an exactly-once external charge.
  3. External payment calls are not automatically part of the SQL transaction.
  4. A transaction can never remain uncommittable after an SQL execution error.

Explanation

Correct Answer

C:

External payment calls are not automatically part of the SQL transaction. A stored procedure can coordinate a transaction with appropriate TRY/CATCH and rollback logic.

Incorrect Answers

A:

Local atomicity needs explicit transactional error handling; an external payment API is outside that transaction without a separate design.

B:

A committed SQL transaction and an external charge are separate; idempotent request handling and compensation are necessary.

D:

A caught error can leave an active or doomed transaction; explicit rollback, rethrow and negative tests prevent misleading success.

Question 22

An observability store retains years of records and regularly archives complete historical months. The team proposes this approach: Design monthly RANGE partition boundaries with aligned indexes and governed switch tests. Which technical principle supports this approach?

  1. Any index on an event date enables metadata-only partition switching without aligned indexes.
  2. A month boundary at the wrong timestamp cannot place rows into an unintended partition.
  3. A compressed table automatically supports switching partitions across incompatible table and index schemas.
  4. Partitioning by a compatible date key can isolate maintenance and permit eligible partition switching.

Explanation

Correct Answer

D:

Partitioning by a compatible date key can isolate maintenance and permit eligible partition switching. Poorly designed partition keys do not necessarily improve queries.

Incorrect Answers

A:

Partition switching depends on compatible boundaries and indexes; ordinary deletes cost row-by-row work and query benefits need predicate alignment.

B:

Boundary semantics define which historical rows are switched or pruned; date-edge fixtures are essential.

C:

Compression does not establish partition-switch eligibility; metadata and target compatibility must be validated before archived months move.

Question 23

A SQL change-event stream feeds a downstream operations service that requires consistent handling of event order and replay. The team is considering this step: Build an ordered-per-key replay-aware consumer with documented deduplication. What evidence should they review before proceeding?

  1. Inspect only total event volume without recording partition keys, event IDs and replay checkpoint position.
  2. Read only the schema of one event and ignore consumer offsets, retention and duplicate delivery behavior.
  3. Inspect only event timestamps and ignore event identity, source key and transaction ordering guarantees.
  4. Examine stream partitioning, event IDs, retention and delivery guarantees.

Explanation

Correct Answer

D:

Examine stream partitioning, event IDs, retention and delivery guarantees. Change event streaming needs consumer checkpoints, ordering assumptions and idempotent processing. Consumer reconstructs tested state after replay without duplicate business effects.

Incorrect Answers

A:

Streams can redeliver and reorder across partitions; per-key sequencing, checkpoints and idempotent effects need explicit tests.

B:

The checkpoint/side-effect failure window must be designed for at-least-once delivery and idempotent replay.

C:

Producer clocks and transport arrival may not define business order; stable entity keys and replay tests are needed.

Question 24

A multi-tenant CRM permits the same email in different tenants but must disallow a duplicate email within one tenant. The team proposes this approach: Add an appropriate composite uniqueness rule after cleaning conflicting rows. Which technical principle supports this approach?

  1. A UNIQUE constraint on Email alone permits a repeated address in a different tenant.
  2. A composite UNIQUE constraint on tenant identifier and normalized email enforces the intended scope.
  3. A UNIQUE constraint on TenantId alone enforces unique email addresses per tenant while allowing multiple users.
  4. Comparing raw email strings always treats casing and equivalent normalized forms consistently in every collation.

Explanation

Correct Answer

B:

A composite UNIQUE constraint on tenant identifier and normalized email enforces the intended scope. Case/collation rules affect which email strings compare equal.

Incorrect Answers

A:

Uniqueness must be scoped to tenant plus normalized email; a global key blocks valid separate tenants, and collation changes comparisons.

C:

A tenant identifier alone does not express per-tenant email uniqueness, and existing conflicts must be reconciled before enforcement.

D:

Business uniqueness depends on normalized values, key collation and NULL treatment; superficial format validation does not suffice.

Question 25

A checkout service must insert payment authorization and order records atomically. The team implemented this change: Implement a transactional stored procedure with explicit failure handling and tests. Which result would best confirm the change worked?

  1. Accept a checkout that succeeds normally without fault-injecting the second insert and checking both tables.
  2. Verify one transaction commits while omitting concurrent duplicate attempts and repeated requests.
  3. Accept HTTP 200 from the stored procedure wrapper while leaving failed partial writes unexamined.
  4. Fault-injection leaves no partially committed paid order.

Explanation

Correct Answer

D:

Fault-injection leaves no partially committed paid order. A stored procedure can coordinate a transaction with appropriate TRY/CATCH and rollback logic. External payment calls are not automatically part of the SQL transaction.

Incorrect Answers

A:

Local atomicity needs explicit transactional error handling; an external payment API is outside that transaction without a separate design.

B:

A committed SQL transaction and an external charge are separate; idempotent request handling and compensation are necessary.

C:

A caught error can leave an active or doomed transaction; explicit rollback, rethrow and negative tests prevent misleading success.

Question 26

A managed application uses a hardcoded SQL username and password that rotate unreliably. Initial testing shows: App connects without stored SQL secrets and unauthorized identities fail. Which limitation should the team still consider?

  1. Managed identity prevents overprivileged permissions automatically without database role review.
  2. A managed identity is immune to access oversharing and never needs role revocation.
  3. Managed identity permissions must still be audited and revoked when no longer needed.
  4. Removing a stored password guarantees that database permissions are least-privilege.

Explanation

Correct Answer

C:

Managed identity permissions must still be audited and revoked when no longer needed. Microsoft Entra authentication and managed identity can eliminate embedded credentials for supported Azure SQL clients. Configure managed identity token-based connection with narrowly scoped database roles.

Incorrect Answers

A:

Passwordless identity requires a supported token flow and narrowly mapped SQL principal; rotation is not elimination of embedded secrets.

B:

Identity provisioning alone does not switch the SQL client to token authentication or constrain its permissions.

D:

Token audience, identity mapping and object grants are distinct controls; passwordless connectivity must not expand privileges.

Question 27

A stored procedure invokes a model endpoint during a transaction and holds locks while the model takes 30 seconds. Initial testing shows: OLTP locks remain short during model latency spikes. Which limitation should the team still consider?

  1. The remote model can never affect OLTP latency because its inference runs outside the database process.
  2. Asynchronous design requires eventual consistency and retry handling.
  3. The main remaining risk is choosing an inaccurate classifier, not holding write locks during network latency.
  4. A model retry performed inside a transaction cannot add blocking or duplicate side effects.

Explanation

Correct Answer

B:

Asynchronous design requires eventual consistency and retry handling. External model calls should be isolated from long-lived SQL transactions where possible. Move model calls outside critical write transactions or into controlled asynchronous worker.

Incorrect Answers

A:

An open SQL transaction can retain locks while waiting for the remote endpoint; a successful HTTP response does not measure blocking.

C:

Model quality is separate from transaction lifetime; accuracy metrics cannot establish that OLTP locks are released promptly.

D:

A waiting transaction can block other sessions without high CPU, and synchronous retries may extend the lock-held interval.

Question 28

An aggregation query spills a large sort to tempdb and saturates IO during reporting. The team is considering this step: Optimize filtering, statistics, indexes or memory grant strategy after measurement. What evidence should they review before proceeding?

  1. Read only query duration without checking actual execution-plan spill warnings, estimates or memory grants.
  2. Inspect only cached-plan compile time and omit actual runtime row counts and spill events.
  3. Inspect actual plan spill warnings, grants, row estimates and tempdb throughput.
  4. Examine the reporting query text but omit missing indexes and observed filter selectivity.

Explanation

Correct Answer

C:

Inspect actual plan spill warnings, grants, row estimates and tempdb throughput. Inadequate memory grants or missing access paths can cause expensive sort/hash spills.

Incorrect Answers

A:

Tempdb capacity is not a substitute for correcting estimates, access paths and memory grants; result accuracy needs separate testing.

B:

Runtime cardinality and memory grants can differ from estimates; indiscriminately higher grants may harm concurrent workloads.

D:

Nonsargable predicates can increase scans and spill work; measured execution plans and representative data are required.

Question 29

An inventory application occasionally inserts negative stock reservations after a faulty API deployment. The team is considering this step: Add a reviewed nonnegative reservation constraint with migration and testing. What evidence should they review before proceeding?

  1. Inspect only the API form-validation message and omit the database column values and CHECK definition.
  2. Inspect only that ReserveQty is numeric without reviewing valid negative adjustments and existing rows.
  3. Locate the offending table, existing CHECK rules and signed quantity business requirements.
  4. Review only the exception text from the faulty API deployment and ignore database enforcement across writers.

Explanation

Correct Answer

C:

Locate the offending table, existing CHECK rules and signed quantity business requirements. A CHECK constraint can reject invalid row values regardless of the calling application. Invalid negative inserts fail while valid reservations complete.

Incorrect Answers

A:

The database must reject invalid row values regardless of caller; cross-row inventory invariants need additional concurrency controls.

B:

Constraints need business-approved predicates and tested migration/backfill; row values and trust state matter.

D:

Detection after the fact is not rejection; an enabled CHECK constraint prevents invalid stored reservations.

Question 30

A customer service team needs to match transposed-character surnames more reliably than exact equality. The team implemented this change: Rank candidate names by a supported similarity function and validate manually. Which result would best confirm the change worked?

  1. Confirm that identical spellings match while ignoring swapped-letter examples and false positives.
  2. Test pairs with transpositions appear while false matches remain manageable.
  3. Accept that known typo pairs rank highly without checking unrelated names also crossing the threshold.
  4. Confirm that phonetically similar names group together without evaluating swapped-letter precision.

Explanation

Correct Answer

B:

Test pairs with transpositions appear while false matches remain manageable. JARO_WINKLER_DISTANCE or similarity functions score character similarity in supported environments. Fuzzy name scores are not suitable as unique identity keys.

Incorrect Answers

A:

Exact equality misses typographical transpositions; similarity ranking proposes candidates, not identity proof.

C:

Fuzzy scores are context-sensitive and probabilistic; threshold calibration and human/identifier validation prevent wrong merges.

D:

Phonetic grouping and name similarity produce candidates only, with possible collisions and language-dependent behavior.

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