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

Question 510

A team must investigate whether a secondary index receives many reads relative to its update overhead before deciding to retain it. What is the most suitable implementation?

  1. sys.dm_os_wait_stats
  2. sys.dm_exec_requests
  3. sys.dm_exec_query_stats
  4. sys.dm_db_index_usage_stats

Explanation

Correct Answer

D:

Index usage DMVs provide counters for seeks, scans, lookups, and updates, with reset and interpretation limitations.

Incorrect Answers

A:

sys.dm_os_wait_stats aggregates wait information at the server scope and must be interpreted alongside workload context; this differs from the sys.dm_db_index_usage_stats requirement described in the scenario.

B:

sys.dm_exec_requests exposes live request information such as waits and blocking identifiers, rather than long-term history; the required capability here is sys.dm_db_index_usage_stats, which this operation does not supply.

C:

sys.dm_exec_query_stats exposes aggregate statistics for queries represented in the plan cache, subject to cache eviction; the alternative cannot substitute for sys.dm_db_index_usage_stats under the given constraints.

Question 511

Customer-care workers should read a narrow approved projection but not gain unrestricted reads against underlying sensitive tables. Which choice best meets these constraints?

  1. RLS block predicate
  2. RLS filter predicate
  3. GRANT EXECUTE
  4. GRANT SELECT on view
  5. Masked column definition

Explanation

Correct Answer

D:

Object-level SELECT permissions can be granted on the view while base-table privileges remain constrained, with ownership chaining considered.

Incorrect Answers

A:

An RLS block predicate can reject changes that would violate a row-access policy during specified DML operations; that function is not interchangeable with GRANT SELECT on view for the requested result.

B:

An RLS filter predicate limits visible rows for eligible operations at the table's security-policy boundary; for the stated conditions, the deciding feature is GRANT SELECT on view rather than this behavior.

C:

EXECUTE allows a principal to invoke a stored procedure and can be paired with safe module ownership or execution-context design; it addresses a different step; the question calls for GRANT SELECT on view.

E:

A masking function on the column changes the result shown to principals lacking UNMASK, leaving the underlying value unchanged; the required capability here is GRANT SELECT on view, which this operation does not supply.

Question 512

A cleanup report needs devices for which no matching active warranty row exists, including when the other table contains NULLs. What is the best primary mechanism to use?

  1. CROSS APPLY
  2. Scalar correlated subquery
  3. EXISTS correlated subquery
  4. NOT EXISTS correlated subquery

Explanation

Correct Answer

D:

NOT EXISTS uses an anti-semi-join existence test and avoids the problematic NULL behavior of some NOT IN subqueries.

Incorrect Answers

A:

CROSS APPLY evaluates a table expression per outer row and includes only outer rows whose applied expression yields results; the alternative cannot substitute for NOT EXISTS correlated subquery under the given constraints.

B:

A scalar correlated subquery can compute one related value per outer row, provided the expression returns at most one value; the condition in the stem is resolved by NOT EXISTS correlated subquery, not by this feature alone.

C:

A correlated EXISTS predicate evaluates whether a related row exists for each outer record without materializing duplicate outer records; the missing behavior is NOT EXISTS correlated subquery; this mechanism does something else.

Question 513

A SELECT should list each product only once if at least one matching inventory row exists, avoiding one result per inventory row. Which technique applies to this scenario?

  1. EXISTS subquery
  2. LEFT JOIN without child filter
  3. Regular INNER JOIN
  4. SHORTEST_PATH graph pattern

Explanation

Correct Answer

A:

EXISTS tests for the presence of qualifying related rows without multiplying outer rows by the number of matches.

Incorrect Answers

B:

An unfiltered LEFT JOIN can preserve products without inventory and can multiply rows for products with many inventory records; it is not a semi-join.

C:

INNER JOIN pairs rows based on a join condition and is the direct relational choice without graph schema requirements; the required capability here is EXISTS subquery, which this operation does not supply.

D:

SHORTEST_PATH works with supported graph path patterns and evaluates a shortest qualifying traversal, not arbitrary relational sorting; the alternative cannot substitute for EXISTS subquery under the given constraints.

Question 514

Copilot-generated T-SQL attempts to query a restricted Fabric SQL table; the caller lacks permissions and must not gain them from generated code. What is the most suitable implementation?

  1. SQL endpoint authorization
  2. Fabric tenant feature settings
  3. Copilot in Fabric chat
  4. Copilot instruction file

Explanation

Correct Answer

A:

Database authorization still applies when queries are proposed by Copilot, so appropriate grants are required separately.

Incorrect Answers

B:

Tenant-level Fabric settings can govern Copilot availability, with scope and capacity prerequisites depending on the specific feature; that function is not interchangeable with SQL endpoint authorization for the requested result.

C:

Microsoft Copilot in Fabric offers AI-assisted authoring in supported Fabric workloads and remains subject to tenant and capacity configuration; for the stated conditions, the deciding feature is SQL endpoint authorization rather than this behavior.

D:

Instructions can guide SQL generation but do not grant permissions that the actual SQL/Fabric endpoint denies to the executing principal.

Question 515

An external model definition must identify the HTTPS inference endpoint that SQL will call for embeddings. Which option satisfies the desired behavior most directly?

  1. API_FORMAT parameter
  2. MODEL_TYPE = EMBEDDINGS
  3. DATABASE SCOPED CREDENTIAL
  4. LOCATION parameter
  5. DROP EXTERNAL MODEL

Explanation

Correct Answer

D:

LOCATION records the target service URI in the external model definition, with provider formatting and TLS constraints.

Incorrect Answers

A:

API_FORMAT identifies the provider-specific request and response contract used by the external model integration; the required capability here is LOCATION parameter, which this operation does not supply.

B:

The embeddings model type indicates that the external definition produces vector representations from supported text input; the alternative cannot substitute for LOCATION parameter under the given constraints.

C:

A database-scoped credential stores or references endpoint authentication data subject to permission and endpoint matching rules; the condition in the stem is resolved by LOCATION parameter, not by this feature alone.

E:

DROP EXTERNAL MODEL removes the model metadata object once relevant dependencies and permissions allow it; that function is not interchangeable with LOCATION parameter for the requested result.

Question 516

Two procedures acquire locks on Accounts and Balances in opposite orders, causing repeatable deadlocks during peak processing. Which technique applies to this scenario?

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

Explanation

Correct Answer

A:

Accessing shared objects in a consistent order can prevent a circular lock acquisition pattern across concurrent transactions.

Incorrect Answers

B:

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

C:

A deadlock graph shows participants, resources, and the victim selection, clarifying the cyclic dependency; this differs from the Consistent object access order requirement described in the scenario.

D:

Analyzing blocking sessions and waits identifies which session owns the incompatible lock that others need; the required capability here is Consistent object access order, which this operation does not supply.

Question 517

A dashboard wants a server-computed count and sum for a permitted filtered collection using supported DAB GraphQL aggregate operations. Which database mechanism should be chosen?

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

Explanation

Correct Answer

C:

Supported GraphQL aggregation fields calculate grouped or overall measures through the API instead of requiring all underlying rows at the client.

Incorrect Answers

A:

GraphQL mutations express write operations exposed by the configured schema and authorized by DAB roles; the condition in the stem is resolved by GraphQL aggregation, not by this feature alone.

B:

Configured GraphQL relationships permit nested traversal between related API entities within one query document; the missing behavior is GraphQL aggregation; this mechanism does something else.

D:

GraphQL projections let callers request only specified fields in a typed query, subject to endpoint permissions; for the stated conditions, the deciding feature is GraphQL aggregation rather than this behavior.

Question 518

A high-volume search application has tight per-request cost and latency budgets and preliminary tests show adequate retrieval quality with a compact model. Which SQL capability is the most direct fit?

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

Explanation

Correct Answer

D:

A smaller embedding model can reduce inference cost and latency, provided measured retrieval quality remains acceptable.

Incorrect Answers

A:

A larger-capacity model may improve representation on difficult content, but benefits must be verified against the target dataset; this differs from the Smaller embedding model requirement described in the scenario.

B:

Load testing reveals throughput, rate-limit, and response-time behavior under the deployment's expected concurrency; the required capability here is Smaller embedding model, which this operation does not supply.

C:

A labeled evaluation set measures real retrieval quality, preventing selection based solely on generic benchmark or model name; the alternative cannot substitute for Smaller embedding model under the given constraints.

Question 519

After publishing a release to staging, the team must verify that the application can call a representative query and routine successfully. What is the best primary mechanism to use?

  1. Integration test
  2. Unit test
  3. Deployment smoke test
  4. Dry-run deployment script
  5. Build-time schema validation

Explanation

Correct Answer

C:

A smoke test checks critical post-deployment operations in the target environment and complements static compilation.

Incorrect Answers

A:

An integration test exercises interactions among database objects and dependent components rather than one isolated expression; that function is not interchangeable with Deployment smoke test for the requested result.

B:

A unit test validates one small programmatic behavior against expected results, catching logic regressions early; for the stated conditions, the deciding feature is Deployment smoke test rather than this behavior.

D:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; this differs from the Deployment smoke test requirement described in the scenario.

E:

Building a database project checks supported schema references and model consistency to catch missing dependencies; the required capability here is Deployment smoke test, which this operation does not supply.

Question 520

A developer wants managed Azure SQL portal charts showing top queries by resource use during a known slow period. Which feature would solve the stated problem?

  1. Azure Monitor metrics
  2. Deadlock retry policy
  3. Log Analytics diagnostic queries
  4. Read/write index tradeoff
  5. Azure SQL Query Performance Insight
  6. Query Store runtime report

Explanation

Correct Answer

E:

Query Performance Insight provides a focused view of captured SQL query performance metrics and historical trends.

Incorrect Answers

A:

Azure Monitor resource metrics expose time-series consumption and pressure signals for capacity planning, but do not replace Query Store for statement-level plan diagnosis; the alternative cannot substitute for Azure SQL Query Performance Insight under the given constraints.

B:

Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects; the condition in the stem is resolved by Azure SQL Query Performance Insight, not by this feature alone.

C:

A Log Analytics workspace stores forwarded log records and supports cross-resource KQL analysis where diagnostics are configured; the missing behavior is Azure SQL Query Performance Insight; this mechanism does something else.

D:

Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification; that function is not interchangeable with Azure SQL Query Performance Insight for the requested result.

F:

Query Store retains statement-level plan and runtime aggregates suitable for comparing the same query across intervals; it addresses a different step; the question calls for Azure SQL Query Performance Insight.

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