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

Question 340

A reporting query filters by CustomerId and reads OrderDate and TotalDue; the team wants to avoid repeated key lookups without modifying the clustered key. Which approach should the team select?

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

Explanation

Correct Answer

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.

Incorrect Answers

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; for the stated conditions, the deciding feature is Nonclustered covering index rather than this behavior.

C:

A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries; this differs from the Nonclustered covering index requirement described in the scenario.

D:

Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets; the required capability here is Nonclustered covering index, which this operation does not supply.

Question 341

An operator needs to see which currently executing request is waiting, what command it runs, and its current blocking-session identifier. Which mechanism addresses the requirement?

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

Explanation

Correct Answer

B:

sys.dm_exec_requests exposes live request information such as waits and blocking identifiers, rather than long-term history.

Incorrect Answers

A:

sys.dm_exec_query_stats exposes aggregate statistics for queries represented in the plan cache, subject to cache eviction; the condition in the stem is resolved by sys.dm_exec_requests, not by this feature alone.

C:

Index usage DMVs provide counters for seeks, scans, lookups, and updates, with reset and interpretation limitations; that function is not interchangeable with sys.dm_exec_requests for the requested result.

D:

sys.dm_os_wait_stats aggregates wait information at the server scope and must be interpreted alongside workload context; for the stated conditions, the deciding feature is sys.dm_exec_requests rather than this behavior.

Question 342

Operations must be notified when detected SQL connectivity failures exceed an agreed threshold within ten minutes. Which database mechanism should be chosen?

  1. Diagnostic setting export
  2. Audit specification
  3. Per-role API operation policy
  4. Alert on suspicious pattern
  5. Dynamic Data Masking
  6. Review stored audit events

Explanation

Correct Answer

D:

A metric or log alert rule evaluates collected telemetry and can initiate actions when a defined condition is met.

Incorrect Answers

A:

Azure resource diagnostic settings route supported log and metric categories to configured destinations and require appropriate permissions; this differs from the Alert on suspicious pattern requirement described in the scenario.

B:

SQL audit specifications define audited action groups or object actions to be captured by the audit mechanism; the required capability here is Alert on suspicious pattern, which this operation does not supply.

C:

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 Alert on suspicious pattern under the given constraints.

E:

Dynamic Data Masking alters query-result presentation for non-exempt users; it is not encryption or a strong isolation boundary for untrusted querying; the missing behavior is Alert on suspicious pattern; this mechanism does something else.

F:

Examining retained audit records supports forensic reconstruction; it does not itself prevent future unauthorized requests; that function is not interchangeable with Alert on suspicious pattern for the requested result.

Question 343

A product table should store each item's fixed-length 1024-dimensional embedding as a native SQL vector rather than a delimited string. Which choice best meets these constraints?

  1. Embedding-model version field
  2. TenantId metadata field
  3. DocumentId foreign key
  4. VECTOR(n) column

Explanation

Correct Answer

D:

The VECTOR type stores fixed-dimensional floating-point arrays in an optimized representation compatible with vector functions.

Incorrect Answers

A:

Recording model version alongside vectors enables matching query embeddings to the correct indexed corpus; that function is not interchangeable with VECTOR(n) column for the requested result.

B:

Tenant metadata supports enforced isolation filters; vector similarity alone does not authorize document access; for the stated conditions, the deciding feature is VECTOR(n) column rather than this behavior.

C:

A stable relational source key links vector rows to documents independently of their numeric embedding contents; it addresses a different step; the question calls for VECTOR(n) column.

Question 344

On SQL Server, a monthly partitioned fact table has all required indexes already aligned. The DBA must define the boundary map, map partitions to filegroups, and perform a compatible metadata-only monthly archival transfer. Which THREE concepts correspond to those tasks? Choose THREE.

  1. Partition switching
  2. Partition scheme
  3. SEQUENCE object
  4. Aligned partitioned index
  5. Partition function
  6. JSON index

Explanation

Correct Answers

A:

ALTER TABLE SWITCH changes partition ownership through metadata when structures, indexes and constraints satisfy switching prerequisites; used in combination with Partition function, Partition scheme, it handles a separate requirement of this combined workflow.

B:

A partition scheme assigns the partitions defined by a function to filegroups and can be referenced by tables and indexes; used in combination with Partition function, Partition switching, it handles a separate requirement of this combined workflow.

E:

A partition function defines boundary points and maps data values to logical partitions without itself choosing storage destinations; used in combination with Partition scheme, Partition switching, it handles a separate requirement of this combined workflow.

Incorrect Answers

C:

A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR; the missing behavior is Partition switching and Partition scheme and Partition function; this mechanism does something else.

D:

Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries; that function is not interchangeable with Partition switching and Partition scheme and Partition function for the requested result.

F:

A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; it addresses a different step; the question calls for Partition switching and Partition scheme and Partition function.

Question 345

Before sending retrieved pages to a model, the system must ensure the authenticated user is allowed to read every referenced source. What is the best primary mechanism to use?

  1. Lexical candidate pipeline
  2. Final document authorization check
  3. RRF fusion stage
  4. Vector candidate pipeline

Explanation

Correct Answer

B:

Authorization-aware source hydration prevents relevant-but-private candidate rows from becoming unauthorized LLM context.

Incorrect Answers

A:

Full-text predicates or table-valued search functions can produce lexical matches and optional rank values as one fusion input; it addresses a different step; the question calls for Final document authorization check.

C:

RRF converts each result rank into reciprocal contributions and sums them across available lists; the required capability here is Final document authorization check, which this operation does not supply.

D:

Vector-neighbor retrieval produces semantic candidate IDs and distance-based order for the second fusion input; the alternative cannot substitute for Final document authorization check under the given constraints.

Question 346

A bulk-loading job must both reject an InvoiceLine whose InvoiceId has no parent and reject a negative invoice quantity. Which TWO database constraints enforce these independent requirements? Choose TWO.

  1. CHECK constraint
  2. FOREIGN KEY constraint
  3. PRIMARY KEY constraint
  4. DEFAULT constraint
  5. Clustered columnstore index

Explanation

Correct Answers

A:

A CHECK constraint can compare columns from a row in one Boolean condition and rejects non-NULL expressions evaluated as FALSE, regardless of the inserting application; used in combination with FOREIGN KEY constraint, it handles a separate requirement of this combined workflow.

B:

A foreign key checks referential integrity between a referencing column and a candidate key in another or the same table; used in combination with CHECK constraint, it handles a separate requirement of this combined workflow.

Incorrect Answers

C:

A primary key declares the table's principal identifier and enforces uniqueness plus nonnullability through an associated index; for the stated conditions, the deciding feature is CHECK constraint and FOREIGN KEY constraint rather than this behavior.

D:

A DEFAULT expression supplies a value for a column when INSERT omits it or uses DEFAULT; it does not automatically override explicit NULL or an explicitly supplied value; it addresses a different step; the question calls for CHECK constraint and FOREIGN KEY constraint.

E:

Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets; this differs from the CHECK constraint and FOREIGN KEY constraint requirement described in the scenario.

Question 347

An inventory allocation must lock a reserved row while checking and decrementing stock to prevent concurrent overselling. Which SQL capability is the most direct fit?

  1. Azure SQL Query Performance Insight
  2. Pessimistic transaction locking
  3. Memory grant spill review
  4. Deadlock retry policy
  5. Atomic transaction
  6. Optimistic rowversion check

Explanation

Correct Answer

B:

Appropriate transaction isolation and update locks can serialize conflicting modifications during the critical reservation step.

Incorrect Answers

A:

Query Performance Insight provides a focused view of captured SQL query performance metrics and historical trends; the required capability here is Pessimistic transaction locking, which this operation does not supply.

C:

Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb; the condition in the stem is resolved by Pessimistic transaction locking, not by this feature alone.

D:

Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects; the missing behavior is Pessimistic transaction locking; this mechanism does something else.

E:

A database transaction groups multiple modifications into one commit-or-rollback unit, preserving all-or-nothing behavior; that function is not interchangeable with Pessimistic transaction locking for the requested result.

F:

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 Pessimistic transaction locking rather than this behavior.

Question 348

A table must reject out-of-range Scores on every write and does not need procedural logging. Which approach should the team select?

  1. CHECK constraint
  2. INSTEAD OF trigger
  3. DEFAULT constraint
  4. AFTER trigger

Explanation

Correct Answer

A:

A CHECK constraint is a declarative integrity rule enforced by the engine when qualifying rows are inserted or updated.

Incorrect Answers

B:

An INSTEAD OF trigger substitutes custom DML processing for the triggering operation, often making certain views writable; it addresses a different step; the question calls for CHECK constraint.

C:

A DEFAULT constraint supplies an omitted insertion value and is simpler than an event-triggered procedural program; this differs from the CHECK constraint requirement described in the scenario.

D:

An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; the required capability here is CHECK constraint, which this operation does not supply.

Question 349

A workload slows only under concurrency; engineers need to determine whether sessions wait on locks, memory, or resources. What should the developer implement?

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

Explanation

Correct Answer

C:

Categorizing waits helps identify the contended resource, but wait evidence should be correlated with relevant sessions and plans.

Incorrect Answers

A:

CPU usage and operator analysis reveal expensive computations or inefficient row processing independently of storage wait times; the condition in the stem is resolved by Wait-type analysis, not by this feature alone.

B:

Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb; the missing behavior is Wait-type analysis; this mechanism does something else.

D:

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 Wait-type analysis rather than this behavior.

Question 350

A sales report needs several grouping levels in one aggregate query, then must retain only those grouped results whose totals exceed a threshold. Which TWO constructs address this? Choose TWO.

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

Explanation

Correct Answers

B:

HAVING filters aggregated groups after grouping, unlike a row-level WHERE filter; used in combination with GROUPING SETS, it handles a separate requirement of this combined workflow.

E:

GROUPING SETS explicitly requests several grouping levels in a single grouped SELECT; used in combination with HAVING, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

A window aggregate computes over partitions while maintaining input-row granularity rather than grouping detail away; this differs from the HAVING and GROUPING SETS requirement described in the scenario.

C:

COUNT(DISTINCT ...) counts distinct non-NULL values in a group or qualifying result set; the alternative cannot substitute for HAVING and GROUPING SETS under the given constraints.

D:

TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; the condition in the stem is resolved by HAVING and GROUPING SETS, not by this feature alone.

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