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

Question 430

Each order stores a JSON items array and the query must join each extracted item back to its source OrderId. Which database mechanism should be chosen?

  1. CROSS APPLY OPENJSON
  2. FOR JSON PATH
  3. JSON_ARRAYAGG
  4. JSON_OBJECT

Explanation

Correct Answer

A:

CROSS APPLY with OPENJSON produces relational item rows correlated to their source order records.

Incorrect Answers

B:

FOR JSON PATH converts SELECT output into a path-controlled JSON representation, often suitable for API responses; the required capability here is CROSS APPLY OPENJSON, which this operation does not supply.

C:

JSON_ARRAYAGG constructs an aggregate JSON array from group members rather than returning multiple relational rows; the alternative cannot substitute for CROSS APPLY OPENJSON under the given constraints.

D:

JSON_OBJECT creates a named-property JSON structure from scalar values and other JSON expressions; the condition in the stem is resolved by CROSS APPLY OPENJSON, not by this feature alone.

Question 431

Query duration is high and plan metrics show heavy CPU in complex expressions rather than read latency; the team needs CPU-focused investigation. What is the most appropriate design decision?

  1. CPU-bound query analysis
  2. I/O latency monitoring
  3. Memory grant spill review
  4. Wait-type analysis

Explanation

Correct Answer

A:

CPU usage and operator analysis reveal expensive computations or inefficient row processing independently of storage wait times.

Incorrect Answers

B:

I/O metrics and file latency telemetry distinguish storage pressure and physical read cost from other query delays; for the stated conditions, the deciding feature is CPU-bound query analysis rather than this behavior.

C:

Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb; it addresses a different step; the question calls for CPU-bound query analysis.

D:

Categorizing waits helps identify the contended resource, but wait evidence should be correlated with relevant sessions and plans; this differs from the CPU-bound query analysis requirement described in the scenario.

Question 432

A service account needs to run dbo.CloseCase, but the DBA does not want to grant general UPDATE privileges on the Cases table. What is the most appropriate design decision?

  1. REVOKE permission
  2. GRANT SELECT on schema
  3. GRANT EXECUTE
  4. DENY permission

Explanation

Correct Answer

C:

EXECUTE allows a principal to invoke a stored procedure and can be paired with safe module ownership or execution-context design.

Incorrect Answers

A:

REVOKE removes a previously granted or denied permission entry and allows remaining permissions to determine effective access; the alternative cannot substitute for GRANT EXECUTE under the given constraints.

B:

Schema-level SELECT grants apply to eligible objects within the specified schema scope, reducing per-object grant administration; the condition in the stem is resolved by GRANT EXECUTE, not by this feature alone.

D:

DENY explicitly prevents the named permission, subject to SQL Server's permission-resolution behavior and exceptions; that function is not interchangeable with GRANT EXECUTE for the requested result.

Question 433

API operators need request durations, failures, and distributed request tracing for the DAB-hosted application layer. What is the best primary mechanism to use?

  1. Query Store
  2. Azure Monitor metrics
  3. Application Insights
  4. Log Analytics

Explanation

Correct Answer

C:

Application Insights collects application telemetry and supports investigation of failed or slow service requests.

Incorrect Answers

A:

Query Store retains query plans and runtime histories, allowing statement-level regression comparison and supported plan-forcing remediation that generic request tracing lacks; it addresses a different step; the question calls for Application Insights.

B:

Azure Monitor captures supported resource metrics and evaluates metric-based alert rules against them; this differs from the Application Insights requirement described in the scenario.

D:

Log Analytics provides searchable aggregated telemetry when the relevant services emit data to that workspace; the alternative cannot substitute for Application Insights under the given constraints.

Question 434

A support bot needs to reward systems that put the first correct answer near rank one, not merely anywhere in a large result list. Which SQL capability is the most direct fit?

  1. P95 query latency
  2. Precision@5 metric
  3. Recall@10 metric
  4. MRR metric

Explanation

Correct Answer

D:

Mean reciprocal rank averages the inverse position of the first relevant hit across test queries.

Incorrect Answers

A:

The 95th-percentile latency captures slow-tail requests and exposes variability that mean response time conceals; the missing behavior is MRR metric; this mechanism does something else.

B:

Precision@5 quantifies relevance density among the top five, helping assess noisy retrieval; that function is not interchangeable with MRR metric for the requested result.

C:

Recall@10 measures how many relevant targets are covered by the top ten results compared with the known relevant set; for the stated conditions, the deciding feature is MRR metric rather than this behavior.

Question 435

Retrieval should return only documents tagged `de-DE` for a German-only compliance assistant despite similar English text nearby. Which choice best meets these constraints?

  1. Filter language metadata
  2. Filter published state
  3. Enforce tenant predicate
  4. P95 query latency
  5. Apply effective-date policy

Explanation

Correct Answer

A:

A language metadata predicate constrains the candidate corpus to the required locale before final answer construction.

Incorrect Answers

B:

A published-state predicate prevents unapproved drafts from entering the context supplied to the model; the alternative cannot substitute for Filter language metadata under the given constraints.

C:

Authorization-aware filtering restricts eligible search rows independently of similarity ranking and should be enforced server-side; the condition in the stem is resolved by Filter language metadata, not by this feature alone.

D:

The 95th-percentile latency captures slow-tail requests and exposes variability that mean response time conceals; the missing behavior is Filter language metadata; this mechanism does something else.

E:

A date-range eligibility predicate excludes outdated or future policies independently of embedding quality; that function is not interchangeable with Filter language metadata for the requested result.

Question 436

An API endpoint accepts bearer tokens, but only tokens with an approved audience and role should permit restricted operations. Which SQL capability is the most direct fit?

  1. Scoped token authorization
  2. Azure Key Vault
  3. Managed identity for model endpoint
  4. HTTPS/TLS endpoint

Explanation

Correct Answer

A:

Server-side token validation and authorization check claims and permissions rather than treating any well-formed bearer token as sufficient.

Incorrect Answers

B:

Key Vault secures secrets and cryptographic materials through authenticated access and audit controls instead of embedding values in repository files; it addresses a different step; the question calls for Scoped token authorization.

C:

A managed identity can obtain service tokens for supported model endpoints when assigned the appropriate resource permissions; this differs from the Scoped token authorization requirement described in the scenario.

D:

TLS encrypts data in transit and authenticates the server endpoint, distinct from authorization to specific database rows; the required capability here is Scoped token authorization, which this operation does not supply.

Question 437

Governance automation needs a machine-readable report describing planned DACPAC changes for policy analysis. Which technique applies to this scenario?

  1. Extract action
  2. SqlPackage Script action
  3. DeployReport action
  4. Script action

Explanation

Correct Answer

C:

DeployReport emits an XML description of deployment changes rather than applying them directly.

Incorrect Answers

A:

Extract captures a database schema into a package suitable for inspection and project initialization, without being a full table-data export; the condition in the stem is resolved by DeployReport action, not by this feature alone.

B:

A generated deployment SQL script describes executable operations; it is not the requested machine-readable XML deployment report.

D:

Script produces the deployment operations as SQL text for inspection before a separately authorized execution; for the stated conditions, the deciding feature is DeployReport action rather than this behavior.

Question 438

A report needs totals by Region, by Product, and overall in one aggregation query without multiple unions. Which database mechanism should be chosen?

  1. HAVING
  2. JSON_OBJECT
  3. SUM(...) OVER
  4. COUNT(DISTINCT CustomerId)
  5. GROUPING SETS

Explanation

Correct Answer

E:

GROUPING SETS explicitly requests several grouping levels in a single grouped SELECT.

Incorrect Answers

A:

HAVING filters aggregated groups after grouping, unlike a row-level WHERE filter; this differs from the GROUPING SETS requirement described in the scenario.

B:

JSON_OBJECT creates a named-property JSON structure from scalar values and other JSON expressions; the required capability here is GROUPING SETS, which this operation does not supply.

C:

A window aggregate computes over partitions while maintaining input-row granularity rather than grouping detail away; the alternative cannot substitute for GROUPING SETS under the given constraints.

D:

COUNT(DISTINCT ...) counts distinct non-NULL values in a group or qualifying result set; the condition in the stem is resolved by GROUPING SETS, not by this feature alone.

Question 439

An application calls a procedure that accesses same-owner tables, and the DBA wants to understand when underlying object permissions are not separately checked. What is the most suitable implementation?

  1. Direct GRANT SELECT on base tables
  2. Module signing
  3. EXECUTE permission
  4. Ownership chaining

Explanation

Correct Answer

D:

Ownership chaining can allow access through a module when ownership and permission conditions are satisfied; cross-owner paths are different.

Incorrect Answers

A:

Direct SELECT grants require callers to hold permissions on the base objects, rather than relying on an intact same-owner ownership chain through the procedure.

B:

Module signing can associate certificate-based permissions with module execution, avoiding blanket grants to all application principals; for the stated conditions, the deciding feature is Ownership chaining rather than this behavior.

C:

Granting EXECUTE on the procedure permits calling that module without granting unrestricted base-table DML, subject to ownership and execution context; it addresses a different step; the question calls for Ownership chaining.

Question 440

A reconciliation SELECT has a complex intermediate result that should be given a readable name for use within one following SQL statement. What is the right solution for this specific requirement?

  1. Recursive CTE
  2. Anchor member
  3. MAXRECURSION query hint
  4. Nonrecursive CTE

Explanation

Correct Answer

D:

A nonrecursive common table expression defines a named query expression in a WITH clause, scoped to the immediately following statement.

Incorrect Answers

A:

A recursive CTE combines anchor and recursive members to iteratively produce reachable levels of a hierarchy; the alternative cannot substitute for Nonrecursive CTE under the given constraints.

B:

The anchor member emits the initial rows of a recursive CTE; those rows seed later recursive expansion; the condition in the stem is resolved by Nonrecursive CTE, not by this feature alone.

C:

OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion; the missing behavior is Nonrecursive CTE; this mechanism does something else.

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