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

Question 300

A public service needs one controlled order-cancellation operation without arbitrary SQL execution against the underlying database. What is the right solution for this specific requirement?

  1. Restricted DAB entity permissions
  2. Stored procedure execution boundary
  3. Parameterized SQL statements
  4. Allowlisted MCP tools

Explanation

Correct Answer

B:

Exposing a reviewed procedure with EXECUTE permission provides a constrained interface compared with granting direct table writes.

Incorrect Answers

A:

DAB entity permissions can limit operations exposed to each role, complementing database-side permissions; for the stated conditions, the deciding feature is Stored procedure execution boundary rather than this behavior.

C:

SQL parameters separate supplied values from executable query syntax and are a core mitigation against injection; this differs from the Stored procedure execution boundary requirement described in the scenario.

D:

An allowlisted set of restricted MCP tools reduces agent capabilities; prompt instructions cannot substitute for backend enforcement; the required capability here is Stored procedure execution boundary, which this operation does not supply.

Question 301

A specific transaction must maintain one consistent snapshot across multiple statements and the database already supports snapshot isolation. Which SQL capability is the most direct fit?

  1. READ COMMITTED locking
  2. SNAPSHOT isolation
  3. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  4. READ_COMMITTED_SNAPSHOT
  5. SERIALIZABLE

Explanation

Correct Answer

C:

SNAPSHOT isolation gives a version-consistent transaction-level view, unlike statement-level read-committed snapshots.

Incorrect Answers

A:

Locking READ COMMITTED prevents dirty reads but readers can block behind writers according to lock acquisition; the condition in the stem is resolved by SET TRANSACTION ISOLATION LEVEL SNAPSHOT, not by this feature alone.

B:

SNAPSHOT uses transaction-level row versions and checks write conflicts when updating rows another transaction has changed after the snapshot began, so the caller may need to retry; the missing behavior is SET TRANSACTION ISOLATION LEVEL SNAPSHOT; this mechanism does something else.

D:

Enabling READ_COMMITTED_SNAPSHOT changes the database's READ COMMITTED read behavior to statement-level row-version snapshots; for the stated conditions, the deciding feature is SET TRANSACTION ISOLATION LEVEL SNAPSHOT rather than this behavior.

E:

SERIALIZABLE applies stronger range-protection behavior at the cost of concurrency, especially for indexed predicates; it addresses a different step; the question calls for SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Question 302

An embedding audit pipeline must process detailed insert, update, and delete events with operation history rather than only changed row IDs. What is the right solution for this specific requirement?

  1. Scheduled full rebuild
  2. Azure Functions SQL trigger
  3. Change Tracking
  4. Change Data Capture

Explanation

Correct Answer

D:

CDC records change operations and row details for configured tables, supporting event processing and replay.

Incorrect Answers

A:

A controlled rebuild recomputes vectors across the corpus, often into a new index or version, rather than mixing incompatible embedding spaces; this differs from the Change Data Capture requirement described in the scenario.

B:

The SQL trigger binding can invoke a function for detected database changes and let the function handle inference and vector updates; the required capability here is Change Data Capture, which this operation does not supply.

C:

Change Tracking identifies changed rows and versions efficiently, making it useful for incremental re-embedding without storing full change history; the alternative cannot substitute for Change Data Capture under the given constraints.

Question 303

Investigators want a cryptographic digest they can compare with retained ledger records to detect subsequent unauthorized changes. What is the most appropriate design decision?

  1. Ledger digests
  2. Graph edge records
  3. Memory-optimized OLTP row
  4. System-versioned history retention

Explanation

Correct Answer

A:

Ledger digests help establish tamper-evidence for ledger content; retaining them securely is key to later verification.

Incorrect Answers

B:

Edge tables encode graph relationships between node instances and support relationship-centric queries; for the stated conditions, the deciding feature is Ledger digests rather than this behavior.

C:

Memory-optimized tables use a specialized engine and must follow feature and schema restrictions rather than conventional disk-based indexing; it addresses a different step; the question calls for Ledger digests.

D:

System-versioned temporal storage records prior row versions in a history table while maintaining a current table; this differs from the Ledger digests requirement described in the scenario.

Question 304

Every department should appear even if its parameterized recent-activity query returns no rows, with NULLs for absent activity. What is the best primary mechanism to use?

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

Explanation

Correct Answer

B:

OUTER APPLY preserves unmatched outer rows while evaluating a correlated table expression for each one.

Incorrect Answers

A:

A LEFT OUTER JOIN preserves each left-side row and supplies NULL for missing right-side matches; the alternative cannot substitute for OUTER APPLY under the given constraints.

C:

An INNER JOIN returns qualifying pairs only and excludes rows lacking a match under its ON predicate; the missing behavior is OUTER APPLY; this mechanism does something else.

D:

CROSS APPLY joins each input row to the output of a correlated rowset expression, without retaining empty expansions; that function is not interchangeable with OUTER APPLY for the requested result.

Question 305

A team wants a managed environment for a containerized DAB API with scaling and ingress configuration, without operating Kubernetes directly. Which option satisfies the desired behavior most directly?

  1. Azure App Service
  2. Azure Container Apps
  3. Local DAB runtime
  4. Azure Kubernetes Service

Explanation

Correct Answer

B:

Azure Container Apps can host a DAB container with managed ingress and scaling suitable for event-driven or API workloads.

Incorrect Answers

A:

App Service provides managed web hosting and deployment features for supported DAB deployment patterns; it addresses a different step; the question calls for Azure Container Apps.

C:

Running DAB locally supports iterative testing and debugging using its configuration and accessible development data source; the required capability here is Azure Container Apps, which this operation does not supply.

D:

AKS hosts containerized DAB workloads with Kubernetes orchestration under customer-managed cluster configurations; the alternative cannot substitute for Azure Container Apps under the given constraints.

Question 306

An application keeps transactions open while calling a remote API, extending lock holds and creating user-visible blocking. Which option would you recommend?

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

Explanation

Correct Answer

C:

Moving slow external work outside short database transactions reduces lock retention and contention windows.

Incorrect Answers

A:

A deadlock graph shows participants, resources, and the victim selection, clarifying the cyclic dependency; the missing behavior is Shorten transaction duration; this mechanism does something else.

B:

Accessing shared objects in a consistent order can prevent a circular lock acquisition pattern across concurrent transactions; that function is not interchangeable with Shorten transaction duration for the requested result.

D:

Analyzing blocking sessions and waits identifies which session owns the incompatible lock that others need; it addresses a different step; the question calls for Shorten transaction duration.

Question 307

A query needs the scalar customer postal code from a JSON document at $.address.postalCode for use in a SELECT list. What should be configured to meet this objective?

  1. JSON index
  2. OPENJSON with a WITH clause
  3. JSON_VALUE expression
  4. Native JSON data type

Explanation

Correct Answer

C:

JSON_VALUE extracts a scalar at the requested JSON path, rather than returning an object or a table of properties.

Incorrect Answers

A:

A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; the required capability here is JSON_VALUE expression, which this operation does not supply.

B:

OPENJSON shreds JSON arrays or objects into rows, while WITH can define the output columns and their SQL data types; the alternative cannot substitute for JSON_VALUE expression under the given constraints.

D:

The native json type is intended for JSON documents and supports JSON-oriented operations on platforms where it is available; support must be checked per engine; the missing behavior is JSON_VALUE expression; this mechanism does something else.

Question 308

A high-throughput SQL Server OLTP workload experiences frequent small transactions and write contention. The target environment supports the specialized in-memory engine and its table restrictions. Which table design most directly uses that engine?

  1. Memory-optimized table
  2. Heap table
  3. Clustered rowstore table
  4. Filtered index predicate
  5. Clustered columnstore table

Explanation

Correct Answer

A:

Memory-optimized tables use the In-Memory OLTP engine and specialized concurrency structures, distinct from disk-based rowstore indexes.

Incorrect Answers

B:

A heap has no clustered index and stores rows without a clustered key order, although secondary indexes can still be added; it addresses a different step; the question calls for Memory-optimized table.

C:

A clustered rowstore index organizes records by its clustering key and suits indexed OLTP access patterns; this differs from the Memory-optimized table requirement described in the scenario.

D:

A WHERE filter on a nonclustered index narrows the indexed rowset to qualifying values when query predicates are compatible; the required capability here is Memory-optimized table, which this operation does not supply.

E:

A clustered columnstore provides compressed columnar storage for analytical scans; it is not tuned primarily around singleton row lookups; the alternative cannot substitute for Memory-optimized table under the given constraints.

Question 309

A platform team already operates Kubernetes and needs DAB deployed under its existing cluster-level networking and operations policies. Which SQL capability is the most direct fit?

  1. Azure Container Apps
  2. Azure App Service
  3. Azure Kubernetes Service
  4. Local DAB runtime

Explanation

Correct Answer

C:

AKS hosts containerized DAB workloads with Kubernetes orchestration under customer-managed cluster configurations.

Incorrect Answers

A:

Azure Container Apps can host a DAB container with managed ingress and scaling suitable for event-driven or API workloads; the condition in the stem is resolved by Azure Kubernetes Service, not by this feature alone.

B:

App Service provides managed web hosting and deployment features for supported DAB deployment patterns; the missing behavior is Azure Kubernetes Service; this mechanism does something else.

D:

Running DAB locally supports iterative testing and debugging using its configuration and accessible development data source; for the stated conditions, the deciding feature is Azure Kubernetes Service rather than this behavior.

Question 310

An analyst needs identifiers present in both a billing query and a support-ticket query without manually joining their tables. What is the right solution for this specific requirement?

  1. UNION ALL
  2. UNION
  3. INTERSECT
  4. EXCEPT

Explanation

Correct Answer

C:

INTERSECT returns distinct compatible rows appearing in both input result sets.

Incorrect Answers

A:

UNION ALL concatenates the result sets without removing duplicate rows, unlike UNION's distinct behavior; this differs from the INTERSECT requirement described in the scenario.

B:

UNION performs set combination with duplicate elimination across compatible query projections; the required capability here is INTERSECT, which this operation does not supply.

D:

EXCEPT returns distinct rows from the first input that are not found in the second input; the condition in the stem is resolved by INTERSECT, not by this feature alone.

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