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

Question 530

A table allows many inactive historical aliases but requires active aliases to be unique among active rows only. What should the developer implement?

  1. Composite natural key
  2. Foreign key to a candidate key
  3. Filtered index for sparse predicate
  4. Surrogate integer key
  5. Filtered unique index

Explanation

Correct Answer

E:

A filtered unique index enforces uniqueness within a predicate-defined subset, unlike a table-wide unique constraint.

Incorrect Answers

A:

A composite key enforces uniqueness over a column combination, matching a many-to-many association's natural identity; the missing behavior is Filtered unique index; this mechanism does something else.

B:

A foreign key may reference an eligible UNIQUE candidate key, preserving referential integrity without depending on the primary key specifically; that function is not interchangeable with Filtered unique index for the requested result.

C:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; for the stated conditions, the deciding feature is Filtered unique index rather than this behavior.

D:

A surrogate key decouples row identity from mutable business data and can support compact joins and stable references; it addresses a different step; the question calls for Filtered unique index.

Question 531

A prompt is assembled from an internal payroll query but the external model should receive only aggregated non-identifying information. Which choice best meets these constraints?

  1. Limit retrieved context
  2. Redact confidential fields
  3. Audit retrieved sources
  4. Retry transient model throttling

Explanation

Correct Answer

B:

Redaction and data minimization remove unnecessary secrets or identifiers before leaving the SQL trust boundary.

Incorrect Answers

A:

Limiting and ranking context passages keeps input within token budgets and reduces irrelevant material sent to the generator; the required capability here is Redact confidential fields, which this operation does not supply.

C:

Logging approved source identifiers and prompt metadata creates a traceable record for investigation without storing unrestricted sensitive content; the condition in the stem is resolved by Redact confidential fields, not by this feature alone.

D:

Rate-limit-aware retries and idempotent request handling reduce failures without overwhelming the inference service; the missing behavior is Redact confidential fields; this mechanism does something else.

Question 532

On a supporting SQL platform, developers want a reusable database object describing the embedding provider endpoint, model type, and credentials. What is the best primary mechanism to use?

  1. GRANT EXECUTE ON EXTERNAL MODEL
  2. DROP EXTERNAL MODEL
  3. CREATE EXTERNAL MODEL
  4. ALTER EXTERNAL MODEL

Explanation

Correct Answer

C:

CREATE EXTERNAL MODEL registers an external inference model definition for supported SQL AI functions rather than storing the model weights as table rows.

Incorrect Answers

A:

Executing an external model requires the appropriate model authorization, separable from general table SELECT permissions; for the stated conditions, the deciding feature is CREATE EXTERNAL MODEL rather than this behavior.

B:

DROP EXTERNAL MODEL removes the model metadata object once relevant dependencies and permissions allow it; it addresses a different step; the question calls for CREATE EXTERNAL MODEL.

D:

ALTER EXTERNAL MODEL changes the stored external model definition, subject to required permissions and supported options; the required capability here is CREATE EXTERNAL MODEL, which this operation does not supply.

Question 533

A search corpus has short descriptive titles and detailed body text; both contribute to whether a document matches a user query. What is the most suitable implementation?

  1. Title plus body
  2. Normalize noisy boilerplate
  3. Preserve source identifiers separately
  4. Exclude secret columns

Explanation

Correct Answer

A:

Including the title and relevant body in a controlled embedding input gives the representation both subject identity and contextual detail.

Incorrect Answers

B:

Removing repeated boilerplate from embedding inputs can increase the signal from content that differentiates documents; the missing behavior is Title plus body; this mechanism does something else.

C:

Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval; that function is not interchangeable with Title plus body for the requested result.

D:

Excluding secret or irrelevant columns reduces data exposure and prevents confidential values from affecting retrieval features; for the stated conditions, the deciding feature is Title plus body rather than this behavior.

Question 534

A graph question needs a shortest qualifying connection path between related nodes on a SQL graph implementation that supports recursive patterns. What is the most appropriate design decision?

  1. SHORTEST_PATH graph pattern
  2. MATCH graph predicate
  3. EXISTS subquery
  4. Regular INNER JOIN

Explanation

Correct Answer

A:

SHORTEST_PATH works with supported graph path patterns and evaluates a shortest qualifying traversal, not arbitrary relational sorting.

Incorrect Answers

B:

MATCH describes node-edge patterns for SQL graph queries rather than expressing every relationship as hand-written identifier joins; the required capability here is SHORTEST_PATH graph pattern, which this operation does not supply.

C:

EXISTS tests for the presence of qualifying related rows without multiplying outer rows by the number of matches; the alternative cannot substitute for SHORTEST_PATH graph pattern under the given constraints.

D:

INNER JOIN pairs rows based on a join condition and is the direct relational choice without graph schema requirements; the condition in the stem is resolved by SHORTEST_PATH graph pattern, not by this feature alone.

Question 535

Queries repeatedly target the small subset of unpaid invoices, and rows outside that subset should not occupy the secondary index. What is the most suitable implementation?

  1. Composite index key order
  2. Included index columns
  3. Filtered index predicate
  4. Index maintenance on fragmentation

Explanation

Correct Answer

C:

A WHERE filter on a nonclustered index narrows the indexed rowset to qualifying values when query predicates are compatible.

Incorrect Answers

A:

Leading index-key columns determine which predicates can support a seek; key order matters for equality and range access; that function is not interchangeable with Filtered index predicate for the requested result.

B:

INCLUDE columns can satisfy output projections without expanding the seek key, often making a nonclustered index covering; for the stated conditions, the deciding feature is Filtered index predicate rather than this behavior.

D:

Reorganizing or rebuilding appropriate indexes addresses physical fragmentation and may improve access efficiency, but should follow measured need; this differs from the Filtered index predicate requirement described in the scenario.

Question 536

A client already has an end cursor for a GraphQL connection and wants the next portion of a related entity collection. Which action is justified by these conditions?

  1. GraphQL pagination `after`
  2. DAB in-memory response cache
  3. `$first` page-size control
  4. DAB Redis cache

Explanation

Correct Answer

A:

A GraphQL after cursor continues pagination beyond a prior page, subject to the configured query shape.

Incorrect Answers

B:

DAB response caching can retain eligible read results for configured durations, subject to invalidation and consistency requirements; the condition in the stem is resolved by GraphQL pagination `after`, not by this feature alone.

C:

The first parameter requests the page size; it does not itself identify where the next page begins; the missing behavior is GraphQL pagination `after`; this mechanism does something else.

D:

A supported distributed Redis cache stores response entries outside individual runtime processes for multi-instance reuse; that function is not interchangeable with GraphQL pagination `after` for the requested result.

Question 537

Two API entities use a logical relationship that consumers should traverse through GraphQL, even though the API schema does not discover it automatically. Which approach should the team select?

  1. Stored-procedure entity
  2. Table-backed entity
  3. View-backed entity
  4. Explicit relationship configuration

Explanation

Correct Answer

D:

Configured relationships describe how exposed entities connect and control relational navigation in generated GraphQL schemas.

Incorrect Answers

A:

DAB can expose supported stored procedures as API entities with operation-specific configuration and permissions; it addresses a different step; the question calls for Explicit relationship configuration.

B:

A DAB entity can map to a table source with configured permissions for REST or GraphQL operations; this differs from the Explicit relationship configuration requirement described in the scenario.

C:

An entity can map to a supported SQL view, with write capabilities subject to view and DAB limitations; the required capability here is Explicit relationship configuration, which this operation does not supply.

Question 538

A support user should see a partially obscured telephone number when querying a permitted column, without changing stored bytes. Which choice best meets these constraints?

  1. Dynamic Data Masking
  2. Always Encrypted
  3. Row-Level Security
  4. Transparent Data Encryption

Explanation

Correct Answer

A:

Dynamic Data Masking alters query-result presentation for non-exempt users; it is not encryption or a strong isolation boundary for untrusted querying.

Incorrect Answers

B:

Always Encrypted protects selected column values with client-side cryptography and keys outside ordinary server access, subject to query-operation limitations; that function is not interchangeable with Dynamic Data Masking for the requested result.

C:

Row-Level Security applies predicate logic inside the database to filter or block access to rows according to security context; for the stated conditions, the deciding feature is Dynamic Data Masking rather than this behavior.

D:

Transparent Data Encryption encrypts database storage at rest and decrypts pages for authorized engine operations; it does not hide plaintext from the engine; it addresses a different step; the question calls for Dynamic Data Masking.

Question 539

A reusable stored procedure should filter customer orders based on a CustomerId supplied by its caller. Which SQL capability is the most direct fit?

  1. RETURN status code
  2. Input parameter
  3. Result set from SELECT
  4. OUTPUT parameter

Explanation

Correct Answer

B:

An input parameter supplies a value for the procedure's execution without embedding a different literal in every call.

Incorrect Answers

A:

A stored procedure RETURN value is an integer status mechanism, conventionally used for control or result codes; the required capability here is Input parameter, which this operation does not supply.

C:

Executing SELECT within a procedure returns a result set that clients can consume, unlike an integer-only return code; the condition in the stem is resolved by Input parameter, not by this feature alone.

D:

An OUTPUT parameter returns a variable value to the caller when the invocation and parameter declaration allow output; the missing behavior is Input parameter; this mechanism does something else.

Question 540

A filter needs customers with any matching chargeback while avoiding duplicated customers caused by a join to multiple chargebacks. Which feature would solve the stated problem?

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

Explanation

Correct Answer

A:

EXISTS expresses a presence test and does not expand the outer result into one row per matching child.

Incorrect Answers

B:

Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases; it addresses a different step; the question calls for EXISTS semi-join.

C:

TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match; this differs from the EXISTS semi-join requirement described in the scenario.

D:

A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column; the required capability here is EXISTS semi-join, which this operation does not supply.

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