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.
Question 371
After a questionable answer, compliance reviewers must reconstruct which documents and prompt version informed the result. What is the most appropriate design decision?
- Audit retrieved sources
- Retry transient model throttling
- Redact confidential fields
- Limit retrieved context
Explanation
Correct Answer
A:Logging approved source identifiers and prompt metadata creates a traceable record for investigation without storing unrestricted sensitive content.
Incorrect Answers
B:Rate-limit-aware retries and idempotent request handling reduce failures without overwhelming the inference service; the alternative cannot substitute for Audit retrieved sources under the given constraints.
C:Redaction and data minimization remove unnecessary secrets or identifiers before leaving the SQL trust boundary; the condition in the stem is resolved by Audit retrieved sources, not by this feature alone.
D:Limiting and ranking context passages keeps input within token budgets and reduces irrelevant material sent to the generator; the missing behavior is Audit retrieved sources; this mechanism does something else.
Question 372
A legacy integration must store arbitrary binary document bytes directly in a database column, not a path or an encoded textual representation. Which mechanism addresses the requirement?
- NVARCHAR(200)
- DATETIME2
- DECIMAL(19,4)
- VARBINARY(MAX)
Explanation
Correct Answer
D:VARBINARY(MAX) holds variable-length binary data; unlike character types, it does not interpret the bytes using a text encoding.
Incorrect Answers
A:NVARCHAR stores variable-length Unicode character content and is appropriate when multilingual text must be preserved; for the stated conditions, the deciding feature is VARBINARY(MAX) rather than this behavior.
B:DATETIME2 represents date and time together with higher supported precision and range than legacy DATETIME; it addresses a different step; the question calls for VARBINARY(MAX).
C:Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale; this differs from the VARBINARY(MAX) requirement described in the scenario.
Question 373
A plan performs thousands of key lookups for just two columns missing from a selective nonclustered index. Which action is justified by these conditions?
- Read/write index tradeoff
- Covering index evaluation
- Histogram selectivity
- Recompile after schema change
Explanation
Correct Answer
B:Adding appropriate INCLUDE columns can make the access path covering and reduce repetitive base-row lookups, if write overhead is acceptable.
Incorrect Answers
A:Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification; the condition in the stem is resolved by Covering index evaluation, not by this feature alone.
C:Statistics histograms describe value distributions used to estimate predicate cardinality and influence operator selection; that function is not interchangeable with Covering index evaluation for the requested result.
D:Plan recompilation allows the optimizer to select operators using current metadata and available statistics; for the stated conditions, the deciding feature is Covering index evaluation rather than this behavior.
Question 374
A windowed ranking should restart from 1 for each customer rather than ranking all customers' transactions together. Which mechanism addresses the requirement?
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
- ORDER BY EventTime in OVER clause
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- PARTITION BY CustomerId
Explanation
Correct Answer
D:PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact.
Incorrect Answers
A:This ROWS window frame contains at most the current row and two prior ordered rows for moving calculations; this differs from the PARTITION BY CustomerId requirement described in the scenario.
B:ORDER BY inside OVER establishes the function's logical row ordering; the outer query's ORDER BY controls presentation separately; the required capability here is PARTITION BY CustomerId, which this operation does not supply.
C:A ROWS frame defined this way forms a growing row-based window and gives predictable cumulative aggregation with a deterministic ORDER BY; the alternative cannot substitute for PARTITION BY CustomerId under the given constraints.
Question 375
A complex view needs custom logic to translate an attempted INSERT into changes in its underlying tables. What is the right solution for this specific requirement?
- AFTER trigger
- DEFAULT constraint
- INSTEAD OF trigger
- CHECK constraint
Explanation
Correct Answer
C:An INSTEAD OF trigger substitutes custom DML processing for the triggering operation, often making certain views writable.
Incorrect Answers
A:An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; that function is not interchangeable with INSTEAD OF trigger for the requested result.
B:A DEFAULT constraint supplies an omitted insertion value and is simpler than an event-triggered procedural program; for the stated conditions, the deciding feature is INSTEAD OF trigger rather than this behavior.
D:A CHECK constraint is a declarative integrity rule enforced by the engine when qualifying rows are inserted or updated; this differs from the INSTEAD OF trigger requirement described in the scenario.
Question 376
A tenant must be prevented from INSERTing or UPDATEing a row tagged with another tenant's identifier. What should be configured to meet this objective?
- Masked column definition
- RLS filter predicate
- RLS block predicate
- GRANT SELECT on view
Explanation
Correct Answer
C:An RLS block predicate can reject changes that would violate a row-access policy during specified DML operations.
Incorrect Answers
A:A masking function on the column changes the result shown to principals lacking UNMASK, leaving the underlying value unchanged; the alternative cannot substitute for RLS block predicate under the given constraints.
B:An RLS filter predicate limits visible rows for eligible operations at the table's security-policy boundary; the condition in the stem is resolved by RLS block predicate, not by this feature alone.
D:Object-level SELECT permissions can be granted on the view while base-table privileges remain constrained, with ownership chaining considered; that function is not interchangeable with RLS block predicate for the requested result.
Question 377
A database-backed REST API must protect customer data crossing public network hops against passive interception. Which database mechanism should be chosen?
- Managed identity to database
- Validate JWT audience
- TLS termination
- Per-role API operation policy
Explanation
Correct Answer
C:TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations.
Incorrect Answers
A:A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately; it addresses a different step; the question calls for TLS termination.
B:Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint; this differs from the TLS termination requirement described in the scenario.
D: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 TLS termination under the given constraints.
Question 378
A reference-data script may run repeatedly and must not create another identical currency-code row on each deployment. Which choice best meets these constraints?
- Post-deployment seed script
- Version-controlled reference CSV
- Idempotent MERGE or upsert logic
- Integration test for referential values
Explanation
Correct Answer
C:An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases.
Incorrect Answers
A:Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow; the missing behavior is Idempotent MERGE or upsert logic; this mechanism does something else.
B:Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; that function is not interchangeable with Idempotent MERGE or upsert logic for the requested result.
D:A targeted integration or smoke test validates that required reference records are present and usable by related constraints; it addresses a different step; the question calls for Idempotent MERGE or upsert logic.
Question 379
A service allows anonymous catalog reads but must prevent the same callers from deleting products or accessing pricing mutations. Which choice best meets these constraints?
- Managed identity to database
- TLS termination
- Per-role API operation policy
- Validate JWT audience
Explanation
Correct Answer
C:Role-level operation restrictions at the API boundary can separate read capabilities from write mutations without confusing authentication with authorization.
Incorrect Answers
A:A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately; the required capability here is Per-role API operation policy, which this operation does not supply.
B:TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations; the alternative cannot substitute for Per-role API operation policy under the given constraints.
D:Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint; the missing behavior is Per-role API operation policy; this mechanism does something else.
Question 380
A release needs to insert or update a few stable ISO country-code rows after schema changes without treating business transactions as source code. Which action is justified by these conditions?
- Version-controlled reference CSV
- Integration test for referential values
- Post-deployment seed script
- Idempotent MERGE or upsert logic
Explanation
Correct Answer
C:Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow.
Incorrect Answers
A:Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; for the stated conditions, the deciding feature is Post-deployment seed script rather than this behavior.
B:A targeted integration or smoke test validates that required reference records are present and usable by related constraints; it addresses a different step; the question calls for Post-deployment seed script.
D:An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases; the required capability here is Post-deployment seed script, which this operation does not supply.