Question 460
A tax calculation stores exact two-decimal monetary amounts and should not accumulate approximate binary floating-point error. Which option satisfies the desired behavior most directly?
- VARCHAR(80)
- DATE type
- DECIMAL(12,2)
- CHAR(2)
Explanation
Correct Answer
C:DECIMAL provides exact base-ten precision and a declared scale appropriate to financial arithmetic.
Incorrect Answers
A:VARCHAR holds variable-length non-Unicode text subject to the column's collation/code page, with less storage than NVARCHAR for many such values; for the stated conditions, the deciding feature is DECIMAL(12,2) rather than this behavior.
B:DATE represents a date without a time-of-day portion, simplifying comparisons that should ignore clock time; it addresses a different step; the question calls for DECIMAL(12,2).
D:CHAR stores fixed-length non-Unicode character data, with padding semantics for shorter supplied values; the required capability here is DECIMAL(12,2), which this operation does not supply.
Question 461
A coding assistant needs to request available tools and invoke the approved SQL schema-inspection tool from a configured remote host. Which option would you recommend?
- Authorization policy on tool calls
- MCP tool server
- Database service identity
- MCP client
Explanation
Correct Answer
D:An MCP client connects to configured servers and consumes advertised capabilities while respecting its security controls.
Incorrect Answers
A:Tool-side authorization and command restrictions should enforce permitted operations independently of natural-language prompt instructions; the condition in the stem is resolved by MCP client, not by this feature alone.
B:An MCP server exposes tool capabilities to a compatible client; the tool's database permissions still depend on its backend identity; the missing behavior is MCP client; this mechanism does something else.
C:A managed or workload identity can acquire authorized tokens for compatible SQL access, requiring appropriate database grants; that function is not interchangeable with MCP client for the requested result.
Question 462
A query optimizer chooses an ineffective join due to bad row estimates. The team must identify the mismatch between observed and predicted cardinality, then refresh out-of-date distribution data. Which TWO steps matter? Choose TWO.
- UPDATE STATISTICS
- Index physical maintenance
- Merge Join
- Filtered statistics
- Actual vs estimated row comparison
Explanation
Correct Answers
A:Updating statistics refreshes cardinality information used during plan selection, though it does not directly rebuild every index; used in combination with Actual vs estimated row comparison, it handles a separate requirement of this combined workflow.
E:Comparing estimated and actual rows at plan operators reveals estimation errors that can explain inappropriate operator choices; used in combination with UPDATE STATISTICS, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:Appropriate index rebuild or reorganize actions address physical layout while statistics alone cannot fix page fragmentation; the required capability here is UPDATE STATISTICS and Actual vs estimated row comparison, which this operation does not supply.
C:Merge Join exploits compatible ordering for efficient matching, though sorting unordered inputs can add significant cost; the alternative cannot substitute for UPDATE STATISTICS and Actual vs estimated row comparison under the given constraints.
D:Filtered statistics capture distributions for rows matching a predicate and can improve estimates for compatible queries; the condition in the stem is resolved by UPDATE STATISTICS and Actual vs estimated row comparison, not by this feature alone.
Question 463
A catalog query should retrieve a product together with its related supplier details in one structured request. Which mechanism addresses the requirement?
- GraphQL field selection
- GraphQL relationship navigation
- GraphQL mutation
- GraphQL aggregation
Explanation
Correct Answer
B:Configured GraphQL relationships permit nested traversal between related API entities within one query document.
Incorrect Answers
A:GraphQL projections let callers request only specified fields in a typed query, subject to endpoint permissions; that function is not interchangeable with GraphQL relationship navigation for the requested result.
C:GraphQL mutations express write operations exposed by the configured schema and authorized by DAB roles; it addresses a different step; the question calls for GraphQL relationship navigation.
D:Supported GraphQL aggregation fields calculate grouped or overall measures through the API instead of requiring all underlying rows at the client; this differs from the GraphQL relationship navigation requirement described in the scenario.
Question 464
An interactive agent uses SQL retrieval and an LLM endpoint; operations need to identify whether vector search or model inference dominates response time. What is the best primary mechanism to use?
- Answer-latency budget
- Groundedness evaluation
- Citation correctness check
- Retrieval recall benchmark
Explanation
Correct Answer
A:Stage-level latency instrumentation attributes end-to-end delay to retrieval, context assembly, and generation for targeted optimization.
Incorrect Answers
B:Groundedness evaluation compares answer claims with supplied evidence, helping flag unsupported generation; the condition in the stem is resolved by Answer-latency budget, not by this feature alone.
C:Citation evaluation checks correspondence between produced claims and referenced evidence instead of merely counting links; the missing behavior is Answer-latency budget; this mechanism does something else.
D:Measuring retrieval recall on labeled questions isolates missing-evidence failures from generation mistakes; that function is not interchangeable with Answer-latency budget for the requested result.
Question 465
An assistant must retrieve only policy pages whose effective date is not in the future and whose expiration has not passed. Which action is justified by these conditions?
- Enforce tenant predicate
- Filter published state
- Filter language metadata
- Apply effective-date policy
Explanation
Correct Answer
D:A date-range eligibility predicate excludes outdated or future policies independently of embedding quality.
Incorrect Answers
A:Authorization-aware filtering restricts eligible search rows independently of similarity ranking and should be enforced server-side; it addresses a different step; the question calls for Apply effective-date policy.
B:A published-state predicate prevents unapproved drafts from entering the context supplied to the model; this differs from the Apply effective-date policy requirement described in the scenario.
C:A language metadata predicate constrains the candidate corpus to the required locale before final answer construction; the required capability here is Apply effective-date policy, which this operation does not supply.
Question 466
A staging API must reject a syntactically valid JSON *object* when the message contract demands a top-level JSON *array*, without parsing the entire array. Which option satisfies the desired behavior most directly?
- JSON_ARRAY
- OPENJSON
- JSON_ARRAYAGG with NULL ON NULL
- JSON_QUERY
- ISJSON with ARRAY type constraint
- JSON_VALUE
Explanation
Correct Answer
E:ISJSON(value, ARRAY) tests both JSON syntax and the required top-level array shape on SQL versions that support its second type-constraint argument.
Incorrect Answers
A:JSON_ARRAY constructs an array value from its supplied scalar expressions; the missing behavior is ISJSON with ARRAY type constraint; this mechanism does something else.
B:OPENJSON converts JSON input to a relational rowset and can use an explicit WITH schema for typed extraction; that function is not interchangeable with ISJSON with ARRAY type constraint for the requested result.
C: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; for the stated conditions, the deciding feature is ISJSON with ARRAY type constraint rather than this behavior.
D:JSON_QUERY extracts an object or array fragment and preserves its JSON structure as an expression result; it addresses a different step; the question calls for ISJSON with ARRAY type constraint.
F:JSON_VALUE extracts a single scalar at a path and is not intended to return entire JSON objects or arrays; the required capability here is ISJSON with ARRAY type constraint, which this operation does not supply.
Question 467
An ultra-low-latency transaction service needs a table implemented with SQL Server In-Memory OLTP storage and compatible indexes. What is the best primary mechanism to use?
- Memory-optimized OLTP row
- Ledger digests
- Graph edge records
- System-versioned history retention
Explanation
Correct Answer
A:Memory-optimized tables use a specialized engine and must follow feature and schema restrictions rather than conventional disk-based indexing.
Incorrect Answers
B:Ledger digests help establish tamper-evidence for ledger content; retaining them securely is key to later verification; the alternative cannot substitute for Memory-optimized OLTP row under the given constraints.
C:Edge tables encode graph relationships between node instances and support relationship-centric queries; the condition in the stem is resolved by Memory-optimized OLTP row, not by this feature alone.
D:System-versioned temporal storage records prior row versions in a history table while maintaining a current table; the missing behavior is Memory-optimized OLTP row; this mechanism does something else.
Question 468
An order contains a varying number of products and the schema must avoid fixed Product1, Product2, Product3 columns. Which SQL capability is the most direct fit?
- Appropriate VARCHAR length
- Junction table
- Separate detail table
- Computed tax total
- UNIQUE alternate identifier
Explanation
Correct Answer
C:A separate OrderLine child table models one-to-many details relationally with a foreign key and allows unbounded line counts.
Incorrect Answers
A: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 Separate detail table rather than this behavior.
B:A junction table resolves the many-to-many association and holds attributes belonging to a specific pairing; it addresses a different step; the question calls for Separate detail table.
D:A computed column expresses the derivation in the database, reducing the risk of independently maintained contradictory values; the required capability here is Separate detail table, which this operation does not supply.
E:A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier; the alternative cannot substitute for Separate detail table under the given constraints.
Question 469
A service accepts only the literal status values 'Draft', 'Active', and 'Closed' even from direct SQL clients. Which feature would solve the stated problem?
- ON DELETE CASCADE foreign key action
- UNIQUE constraint
- NOT NULL constraint
- CHECK with IN predicate
- Filtered index predicate
Explanation
Correct Answer
D:A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation.
Incorrect Answers
A:A cascading DELETE relationship automatically deletes referencing child rows when the referenced parent is deleted; the condition in the stem is resolved by CHECK with IN predicate, not by this feature alone.
B: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 CHECK with IN predicate; this mechanism does something else.
C:NOT NULL forbids the NULL marker on the designated column, regardless of whether a default exists; that function is not interchangeable with CHECK with IN predicate for the requested result.
E:A WHERE filter on a nonclustered index narrows the indexed rowset to qualifying values when query predicates are compatible; it addresses a different step; the question calls for CHECK with IN predicate.
Question 470
A medical support application must match exact equipment model numbers while also retrieving semantically related failure descriptions. Which technique applies to this scenario?
- Structured relational filter
- Post-retrieval reranking
- Hybrid keyword-plus-vector search
- Semantic vector search
Explanation
Correct Answer
C:Hybrid retrieval combines lexical and embedding evidence, capturing both exact-term and conceptual relevance.
Incorrect Answers
A:Ordinary indexed SQL predicates are the direct mechanism for precise structured filtering without embedding model inference; this differs from the Hybrid keyword-plus-vector search requirement described in the scenario.
B:A reranker orders candidates that have already been retrieved; by itself, it does not retrieve both literal model-number matches and paraphrase matches.
D:Vector search compares embedding representations and can retrieve semantic neighbors despite different literal words; the condition in the stem is resolved by Hybrid keyword-plus-vector search, not by this feature alone.