Question 240
Ten analysts need identical table-level SELECT grants and changes should be administered at one shared membership location. Which database mechanism should be chosen?
- Database role membership
- Certificate-signed SQL module
- Group-based database access
- Dedicated managed identity
Explanation
Correct Answer
A:A database role collects permissions for members and reduces the need to duplicate identical grants on individual principals.
Incorrect Answers
B:Signing an SQL module can attach narrowly scoped certificate-associated permissions during execution without broad user privilege escalation; the condition in the stem is resolved by Database role membership, not by this feature alone.
C:Group-backed principals allow permissions to be managed through authorized group membership while database grants remain scoped; the missing behavior is Database role membership; this mechanism does something else.
D:A dedicated managed identity offers a workload principal whose token authorization can be controlled independently of human account lifecycle; that function is not interchangeable with Database role membership for the requested result.
Question 241
An expensive model should rerank only a small set of shortlisted search candidates rather than every document in the corpus. What is the best primary mechanism to use?
- Approximate top-k search
- Hybrid union of candidate IDs
- Post-retrieval reranking
- TOP with ordered distance
Explanation
Correct Answer
C:A reranking stage refines top candidates after initial retrieval, reducing cost compared with full-corpus cross-encoding.
Incorrect Answers
A:Approximate TOP with VECTOR_SEARCH can leverage supported vector indexes for low-latency ANN retrieval; it addresses a different step; the question calls for Post-retrieval reranking.
B:Joining or unioning candidate IDs from both sources allows duplicate documents to receive contributions from multiple ranking lists; this differs from the Post-retrieval reranking requirement described in the scenario.
D:Ordering by the computed distance ascending and selecting TOP limits results to nearest eligible vectors under exact scoring; the alternative cannot substitute for Post-retrieval reranking under the given constraints.
Question 242
A new prompt produces fluent answers that sometimes contain statements not supported by the retrieved documents, and the team needs a quality check. Which approach should the team select?
- Groundedness evaluation
- Retrieval recall benchmark
- Answer-latency budget
- Citation correctness check
Explanation
Correct Answer
A:Groundedness evaluation compares answer claims with supplied evidence, helping flag unsupported generation.
Incorrect Answers
B:Measuring retrieval recall on labeled questions isolates missing-evidence failures from generation mistakes; that function is not interchangeable with Groundedness evaluation for the requested result.
C:Stage-level latency instrumentation attributes end-to-end delay to retrieval, context assembly, and generation for targeted optimization; for the stated conditions, the deciding feature is Groundedness evaluation rather than this behavior.
D:Citation evaluation checks correspondence between produced claims and referenced evidence instead of merely counting links; it addresses a different step; the question calls for Groundedness evaluation.
Question 243
An RLS policy needs a schema-bound function to evaluate whether the current session's tenant may access a row. Which action is justified by these conditions?
- CREATE SECURITY POLICY
- EXECUTE AS for testing
- SESSION_CONTEXT
- Inline table-valued predicate function
Explanation
Correct Answer
D:RLS uses predicate functions to express per-row access rules evaluated by security policies, often with SCHEMABINDING.
Incorrect Answers
A:CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; the required capability here is Inline table-valued predicate function, which this operation does not supply.
B:EXECUTE AS allows supported impersonation for evaluating effective permissions and RLS behavior, without permanently changing the policy; the alternative cannot substitute for Inline table-valued predicate function under the given constraints.
C:SESSION_CONTEXT exposes key-value session state that an RLS predicate can read, provided the application prevents identity spoofing; the condition in the stem is resolved by Inline table-valued predicate function, not by this feature alone.
Question 244
Before a release, reviewers want an XML description of the expected target schema changes without executing the deployment. What is the best primary mechanism to use?
- Declarative object file
- SqlPackage DeployReport
- Schema Compare
- SqlPackage Script
Explanation
Correct Answer
B:DeployReport describes planned deployment changes derived from model comparison and supports approval workflows.
Incorrect Answers
A:SQL projects store declarative object definitions, which DacFx compares with deployed schemas to derive necessary changes; for the stated conditions, the deciding feature is SqlPackage DeployReport rather than this behavior.
C:Schema Compare contrasts schema definitions from two sources and can help identify drift and reconcile them; this differs from the SqlPackage DeployReport requirement described in the scenario.
D:SqlPackage Script emits deployment SQL instead of immediately publishing the changes to the target database; the required capability here is SqlPackage DeployReport, which this operation does not supply.
Question 245
A search service is migrating to a new model dimension and must prevent old and new vectors from being silently compared as though compatible. Which database mechanism should be chosen?
- Versioned embedding column
- Update-on-change processing
- Delete propagation
- Idempotent embedding upsert
Explanation
Correct Answer
A:Storing embedding-model identity or version with each vector permits controlled migration and query-to-corpus compatibility checks.
Incorrect Answers
B:Change-driven maintenance processes affected rows and preserves a checkpoint for incremental vector refresh; the missing behavior is Versioned embedding column; this mechanism does something else.
C:A deletion-aware sync process removes or invalidates vectors and searchable text tied to the erased source record; that function is not interchangeable with Versioned embedding column for the requested result.
D:Upsert by a stable document/chunk/version key allows retries without duplicating records, supporting at-least-once processing; for the stated conditions, the deciding feature is Versioned embedding column rather than this behavior.
Question 246
An older sample uses deprecated TOP_N inside VECTOR_SEARCH; an engineer needs the current recommended approximate TOP form where available. What is the most appropriate design decision?
- Version-specific syntax review
- SQL Server 2025 preview indexing
- Exact SQL vector functions
- Azure SQL generally available vector index
Explanation
Correct Answer
A:Current documentation favors SELECT TOP (N) WITH APPROXIMATE for modern indexes, while older TOP_N syntax remains a compatibility concern.
Incorrect Answers
B:On SQL Server 2025, vector indexing and VECTOR_SEARCH have preview conditions that differ from generally available Azure SQL support; the required capability here is Version-specific syntax review, which this operation does not supply.
C:VECTOR and VECTOR_DISTANCE provide exact vector operations separately from optional approximate index support; the alternative cannot substitute for Version-specific syntax review under the given constraints.
D:Vector indexes and approximate VECTOR_SEARCH are generally available in Azure SQL Database, with version-specific syntax requirements; the condition in the stem is resolved by Version-specific syntax review, not by this feature alone.
Question 247
New catalog descriptions should produce embeddings asynchronously when a change-tracking-based function detects a write. Which action is justified by these conditions?
- AI_GENERATE_EMBEDDINGS
- Azure Function on change event
- External batching worker
- Database trigger with guarded work
Explanation
Correct Answer
B:A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes.
Incorrect Answers
A:AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; that function is not interchangeable with Azure Function on change event for the requested result.
C:An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions; it addresses a different step; the question calls for Azure Function on change event.
D:A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously; this differs from the Azure Function on change event requirement described in the scenario.
Question 248
In a TRY/CATCH procedure, a failed transaction has entered state -1; the handler needs to detect that the transaction cannot commit or write additional log rows until it is rolled back. What is the most appropriate design decision?
- SAVE TRANSACTION
- BEGIN TRY / BEGIN CATCH
- THROW without arguments
- TRY_CONVERT
- XACT_STATE()
Explanation
Correct Answer
E:XACT_STATE() returns -1 for an uncommittable active transaction, permitting only rollback and read operations until that transaction is ended.
Incorrect Answers
A:SAVE TRANSACTION establishes a savepoint inside an active transaction, allowing partial rollback when the transaction's state permits it; the alternative cannot substitute for XACT_STATE() under the given constraints.
B:TRY...CATCH provides a structured handler for many run-time T-SQL errors, allowing recovery code to run in the CATCH branch; the condition in the stem is resolved by XACT_STATE(), not by this feature alone.
C:THROW without arguments inside CATCH rethrows the caught exception, preserving its original error details rather than inventing a replacement exception; the missing behavior is XACT_STATE(); this mechanism does something else.
D:TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; that function is not interchangeable with XACT_STATE() for the requested result.
Question 249
The customer-record schema must reject any insertion where RequiredCountry is NULL, including updates that try to clear the field. Which option would you recommend?
- UNIQUE constraint
- ON DELETE CASCADE foreign key action
- CHECK with IN predicate
- NOT NULL constraint
Explanation
Correct Answer
D:NOT NULL forbids the NULL marker on the designated column, regardless of whether a default exists.
Incorrect Answers
A:UNIQUE enforces a candidate key distinct from the primary key and prevents duplicate non-NULL key combinations according to platform semantics; it addresses a different step; the question calls for NOT NULL constraint.
B:A cascading DELETE relationship automatically deletes referencing child rows when the referenced parent is deleted; this differs from the NOT NULL constraint requirement described in the scenario.
C:A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation; the required capability here is NOT NULL constraint, which this operation does not supply.
Question 250
A hosted inference integration needs independently revocable Azure permissions that are not tied to an engineer's personal login. Which option would you recommend?
- Database role membership
- Personal developer token
- Group-based database access
- Dedicated managed identity
Explanation
Correct Answer
D:A dedicated managed identity offers a workload principal whose token authorization can be controlled independently of human account lifecycle.
Incorrect Answers
A:A database role collects permissions for members and reduces the need to duplicate identical grants on individual principals; the missing behavior is Dedicated managed identity; this mechanism does something else.
B:A personal developer token ties service access to an individual and may require rotation if that person leaves; it is not a separate revocable workload identity.
C:Group-backed principals allow permissions to be managed through authorized group membership while database grants remain scoped; for the stated conditions, the deciding feature is Dedicated managed identity rather than this behavior.