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.
Question 471
A query has several selected SQL result rows and must serialize them as nested JSON for inclusion in a model request. Which feature would solve the stated problem?
- STRING_AGG
- FOR JSON PATH
- JSON_OBJECT
- JSON_ARRAYAGG
Explanation
Correct Answer
B:FOR JSON PATH transforms SQL query results into JSON structures using explicit alias-based paths.
Incorrect Answers
A:STRING_AGG concatenates values across rows with a selected delimiter, subject to ordering and type considerations; that function is not interchangeable with FOR JSON PATH for the requested result.
C:JSON_OBJECT composes named key-value JSON data without fragile manual quote and comma concatenation; it addresses a different step; the question calls for FOR JSON PATH.
D:JSON_ARRAYAGG aggregates expressions into JSON arrays using supported SQL engine functionality; this differs from the FOR JSON PATH requirement described in the scenario.
Question 472
An assistant includes document links, but auditors need to verify that each cited chunk actually supports the accompanying statement. Which approach should the team select?
- Citation correctness check
- Groundedness evaluation
- Retrieval recall benchmark
- Answer-latency budget
Explanation
Correct Answer
A:Citation evaluation checks correspondence between produced claims and referenced evidence instead of merely counting links.
Incorrect Answers
B:Groundedness evaluation compares answer claims with supplied evidence, helping flag unsupported generation; the condition in the stem is resolved by Citation correctness check, not by this feature alone.
C:Measuring retrieval recall on labeled questions isolates missing-evidence failures from generation mistakes; the missing behavior is Citation correctness check; this mechanism does something else.
D:Stage-level latency instrumentation attributes end-to-end delay to retrieval, context assembly, and generation for targeted optimization; that function is not interchangeable with Citation correctness check for the requested result.
Question 473
A separate model evaluator emits raw dot-product similarity scores and must rank the larger value as more aligned. Which option would you recommend?
- Negative dot-product distance
- Lower cosine distance
- Higher inner-product similarity
- Lower Euclidean distance
Explanation
Correct Answer
C:Ordinary dot-product similarity is larger for better alignment under its assumptions, unlike SQL's negative-dot distance convention.
Incorrect Answers
A:The supported dot metric returns negative dot-product distance, so ascending output still corresponds to stronger positive dot products; it addresses a different step; the question calls for Higher inner-product similarity.
B:For VECTOR_DISTANCE with cosine metric, smaller distances indicate closer angular similarity, so ascending order is appropriate; this differs from the Higher inner-product similarity requirement described in the scenario.
D:Euclidean distance measures separation, so lower values indicate nearer vectors under that metric; the alternative cannot substitute for Higher inner-product similarity under the given constraints.
Question 474
A developer must change a column type in source control and expects one authoritative CREATE TABLE definition to represent the desired state. What is the most appropriate design decision?
- Declarative object file
- SqlPackage DeployReport
- Post-deployment seed script
- SqlPackage Script
Explanation
Correct Answer
A:SQL projects store declarative object definitions, which DacFx compares with deployed schemas to derive necessary changes.
Incorrect Answers
B:DeployReport describes planned deployment changes derived from model comparison and supports approval workflows; that function is not interchangeable with Declarative object file for the requested result.
C:A seed script updates reference data after publication; it is not the authoritative declarative CREATE TABLE definition for a column type.
D:SqlPackage Script emits deployment SQL instead of immediately publishing the changes to the target database; it addresses a different step; the question calls for Declarative object file.
Question 475
Booking rows must be rejected when CheckOutDate precedes CheckInDate; the validation compares two columns in the same row, including writes from any client. Which mechanism addresses the requirement?
- PRIMARY KEY constraint
- CHECK constraint
- FOREIGN KEY constraint
- DEFAULT constraint
Explanation
Correct Answer
B: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.
Incorrect Answers
A:A primary key declares the table's principal identifier and enforces uniqueness plus nonnullability through an associated index; the required capability here is CHECK constraint, which this operation does not supply.
C:A foreign key checks referential integrity between a referencing column and a candidate key in another or the same table; the condition in the stem is resolved by CHECK constraint, not by this feature alone.
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; the missing behavior is CHECK constraint; this mechanism does something else.
Question 476
Two large join inputs are already ordered on their join keys, allowing the engine to stream through matches efficiently. Which choice best meets these constraints?
- Merge Join
- Adaptive Join operator
- Hash Match join
- Nested Loops join
Explanation
Correct Answer
A:Merge Join exploits compatible ordering for efficient matching, though sorting unordered inputs can add significant cost.
Incorrect Answers
B:An adaptive join chooses among physical join strategies based on runtime row counts; it is not a promise of the ordered merge-join streaming behavior described.
C:Hash Match uses a hash table to match join keys and often suits large unordered joins at a memory cost; this differs from the Merge Join requirement described in the scenario.
D:Nested Loops repeatedly probes an inner input for outer rows and can be efficient for selective joins with good indexes; the required capability here is Merge Join, which this operation does not supply.
Question 477
A self-hosted SQL Server 2025 deployment is considering approximate vector indexes and must account for preview status and configuration. What is the right solution for this specific requirement?
- Version-specific syntax review
- Exact SQL vector functions
- SQL Server 2025 preview indexing
- Azure SQL generally available vector index
Explanation
Correct Answer
C:On SQL Server 2025, vector indexing and VECTOR_SEARCH have preview conditions that differ from generally available Azure SQL support.
Incorrect Answers
A:Current documentation favors SELECT TOP (N) WITH APPROXIMATE for modern indexes, while older TOP_N syntax remains a compatibility concern; the condition in the stem is resolved by SQL Server 2025 preview indexing, not by this feature alone.
B:VECTOR and VECTOR_DISTANCE provide exact vector operations separately from optional approximate index support; the missing behavior is SQL Server 2025 preview indexing; this mechanism does something else.
D:Vector indexes and approximate VECTOR_SEARCH are generally available in Azure SQL Database, with version-specific syntax requirements; for the stated conditions, the deciding feature is SQL Server 2025 preview indexing rather than this behavior.
Question 478
A Data API builder deployment must allow a consumer role to read Orders but deny create and delete actions through that API. What is the right solution for this specific requirement?
- Restricted DAB entity permissions
- Stored procedure execution boundary
- Parameterized SQL statements
- GraphQL relationship configuration
Explanation
Correct Answer
A:DAB entity permissions can limit operations exposed to each role, complementing database-side permissions.
Incorrect Answers
B:Exposing a reviewed procedure with EXECUTE permission provides a constrained interface compared with granting direct table writes; the required capability here is Restricted DAB entity permissions, which this operation does not supply.
C:SQL parameters separate supplied values from executable query syntax and are a core mitigation against injection; the alternative cannot substitute for Restricted DAB entity permissions under the given constraints.
D:GraphQL relationship metadata describes navigable associations between exposed entities; it is not a Reader role operation allow/deny policy.
Question 479
An app requires read-committed semantics on a database configured for conventional shared-lock-based reads. What is the best primary mechanism to use?
- READ COMMITTED locking
- READ_COMMITTED_SNAPSHOT
- SET TRANSACTION ISOLATION LEVEL SNAPSHOT
- SERIALIZABLE
Explanation
Correct Answer
A:Locking READ COMMITTED prevents dirty reads but readers can block behind writers according to lock acquisition.
Incorrect Answers
B:Enabling READ_COMMITTED_SNAPSHOT changes the database's READ COMMITTED read behavior to statement-level row-version snapshots; for the stated conditions, the deciding feature is READ COMMITTED locking rather than this behavior.
C:SNAPSHOT isolation gives a version-consistent transaction-level view, unlike statement-level read-committed snapshots; it addresses a different step; the question calls for READ COMMITTED locking.
D:SERIALIZABLE applies stronger range-protection behavior at the cost of concurrency, especially for indexed predicates; this differs from the READ COMMITTED locking requirement described in the scenario.
Question 480
An approved SQL RAG procedure already has the database permissions for outbound calls. It must send a prepared JSON prompt with a SQL REST procedure, using a named database-scoped endpoint credential specified by policy rather than inline secret headers or a managed identity. Which TWO components meet these specific requirements? Choose TWO.
- External-endpoint permission
- Managed identity authorization
- sp_invoke_external_rest_endpoint
- DATABASE SCOPED CREDENTIAL
- STRING_AGG
Explanation
Correct Answers
C:sp_invoke_external_rest_endpoint invokes HTTPS endpoints with configured payload, headers, timeout, and credentials where supported; used in combination with DATABASE SCOPED CREDENTIAL, it handles a separate requirement of this combined workflow.
D:A DATABASE SCOPED CREDENTIAL provides SQL-managed endpoint authentication material referenced by outbound calls, satisfying the specified named-object policy instead of embedding a literal key.
Incorrect Answers
A:EXECUTE ANY EXTERNAL ENDPOINT is the relevant database permission for calling the supported external REST procedure; the alternative cannot substitute for sp_invoke_external_rest_endpoint and DATABASE SCOPED CREDENTIAL under the given constraints.
B:Managed identity authentication can authorize a supported outbound model call when the identity has the required destination role; the condition in the stem is resolved by sp_invoke_external_rest_endpoint and DATABASE SCOPED CREDENTIAL, not by this feature alone.
E:STRING_AGG concatenates values across rows with a selected delimiter, subject to ordering and type considerations; for the stated conditions, the deciding feature is sp_invoke_external_rest_endpoint and DATABASE SCOPED CREDENTIAL rather than this behavior.