Microsoft DP-800 (Developing AI-Enabled Database Solutions) Practice Test 22

Question 210

A supported solution needs permitted comparisons or transformations that ordinary client-side encrypted columns cannot handle efficiently. What is the best primary mechanism to use?

  1. Always Encrypted with secure enclaves
  2. Randomized Always Encrypted
  3. TDE key hierarchy
  4. Deterministic Always Encrypted

Explanation

Correct Answer

A:

Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites.

Incorrect Answers

B:

Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support; that function is not interchangeable with Always Encrypted with secure enclaves for the requested result.

C:

TDE protects the database encryption key through configured key hierarchy, so key backup and availability matter for restored data; for the stated conditions, the deciding feature is Always Encrypted with secure enclaves rather than this behavior.

D:

Deterministic column encryption yields the same ciphertext for equal plaintext with the same key and supports restricted equality comparisons; it addresses a different step; the question calls for Always Encrypted with secure enclaves.

Question 211

A developer is redesigning a stored procedure and should isolate unreviewed schema changes from the protected main branch. Which mechanism addresses the requirement?

  1. Merge conflict resolution
  2. Pull request
  3. Feature branch
  4. Protected main branch

Explanation

Correct Answer

C:

A feature branch provides a separate line of changes for review and integration without directly changing the release branch.

Incorrect Answers

A:

Resolving the conflicting source edits produces one coherent schema definition before a merged database project can be compiled; the required capability here is Feature branch, which this operation does not supply.

B:

A pull request provides a controlled review and merge workflow for database-source changes; the alternative cannot substitute for Feature branch under the given constraints.

D:

Branch protection or rulesets impose merge policies, required checks, and review conditions in the repository; the missing behavior is Feature branch; this mechanism does something else.

Question 212

For one SQL query whose latency increased, a DBA needs historical categories of waits attributed to that query, not an audit record of who signed in. Which capability exposes the needed wait breakdown?

  1. SQL auditing
  2. Log Analytics workspace
  3. Azure Monitor alerts
  4. Query Store wait statistics

Explanation

Correct Answer

D:

Query Store can retain per-query wait statistics, helping distinguish locking, I/O, and other wait categories for the affected query in supported configurations.

Incorrect Answers

A:

SQL auditing records configured security and data-access events; it does not organize historical wait categories per Query Store statement.

B:

A Log Analytics workspace receives selected logs and telemetry; this does not by itself substitute for SQL Query Store wait attribution at the query level.

C:

Azure Monitor alert rules detect thresholds or signals but do not provide the stored Query Store per-statement wait breakdown requested here.

Question 213

A pull request changes an indexed view; it must not merge unless SQL project compilation and tests pass. What should be configured to meet this objective?

  1. Required status checks
  2. Pull-request approval rule
  3. Restricted workflow permissions
  4. CODEOWNERS review

Explanation

Correct Answer

A:

Required status checks make successful automated validation a condition for merging into a protected branch.

Incorrect Answers

B:

Review requirements enforce separation of duties and recorded authorization before merge; the missing behavior is Required status checks; this mechanism does something else.

C:

Limiting workflow token permissions reduces the impact of compromised repository content and avoids unnecessary deployment authority; that function is not interchangeable with Required status checks for the requested result.

D:

CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules; for the stated conditions, the deciding feature is Required status checks rather than this behavior.

Question 214

A legacy deployment integration still requires a credential, but only approved production jobs should be able to read it. What should the developer implement?

  1. Credential rotation
  2. Database contained user grant
  3. Protected environment secret
  4. Federated GitHub identity

Explanation

Correct Answer

C:

Environment-scoped secret controls restrict availability to authorized workflows, although long-lived passwords should be minimized.

Incorrect Answers

A:

Revoking and rotating compromised credentials eliminates continued use of exposed secrets, unlike merely deleting a log message; this differs from the Protected environment secret requirement described in the scenario.

B:

Authentication alone does not grant SQL object privileges, so a contained user or equivalent principal must receive deployment rights; the required capability here is Protected environment secret, which this operation does not supply.

D:

Federated workload credentials exchange trusted action identity assertions for Azure access, requiring explicit trust configuration; the condition in the stem is resolved by Protected environment secret, not by this feature alone.

Question 215

Read-heavy reports are slow and storage latency counters rise, suggesting data access waits rather than merely bad SQL syntax. What is the most appropriate design decision?

  1. Wait-type analysis
  2. CPU-bound query analysis
  3. Memory grant spill review
  4. I/O latency monitoring

Explanation

Correct Answer

D:

I/O metrics and file latency telemetry distinguish storage pressure and physical read cost from other query delays.

Incorrect Answers

A:

Categorizing waits helps identify the contended resource, but wait evidence should be correlated with relevant sessions and plans; that function is not interchangeable with I/O latency monitoring for the requested result.

B:

CPU usage and operator analysis reveal expensive computations or inefficient row processing independently of storage wait times; for the stated conditions, the deciding feature is I/O latency monitoring rather than this behavior.

C:

Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb; it addresses a different step; the question calls for I/O latency monitoring.

Question 216

A vector result must link back to its authorized source row and citation metadata without relying on reconstructing identity from embedding numbers. Which choice best meets these constraints?

  1. Title plus body
  2. Normalize noisy boilerplate
  3. Exclude secret columns
  4. Preserve source identifiers separately

Explanation

Correct Answer

D:

Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval.

Incorrect Answers

A:

Including the title and relevant body in a controlled embedding input gives the representation both subject identity and contextual detail; the alternative cannot substitute for Preserve source identifiers separately under the given constraints.

B:

Removing repeated boilerplate from embedding inputs can increase the signal from content that differentiates documents; the condition in the stem is resolved by Preserve source identifiers separately, not by this feature alone.

C:

Excluding secret or irrelevant columns reduces data exposure and prevents confidential values from affecting retrieval features; the missing behavior is Preserve source identifiers separately; this mechanism does something else.

Question 217

An integration service wants a newly generated uniqueidentifier value to use as a correlation identifier across services. Which action is justified by these conditions?

  1. SEQUENCE object
  2. ROWVERSION column
  3. IDENTITY property
  4. NEWID()

Explanation

Correct Answer

D:

NEWID produces a GUID-shaped uniqueidentifier; it is not a monotonically increasing table identity.

Incorrect Answers

A:

A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR; it addresses a different step; the question calls for NEWID().

B:

ROWVERSION is an automatically changing binary token for row modifications, useful in equality comparisons for optimistic concurrency and not a clock timestamp; this differs from the NEWID() requirement described in the scenario.

C:

IDENTITY automatically produces values for an inserted column, although applications must not assume gap-free numbering or transactional sequences; the required capability here is NEWID(), which this operation does not supply.

Question 218

A service must set the JSON property $.shipping.delivered to true inside a stored JSON value while retaining other properties. What is the most suitable implementation?

  1. OPENJSON WITH
  2. JSON_MODIFY
  3. JSON_PATH_EXISTS
  4. JSON_CONTAINS

Explanation

Correct Answer

B:

JSON_MODIFY updates content at a specified JSON path and returns the modified document expression.

Incorrect Answers

A:

An OPENJSON WITH clause maps JSON paths to named typed relational columns during rowset extraction; the missing behavior is JSON_MODIFY; this mechanism does something else.

C:

JSON_PATH_EXISTS checks whether an SQL/JSON path exists, avoiding the need to return the path's value; for the stated conditions, the deciding feature is JSON_MODIFY rather than this behavior.

D:

JSON_CONTAINS evaluates containment semantics for JSON values rather than merely extracting a scalar for presentation; it addresses a different step; the question calls for JSON_MODIFY.

Question 219

A chatbot frequently retrieves loosely related passages; the team needs the percentage of its first five hits that are actually relevant. What is the most appropriate design decision?

  1. Embedding drift check
  2. Precision at k
  3. Recall at k
  4. Latency percentile

Explanation

Correct Answer

B:

Precision@k measures relevance within the top-k returned results rather than the total pool's coverage.

Incorrect Answers

A:

Periodic evaluation on recent representative queries can detect semantic quality drift and trigger reindexing or model comparison; the required capability here is Precision at k, which this operation does not supply.

C:

Recall@k measures the fraction of relevant items retrieved in a top-k result and reveals missed evidence; the condition in the stem is resolved by Precision at k, not by this feature alone.

D:

Latency percentiles expose tail-response behavior that mean timings can conceal in production; the missing behavior is Precision at k; this mechanism does something else.

Question 220

A module must perform controlled work using a designated execution principal instead of the caller's full original context. What is the most appropriate design decision?

  1. Ownership chaining
  2. EXECUTE AS execution context
  3. Module signing
  4. EXECUTE permission
  5. INSTEAD OF DELETE trigger

Explanation

Correct Answer

B:

EXECUTE AS switches a supported module's permission-checking context, and should be used with explicit least-privilege considerations.

Incorrect Answers

A:

Ownership chaining can allow access through a module when ownership and permission conditions are satisfied; cross-owner paths are different; for the stated conditions, the deciding feature is EXECUTE AS execution context rather than this behavior.

C:

Module signing can associate certificate-based permissions with module execution, avoiding blanket grants to all application principals; this differs from the EXECUTE AS execution context requirement described in the scenario.

D:

Granting EXECUTE on the procedure permits calling that module without granting unrestricted base-table DML, subject to ownership and execution context; the required capability here is EXECUTE AS execution context, which this operation does not supply.

E:

An INSTEAD OF DELETE trigger can replace a requested deletion with custom SQL logic, including soft-delete behavior; the alternative cannot substitute for EXECUTE AS execution context under the given constraints.

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