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

Question 120

Engineers want to query centralized logs from SQL databases and container-hosted DAB services in one KQL workspace. Which option would you recommend?

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

Explanation

Correct Answer

A:

Log Analytics provides searchable aggregated telemetry when the relevant services emit data to that workspace.

Incorrect Answers

B:

Query Store retains query plans and runtime histories, allowing statement-level regression comparison and supported plan-forcing remediation that generic request tracing lacks; the condition in the stem is resolved by Log Analytics, not by this feature alone.

C:

Azure Monitor captures supported resource metrics and evaluates metric-based alert rules against them; the missing behavior is Log Analytics; this mechanism does something else.

D:

Application Insights collects application telemetry and supports investigation of failed or slow service requests; that function is not interchangeable with Log Analytics for the requested result.

Question 121

Technical manuals contain headings and subsections; retrieved passages should usually correspond to meaningful topical sections. Which feature would solve the stated problem?

  1. Fixed-size token chunks
  2. Overlapping chunks
  3. Hierarchical parent-child chunks
  4. Semantic section chunking

Explanation

Correct Answer

D:

Semantic chunking follows document structure or topic boundaries, making returned units more coherent than arbitrary cuts.

Incorrect Answers

A:

Fixed-size chunking divides content into bounded segments, with boundaries that may not respect semantic topics; it addresses a different step; the question calls for Semantic section chunking.

B:

Overlap repeats limited context between adjacent chunks to reduce information loss at boundaries, at added storage and embedding cost; this differs from the Semantic section chunking requirement described in the scenario.

C:

Parent-child chunk mapping keeps fine-grained searchable chunks linked to larger source sections for context expansion; the required capability here is Semantic section chunking, which this operation does not supply.

Question 122

A transfer between accounts should either debit and credit together or roll back both changes on any failure. What is the best primary mechanism to use?

  1. Atomic transaction
  2. Pessimistic transaction locking
  3. Optimistic rowversion check
  4. Deadlock retry policy

Explanation

Correct Answer

A:

A database transaction groups multiple modifications into one commit-or-rollback unit, preserving all-or-nothing behavior.

Incorrect Answers

B:

Appropriate transaction isolation and update locks can serialize conflicting modifications during the critical reservation step; that function is not interchangeable with Atomic transaction for the requested result.

C:

Comparing the original ROWVERSION token in UPDATE's WHERE clause allows a zero-row result to signal a stale write; for the stated conditions, the deciding feature is Atomic transaction rather than this behavior.

D:

Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects; it addresses a different step; the question calls for Atomic transaction.

Question 123

Audit logic must report how many times a pattern appears within each free-text message. Which option satisfies the desired behavior most directly?

  1. REGEXP_INSTR
  2. REGEXP_LIKE
  3. REGEXP_REPLACE
  4. REGEXP_COUNT

Explanation

Correct Answer

D:

REGEXP_COUNT counts matches of a regular expression in a source string.

Incorrect Answers

A:

REGEXP_INSTR returns a match position for a supplied regular-expression pattern; the required capability here is REGEXP_COUNT, which this operation does not supply.

B:

REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; the alternative cannot substitute for REGEXP_COUNT under the given constraints.

C:

REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms; the condition in the stem is resolved by REGEXP_COUNT, not by this feature alone.

Question 124

A consolidated history table runs wide aggregation queries over hundreds of millions of facts with limited transactional mutations. Which SQL capability is the most direct fit?

  1. Memory-optimized table
  2. Clustered columnstore table
  3. Heap table
  4. Clustered rowstore table

Explanation

Correct Answer

B:

A clustered columnstore provides compressed columnar storage for analytical scans; it is not tuned primarily around singleton row lookups.

Incorrect Answers

A:

Memory-optimized tables use the In-Memory OLTP engine and specialized concurrency structures, distinct from disk-based rowstore indexes; for the stated conditions, the deciding feature is Clustered columnstore table rather than this behavior.

C:

A heap has no clustered index and stores rows without a clustered key order, although secondary indexes can still be added; this differs from the Clustered columnstore table requirement described in the scenario.

D:

A clustered rowstore index organizes records by its clustering key and suits indexed OLTP access patterns; the required capability here is Clustered columnstore table, which this operation does not supply.

Question 125

A serverless embedding generator should react to tracked changes with minimal custom polling infrastructure. Which action is justified by these conditions?

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

Explanation

Correct Answer

C:

The SQL trigger binding can invoke a function for detected database changes and let the function handle inference and vector updates.

Incorrect Answers

A:

A controlled rebuild recomputes vectors across the corpus, often into a new index or version, rather than mixing incompatible embedding spaces; the condition in the stem is resolved by Azure Functions SQL trigger, not by this feature alone.

B:

CDC records change operations and row details for configured tables, supporting event processing and replay; the missing behavior is Azure Functions SQL trigger; this mechanism does something else.

D:

Change Tracking identifies changed rows and versions efficiently, making it useful for incremental re-embedding without storing full change history; for the stated conditions, the deciding feature is Azure Functions SQL trigger rather than this behavior.

Question 126

An online-ordering session should avoid reading another session's uncommitted data using the database's conventional committed-data isolation level. Which technique applies to this scenario?

  1. READ COMMITTED
  2. SERIALIZABLE isolation
  3. READ UNCOMMITTED
  4. SNAPSHOT isolation

Explanation

Correct Answer

A:

READ COMMITTED prevents dirty reads, with blocking or row-version behavior depending on the configured implementation.

Incorrect Answers

B:

SERIALIZABLE is the strongest standard isolation level and can protect qualifying key ranges from concurrent phantom changes, increasing contention; the required capability here is READ COMMITTED, which this operation does not supply.

C:

READ UNCOMMITTED allows access to uncommitted changes and can produce inconsistent values, so it is unsuitable for authoritative reports; the alternative cannot substitute for READ COMMITTED under the given constraints.

D:

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 condition in the stem is resolved by READ COMMITTED, not by this feature alone.

Question 127

Reports using READ COMMITTED share-lock reads are frequently blocked behind writers, and database policy allows versioned reads. Which database mechanism should be chosen?

  1. Enable appropriate row versioning
  2. Reduce lock footprint
  3. Find open transaction owner
  4. Consistent lock ordering

Explanation

Correct Answer

A:

READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking.

Incorrect Answers

B:

Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness; for the stated conditions, the deciding feature is Enable appropriate row versioning rather than this behavior.

C:

Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation; it addresses a different step; the question calls for Enable appropriate row versioning.

D:

A shared acquisition order reduces circular-wait opportunities across concurrent operations; this differs from the Enable appropriate row versioning requirement described in the scenario.

Question 128

A SELECT should construct a JSON array from three explicit values in a specific order without manual concatenation. Which feature would solve the stated problem?

  1. JSON_ARRAYAGG with NULL ON NULL
  2. FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  3. JSON_OBJECT with ABSENT ON NULL
  4. JSON_ARRAY

Explanation

Correct Answer

D:

JSON_ARRAY constructs an array value from its supplied scalar expressions.

Incorrect Answers

A:

JSON_ARRAYAGG with NULL ON NULL retains SQL NULL inputs as JSON null elements; its usual ABSENT ON NULL behavior would omit those array slots; the alternative cannot substitute for JSON_ARRAY under the given constraints.

B:

FOR JSON PATH with WITHOUT_ARRAY_WRAPPER removes the enclosing JSON array for a single-row result; use it only when cardinality and JSON validity are controlled; the condition in the stem is resolved by JSON_ARRAY, not by this feature alone.

C:

JSON_OBJECT with ABSENT ON NULL suppresses keys whose SQL expressions are NULL; its default NULL ON NULL behavior would instead emit those keys with JSON null values; the missing behavior is JSON_ARRAY; this mechanism does something else.

Question 129

A view's dependencies must be protected from changes that would invalidate its schema-bound definition. Which database mechanism should be chosen?

  1. SCHEMABINDING
  2. WITH CHECK OPTION
  3. Indexed view
  4. Projection view

Explanation

Correct Answer

A:

SCHEMABINDING binds an eligible view to referenced objects and prevents incompatible schema changes until the binding is addressed.

Incorrect Answers

B:

WITH CHECK OPTION requires changes made through the view to remain visible under the view's WHERE condition when supported; this differs from the SCHEMABINDING requirement described in the scenario.

C:

An indexed view materializes indexed results once the required unique clustered index and schema prerequisites are met; the required capability here is SCHEMABINDING, which this operation does not supply.

D:

A view can project selected columns and simplify controlled access when base-table permissions are restricted appropriately; the alternative cannot substitute for SCHEMABINDING under the given constraints.

Question 130

Many executions use different CustomerId values; the app should avoid literal SQL concatenation and support safe parameter binding. Which option would you recommend?

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

Explanation

Correct Answer

D:

Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases.

Incorrect Answers

A:

EXISTS expresses a presence test and does not expand the outer result into one row per matching child; the missing behavior is Parameterized query; this mechanism does something else.

B:

TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match; that function is not interchangeable with Parameterized query for the requested result.

C:

A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column; for the stated conditions, the deciding feature is Parameterized query rather than this behavior.

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