Question 560
A heap has no useful index for an unrestricted read, so the execution plan visits the table's pages directly. What is the most appropriate design decision?
- Table Scan
- Key Lookup
- Index Scan
- Index Seek
Explanation
Correct Answer
A:A Table Scan accesses heap data without using an index seek and may be reasonable when most rows are needed.
Incorrect Answers
B:A Key Lookup obtains missing columns from the clustered data using keys retrieved from another index; covering may reduce this work; the condition in the stem is resolved by Table Scan, not by this feature alone.
C:An Index Scan walks a substantial part of an index and can be appropriate for broad result sets; the missing behavior is Table Scan; this mechanism does something else.
D:An Index Seek uses an index access path to locate qualifying key ranges, though residual predicates may still filter rows; that function is not interchangeable with Table Scan for the requested result.
Question 561
A question is paraphrased extensively and has no exact rare codes, so semantic similarity should drive the candidate order. Which database mechanism should be chosen?
- Reciprocal Rank Fusion
- Keyword-only ranking
- Weighted score normalization
- Vector-only ranking
Explanation
Correct Answer
D:Vector-only ranking uses embedding proximity and may miss exact special identifiers that lexical retrieval handles better.
Incorrect Answers
A:RRF adds reciprocal contributions based on each result's rank position, making lexical and vector rankings comparable without raw-score calibration; it addresses a different step; the question calls for Vector-only ranking.
B:Lexical ranking rewards occurrences of specified terms, supporting exact identifiers that embeddings may not distinguish reliably; this differs from the Vector-only ranking requirement described in the scenario.
C:Normalizing and weighting scores can combine modalities, but requires understanding score distributions and calibration; the required capability here is Vector-only ranking, which this operation does not supply.
Question 562
SQL graph tables connect Products to Distributors and then Distributors to Suppliers through two typed edge relationships. The query must express the entire two-hop pattern with both edge aliases in SQL graph syntax, without asking for the shortest variable-length path. Which graph construct supports this pattern?
- Regular INNER JOIN
- SHORTEST_PATH graph pattern
- MATCH graph predicate
- EXISTS subquery
Explanation
Correct Answer
C:MATCH can express a pattern containing multiple typed graph edges and intermediate node aliases, allowing a fixed two-hop traversal in one SQL graph query.
Incorrect Answers
A:INNER JOIN pairs rows based on a join condition and is the direct relational choice without graph schema requirements; the missing behavior is MATCH graph predicate; this mechanism does something else.
B:SHORTEST_PATH works with supported graph path patterns and evaluates a shortest qualifying traversal, not arbitrary relational sorting; that function is not interchangeable with MATCH graph predicate for the requested result.
D:EXISTS tests for the presence of qualifying related rows without multiplying outer rows by the number of matches; it addresses a different step; the question calls for MATCH graph predicate.
Question 563
A currently running long query must be observed while operators are processing data rather than only after completion. What is the right solution for this specific requirement?
- Live query statistics
- Query Store plan history
- Estimated execution plan
- Actual execution plan
Explanation
Correct Answer
A:Live Query Statistics can expose progress and operator activity for running supported queries, assisting diagnosis of ongoing execution.
Incorrect Answers
B:Query Store preserves eligible query texts, plans, and aggregated runtime statistics so plan changes can be correlated with regressions; the alternative cannot substitute for Live query statistics under the given constraints.
C:An estimated plan shows the optimizer's anticipated strategy without running the full statement and therefore lacks actual runtime counts; the condition in the stem is resolved by Live query statistics, not by this feature alone.
D:An actual plan records the executed query operators and runtime row counts, enabling comparison against estimates; the missing behavior is Live query statistics; this mechanism does something else.
Question 564
A business rule requires a notification row only when an existing invoice is actually updated by a statement. What is the best primary mechanism to use?
- INSTEAD OF DELETE trigger
- AFTER UPDATE trigger
- inserted logical table
- deleted logical table
Explanation
Correct Answer
B:An AFTER UPDATE trigger fires in response to UPDATE operations and can inspect affected rowsets, without being a scheduler.
Incorrect Answers
A:An INSTEAD OF DELETE trigger can replace a requested deletion with custom SQL logic, including soft-delete behavior; for the stated conditions, the deciding feature is AFTER UPDATE trigger rather than this behavior.
C:The inserted logical table contains newly inserted or updated row versions available to a DML trigger and should be handled set-wise; this differs from the AFTER UPDATE trigger requirement described in the scenario.
D:The deleted logical table contains prior versions of updated or deleted rows, supporting set-based old-versus-new comparisons; the required capability here is AFTER UPDATE trigger, which this operation does not supply.
Question 565
A query seeks by TenantId and Date, then projects several columns not in its index. The team must choose key-column order and avoid many base-table lookups. Which TWO index design ideas matter? Choose TWO.
- Index maintenance on fragmentation
- Filtered index predicate
- CHECK with IN predicate
- Composite index key order
- Included index columns
Explanation
Correct Answers
D:Leading index-key columns determine which predicates can support a seek; key order matters for equality and range access; used in combination with Included index columns, it handles a separate requirement of this combined workflow.
E:INCLUDE columns can satisfy output projections without expanding the seek key, often making a nonclustered index covering; used in combination with Composite index key order, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:Reorganizing or rebuilding appropriate indexes addresses physical fragmentation and may improve access efficiency, but should follow measured need; the condition in the stem is resolved by Composite index key order and Included index columns, not by this feature alone.
B:A WHERE filter on a nonclustered index narrows the indexed rowset to qualifying values when query predicates are compatible; the missing behavior is Composite index key order and Included index columns; this mechanism does something else.
C:A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation; that function is not interchangeable with Composite index key order and Included index columns for the requested result.
Question 566
An archival process must move an entire ready-to-retire monthly partition to a compatible staging table with minimal data movement. Which option would you recommend?
- Aligned partitioned index
- Partition scheme
- Partition switching
- SELECT INTO archive table
Explanation
Correct Answer
C:ALTER TABLE SWITCH changes partition ownership through metadata when structures, indexes and constraints satisfy switching prerequisites.
Incorrect Answers
A:Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries; this differs from the Partition switching requirement described in the scenario.
B:A partition scheme assigns the partitions defined by a function to filegroups and can be referenced by tables and indexes; the required capability here is Partition switching, which this operation does not supply.
D:SELECT INTO copies matching rows into a newly created table; it does not perform the metadata-oriented partition switch requested for a compatible archive.
Question 567
A SQL tool is advertised to the assistant but database requests fail with a denied token or login; the path must be fixed at identity configuration. What is the most suitable implementation?
- Stale schema context
- Unavailable MCP tool
- Unsafe prompt injection in data
- Authentication failure from tool server
Explanation
Correct Answer
D:The server's database identity needs valid authentication and the necessary SQL permissions; chat access alone does not grant data access.
Incorrect Answers
A:Refreshing schema metadata before generation or execution avoids relying on obsolete object definitions; that function is not interchangeable with Authentication failure from tool server for the requested result.
B:Tool discovery depends on the capabilities exposed by the server; adding a prompt instruction cannot create a missing backend tool; for the stated conditions, the deciding feature is Authentication failure from tool server rather than this behavior.
C:Data returned from a tool is lower-trust context, and its instructions must not override tool policies or reveal confidential secrets; it addresses a different step; the question calls for Authentication failure from tool server.
Question 568
A user enters a natural-language description and wants SQL full-text matching based on term variations rather than an exact literal phrase. What is the most appropriate design decision?
- Full-text index
- FREETEXT
- CONTAINSTABLE
- CONTAINS
- CREATE VECTOR INDEX
Explanation
Correct Answer
B:FREETEXT performs linguistic search using word-breaking and related term processing for broader lexical matching.
Incorrect Answers
A:A full-text index maintains searchable term structures required by SQL full-text predicates on supported columns; the alternative cannot substitute for FREETEXT under the given constraints.
C:CONTAINSTABLE returns matching keys with a full-text rank value as a relational result set; the missing behavior is FREETEXT; this mechanism does something else.
D:CONTAINS evaluates full-text search conditions such as words and phrases against indexed text columns; that function is not interchangeable with FREETEXT for the requested result.
E:CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; for the stated conditions, the deciding feature is FREETEXT rather than this behavior.
Question 569
DacFx plans to drop a live column not present in source, and administrators must verify loss implications before publishing. What is the best primary mechanism to use?
- Pre-deployment backup
- CODEOWNERS review
- Backward-compatible additive change
- Explicit destructive-change review
- Expand-and-contract migration
Explanation
Correct Answer
D:A destructive-change gate reviews planned drops and data transformations rather than assuming model synchronization is risk-free.
Incorrect Answers
A:An appropriate backup or recovery strategy protects recoverability when deployment scripts cannot simply reverse data loss; it addresses a different step; the question calls for Explicit destructive-change review.
B:CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules; this differs from the Explicit destructive-change review requirement described in the scenario.
C:An additive compatible migration can allow old and new application versions to coexist during staged cutover; the required capability here is Explicit destructive-change review, which this operation does not supply.
E:Expand-and-contract introduces new schema, migrates users and data, then removes old objects only after dependents move; the condition in the stem is resolved by Explicit destructive-change review, not by this feature alone.
Question 570
Deployment scripts must update an existing procedure body or create it if absent without manually branching on existence. Which option satisfies the desired behavior most directly?
- ALTER FUNCTION
- CREATE OR ALTER PROCEDURE
- DROP VIEW
- sp_refreshview
Explanation
Correct Answer
B:CREATE OR ALTER for supported modules makes a definition idempotent while preserving the object's logical identity.
Incorrect Answers
A:ALTER FUNCTION changes a function's definition, subject to dependencies and function-kind restrictions; the missing behavior is CREATE OR ALTER PROCEDURE; this mechanism does something else.
C:DROP VIEW removes the view object, leaving its underlying tables intact subject to dependency constraints; for the stated conditions, the deciding feature is CREATE OR ALTER PROCEDURE rather than this behavior.
D:sp_refreshview updates metadata for an eligible non-schema-bound view after referenced schema changes, rather than altering source tables; it addresses a different step; the question calls for CREATE OR ALTER PROCEDURE.