Question 360
Removing a parent shopping cart must also remove its related cart lines in the same enforced referential action. Which choice best meets these constraints?
- CHECK with IN predicate
- NOT NULL constraint
- UNIQUE constraint
- ON DELETE CASCADE foreign key action
Explanation
Correct Answer
D:A cascading DELETE relationship automatically deletes referencing child rows when the referenced parent is deleted.
Incorrect Answers
A:A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation; the alternative cannot substitute for ON DELETE CASCADE foreign key action under the given constraints.
B:NOT NULL forbids the NULL marker on the designated column, regardless of whether a default exists; the condition in the stem is resolved by ON DELETE CASCADE foreign key action, not by this feature alone.
C:UNIQUE enforces a candidate key distinct from the primary key and prevents duplicate non-NULL key combinations according to platform semantics; the missing behavior is ON DELETE CASCADE foreign key action; this mechanism does something else.
Question 361
Platform operations must alert when a SQL resource exceeds CPU or connection thresholds over an interval. Which option would you recommend?
- Query Store
- Application Insights
- Azure Monitor metrics
- Log Analytics
- Azure Container Apps
Explanation
Correct Answer
C:Azure Monitor captures supported resource metrics and evaluates metric-based alert rules against them.
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 Azure Monitor metrics.
B:Application Insights collects application telemetry and supports investigation of failed or slow service requests; this differs from the Azure Monitor metrics 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 Azure Monitor metrics under the given constraints.
E: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 Monitor metrics, not by this feature alone.
Question 362
Projects and consultants can each participate in many assignments, with assignment dates attached to each relationship. What should the developer implement?
- Filtered nonclustered index
- Separate detail table
- Appropriate VARCHAR length
- Computed tax total
- Junction table
Explanation
Correct Answer
E:A junction table resolves the many-to-many association and holds attributes belonging to a specific pairing.
Incorrect Answers
A:A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries; the missing behavior is Junction table; this mechanism does something else.
B:A separate OrderLine child table models one-to-many details relationally with a foreign key and allows unbounded line counts; that function is not interchangeable with Junction table for the requested result.
C:Using a bounded variable-length string type expresses the data contract and avoids needlessly applying large-object semantics; for the stated conditions, the deciding feature is Junction table rather than this behavior.
D:A computed column expresses the derivation in the database, reducing the risk of independently maintained contradictory values; it addresses a different step; the question calls for Junction table.
Question 363
Investigators want instance-level accumulated wait categories to identify broad resource contention patterns. What is the best primary mechanism to use?
- sys.dm_os_wait_stats
- sys.dm_db_index_usage_stats
- sys.dm_exec_query_stats
- sys.dm_exec_requests
Explanation
Correct Answer
A:sys.dm_os_wait_stats aggregates wait information at the server scope and must be interpreted alongside workload context.
Incorrect Answers
B:Index usage DMVs provide counters for seeks, scans, lookups, and updates, with reset and interpretation limitations; the alternative cannot substitute for sys.dm_os_wait_stats under the given constraints.
C: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_os_wait_stats, not by this feature alone.
D:sys.dm_exec_requests exposes live request information such as waits and blocking identifiers, rather than long-term history; the missing behavior is sys.dm_os_wait_stats; this mechanism does something else.
Question 364
Retrieval should find a short precise paragraph but provide its entire section as context to the answer model. Which technique applies to this scenario?
- Overlapping chunks
- Fixed-size token chunks
- Semantic section chunking
- Hierarchical parent-child chunks
Explanation
Correct Answer
D:Parent-child chunk mapping keeps fine-grained searchable chunks linked to larger source sections for context expansion.
Incorrect Answers
A:Overlap repeats limited context between adjacent chunks to reduce information loss at boundaries, at added storage and embedding cost; for the stated conditions, the deciding feature is Hierarchical parent-child chunks rather than this behavior.
B: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 Hierarchical parent-child chunks.
C:Semantic chunking follows document structure or topic boundaries, making returned units more coherent than arbitrary cuts; this differs from the Hierarchical parent-child chunks requirement described in the scenario.
Question 365
A monthly report needs each original sales row plus its region subtotal without collapsing the detail rows. What is the most appropriate design decision?
- COUNT(DISTINCT CustomerId)
- GROUPING SETS
- HAVING
- SUM(...) OVER
Explanation
Correct Answer
D:A window aggregate computes over partitions while maintaining input-row granularity rather than grouping detail away.
Incorrect Answers
A:COUNT(DISTINCT ...) counts distinct non-NULL values in a group or qualifying result set; the condition in the stem is resolved by SUM(...) OVER, not by this feature alone.
B:GROUPING SETS explicitly requests several grouping levels in a single grouped SELECT; the missing behavior is SUM(...) OVER; this mechanism does something else.
C:HAVING filters aggregated groups after grouping, unlike a row-level WHERE filter; that function is not interchangeable with SUM(...) OVER for the requested result.
Question 366
A query must return a total ordering unsupported by an existing index and its plan has to order intermediate rows. Which option satisfies the desired behavior most directly?
- Sort operator
- Merge Join
- Nested Loops join
- Hash Match join
Explanation
Correct Answer
A:Sort produces ordered rows for ORDER BY or other operators and can become expensive or spill when memory is insufficient.
Incorrect Answers
B:Merge Join exploits compatible ordering for efficient matching, though sorting unordered inputs can add significant cost; the required capability here is Sort operator, which this operation does not supply.
C:Nested Loops repeatedly probes an inner input for outer rows and can be efficient for selective joins with good indexes; the alternative cannot substitute for Sort operator under the given constraints.
D:Hash Match uses a hash table to match join keys and often suits large unordered joins at a memory cost; the condition in the stem is resolved by Sort operator, not by this feature alone.
Question 367
A return table must be populated in several procedural steps before the caller consumes its rows. Which option would you recommend?
- Stored procedure
- Inline table-valued function
- Multi-statement table-valued function
- Scalar function
Explanation
Correct Answer
C:A multi-statement TVF declares a table return variable and populates it with multiple statements, subject to different optimizer behavior than inline TVFs.
Incorrect Answers
A:A stored procedure is an executable unit suited to procedural database operations; it is not directly a relational table expression; that function is not interchangeable with Multi-statement table-valued function for the requested result.
B:An inline TVF returns the result of a single SELECT definition and is commonly optimized similarly to a parameterized view; for the stated conditions, the deciding feature is Multi-statement table-valued function rather than this behavior.
D:A scalar UDF returns a single scalar value, although calling patterns and implementation determine its performance; this differs from the Multi-statement table-valued function requirement described in the scenario.
Question 368
A contest report must give equal scores the same rank and leave gaps after tied positions, such as ranks 1, 1, 3. Which action is justified by these conditions?
- DENSE_RANK()
- ROW_NUMBER()
- RANK()
- NTILE(4)
Explanation
Correct Answer
C:RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows.
Incorrect Answers
A:DENSE_RANK numbers distinct ordered values without gaps after ties within the partition; the alternative cannot substitute for RANK() under the given constraints.
B:ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules; the condition in the stem is resolved by RANK(), not by this feature alone.
D:NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row; that function is not interchangeable with RANK() for the requested result.
Question 369
An order-processing API must execute multiple dependent UPDATE and INSERT statements under controlled database permissions and return a completion status to a caller. What is the right solution for this specific requirement?
- Stored procedure
- View
- Scalar user-defined function
- Table-valued function
Explanation
Correct Answer
A:A stored procedure can encapsulate multi-statement DML and transactions, accept input parameters, and report status or rowsets to application callers.
Incorrect Answers
B:A regular view encapsulates a SELECT definition as a database object and generally resolves its rows from underlying tables when queried; this differs from the Stored procedure requirement described in the scenario.
C:A scalar function produces a single value and can be invoked in supported expressions, with performance considerations depending on its implementation; the required capability here is Stored procedure, which this operation does not supply.
D:A table-valued function returns a relational result, and an inline version can expose an optimizer-friendly SELECT expression; the alternative cannot substitute for Stored procedure under the given constraints.
Question 370
Two retrieval systems emit numeric scores on different scales and the team wants a carefully calibrated weighted blend. What is the most appropriate design decision?
- Keyword-only ranking
- Reciprocal Rank Fusion
- Weighted score normalization
- Vector-only ranking
Explanation
Correct Answer
C:Normalizing and weighting scores can combine modalities, but requires understanding score distributions and calibration.
Incorrect Answers
A:Lexical ranking rewards occurrences of specified terms, supporting exact identifiers that embeddings may not distinguish reliably; the missing behavior is Weighted score normalization; this mechanism does something else.
B:RRF adds reciprocal contributions based on each result's rank position, making lexical and vector rankings comparable without raw-score calibration; that function is not interchangeable with Weighted score normalization for the requested result.
D:Vector-only ranking uses embedding proximity and may miss exact special identifiers that lexical retrieval handles better; it addresses a different step; the question calls for Weighted score normalization.