Question 500
A catalog contains paragraphs and needs numeric semantic representations that can be stored in a VECTOR column for similarity search. Which action is justified by these conditions?
- Structured-output language model
- Multilingual embedding model
- Text embedding model
- Multimodal model
Explanation
Correct Answer
C:An embedding model maps input content to numeric vectors for semantic comparison rather than producing a conversational answer.
Incorrect Answers
A:A generation model with schema-constrained structured output can produce predictable field layouts for downstream validation; for the stated conditions, the deciding feature is Text embedding model rather than this behavior.
B:A multilingual embedding model can represent semantically related text across supported languages in a shared vector space; it addresses a different step; the question calls for Text embedding model.
D:A multimodal model handles supported image and text inputs together, unlike text-only embedding or generation models; the required capability here is Text embedding model, which this operation does not supply.
Question 501
Lexical and vector searches return overlapping document identifiers and the system must combine them before computing an RRF rank. Which action is justified by these conditions?
- Approximate top-k search
- Post-retrieval reranking
- TOP with ordered distance
- Hybrid union of candidate IDs
Explanation
Correct Answer
D:Joining or unioning candidate IDs from both sources allows duplicate documents to receive contributions from multiple ranking lists.
Incorrect Answers
A:Approximate TOP with VECTOR_SEARCH can leverage supported vector indexes for low-latency ANN retrieval; the condition in the stem is resolved by Hybrid union of candidate IDs, not by this feature alone.
B:A reranking stage refines top candidates after initial retrieval, reducing cost compared with full-corpus cross-encoding; the missing behavior is Hybrid union of candidate IDs; this mechanism does something else.
C:Ordering by the computed distance ascending and selecting TOP limits results to nearest eligible vectors under exact scoring; that function is not interchangeable with Hybrid union of candidate IDs for the requested result.
Question 502
Generated SQL should be evaluated on realistic input shapes without transferring customer-identifiable production records to the test environment. What is the right solution for this specific requirement?
- Parameterize generated SQL
- Synthetic test data
- Empty schema-only test database
- Schema metadata only
Explanation
Correct Answer
B:Synthetic records can reproduce structural and edge-case patterns while reducing the need to expose personal production data.
Incorrect Answers
A:Parameterized execution separates user data from executable SQL, reducing injection risk even if the initial query text was AI-assisted; this differs from the Synthetic test data requirement described in the scenario.
C:A schema-only environment has no realistic data distributions or edge cases until a separate data generation process populates it.
D:Restricting context to schema metadata can support query drafting without exposing record contents to the model; the condition in the stem is resolved by Synthetic test data, not by this feature alone.
Question 503
A serverless handler needs to read rows from Azure SQL as input to its function without writing full connection plumbing. What should be configured to meet this objective?
- CDC-based ingestion service
- Azure Functions SQL output binding
- Azure Functions SQL input binding
- Azure Logic Apps SQL connector
Explanation
Correct Answer
C:A SQL input binding can supply configured database query results to a supported Azure Function invocation.
Incorrect Answers
A:A CDC consumer reads durable change captures and can reconstruct supported insert/update/delete change activity; that function is not interchangeable with Azure Functions SQL input binding for the requested result.
B:A SQL output binding can persist supported output values through configured SQL connectivity and binding semantics; for the stated conditions, the deciding feature is Azure Functions SQL input binding rather than this behavior.
D:Logic Apps offers managed connectors and workflow orchestration for database-triggered or database-driven business processes; this differs from the Azure Functions SQL input binding requirement described in the scenario.
Question 504
A service principal needs INSERT on a designated staging table, not membership in a broad writer role for all schemas. What should the developer implement?
- Row-level security policy
- Database permission GRANT
- View-based projection
- Endpoint role authorization
Explanation
Correct Answer
B:An object-scoped GRANT provides the minimum necessary database operation at the specified object boundary.
Incorrect Answers
A:An RLS security policy applies row predicates at the database tier rather than depending on every client to implement a filter; the alternative cannot substitute for Database permission GRANT under the given constraints.
C:Granting SELECT on a narrow view and restricting base-table access limits the visible projection when the execution and ownership model permit it; the missing behavior is Database permission GRANT; this mechanism does something else.
D:Gateway authorization maps authenticated principals to permitted API operations and should complement database enforcement; that function is not interchangeable with Database permission GRANT for the requested result.
Question 505
A developer needs to choose an appropriate available conversational model for a code-review session rather than changing the SQL engine's query optimizer. What is the best primary mechanism to use?
- Chat model selection
- MCP server configuration
- Repository copilot-instructions.md
- Chat tool permissions
Explanation
Correct Answer
A:The assistant's model selection controls model behavior in the supported chat tool, not the runtime semantics of deployed T-SQL.
Incorrect Answers
B:Configuring an MCP server endpoint tells an MCP-capable client which tool host it may contact, subject to authentication and policy; this differs from the Chat model selection requirement described in the scenario.
C:A repository instruction file can communicate persistent project-specific guidance to compatible GitHub Copilot tooling, without changing database enforcement rules; the required capability here is Chat model selection, which this operation does not supply.
D:Tool permission and approval settings control reachable actions in the assistant session and need restriction before permitting database access; the alternative cannot substitute for Chat model selection under the given constraints.
Question 506
Two ordinary relational tables share CustomerId and need rows for matching identifiers; no graph tables exist. Which technique applies to this scenario?
- EXISTS subquery
- MATCH graph predicate
- Regular INNER JOIN
- SHORTEST_PATH graph pattern
Explanation
Correct Answer
C:INNER JOIN pairs rows based on a join condition and is the direct relational choice without graph schema requirements.
Incorrect Answers
A:EXISTS tests for the presence of qualifying related rows without multiplying outer rows by the number of matches; the missing behavior is Regular INNER JOIN; this mechanism does something else.
B:MATCH describes node-edge patterns for SQL graph queries rather than expressing every relationship as hand-written identifier joins; that function is not interchangeable with Regular INNER JOIN for the requested result.
D:SHORTEST_PATH works with supported graph path patterns and evaluates a shortest qualifying traversal, not arbitrary relational sorting; it addresses a different step; the question calls for Regular INNER JOIN.
Question 507
A trigger processing a multirow INSERT needs the new values for every changed row, not just the most recently processed record. Which choice best meets these constraints?
- deleted logical table
- inserted logical table
- INSTEAD OF DELETE trigger
- OUTPUT INSERTED clause
Explanation
Correct Answer
B:The inserted logical table contains newly inserted or updated row versions available to a DML trigger and should be handled set-wise.
Incorrect Answers
A:The deleted logical table contains prior versions of updated or deleted rows, supporting set-based old-versus-new comparisons; the required capability here is inserted logical table, which this operation does not supply.
C:An INSTEAD OF DELETE trigger can replace a requested deletion with custom SQL logic, including soft-delete behavior; the condition in the stem is resolved by inserted logical table, not by this feature alone.
D:The OUTPUT clause can expose affected rows from the original DML statement to its consumer; inside a trigger, the required set-based pseudo-table is inserted.
Question 508
A global embedding corpus contains records from multiple tenants, and relevance ranking must not expose unauthorized neighbors across tenants. Which SQL capability is the most direct fit?
- Embedding normalization
- Model dimension compatibility
- VECTOR(1536) column
- Metadata-filtered retrieval
Explanation
Correct Answer
D:Enforcing tenant metadata and database authorization filters during retrieval constrains candidate content independently of vector similarity.
Incorrect Answers
A:Normalization scales vectors to a consistent norm for appropriate metrics, which can make some similarity comparisons more consistent; for the stated conditions, the deciding feature is Metadata-filtered retrieval rather than this behavior.
B:Query and stored vectors must have compatible dimensionality and semantic model space for meaningful distance calculations; it addresses a different step; the question calls for Metadata-filtered retrieval.
C:VECTOR(n) declares the element count for a vector, so stored values must match the intended embedding dimension; this differs from the Metadata-filtered retrieval requirement described in the scenario.
Question 509
A document appears near the top of both lexical and vector results, and the merger should reward evidence from both channels. Which option would you recommend?
- Dual-list presence
- Rank fusion without score calibration
- Higher rank position
- Reciprocal-rank contribution
Explanation
Correct Answer
A:RRF aggregates contributions across lists, so consistently highly ranked documents can outrank one-list specialists.
Incorrect Answers
B:RRF works on rank positions, avoiding a need to equate arbitrary lexical and vector score magnitudes; the missing behavior is Dual-list presence; this mechanism does something else.
C:Reciprocal rank scoring gives a larger contribution to the higher-ranked item because its rank denominator is smaller; that function is not interchangeable with Dual-list presence for the requested result.
D:RRF sums reciprocal-rank terms for each ranking list where a document appears, so missing-list contributions are simply absent; for the stated conditions, the deciding feature is Dual-list presence rather than this behavior.
Question 510
A team must investigate whether a secondary index receives many reads relative to its update overhead before deciding to retain it. What is the most suitable implementation?
- sys.dm_os_wait_stats
- sys.dm_exec_requests
- sys.dm_exec_query_stats
- sys.dm_db_index_usage_stats
Explanation
Correct Answer
D:Index usage DMVs provide counters for seeks, scans, lookups, and updates, with reset and interpretation limitations.
Incorrect Answers
A:sys.dm_os_wait_stats aggregates wait information at the server scope and must be interpreted alongside workload context; this differs from the sys.dm_db_index_usage_stats requirement described in the scenario.
B:sys.dm_exec_requests exposes live request information such as waits and blocking identifiers, rather than long-term history; the required capability here is sys.dm_db_index_usage_stats, which this operation does not supply.
C:sys.dm_exec_query_stats exposes aggregate statistics for queries represented in the plan cache, subject to cache eviction; the alternative cannot substitute for sys.dm_db_index_usage_stats under the given constraints.