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

Question 90

A customer profile JSON result must omit an optional MiddleName property entirely if it is SQL NULL, while retaining required name and identifier fields. Which technique applies to this scenario?

  1. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  2. JSON_ARRAYAGG with NULL ON NULL
  3. JSON_ARRAY
  4. JSON_OBJECT with ABSENT ON NULL

Explanation

Correct Answer

D:

JSON_OBJECT with ABSENT ON NULL suppresses keys whose SQL expressions are NULL; its default NULL ON NULL behavior would instead emit those keys with JSON null values.

Incorrect Answers

A:

FOR JSON PATH with WITHOUT_ARRAY_WRAPPER removes the enclosing JSON array for a single-row result; use it only when cardinality and JSON validity are controlled; the missing behavior is JSON_OBJECT with ABSENT ON NULL; this mechanism does something else.

B:

JSON_ARRAYAGG with NULL ON NULL retains SQL NULL inputs as JSON null elements; its usual ABSENT ON NULL behavior would omit those array slots; that function is not interchangeable with JSON_OBJECT with ABSENT ON NULL for the requested result.

C:

JSON_ARRAY constructs an array value from its supplied scalar expressions; for the stated conditions, the deciding feature is JSON_OBJECT with ABSENT ON NULL rather than this behavior.

Question 91

Source policies change weekly and users must not receive last quarter's rules because embeddings were never updated. Which approach should the team select?

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

Explanation

Correct Answer

B:

Updating or invalidating affected chunk embeddings keeps retrieved context aligned with source changes and effective dates.

Incorrect Answers

A:

Keeping source and chunk IDs through retrieval and prompt construction supports traceability and user-verifiable citations; the required capability here is Freshness-aware indexing, which this operation does not supply.

C:

Authorization-aware retrieval filters evidence before model input, preventing cross-tenant disclosure through generation; the condition in the stem is resolved by Freshness-aware indexing, not by this feature alone.

D:

Directing generation to use only retrieved evidence and abstain when evidence is missing reduces unsupported claims, though validation remains necessary; the missing behavior is Freshness-aware indexing; this mechanism does something else.

Question 92

A selective predicate on the leading key should navigate directly to a narrow range of entries instead of scanning all keys. Which action is justified by these conditions?

  1. Key Lookup
  2. Index Scan
  3. Table Scan
  4. Hash Match join
  5. Index Seek

Explanation

Correct Answer

E:

An Index Seek uses an index access path to locate qualifying key ranges, though residual predicates may still filter rows.

Incorrect Answers

A:

A Key Lookup obtains missing columns from the clustered data using keys retrieved from another index; covering may reduce this work; for the stated conditions, the deciding feature is Index Seek rather than this behavior.

B:

An Index Scan walks a substantial part of an index and can be appropriate for broad result sets; it addresses a different step; the question calls for Index Seek.

C:

A Table Scan accesses heap data without using an index seek and may be reasonable when most rows are needed; this differs from the Index Seek requirement described in the scenario.

D:

Hash Match uses a hash table to match join keys and often suits large unordered joins at a memory cost; the required capability here is Index Seek, which this operation does not supply.

Question 93

A reservation transaction selects all active reservations within a date range and must prevent phantoms until commit. What is the most appropriate design decision?

  1. READ COMMITTED SNAPSHOT
  2. REPEATABLE READ
  3. SNAPSHOT isolation
  4. SERIALIZABLE

Explanation

Correct Answer

D:

SERIALIZABLE applies range semantics that protect qualifying predicate ranges against conflicting concurrent insertions.

Incorrect Answers

A:

Statement-level row versioning under RCSI can allow committed reads without ordinary shared read locks; the condition in the stem is resolved by SERIALIZABLE, not by this feature alone.

B:

REPEATABLE READ holds appropriate locks to protect previously read rows, but qualifying new rows may still appear on subsequent range queries; the missing behavior is SERIALIZABLE; this mechanism does something else.

C:

SNAPSHOT delivers transaction-consistent versioned reads while maintaining update conflict considerations; that function is not interchangeable with SERIALIZABLE for the requested result.

Question 94

A query computes cosine distance for every row in a million-vector table and sorts all results without an approximate access strategy. What is the right solution for this specific requirement?

  1. Distance-computation cost
  2. Filter-selectivity assessment
  3. Top-k selection
  4. ANN index evaluation

Explanation

Correct Answer

A:

Exhaustive distance calculations scale with candidate count and vector dimension, creating CPU and sorting pressure.

Incorrect Answers

B:

Selective eligibility predicates and supported filter execution behavior affect vector retrieval work and correctness; the required capability here is Distance-computation cost, which this operation does not supply.

C:

Limiting k reduces result volume and downstream processing while preserving the intended nearest-neighbor objective; the alternative cannot substitute for Distance-computation cost under the given constraints.

D:

ANN indexing can reduce comparisons and improve speed, subject to measured recall and platform support; the condition in the stem is resolved by Distance-computation cost, not by this feature alone.

Question 95

Two query branches yield overlapping identifier rows, but the final list should contain each distinct row combination once. Which mechanism addresses the requirement?

  1. INTERSECT
  2. EXCEPT
  3. UNION ALL
  4. UNION

Explanation

Correct Answer

D:

UNION performs set combination with duplicate elimination across compatible query projections.

Incorrect Answers

A:

INTERSECT returns distinct compatible rows appearing in both input result sets; that function is not interchangeable with UNION for the requested result.

B:

EXCEPT returns distinct rows from the first input that are not found in the second input; for the stated conditions, the deciding feature is UNION rather than this behavior.

C:

UNION ALL concatenates the result sets without removing duplicate rows, unlike UNION's distinct behavior; it addresses a different step; the question calls for UNION.

Question 96

A specialized feature-vector model treats absolute geometric separation in its numeric space as meaningful. Which option satisfies the desired behavior most directly?

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

Explanation

Correct Answer

A:

Euclidean distance measures geometric distance using coordinate differences and incorporates magnitude.

Incorrect Answers

B:

Cosine distance evaluates angular similarity, making it useful for text embeddings where direction carries semantic signal; the condition in the stem is resolved by Euclidean distance, not by this feature alone.

C:

Dot-product-based vector distance applies the supported negative dot-product convention, with ordering affected by normalization and magnitudes; the missing behavior is Euclidean distance; this mechanism does something else.

D:

Normalization rescales vectors to a selected norm, making downstream distance comparisons reflect a controlled magnitude basis; that function is not interchangeable with Euclidean distance for the requested result.

Question 97

An incident dashboard queries only rows with Escalated = 1, a tiny fraction of the table, and needs a lightweight index. What is the most suitable implementation?

  1. Nonclustered index on join key
  2. Filtered index for sparse predicate
  3. Clustered columnstore index for aggregations
  4. Indexed view

Explanation

Correct Answer

B:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows.

Incorrect Answers

A:

An index with HeaderId as a leading key can accelerate lookups and joins from headers to matching details; it addresses a different step; the question calls for Filtered index for sparse predicate.

C:

Columnstore storage is optimized for large analytical scans and compression of similar column values; the required capability here is Filtered index for sparse predicate, which this operation does not supply.

D:

An eligible indexed view stores indexed relational results that the SQL engine maintains as qualifying base rows change, at the cost of additional write and index maintenance; the alternative cannot substitute for Filtered index for sparse predicate under the given constraints.

Question 98

Performance counters show severe page fragmentation and low density from random inserts, with statistics already accurate. Which mechanism addresses the requirement?

  1. UPDATE STATISTICS
  2. Index physical maintenance
  3. Query Store plan forcing
  4. Filtered statistics

Explanation

Correct Answer

B:

Appropriate index rebuild or reorganize actions address physical layout while statistics alone cannot fix page fragmentation.

Incorrect Answers

A:

Updating statistics refreshes cardinality information used during plan selection, though it does not directly rebuild every index; the missing behavior is Index physical maintenance; this mechanism does something else.

C:

Forcing a previously good query plan is an execution-plan intervention, not a repair for the diagnosed leaf-level fragmentation and page density.

D:

Filtered statistics capture distributions for rows matching a predicate and can improve estimates for compatible queries; it addresses a different step; the question calls for Index physical maintenance.

Question 99

All write paths must prevent zero or negative Quantity values on invoice lines using a database-enforced rule. Which feature would solve the stated problem?

  1. DEFAULT for CreatedAt
  2. FOREIGN KEY validation
  3. UNIQUE alternate identifier
  4. CHECK (Quantity > 0)

Explanation

Correct Answer

D:

A CHECK predicate evaluates the supplied value at write time and rejects rows that violate its Boolean condition.

Incorrect Answers

A:

A DEFAULT expression can supply an insertion-time value when the column is not explicitly populated; the required capability here is CHECK (Quantity > 0), which this operation does not supply.

B:

Foreign-key constraints protect relational references independently of the application that writes data; the alternative cannot substitute for CHECK (Quantity > 0) under the given constraints.

C:

A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier; the condition in the stem is resolved by CHECK (Quantity > 0), not by this feature alone.

Question 100

A similarity query accidentally compares a new 768-dimensional query vector with a corpus of 1536-dimensional vectors and must be corrected. What should be configured to meet this objective?

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

Explanation

Correct Answer

C:

Query and stored vectors must have compatible dimensionality and semantic model space for meaningful distance calculations.

Incorrect Answers

A:

VECTOR(n) declares the element count for a vector, so stored values must match the intended embedding dimension; for the stated conditions, the deciding feature is Model dimension compatibility rather than this behavior.

B:

Normalization scales vectors to a consistent norm for appropriate metrics, which can make some similarity comparisons more consistent; it addresses a different step; the question calls for Model dimension compatibility.

D:

Enforcing tenant metadata and database authorization filters during retrieval constrains candidate content independently of vector similarity; the required capability here is Model dimension compatibility, which this operation does not supply.

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