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

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.

Question 171

Embeddings were trained and normalized for dot-product retrieval and the service needs to preserve that scorer's ordering. What is the right solution for this specific requirement?

  1. Cosine distance
  2. Normalized vectors
  3. Dot-product distance
  4. Euclidean distance

Explanation

Correct Answer

C:

Dot-product-based vector distance applies the supported negative dot-product convention, with ordering affected by normalization and magnitudes.

Incorrect Answers

A:

Cosine distance evaluates angular similarity, making it useful for text embeddings where direction carries semantic signal; the required capability here is Dot-product distance, which this operation does not supply.

B:

Normalization rescales vectors to a selected norm, making downstream distance comparisons reflect a controlled magnitude basis; the alternative cannot substitute for Dot-product distance under the given constraints.

D:

Euclidean distance measures geometric distance using coordinate differences and incorporates magnitude; the missing behavior is Dot-product distance; this mechanism does something else.

Question 172

Approximate search gets faster after index tuning but may miss relevant documents, so its quality must be compared with exact top-k results. What is the best primary mechanism to use?

  1. Consistent distance metric
  2. Monitor recall against exact baseline
  3. Rebuild embeddings after model change
  4. Matching vector dimension

Explanation

Correct Answer

B:

Exact-neighbor evaluation provides a reference for estimating recall lost by approximate-index strategies.

Incorrect Answers

A:

Metric consistency preserves meaningful ranking and avoids evaluating an index under unintended distance assumptions; for the stated conditions, the deciding feature is Monitor recall against exact baseline rather than this behavior.

C:

Re-embedding source content into a consistent model space is necessary because equal dimension does not imply semantic compatibility; this differs from the Monitor recall against exact baseline requirement described in the scenario.

D:

Compatible vector dimensions are required for distance operations; embedding spaces also need consistent model semantics; the required capability here is Monitor recall against exact baseline, which this operation does not supply.

Question 173

A semi-structured log needs to split entries at one or more regex delimiter patterns into multiple output rows. Which technique applies to this scenario?

  1. REGEXP_SUBSTR
  2. CHARINDEX
  3. REGEXP_SPLIT_TO_TABLE
  4. REGEXP_MATCHES

Explanation

Correct Answer

C:

REGEXP_SPLIT_TO_TABLE splits input text using a regular-expression delimiter and yields a tabular set of segments.

Incorrect Answers

A:

REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; the condition in the stem is resolved by REGEXP_SPLIT_TO_TABLE, not by this feature alone.

B:

CHARINDEX finds the starting position of a literal substring in a string, without regex pattern semantics; the missing behavior is REGEXP_SPLIT_TO_TABLE; this mechanism does something else.

D:

REGEXP_MATCHES returns matching occurrences as relational rows on supporting versions, unlike a single scalar extraction; for the stated conditions, the deciding feature is REGEXP_SPLIT_TO_TABLE rather than this behavior.

Question 174

A tracked SQL table changes several times between function invocations. A serverless handler must receive the affected rows in batches together with Insert, Update, or Delete operation metadata through a SQL trigger binding. Which integration delivers these batches?

  1. Change event streaming
  2. Change Tracking
  3. Change Data Capture
  4. Azure Functions SQL trigger

Explanation

Correct Answer

D:

The Azure Functions SQL trigger binding observes change-tracked table changes and delivers batches of rows with operation information for the function to process.

Incorrect Answers

A:

Change event streaming sends supported database change events to a configured streaming destination, with platform-specific prerequisites; this differs from the Azure Functions SQL trigger requirement described in the scenario.

B:

Change Tracking maintains lightweight change versions and keys instead of full historical before-and-after values; the required capability here is Azure Functions SQL trigger, which this operation does not supply.

C:

CDC captures data changes in change tables for later consumption, including row images and operation information subject to configuration; the alternative cannot substitute for Azure Functions SQL trigger under the given constraints.

Question 175

An index exists, but the optimizer expects too many matches for a highly selective value; the DBA needs to check underlying column distribution estimates. What is the most suitable implementation?

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

Explanation

Correct Answer

D:

Statistics histograms describe value distributions used to estimate predicate cardinality and influence operator selection.

Incorrect Answers

A:

Plan recompilation allows the optimizer to select operators using current metadata and available statistics; that function is not interchangeable with Histogram selectivity for the requested result.

B:

Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification; for the stated conditions, the deciding feature is Histogram selectivity rather than this behavior.

C:

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 Histogram selectivity.

Question 176

An export requires the five most recently created jobs, and the chosen rows must be deterministic where timestamps differ. Which feature would solve the stated problem?

  1. MAXRECURSION query hint
  2. EXISTS semi-join
  3. SARGable date range
  4. Parameterized query
  5. TOP with ORDER BY

Explanation

Correct Answer

E:

TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match.

Incorrect Answers

A:

OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion; the alternative cannot substitute for TOP with ORDER BY under the given constraints.

B:

EXISTS expresses a presence test and does not expand the outer result into one row per matching child; the condition in the stem is resolved by TOP with ORDER BY, not by this feature alone.

C:

A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column; the missing behavior is TOP with ORDER BY; this mechanism does something else.

D:

Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases; that function is not interchangeable with TOP with ORDER BY for the requested result.

Question 177

An infrequently read derived amount should be computed from Qty and UnitPrice without storing a duplicate column value. Which action is justified by these conditions?

  1. Persisted computed column
  2. Case-sensitive collation
  3. Binary collation
  4. Nonpersisted computed column

Explanation

Correct Answer

D:

A regular computed column defines an expression evaluated as needed rather than necessarily materialized on disk.

Incorrect Answers

A:

A PERSISTED computed column materializes the computed result for eligible expressions, with extra storage and update work; it addresses a different step; the question calls for Nonpersisted computed column.

B:

A case-sensitive collation changes comparison and ordering behavior so character case is significant; this differs from the Nonpersisted computed column requirement described in the scenario.

C:

Binary collations compare encoded character values using binary rules, differing from culture-aware linguistic collations; the required capability here is Nonpersisted computed column, which this operation does not supply.

Question 178

A chat assistant proposes a schema-modifying SQL action; policy demands explicit developer approval before any tool performs it. Which SQL capability is the most direct fit?

  1. Read-only database principal
  2. Redaction of sensitive prompt context
  3. Secret scanning in GitHub
  4. Human approval before tool execution

Explanation

Correct Answer

D:

Requiring human confirmation for sensitive tool calls reduces the risk of autonomous changes and maintains an accountable approval point.

Incorrect Answers

A:

A read-only database principal with narrowly scoped SELECT access limits potential writes at the database authorization boundary; the missing behavior is Human approval before tool execution; this mechanism does something else.

B:

Removing or masking confidential fields before prompt submission minimizes unnecessary exposure through an AI-assisted workflow; that function is not interchangeable with Human approval before tool execution for the requested result.

C:

Secret scanning can identify supported credential patterns in repositories and complements, rather than replaces, credential rotation; for the stated conditions, the deciding feature is Human approval before tool execution rather than this behavior.

Question 179

An embedding model emits exactly 1536 numeric dimensions and SQL must store each output in a fixed-dimension vector column. Which SQL capability is the most direct fit?

  1. Metadata-filtered retrieval
  2. Embedding normalization
  3. Model dimension compatibility
  4. VECTOR(1536) column
  5. Idempotent embedding upsert

Explanation

Correct Answer

D:

VECTOR(n) declares the element count for a vector, so stored values must match the intended embedding dimension.

Incorrect Answers

A:

Enforcing tenant metadata and database authorization filters during retrieval constrains candidate content independently of vector similarity; the required capability here is VECTOR(1536) column, which this operation does not supply.

B:

Normalization scales vectors to a consistent norm for appropriate metrics, which can make some similarity comparisons more consistent; the alternative cannot substitute for VECTOR(1536) column under the given constraints.

C:

Query and stored vectors must have compatible dimensionality and semantic model space for meaningful distance calculations; the condition in the stem is resolved by VECTOR(1536) column, not by this feature alone.

E:

Upsert by a stable document/chunk/version key allows retries without duplicating records, supporting at-least-once processing; that function is not interchangeable with VECTOR(1536) column for the requested result.

Question 180

One transaction was selected as a deadlock victim during a correct concurrent design; the application must recover safely from a transient victim error. Which option satisfies the desired behavior most directly?

  1. Optimistic rowversion check
  2. Deadlock retry policy
  3. Atomic transaction
  4. Pessimistic transaction locking

Explanation

Correct Answer

B:

Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects.

Incorrect Answers

A:

Comparing the original ROWVERSION token in UPDATE's WHERE clause allows a zero-row result to signal a stale write; for the stated conditions, the deciding feature is Deadlock retry policy rather than this behavior.

C:

A database transaction groups multiple modifications into one commit-or-rollback unit, preserving all-or-nothing behavior; this differs from the Deadlock retry policy requirement described in the scenario.

D:

Appropriate transaction isolation and update locks can serialize conflicting modifications during the critical reservation step; the required capability here is Deadlock retry policy, which this operation does not supply.

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