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

Question 10

Hundreds of documents share identical legal footers that dominate the text and weaken semantic discrimination. Which feature would solve the stated problem?

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

Explanation

Correct Answer

B:

Removing repeated boilerplate from embedding inputs can increase the signal from content that differentiates documents.

Incorrect Answers

A:

Excluding secret or irrelevant columns reduces data exposure and prevents confidential values from affecting retrieval features; the missing behavior is Normalize noisy boilerplate; this mechanism does something else.

C:

Including the title and relevant body in a controlled embedding input gives the representation both subject identity and contextual detail; for the stated conditions, the deciding feature is Normalize noisy boilerplate rather than this behavior.

D:

Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval; it addresses a different step; the question calls for Normalize noisy boilerplate.

Question 11

A developer can query tables but cannot invoke any outbound REST endpoint because the SQL database denies that capability. What is the right solution for this specific requirement?

  1. sp_invoke_external_rest_endpoint
  2. Managed identity authorization
  3. DATABASE SCOPED CREDENTIAL
  4. External-endpoint permission

Explanation

Correct Answer

D:

EXECUTE ANY EXTERNAL ENDPOINT is the relevant database permission for calling the supported external REST procedure.

Incorrect Answers

A:

sp_invoke_external_rest_endpoint invokes HTTPS endpoints with configured payload, headers, timeout, and credentials where supported; the required capability here is External-endpoint permission, which this operation does not supply.

B:

Managed identity authentication can authorize a supported outbound model call when the identity has the required destination role; the alternative cannot substitute for External-endpoint permission under the given constraints.

C:

Database-scoped credentials support controlled outbound endpoint authentication, subject to URL, identity, and access rules; the condition in the stem is resolved by External-endpoint permission, not by this feature alone.

Question 12

A relationship query must find customer nodes linked to product nodes through purchased edges inside SQL graph tables. Which SQL capability is the most direct fit?

  1. Ledger verification
  2. Graph MATCH predicate
  3. External data access
  4. FOR SYSTEM_TIME AS OF

Explanation

Correct Answer

B:

MATCH expresses a graph traversal pattern over node and edge tables rather than a plain scalar comparison.

Incorrect Answers

A:

Ledger verification uses generated digests and recorded history to detect tampering; it is not simply a time-travel query; for the stated conditions, the deciding feature is Graph MATCH predicate rather than this behavior.

C:

An external table exposes remote data through metadata and suitable connectivity without copying it into a regular local table first; this differs from the Graph MATCH predicate requirement described in the scenario.

D:

FOR SYSTEM_TIME AS OF retrieves the temporal version valid at a requested instant using the system-maintained period columns; the required capability here is Graph MATCH predicate, which this operation does not supply.

Question 13

A successful publish command is not enough; the team must confirm core queries, API operations, and expected objects still function. Which approach should the team select?

  1. Dry-run deployment script
  2. Post-release verification
  3. Environment approval gate
  4. Build once, deploy same artifact

Explanation

Correct Answer

B:

Post-deployment verification tests operational outcomes after schema changes, not merely tool exit codes.

Incorrect Answers

A:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; the condition in the stem is resolved by Post-release verification, not by this feature alone.

C:

Approval gates block promotion to sensitive environments until designated reviewers approve the deployment job; that function is not interchangeable with Post-release verification for the requested result.

D:

Promoting one immutable DACPAC across environments preserves a traceable model artifact while target comparisons remain environment-specific; for the stated conditions, the deciding feature is Post-release verification rather than this behavior.

Question 14

A billing database must retain exact four-decimal currency amounts and cannot tolerate floating-point rounding changes during reconciliation. Which approach should the team select?

  1. DATETIME2
  2. DECIMAL(19,4)
  3. VARBINARY(MAX)
  4. NVARCHAR(200)

Explanation

Correct Answer

B:

Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale.

Incorrect Answers

A:

DATETIME2 represents date and time together with higher supported precision and range than legacy DATETIME; this differs from the DECIMAL(19,4) requirement described in the scenario.

C:

VARBINARY(MAX) holds variable-length binary data; unlike character types, it does not interpret the bytes using a text encoding; the alternative cannot substitute for DECIMAL(19,4) under the given constraints.

D:

NVARCHAR stores variable-length Unicode character content and is appropriate when multilingual text must be preserved; the condition in the stem is resolved by DECIMAL(19,4), not by this feature alone.

Question 15

A matching service needs a normalized similarity score derived from edit distance to compare names of different lengths. Which SQL capability is the most direct fit?

  1. EDIT_DISTANCE_SIMILARITY
  2. JARO_WINKLER_DISTANCE
  3. SOUNDEX
  4. EDIT_DISTANCE

Explanation

Correct Answer

A:

EDIT_DISTANCE_SIMILARITY converts an edit-distance relationship into a similarity measure, making threshold comparisons easier.

Incorrect Answers

B:

JARO_WINKLER_DISTANCE applies Jaro-Winkler name-oriented matching behavior rather than simple adjacent-edit counting; for the stated conditions, the deciding feature is EDIT_DISTANCE_SIMILARITY rather than this behavior.

C:

SOUNDEX converts a string into a phonetic code for approximate pronunciation comparison, with limits across languages; it addresses a different step; the question calls for EDIT_DISTANCE_SIMILARITY.

D:

EDIT_DISTANCE measures Levenshtein-style edit operations between two strings and returns a distance rather than a similarity label; this differs from the EDIT_DISTANCE_SIMILARITY requirement described in the scenario.

Question 16

Each product must display its most recent transaction date as a single value while retaining exactly one product row. Which mechanism addresses the requirement?

  1. NOT EXISTS correlated subquery
  2. EXISTS correlated subquery
  3. CROSS APPLY
  4. Scalar correlated subquery

Explanation

Correct Answer

D:

A scalar correlated subquery can compute one related value per outer row, provided the expression returns at most one value.

Incorrect Answers

A:

NOT EXISTS uses an anti-semi-join existence test and avoids the problematic NULL behavior of some NOT IN subqueries; the alternative cannot substitute for Scalar correlated subquery under the given constraints.

B:

A correlated EXISTS predicate evaluates whether a related row exists for each outer record without materializing duplicate outer records; the condition in the stem is resolved by Scalar correlated subquery, not by this feature alone.

C:

CROSS APPLY evaluates a table expression per outer row and includes only outer rows whose applied expression yields results; the missing behavior is Scalar correlated subquery; this mechanism does something else.

Question 17

A recursive query currently has no specified starting employee and must begin at the executive root rows before expanding to reports. What is the best primary mechanism to use?

  1. MAXRECURSION query hint
  2. Anchor member
  3. Recursive CTE
  4. Nonrecursive CTE

Explanation

Correct Answer

B:

The anchor member emits the initial rows of a recursive CTE; those rows seed later recursive expansion.

Incorrect Answers

A:

OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion; it addresses a different step; the question calls for Anchor member.

C:

A recursive CTE combines anchor and recursive members to iteratively produce reachable levels of a hierarchy; the required capability here is Anchor member, which this operation does not supply.

D:

A nonrecursive common table expression defines a named query expression in a WITH clause, scoped to the immediately following statement; the alternative cannot substitute for Anchor member under the given constraints.

Question 18

A team must give analysts read access to a small set of views without broad database administration or write rights. Which action is justified by these conditions?

  1. Contained database user
  2. Azure Monitor alerts
  3. Managed identity
  4. Microsoft Entra authentication
  5. Least-privilege database role

Explanation

Correct Answer

E:

A custom database role can aggregate narrowly defined GRANT permissions and simplify managed membership.

Incorrect Answers

A:

A contained database user represents an identity at database scope and can receive database permissions or role membership; the missing behavior is Least-privilege database role; this mechanism does something else.

B:

Azure Monitor evaluates selected signals against alert rules and can notify or trigger responses; it is not a row-authorization mechanism; that function is not interchangeable with Least-privilege database role for the requested result.

C:

A managed identity gives an eligible Azure workload an automatically managed service identity, with database access still requiring grants; for the stated conditions, the deciding feature is Least-privilege database role rather than this behavior.

D:

Microsoft Entra authentication supports identity-provider tokens for compatible SQL connections and database principal authorization; it addresses a different step; the question calls for Least-privilege database role.

Question 19

A current Azure SQL vector index should accelerate interactive nearest-neighbor retrieval with the platform's approximate-query form. Which option satisfies the desired behavior most directly?

  1. Hybrid union of candidate IDs
  2. TOP with ordered distance
  3. Post-retrieval reranking
  4. Approximate top-k search

Explanation

Correct Answer

D:

Approximate TOP with VECTOR_SEARCH can leverage supported vector indexes for low-latency ANN retrieval.

Incorrect Answers

A:

Joining or unioning candidate IDs from both sources allows duplicate documents to receive contributions from multiple ranking lists; the required capability here is Approximate top-k search, which this operation does not supply.

B:

Ordering by the computed distance ascending and selecting TOP limits results to nearest eligible vectors under exact scoring; the alternative cannot substitute for Approximate top-k search under the given constraints.

C:

A reranking stage refines top candidates after initial retrieval, reducing cost compared with full-corpus cross-encoding; the condition in the stem is resolved by Approximate top-k search, not by this feature alone.

Question 20

A security architect wants key metadata identifying the external key that protects column encryption keys, with key material kept in an external trust boundary. What is the best primary mechanism to use?

  1. Column encryption key
  2. Always Encrypted-enabled driver
  3. Secure enclave
  4. Column master key

Explanation

Correct Answer

D:

A column master key describes the key-encryption root used to protect column encryption keys; the underlying key should remain outside routine database access.

Incorrect Answers

A:

A column encryption key encrypts Always Encrypted column data, while its encrypted form is stored as database metadata; for the stated conditions, the deciding feature is Column master key rather than this behavior.

B:

A compatible driver with column encryption enabled can perform encryption and decryption using approved key access outside the database engine; it addresses a different step; the question calls for Column master key.

C:

A secure enclave can support approved operations on protected data while isolating cryptographic processing from the rest of the database environment; this differs from the Column master key requirement described in the scenario.

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