Question 260
Vector results are slow because almost all rows belong to ineligible tenants; engineers need to evaluate filtering strategy and candidate sizes. Which option satisfies the desired behavior most directly?
- ANN index evaluation
- Distance-computation cost
- Filter-selectivity assessment
- Top-k selection
Explanation
Correct Answer
C:Selective eligibility predicates and supported filter execution behavior affect vector retrieval work and correctness.
Incorrect Answers
A:ANN indexing can reduce comparisons and improve speed, subject to measured recall and platform support; for the stated conditions, the deciding feature is Filter-selectivity assessment rather than this behavior.
B:Exhaustive distance calculations scale with candidate count and vector dimension, creating CPU and sorting pressure; it addresses a different step; the question calls for Filter-selectivity assessment.
D:Limiting k reduces result volume and downstream processing while preserving the intended nearest-neighbor objective; the required capability here is Filter-selectivity assessment, which this operation does not supply.
Question 261
A month-end process issues several SELECT statements that must all see the same transaction-start database image. Which feature would solve the stated problem?
- REPEATABLE READ
- SNAPSHOT isolation
- READ COMMITTED SNAPSHOT
- SERIALIZABLE
Explanation
Correct Answer
B:SNAPSHOT delivers transaction-consistent versioned reads while maintaining update conflict considerations.
Incorrect Answers
A:REPEATABLE READ holds appropriate locks to protect previously read rows, but qualifying new rows may still appear on subsequent range queries; the condition in the stem is resolved by SNAPSHOT isolation, not by this feature alone.
C:Statement-level row versioning under RCSI can allow committed reads without ordinary shared read locks; that function is not interchangeable with SNAPSHOT isolation for the requested result.
D:SERIALIZABLE applies range semantics that protect qualifying predicate ranges against conflicting concurrent insertions; for the stated conditions, the deciding feature is SNAPSHOT isolation rather than this behavior.
Question 262
A large equijoin has substantial input volume and no helpful ordering, making a build-and-probe strategy appropriate if memory permits. Which mechanism addresses the requirement?
- Merge Join
- Sort operator
- Hash Match join
- Nested Loops join
Explanation
Correct Answer
C:Hash Match uses a hash table to match join keys and often suits large unordered joins at a memory cost.
Incorrect Answers
A:Merge Join exploits compatible ordering for efficient matching, though sorting unordered inputs can add significant cost; this differs from the Hash Match join requirement described in the scenario.
B:Sort produces ordered rows for ORDER BY or other operators and can become expensive or spill when memory is insufficient; the required capability here is Hash Match join, which this operation does not supply.
D:Nested Loops repeatedly probes an inner input for outer rows and can be efficient for selective joins with good indexes; the condition in the stem is resolved by Hash Match join, not by this feature alone.
Question 263
A DBA needs to compare a statement's SQL execution duration before and after an index deployment, not just compute overall database CPU. Which option would you recommend?
- Azure SQL Query Performance Insight
- Log Analytics diagnostic queries
- Query Store runtime report
- Azure Monitor metrics
Explanation
Correct Answer
C:Query Store retains statement-level plan and runtime aggregates suitable for comparing the same query across intervals.
Incorrect Answers
A:Query Performance Insight provides a focused view of captured SQL query performance metrics and historical trends; that function is not interchangeable with Query Store runtime report for the requested result.
B:A Log Analytics workspace stores forwarded log records and supports cross-resource KQL analysis where diagnostics are configured; for the stated conditions, the deciding feature is Query Store runtime report rather than this behavior.
D:Azure Monitor resource metrics expose time-series consumption and pressure signals for capacity planning, but do not replace Query Store for statement-level plan diagnosis; this differs from the Query Store runtime report requirement described in the scenario.
Question 264
A product-search application must interpret both an uploaded equipment photo and a short text instruction in one model interaction. Which approach should the team select?
- Multimodal model
- Text embedding model
- Structured-output language model
- Multilingual embedding model
Explanation
Correct Answer
A:A multimodal model handles supported image and text inputs together, unlike text-only embedding or generation models.
Incorrect Answers
B:An embedding model maps input content to numeric vectors for semantic comparison rather than producing a conversational answer; the condition in the stem is resolved by Multimodal model, not by this feature alone.
C:A generation model with schema-constrained structured output can produce predictable field layouts for downstream validation; the missing behavior is Multimodal model; this mechanism does something else.
D:A multilingual embedding model can represent semantically related text across supported languages in a shared vector space; that function is not interchangeable with Multimodal model for the requested result.
Question 265
All products must appear beside optional category descriptions, including products with no matching category row. Which mechanism addresses the requirement?
- INNER JOIN
- OUTER APPLY
- LEFT JOIN
- CROSS APPLY
Explanation
Correct Answer
C:A LEFT OUTER JOIN preserves each left-side row and supplies NULL for missing right-side matches.
Incorrect Answers
A:An INNER JOIN returns qualifying pairs only and excludes rows lacking a match under its ON predicate; it addresses a different step; the question calls for LEFT JOIN.
B:OUTER APPLY preserves unmatched outer rows while evaluating a correlated table expression for each one; this differs from the LEFT JOIN requirement described in the scenario.
D:CROSS APPLY joins each input row to the output of a correlated rowset expression, without retaining empty expansions; the alternative cannot substitute for LEFT JOIN under the given constraints.
Question 266
An Azure-hosted SQL-to-model integration must obtain service authorization through its assigned workload identity without storing any API key or self-managed client credential. Which authentication approach fits?
- Azure Key Vault
- HTTPS/TLS endpoint
- Scoped token authorization
- Managed identity for model endpoint
Explanation
Correct Answer
D:Managed identity lets an Azure workload acquire service access tokens for authorized model endpoints without storing a reusable API key or separate client secret in the SQL code.
Incorrect Answers
A:Key Vault secures secrets and cryptographic materials through authenticated access and audit controls instead of embedding values in repository files; the missing behavior is Managed identity for model endpoint; this mechanism does something else.
B:TLS encrypts data in transit and authenticates the server endpoint, distinct from authorization to specific database rows; that function is not interchangeable with Managed identity for model endpoint for the requested result.
C:Server-side token validation and authorization check claims and permissions rather than treating any well-formed bearer token as sufficient; for the stated conditions, the deciding feature is Managed identity for model endpoint rather than this behavior.
Question 267
Two procedures deadlock because they visit Account and Balance rows in opposite order. The investigation requires the engine-produced wait-cycle evidence, and the preventive code change must make both procedures access the objects in a consistent order. Which TWO items satisfy those specified needs? Choose TWO.
- Consistent object access order
- SET TRANSACTION ISOLATION LEVEL SNAPSHOT
- Deadlock graph
- Shorten transaction duration
- Blocking chain inspection
Explanation
Correct Answers
A:Accessing shared objects in a consistent order can prevent a circular lock acquisition pattern across concurrent transactions; used in combination with Deadlock graph, it handles a separate requirement of this combined workflow.
C:A deadlock graph shows participants, resources, and the victim selection, clarifying the cyclic dependency; used in combination with Consistent object access order, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:SNAPSHOT isolation gives a version-consistent transaction-level view, unlike statement-level read-committed snapshots; the alternative cannot substitute for Consistent object access order and Deadlock graph under the given constraints.
D:Moving slow external work outside short database transactions reduces lock retention and contention windows; the missing behavior is Consistent object access order and Deadlock graph; this mechanism does something else.
E:Analyzing blocking sessions and waits identifies which session owns the incompatible lock that others need; that function is not interchangeable with Consistent object access order and Deadlock graph for the requested result.
Question 268
A configured MCP server is reachable, but the assistant cannot invoke a requested SQL action because it is not present in the server's exposed tool list. Which option satisfies the desired behavior most directly?
- Unavailable MCP tool
- Authentication failure from tool server
- Stale schema context
- Unsafe prompt injection in data
Explanation
Correct Answer
A:Tool discovery depends on the capabilities exposed by the server; adding a prompt instruction cannot create a missing backend tool.
Incorrect Answers
B:The server's database identity needs valid authentication and the necessary SQL permissions; chat access alone does not grant data access; it addresses a different step; the question calls for Unavailable MCP tool.
C:Refreshing schema metadata before generation or execution avoids relying on obsolete object definitions; this differs from the Unavailable MCP tool requirement described in the scenario.
D:Data returned from a tool is lower-trust context, and its instructions must not override tool policies or reveal confidential secrets; the required capability here is Unavailable MCP tool, which this operation does not supply.
Question 269
A SQL event procedure builds an object with version and payload properties. Its optional diagnostic field must be entirely omitted when the SQL expression is NULL, instead of appearing as JSON null. Which choice directly controls this output?
- JSON_ARRAYAGG
- JSON_OBJECT with ABSENT ON NULL
- FOR JSON PATH
- CROSS APPLY OPENJSON
Explanation
Correct Answer
B:JSON_OBJECT constructs the named event fields, and ABSENT ON NULL omits any key whose SQL value is NULL; the default NULL ON NULL would retain that key with a JSON null value.
Incorrect Answers
A:JSON_ARRAYAGG combines row values into an array, not a keyed version/payload object with NULL-property suppression.
C:FOR JSON PATH can serialize relational result sets; this question asks for explicit optional-property omission in one JSON_OBJECT expression, not rowset serialization.
D:CROSS APPLY OPENJSON expands an existing JSON value into relational rows; it does not construct the output envelope.
Question 270
A developer receives a generated SQL statement that appears plausible and needs to verify output on actual test data before adoption. Which database mechanism should be chosen?
- SQL endpoint authorization
- Fabric tenant feature settings
- Copilot in Fabric chat
- Execute and inspect results
Explanation
Correct Answer
D:Running the query against representative data and reviewing results validates behavior; an AI suggestion is not a substitute for execution.
Incorrect Answers
A:Database authorization still applies when queries are proposed by Copilot, so appropriate grants are required separately; this differs from the Execute and inspect results requirement described in the scenario.
B:Tenant-level Fabric settings can govern Copilot availability, with scope and capacity prerequisites depending on the specific feature; the required capability here is Execute and inspect results, which this operation does not supply.
C:Microsoft Copilot in Fabric offers AI-assisted authoring in supported Fabric workloads and remains subject to tenant and capacity configuration; the alternative cannot substitute for Execute and inspect results under the given constraints.