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

Question 160

A booking allocator checks for an unclaimed indexed key inside a transaction and then inserts it. It must lock the probed key range and avoid a shared-to-exclusive lock-conversion race without raising the isolation level for every table access. Which table hint combination fits the probe?

  1. READ_COMMITTED_SNAPSHOT
  2. READ COMMITTED locking
  3. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  4. WITH (UPDLOCK, HOLDLOCK)

Explanation

Correct Answer

D:

HOLDLOCK supplies serializable-style key-range protection for the probed table, while UPDLOCK takes update locks during the read to help avoid conversion deadlocks before the insert. The check and insert must remain in the same transaction with suitable indexing.

Incorrect Answers

A:

READ_COMMITTED_SNAPSHOT changes default READ COMMITTED read behavior to statement snapshots; it does not take the update/key-range locks on the booking probe required before insert.

B:

Ordinary READ COMMITTED locking protects rows while read locks are held, but does not hold a serializable key-range lock for the full check-and-insert transaction.

C:

SNAPSHOT uses row versions for consistent reads and detects update conflicts; it does not reserve an empty insertion range against another concurrent allocator.

Question 161

A regulated assistant must decline unsupported answers rather than fill gaps with plausible-sounding guesses. Which SQL capability is the most direct fit?

  1. Freshness-aware indexing
  2. Source-document identifiers
  3. Evidence-only prompt instruction
  4. Tenant-safe retrieval

Explanation

Correct Answer

C:

Directing generation to use only retrieved evidence and abstain when evidence is missing reduces unsupported claims, though validation remains necessary.

Incorrect Answers

A:

Updating or invalidating affected chunk embeddings keeps retrieved context aligned with source changes and effective dates; it addresses a different step; the question calls for Evidence-only prompt instruction.

B:

Keeping source and chunk IDs through retrieval and prompt construction supports traceability and user-verifiable citations; this differs from the Evidence-only prompt instruction requirement described in the scenario.

D:

Authorization-aware retrieval filters evidence before model input, preventing cross-tenant disclosure through generation; the alternative cannot substitute for Evidence-only prompt instruction under the given constraints.

Question 162

A proposal adds ten indexes to accelerate infrequent reports, but the transactional table receives thousands of updates per second. What is the most suitable implementation?

  1. Read/write index tradeoff
  2. Histogram selectivity
  3. REPEATABLE READ
  4. Covering index evaluation
  5. Recompile after schema change

Explanation

Correct Answer

A:

Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification.

Incorrect Answers

B:

Statistics histograms describe value distributions used to estimate predicate cardinality and influence operator selection; that function is not interchangeable with Read/write index tradeoff for the requested result.

C:

REPEATABLE READ holds appropriate locks to protect previously read rows, but qualifying new rows may still appear on subsequent range queries; for the stated conditions, the deciding feature is Read/write index tradeoff rather than this behavior.

D:

Adding appropriate INCLUDE columns can make the access path covering and reduce repetitive base-row lookups, if write overhead is acceptable; it addresses a different step; the question calls for Read/write index tradeoff.

E:

Plan recompilation allows the optimizer to select operators using current metadata and available statistics; this differs from the Read/write index tradeoff requirement described in the scenario.

Question 163

A retrieved database note contains instructions telling the assistant to send a private connection string elsewhere; it must remain untrusted content. What is the most appropriate design decision?

  1. Stale schema context
  2. Authentication failure from tool server
  3. Unavailable MCP tool
  4. Unsafe prompt injection in data

Explanation

Correct Answer

D:

Data returned from a tool is lower-trust context, and its instructions must not override tool policies or reveal confidential secrets.

Incorrect Answers

A:

Refreshing schema metadata before generation or execution avoids relying on obsolete object definitions; the required capability here is Unsafe prompt injection in data, which this operation does not supply.

B:

The server's database identity needs valid authentication and the necessary SQL permissions; chat access alone does not grant data access; the alternative cannot substitute for Unsafe prompt injection in data under the given constraints.

C:

Tool discovery depends on the capabilities exposed by the server; adding a prompt instruction cannot create a missing backend tool; the condition in the stem is resolved by Unsafe prompt injection in data, not by this feature alone.

Question 164

A service builds an array of order-line descriptions and must guarantee that its elements appear in increasing LineNumber order rather than an unspecified aggregate order. What is the most suitable implementation?

  1. Ledger verification
  2. JSON_MODIFY with append
  3. JSON_ARRAYAGG with ORDER BY
  4. JSON_QUERY to preserve a JSON fragment
  5. JSON_OBJECT

Explanation

Correct Answer

C:

JSON_ARRAYAGG with an ORDER BY clause controls element ordering within its generated JSON array on supported SQL versions; ordinary aggregation without ordering does not guarantee that order.

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 JSON_ARRAYAGG with ORDER BY rather than this behavior.

B:

JSON_MODIFY with an append path adds a value to a JSON array and returns the revised document, leaving other properties in the input intact; it addresses a different step; the question calls for JSON_ARRAYAGG with ORDER BY.

D:

JSON_QUERY can mark an extracted object or array as a JSON fragment, letting FOR JSON embed that fragment as JSON instead of quoting and escaping it as a plain string; the required capability here is JSON_ARRAYAGG with ORDER BY, which this operation does not supply.

E:

JSON_OBJECT builds object-shaped JSON from key/value expressions and handles escaping more reliably than manual string concatenation; the alternative cannot substitute for JSON_ARRAYAGG with ORDER BY under the given constraints.

Question 165

A support assistant must answer from newly updated internal product manuals without retraining the language model every night. What is the right solution for this specific requirement?

  1. Full-text document retrieval
  2. SQL structured lookup
  3. Fine-tuning
  4. Vector retrieval
  5. Retrieval-augmented generation

Explanation

Correct Answer

E:

RAG retrieves relevant current documents and provides them as context to the generator, keeping knowledge retrieval distinct from model weights.

Incorrect Answers

A:

Full-text search can locate matching passages directly; generation adds complexity when the task is simply retrieval; the condition in the stem is resolved by Retrieval-augmented generation, not by this feature alone.

B:

A parameterized structured SQL lookup provides an exact current record without relying on probabilistic text generation; the missing behavior is Retrieval-augmented generation; this mechanism does something else.

C:

Fine-tuning adjusts model behavior through training on examples; it is not the usual replacement for retrieving frequently changing knowledge; that function is not interchangeable with Retrieval-augmented generation for the requested result.

D:

Vector-neighbor search ranks stored embeddings by proximity to the query representation and returns source identifiers; for the stated conditions, the deciding feature is Retrieval-augmented generation rather than this behavior.

Question 166

A stored procedure inserts an order and inventory reservation; the team must test the complete transaction with actual database constraints. Which choice best meets these constraints?

  1. Unit test
  2. Deployment smoke test
  3. Integration test
  4. Build-time schema validation

Explanation

Correct Answer

C:

An integration test exercises interactions among database objects and dependent components rather than one isolated expression.

Incorrect Answers

A:

A unit test validates one small programmatic behavior against expected results, catching logic regressions early; this differs from the Integration test requirement described in the scenario.

B:

A smoke test checks critical post-deployment operations in the target environment and complements static compilation; the required capability here is Integration test, which this operation does not supply.

D:

Building a database project checks supported schema references and model consistency to catch missing dependencies; the condition in the stem is resolved by Integration test, not by this feature alone.

Question 167

A query must compute an exact cosine distance between a stored embedding and a provided query embedding for every qualifying row. Which choice best meets these constraints?

  1. VECTOR_SEARCH
  2. VECTOR_NORMALIZE
  3. AI_GENERATE_EMBEDDINGS
  4. VECTOR_DISTANCE

Explanation

Correct Answer

D:

VECTOR_DISTANCE calculates an exact metric-specific distance and does not automatically use an approximate vector index.

Incorrect Answers

A:

VECTOR_SEARCH provides vector-neighbor retrieval and can use supported ANN indexing under the appropriate query form; that function is not interchangeable with VECTOR_DISTANCE for the requested result.

B:

VECTOR_NORMALIZE produces a norm-adjusted vector for supported vector types and normalization modes; for the stated conditions, the deciding feature is VECTOR_DISTANCE rather than this behavior.

C:

AI_GENERATE_EMBEDDINGS can turn text into a vector with an external model; it does not directly calculate the cosine distance between two vectors already present.

Question 168

A T-SQL query should construct a JSON object from typed column values without hand-concatenating quotes and punctuation. What should the developer implement?

  1. JSON_QUERY to preserve a JSON fragment
  2. JSON_MODIFY with append
  3. JSON_OBJECT
  4. JSON_ARRAYAGG with ORDER BY

Explanation

Correct Answer

C:

JSON_OBJECT builds object-shaped JSON from key/value expressions and handles escaping more reliably than manual string concatenation.

Incorrect Answers

A:

JSON_QUERY can mark an extracted object or array as a JSON fragment, letting FOR JSON embed that fragment as JSON instead of quoting and escaping it as a plain string; the alternative cannot substitute for JSON_OBJECT under the given constraints.

B:

JSON_MODIFY with an append path adds a value to a JSON array and returns the revised document, leaving other properties in the input intact; the condition in the stem is resolved by JSON_OBJECT, not by this feature alone.

D:

JSON_ARRAYAGG with an ORDER BY clause controls element ordering within its generated JSON array on supported SQL versions; ordinary aggregation without ordering does not guarantee that order; that function is not interchangeable with JSON_OBJECT for the requested result.

Question 169

A registered external model is used by AI_GENERATE_EMBEDDINGS and must declare the appropriate inference operation category. Which option would you recommend?

  1. DATABASE SCOPED CREDENTIAL
  2. LOCATION parameter
  3. MODEL_TYPE = EMBEDDINGS
  4. API_FORMAT parameter

Explanation

Correct Answer

C:

The embeddings model type indicates that the external definition produces vector representations from supported text input.

Incorrect Answers

A:

A database-scoped credential stores or references endpoint authentication data subject to permission and endpoint matching rules; it addresses a different step; the question calls for MODEL_TYPE = EMBEDDINGS.

B:

LOCATION records the target service URI in the external model definition, with provider formatting and TLS constraints; this differs from the MODEL_TYPE = EMBEDDINGS requirement described in the scenario.

D:

API_FORMAT identifies the provider-specific request and response contract used by the external model integration; the alternative cannot substitute for MODEL_TYPE = EMBEDDINGS under the given constraints.

Question 170

An application stores people and friendships and wants to traverse explicitly typed relationships using graph patterns. Which technique applies to this scenario?

  1. Updatable ledger table
  2. Graph node and edge tables
  3. System-versioned temporal table
  4. External table

Explanation

Correct Answer

B:

SQL graph node and edge tables represent entities and relationships, enabling MATCH-based queries over connected records.

Incorrect Answers

A:

An updatable ledger table retains audit history with digest-based verification of data integrity rather than merely exposing prior row versions; the missing behavior is Graph node and edge tables; this mechanism does something else.

C:

Temporal tables maintain current and history rows with system-maintained validity periods, allowing FOR SYSTEM_TIME point-in-time queries; for the stated conditions, the deciding feature is Graph node and edge tables rather than this behavior.

D:

An external table supplies database metadata for accessing external data; it does not turn the remote data into ordinary local stored rows; it addresses a different step; the question calls for Graph node and edge tables.

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