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

Question 50

A telemetry record should be expanded through OPENJSON into multiple sample rows, excluding source rows whose expansion is empty. What is the most appropriate design decision?

  1. LEFT JOIN
  2. OUTER APPLY
  3. CROSS APPLY
  4. INNER JOIN

Explanation

Correct Answer

C:

CROSS APPLY joins each input row to the output of a correlated rowset expression, without retaining empty expansions.

Incorrect Answers

A:

A LEFT OUTER JOIN preserves each left-side row and supplies NULL for missing right-side matches; the missing behavior is CROSS APPLY; this mechanism does something else.

B:

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

D:

An INNER JOIN returns qualifying pairs only and excludes rows lacking a match under its ON predicate; it addresses a different step; the question calls for CROSS APPLY.

Question 51

A high-volume order table is normally retrieved by an increasing order identifier; developers want the underlying rowstore rows organized by that key for narrow seeks. What is the most suitable implementation?

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

Explanation

Correct Answer

A:

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.

Incorrect Answers

B:

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; the alternative cannot substitute for Clustered rowstore index under the given constraints.

C:

Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets; the condition in the stem is resolved by Clustered rowstore index, not by this feature alone.

D:

A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries; the missing behavior is Clustered rowstore index; this mechanism does something else.

Question 52

A reporting query intentionally consumes most rows from an index and sequential traversal is more efficient than many seeks. Which approach should the team select?

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

Explanation

Correct Answer

A:

An Index Scan walks a substantial part of an index and can be appropriate for broad result sets.

Incorrect Answers

B:

A Key Lookup obtains missing columns from the clustered data using keys retrieved from another index; covering may reduce this work; it addresses a different step; the question calls for Index Scan.

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 Scan requirement described in the scenario.

D:

An Index Seek uses an index access path to locate qualifying key ranges, though residual predicates may still filter rows; the required capability here is Index Scan, which this operation does not supply.

Question 53

A government-issued document reference must be nonduplicated across accounts while AccountId remains the primary key. What should be configured to meet this objective?

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

Explanation

Correct Answer

C:

A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier.

Incorrect Answers

A:

Foreign-key constraints protect relational references independently of the application that writes data; the condition in the stem is resolved by UNIQUE alternate identifier, not by this feature alone.

B:

A CHECK predicate evaluates the supplied value at write time and rejects rows that violate its Boolean condition; the missing behavior is UNIQUE alternate identifier; this mechanism does something else.

D:

A DEFAULT expression can supply an insertion-time value when the column is not explicitly populated; for the stated conditions, the deciding feature is UNIQUE alternate identifier rather than this behavior.

Question 54

A DAB configuration exists, but Products must now be exposed as a new named API entity backed by a database table. Which technique applies to this scenario?

  1. `dab validate`
  2. `dab init`
  3. `dab start`
  4. `dab add`

Explanation

Correct Answer

D:

The add command registers an entity and its source and permissions in a DAB configuration.

Incorrect Answers

A:

The validate command checks a DAB configuration against supported validation rules without serving the application; this differs from the `dab add` requirement described in the scenario.

B:

The init command scaffolds an initial DAB runtime configuration containing the chosen data-source and endpoint settings; the required capability here is `dab add`, which this operation does not supply.

C:

The start command runs the configured DAB service and hosts enabled endpoints for requests; the alternative cannot substitute for `dab add` under the given constraints.

Question 55

A GraphQL client wants each product's Id and Name but no Price or Description fields in that response. Which feature would solve the stated problem?

  1. GraphQL aggregation
  2. GraphQL mutation
  3. GraphQL field selection
  4. GraphQL relationship navigation

Explanation

Correct Answer

C:

GraphQL projections let callers request only specified fields in a typed query, subject to endpoint permissions.

Incorrect Answers

A:

Supported GraphQL aggregation fields calculate grouped or overall measures through the API instead of requiring all underlying rows at the client; that function is not interchangeable with GraphQL field selection for the requested result.

B:

GraphQL mutations express write operations exposed by the configured schema and authorized by DAB roles; for the stated conditions, the deciding feature is GraphQL field selection rather than this behavior.

D:

Configured GraphQL relationships permit nested traversal between related API entities within one query document; this differs from the GraphQL field selection requirement described in the scenario.

Question 56

A parameterized SELECT should be reusable in joins and expose a single relational query definition to the optimizer. Which SQL capability is the most direct fit?

  1. Scalar function
  2. Multi-statement table-valued function
  3. Stored procedure
  4. Inline table-valued function

Explanation

Correct Answer

D:

An inline TVF returns the result of a single SELECT definition and is commonly optimized similarly to a parameterized view.

Incorrect Answers

A:

A scalar UDF returns a single scalar value, although calling patterns and implementation determine its performance; the alternative cannot substitute for Inline table-valued function under the given constraints.

B:

A multi-statement TVF declares a table return variable and populates it with multiple statements, subject to different optimizer behavior than inline TVFs; the condition in the stem is resolved by Inline table-valued function, not by this feature alone.

C:

A stored procedure is an executable unit suited to procedural database operations; it is not directly a relational table expression; the missing behavior is Inline table-valued function; this mechanism does something else.

Question 57

A containerized API needs to connect to Azure SQL using a workload identity rather than a hard-coded administrative password. Which mechanism addresses the requirement?

  1. TLS termination
  2. Managed identity to database
  3. Validate JWT audience
  4. Per-role API operation policy

Explanation

Correct Answer

B:

A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately.

Incorrect Answers

A:

TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations; it addresses a different step; the question calls for Managed identity to database.

C:

Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint; the required capability here is Managed identity to database, which this operation does not supply.

D:

Role-level operation restrictions at the API boundary can separate read capabilities from write mutations without confusing authentication with authorization; the alternative cannot substitute for Managed identity to database under the given constraints.

Question 58

SQL Server terminated one of two transactions because both held locks needed by the other; the DBA needs the resource dependency cycle. Which approach should the team select?

  1. Consistent object access order
  2. Deadlock graph
  3. Blocking chain inspection
  4. Shorten transaction duration

Explanation

Correct Answer

B:

A deadlock graph shows participants, resources, and the victim selection, clarifying the cyclic dependency.

Incorrect Answers

A:

Accessing shared objects in a consistent order can prevent a circular lock acquisition pattern across concurrent transactions; the missing behavior is Deadlock graph; this mechanism does something else.

C:

Analyzing blocking sessions and waits identifies which session owns the incompatible lock that others need; for the stated conditions, the deciding feature is Deadlock graph rather than this behavior.

D:

Moving slow external work outside short database transactions reduces lock retention and contention windows; it addresses a different step; the question calls for Deadlock graph.

Question 59

Search must find a Spanish customer question relevant to an English technical paragraph without requiring manually translated keywords. Which option would you recommend?

  1. Text embedding model
  2. Multilingual embedding model
  3. Multimodal model
  4. Structured-output language model

Explanation

Correct Answer

B:

A multilingual embedding model can represent semantically related text across supported languages in a shared vector space.

Incorrect Answers

A:

An embedding model maps input content to numeric vectors for semantic comparison rather than producing a conversational answer; the required capability here is Multilingual embedding model, which this operation does not supply.

C:

A multimodal model handles supported image and text inputs together, unlike text-only embedding or generation models; the condition in the stem is resolved by Multilingual embedding model, not by this feature alone.

D:

A generation model with schema-constrained structured output can produce predictable field layouts for downstream validation; the missing behavior is Multilingual embedding model; this mechanism does something else.

Question 60

A correctness benchmark must rank all filtered vectors precisely even though an ANN index is present. Which option satisfies the desired behavior most directly?

  1. VECTOR_DISTANCE exact ordering
  2. SELECT TOP (N) WITH APPROXIMATE
  3. CREATE VECTOR INDEX
  4. FORCE_ANN_ONLY hint

Explanation

Correct Answer

A:

Sorting by VECTOR_DISTANCE calculates exact distances and can benchmark recall against indexed approximate results.

Incorrect Answers

B:

TOP with APPROXIMATE and an ordered vector-search distance permits eligible approximate index-based retrieval in current supported versions; it addresses a different step; the question calls for VECTOR_DISTANCE exact ordering.

C:

CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; this differs from the VECTOR_DISTANCE exact ordering requirement described in the scenario.

D:

FORCE_ANN_ONLY requests the approximate index strategy and has prerequisites including an appropriate index and approximate top-k query; the required capability here is VECTOR_DISTANCE exact ordering, which this operation does not supply.

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