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

Question 130

Many executions use different CustomerId values; the app should avoid literal SQL concatenation and support safe parameter binding. Which option would you recommend?

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

Explanation

Correct Answer

D:

Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases.

Incorrect Answers

A:

EXISTS expresses a presence test and does not expand the outer result into one row per matching child; the missing behavior is Parameterized query; this mechanism does something else.

B:

TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match; that function is not interchangeable with Parameterized query for the requested result.

C:

A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column; for the stated conditions, the deciding feature is Parameterized query rather than this behavior.

Question 131

An operational chart needs a three-sample moving average over the current and two immediately preceding samples. Which choice best meets these constraints?

  1. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  2. PARTITION BY CustomerId
  3. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  4. ORDER BY EventTime in OVER clause

Explanation

Correct Answer

C:

This ROWS window frame contains at most the current row and two prior ordered rows for moving calculations.

Incorrect Answers

A:

A ROWS frame defined this way forms a growing row-based window and gives predictable cumulative aggregation with a deterministic ORDER BY; the required capability here is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, which this operation does not supply.

B:

PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the alternative cannot substitute for ROWS BETWEEN 2 PRECEDING AND CURRENT ROW under the given constraints.

D:

ORDER BY inside OVER establishes the function's logical row ordering; the outer query's ORDER BY controls presentation separately; the missing behavior is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW; this mechanism does something else.

Question 132

A pull-request pipeline needs to compile declarative T-SQL into a DACPAC and fail when model references or target-platform syntax are invalid. What should be configured to meet this objective?

  1. `dotnet build` of SQL project
  2. Static analysis rule
  3. Target platform configuration
  4. Database reference

Explanation

Correct Answer

A:

Building an SDK-style SQL project validates its schema model and emits a DACPAC when the build succeeds.

Incorrect Answers

B:

SQL code analysis can inspect modeled schema and code against configured rules as part of validation, complementing runtime tests; it addresses a different step; the question calls for `dotnet build` of SQL project.

C:

SQL project target-platform settings influence build-time compatibility checks for supported database features and syntax; this differs from the `dotnet build` of SQL project requirement described in the scenario.

D:

Database references supply schema models for external dependencies and help SQL project builds validate cross-database object references; the required capability here is `dotnet build` of SQL project, which this operation does not supply.

Question 133

A query should produce one JSON array of retrieved document texts rather than manually append array brackets around a string. Which database mechanism should be chosen?

  1. STRING_AGG
  2. JSON_OBJECT
  3. FOR JSON PATH
  4. JSON_ARRAYAGG

Explanation

Correct Answer

D:

JSON_ARRAYAGG aggregates expressions into JSON arrays using supported SQL engine functionality.

Incorrect Answers

A:

STRING_AGG concatenates values across rows with a selected delimiter, subject to ordering and type considerations; the condition in the stem is resolved by JSON_ARRAYAGG, not by this feature alone.

B:

JSON_OBJECT composes named key-value JSON data without fragile manual quote and comma concatenation; the missing behavior is JSON_ARRAYAGG; this mechanism does something else.

C:

FOR JSON PATH transforms SQL query results into JSON structures using explicit alias-based paths; that function is not interchangeable with JSON_ARRAYAGG for the requested result.

Question 134

A query includes a precise part number, and strict literal match priority matters more than semantic expansion. Which option would you recommend?

  1. Weighted score normalization
  2. Reciprocal Rank Fusion
  3. Keyword-only ranking
  4. Vector-only ranking

Explanation

Correct Answer

C:

Lexical ranking rewards occurrences of specified terms, supporting exact identifiers that embeddings may not distinguish reliably.

Incorrect Answers

A:

Normalizing and weighting scores can combine modalities, but requires understanding score distributions and calibration; this differs from the Keyword-only ranking requirement described in the scenario.

B:

RRF adds reciprocal contributions based on each result's rank position, making lexical and vector rankings comparable without raw-score calibration; the required capability here is Keyword-only ranking, which this operation does not supply.

D:

Vector-only ranking uses embedding proximity and may miss exact special identifiers that lexical retrieval handles better; the condition in the stem is resolved by Keyword-only ranking, not by this feature alone.

Question 135

An incident review needs to determine which reviewed schema source version produced the deployed DACPAC for release 4.2. Which SQL capability is the most direct fit?

  1. Immutable release artifacts
  2. Source object definitions
  3. Environment-specific publish profile
  4. Git tags for releases

Explanation

Correct Answer

D:

Tags can mark repository states for releases, supporting traceability between commits, built artifacts, and deployments.

Incorrect Answers

A:

Artifact promotion preserves the reviewed DACPAC across environments, avoiding accidental rebuilds from later commits; that function is not interchangeable with Git tags for releases for the requested result.

B:

Declarative .sql object files in a SQL project provide a maintainable source of truth for modeled database objects; for the stated conditions, the deciding feature is Git tags for releases rather than this behavior.

C:

Environment configuration and publish profiles keep target-specific settings separate from the source schema definition; it addresses a different step; the question calls for Git tags for releases.

Question 136

Several related tables need to draw identifier values from one database object before rows are inserted. Which mechanism addresses the requirement?

  1. NEWID()
  2. SEQUENCE object
  3. ROWVERSION column
  4. IDENTITY property

Explanation

Correct Answer

B:

A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR.

Incorrect Answers

A:

NEWID produces a GUID-shaped uniqueidentifier; it is not a monotonically increasing table identity; the alternative cannot substitute for SEQUENCE object under the given constraints.

C:

ROWVERSION is an automatically changing binary token for row modifications, useful in equality comparisons for optimistic concurrency and not a clock timestamp; the missing behavior is SEQUENCE object; this mechanism does something else.

D:

IDENTITY automatically produces values for an inserted column, although applications must not assume gap-free numbering or transactional sequences; that function is not interchangeable with SEQUENCE object for the requested result.

Question 137

Every Shipment.OrderId must match a declared existing order identifier, even when inserted by bulk-processing code. Which technique applies to this scenario?

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

Explanation

Correct Answer

D:

Foreign-key constraints protect relational references independently of the application that writes data.

Incorrect Answers

A:

A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier; it addresses a different step; the question calls for FOREIGN KEY validation.

B:

A CHECK predicate evaluates the supplied value at write time and rejects rows that violate its Boolean condition; this differs from the FOREIGN KEY validation requirement described in the scenario.

C:

A DEFAULT expression can supply an insertion-time value when the column is not explicitly populated; the required capability here is FOREIGN KEY validation, which this operation does not supply.

Question 138

A code assistant changed a stored procedure, and the team needs to confirm expected outputs and transaction behavior before production release. What should be configured to meet this objective?

  1. Validate against target SQL dialect
  2. Review security privileges
  3. Run unit and integration tests
  4. Review generated query plans

Explanation

Correct Answer

C:

Automated tests exercise database contracts and interactions, detecting regressions that a plausible generated explanation may conceal.

Incorrect Answers

A:

Compiling and testing against the actual target platform identifies unsupported syntax or version-specific behaviors that prompt text cannot guarantee; the missing behavior is Run unit and integration tests; this mechanism does something else.

B:

Effective permission analysis identifies the required object-level rights rather than assuming broad database ownership is necessary; that function is not interchangeable with Run unit and integration tests for the requested result.

D:

Reviewing actual execution plans and representative workload metrics reveals access paths and cardinality issues that text generation alone cannot verify; it addresses a different step; the question calls for Run unit and integration tests.

Question 139

A transient staging table needs fast append-style loading and no primary clustering order is justified yet. Which approach should the team select?

  1. Clustered columnstore table
  2. Memory-optimized table
  3. Heap table
  4. Clustered rowstore table

Explanation

Correct Answer

C:

A heap has no clustered index and stores rows without a clustered key order, although secondary indexes can still be added.

Incorrect Answers

A:

A clustered columnstore provides compressed columnar storage for analytical scans; it is not tuned primarily around singleton row lookups; the required capability here is Heap table, which this operation does not supply.

B:

Memory-optimized tables use the In-Memory OLTP engine and specialized concurrency structures, distinct from disk-based rowstore indexes; the alternative cannot substitute for Heap table under the given constraints.

D:

A clustered rowstore index organizes records by its clustering key and suits indexed OLTP access patterns; the missing behavior is Heap table; this mechanism does something else.

Question 140

An assistant needs table and column names to write a join but must not receive any raw patient or payroll rows. What is the right solution for this specific requirement?

  1. Synthetic test data
  2. Parameterize generated SQL
  3. Schema metadata only
  4. Scoped temporary credentials

Explanation

Correct Answer

C:

Restricting context to schema metadata can support query drafting without exposing record contents to the model.

Incorrect Answers

A:

Synthetic records can reproduce structural and edge-case patterns while reducing the need to expose personal production data; for the stated conditions, the deciding feature is Schema metadata only rather than this behavior.

B:

Parameterized execution separates user data from executable SQL, reducing injection risk even if the initial query text was AI-assisted; it addresses a different step; the question calls for Schema metadata only.

D:

Short-lived, narrowly authorized credentials limit exposure relative to reusable administrative secrets embedded in tool configuration; the required capability here is Schema metadata only, which this operation does not supply.

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