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

Question 550

A model performs well on one request but the system must support hundreds of concurrent embeddings during nightly indexing. What should the developer implement?

  1. Smaller embedding model
  2. Evaluation on representative queries
  3. Larger embedding model
  4. Latency and throughput load test

Explanation

Correct Answer

D:

Load testing reveals throughput, rate-limit, and response-time behavior under the deployment's expected concurrency.

Incorrect Answers

A:

A smaller embedding model can reduce inference cost and latency, provided measured retrieval quality remains acceptable; this differs from the Latency and throughput load test requirement described in the scenario.

B:

A labeled evaluation set measures real retrieval quality, preventing selection based solely on generic benchmark or model name; the required capability here is Latency and throughput load test, which this operation does not supply.

C:

A larger-capacity model may improve representation on difficult content, but benefits must be verified against the target dataset; the alternative cannot substitute for Latency and throughput load test under the given constraints.

Question 551

A deliberately approximate diagnostic query accepts possible dirty reads and inconsistent results in exchange for reduced shared-lock constraints. Which database mechanism should be chosen?

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

Explanation

Correct Answer

A:

READ UNCOMMITTED allows access to uncommitted changes and can produce inconsistent values, so it is unsuitable for authoritative reports.

Incorrect Answers

B:

SERIALIZABLE is the strongest standard isolation level and can protect qualifying key ranges from concurrent phantom changes, increasing contention; for the stated conditions, the deciding feature is READ UNCOMMITTED rather than this behavior.

C:

SNAPSHOT uses transaction-level row versions and checks write conflicts when updating rows another transaction has changed after the snapshot began, so the caller may need to retry; it addresses a different step; the question calls for READ UNCOMMITTED.

D:

READ COMMITTED prevents dirty reads, with blocking or row-version behavior depending on the configured implementation; this differs from the READ UNCOMMITTED requirement described in the scenario.

Question 552

A CATCH block has logged context for a runtime failure and must rethrow the *original* error number and location rather than replacing it with a new error message. Which approach should the team select?

  1. SAVE TRANSACTION
  2. XACT_STATE()
  3. TRY_CONVERT
  4. OUTER APPLY
  5. THROW without arguments
  6. BEGIN TRY / BEGIN CATCH

Explanation

Correct Answer

E:

THROW without arguments inside CATCH rethrows the caught exception, preserving its original error details rather than inventing a replacement exception.

Incorrect Answers

A:

SAVE TRANSACTION establishes a savepoint inside an active transaction, allowing partial rollback when the transaction's state permits it; the alternative cannot substitute for THROW without arguments under the given constraints.

B:

XACT_STATE() returns -1 for an uncommittable active transaction, permitting only rollback and read operations until that transaction is ended; the condition in the stem is resolved by THROW without arguments, not by this feature alone.

C:

TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; the missing behavior is THROW without arguments; this mechanism does something else.

D:

OUTER APPLY preserves unmatched outer rows while evaluating a correlated table expression for each one; that function is not interchangeable with THROW without arguments for the requested result.

F:

TRY...CATCH provides a structured handler for many run-time T-SQL errors, allowing recovery code to run in the CATCH branch; it addresses a different step; the question calls for THROW without arguments.

Question 553

A user searches a long incident archive for an exact alert token `ERR-40391` and similar meanings would be harmful. Which approach should the team select?

  1. Combined identifier and symptom retrieval
  2. Boolean regulatory filter
  3. Exact serial-number retrieval
  4. Paraphrase retrieval

Explanation

Correct Answer

C:

Full-text or exact structured predicates can preserve the literal identifier, unlike semantic vectors that may cluster unrelated codes.

Incorrect Answers

A:

Hybrid search uses exact-term and semantic candidate lists so both precise identifiers and related descriptions influence ranking; it addresses a different step; the question calls for Exact serial-number retrieval.

B:

A structured metadata predicate enforces eligibility; neither full-text relevance nor vector closeness is a security filter by itself; this differs from the Exact serial-number retrieval requirement described in the scenario.

D:

Semantic embedding retrieval handles meaning-based paraphrases that lexical token matching can miss; the alternative cannot substitute for Exact serial-number retrieval under the given constraints.

Question 554

A column rename would affect a dozen dependent views and procedures, and developers need to validate references before rollout. What should the developer implement?

  1. Block drifted release
  2. Import changes into SQL project
  3. Compare live schema to DACPAC
  4. Review object dependency graph

Explanation

Correct Answer

D:

Model dependency validation and schema comparison help reveal impacts that a text-only change review may miss.

Incorrect Answers

A:

A drift gate can halt deployment when actual target state deviates materially from the expected controlled baseline; the missing behavior is Review object dependency graph; this mechanism does something else.

B:

Updating the project from the database can reconcile reviewed schema changes back into declarative source control; that function is not interchangeable with Review object dependency graph for the requested result.

C:

Comparing the deployed database with the approved DACPAC reveals schema drift that normal source-code reviews cannot see; for the stated conditions, the deciding feature is Review object dependency graph rather than this behavior.

Question 555

Compliance requires records of sensitive SELECT and permission-change events for review after the statements finish. Which choice best meets these constraints?

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

Explanation

Correct Answer

B:

SQL auditing records configured database or server actions to an audit target, supporting investigations rather than blocking the statements themselves.

Incorrect Answers

A:

Azure Monitor evaluates selected signals against alert rules and can notify or trigger responses; it is not a row-authorization mechanism; the required capability here is SQL auditing, which this operation does not supply.

C:

Log Analytics stores supported telemetry for KQL-based investigation when diagnostics are configured to send records there; the condition in the stem is resolved by SQL auditing, not by this feature alone.

D:

Query Store persists execution-plan and runtime-performance information for eligible queries rather than serving as an authentication audit log; the missing behavior is SQL auditing; this mechanism does something else.

Question 556

An assistant serves several companies and must avoid putting a semantically similar but unauthorized document into any prompt. Which choice best meets these constraints?

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

Explanation

Correct Answer

B:

Authorization-aware retrieval filters evidence before model input, preventing cross-tenant disclosure through generation.

Incorrect Answers

A:

Directing generation to use only retrieved evidence and abstain when evidence is missing reduces unsupported claims, though validation remains necessary; for the stated conditions, the deciding feature is Tenant-safe retrieval rather than this behavior.

C:

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

D:

Updating or invalidating affected chunk embeddings keeps retrieved context aligned with source changes and effective dates; the required capability here is Tenant-safe retrieval, which this operation does not supply.

Question 557

Salary values in a protected SQL column must be encrypted with the symmetric key selected for that column. What is the right solution for this specific requirement?

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

Explanation

Correct Answer

D:

A column encryption key encrypts Always Encrypted column data, while its encrypted form is stored as database metadata.

Incorrect Answers

A:

A compatible driver with column encryption enabled can perform encryption and decryption using approved key access outside the database engine; the condition in the stem is resolved by Column encryption key, not by this feature alone.

B:

A column master key describes the key-encryption root used to protect column encryption keys; the underlying key should remain outside routine database access; the missing behavior is Column encryption key; this mechanism does something else.

C:

A secure enclave can support approved operations on protected data while isolating cryptographic processing from the rest of the database environment; that function is not interchangeable with Column encryption key for the requested result.

Question 558

A telemetry array contains mixed JSON fields; the query must produce explicit INT and DATETIME2 columns for SQL joins. Which option satisfies the desired behavior most directly?

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

Explanation

Correct Answer

D:

An OPENJSON WITH clause maps JSON paths to named typed relational columns during rowset extraction.

Incorrect Answers

A:

JSON_VALUE extracts an individual scalar at one JSON path; it does not project array elements into multiple typed SQL rows.

B:

JSON_CONTAINS evaluates containment semantics for JSON values rather than merely extracting a scalar for presentation; the required capability here is OPENJSON WITH, which this operation does not supply.

C:

JSON_MODIFY updates content at a specified JSON path and returns the modified document expression; the alternative cannot substitute for OPENJSON WITH under the given constraints.

Question 559

A warehouse holds millions of immutable sales records and repeatedly aggregates several measures across large date ranges; point updates are not the priority. What is the most appropriate design decision?

  1. Filtered nonclustered index
  2. Clustered rowstore index
  3. Clustered columnstore index
  4. Nonclustered covering index

Explanation

Correct Answer

C:

Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets.

Incorrect Answers

A:

A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries; that function is not interchangeable with Clustered columnstore index for the requested result.

B:

A clustered B-tree determines the rowstore table's data organization and supports efficient key-based access, but it is not a compressed analytics-oriented layout; for the stated conditions, the deciding feature is Clustered columnstore index rather than this behavior.

D:

A nonclustered index can use CustomerId as its key and INCLUDE the projected fields, satisfying the query from the secondary index rather than fetching every base row; this differs from the Clustered columnstore index requirement described in the scenario.

Question 560

A heap has no useful index for an unrestricted read, so the execution plan visits the table's pages directly. What is the most appropriate design decision?

  1. Table Scan
  2. Key Lookup
  3. Index Scan
  4. Index Seek

Explanation

Correct Answer

A:

A Table Scan accesses heap data without using an index seek and may be reasonable when most rows are needed.

Incorrect Answers

B:

A Key Lookup obtains missing columns from the clustered data using keys retrieved from another index; covering may reduce this work; the condition in the stem is resolved by Table Scan, not by this feature alone.

C:

An Index Scan walks a substantial part of an index and can be appropriate for broad result sets; the missing behavior is Table Scan; this mechanism does something else.

D:

An Index Seek uses an index access path to locate qualifying key ranges, though residual predicates may still filter rows; that function is not interchangeable with Table Scan for the requested result.

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