Question 150
A skewed predicate targets a small subset whose distribution differs sharply from the full table, and estimates need subset-specific data. What should the developer implement?
- Index physical maintenance
- Actual vs estimated row comparison
- Filtered statistics
- UPDATE STATISTICS
Explanation
Correct Answer
C:Filtered statistics capture distributions for rows matching a predicate and can improve estimates for compatible queries.
Incorrect Answers
A:Appropriate index rebuild or reorganize actions address physical layout while statistics alone cannot fix page fragmentation; this differs from the Filtered statistics requirement described in the scenario.
B:Comparing estimated and actual rows at plan operators reveals estimation errors that can explain inappropriate operator choices; the required capability here is Filtered statistics, which this operation does not supply.
D:Updating statistics refreshes cardinality information used during plan selection, though it does not directly rebuild every index; the condition in the stem is resolved by Filtered statistics, not by this feature alone.
Question 151
A search endpoint needs only the most similar eight documents rather than returning thousands of ordered candidates. What is the most suitable implementation?
- Top-k selection
- ANN index evaluation
- Filter-selectivity assessment
- Distance-computation cost
Explanation
Correct Answer
A:Limiting k reduces result volume and downstream processing while preserving the intended nearest-neighbor objective.
Incorrect Answers
B:ANN indexing can reduce comparisons and improve speed, subject to measured recall and platform support; for the stated conditions, the deciding feature is Top-k selection rather than this behavior.
C:Selective eligibility predicates and supported filter execution behavior affect vector retrieval work and correctness; it addresses a different step; the question calls for Top-k selection.
D:Exhaustive distance calculations scale with candidate count and vector dimension, creating CPU and sorting pressure; this differs from the Top-k selection requirement described in the scenario.
Question 152
A workflow must coordinate SQL records with approvals and SaaS notifications using configured triggers and actions, with little custom code. What is the most appropriate design decision?
- CDC-based ingestion service
- Azure Functions SQL input binding
- Azure Logic Apps SQL connector
- Azure Functions SQL output binding
Explanation
Correct Answer
C:Logic Apps offers managed connectors and workflow orchestration for database-triggered or database-driven business processes.
Incorrect Answers
A:A CDC consumer reads durable change captures and can reconstruct supported insert/update/delete change activity; the alternative cannot substitute for Azure Logic Apps SQL connector under the given constraints.
B:A SQL input binding can supply configured database query results to a supported Azure Function invocation; the condition in the stem is resolved by Azure Logic Apps SQL connector, not by this feature alone.
D:A SQL output binding can persist supported output values through configured SQL connectivity and binding semantics; that function is not interchangeable with Azure Logic Apps SQL connector for the requested result.
Question 153
A table schema must have a diffable, reviewable version that developers can modify through ordinary Git commits. Which option satisfies the desired behavior most directly?
- Immutable release artifacts
- Environment-specific publish profile
- Git tags for releases
- Source object definitions
Explanation
Correct Answer
D:Declarative .sql object files in a SQL project provide a maintainable source of truth for modeled database objects.
Incorrect Answers
A:Artifact promotion preserves the reviewed DACPAC across environments, avoiding accidental rebuilds from later commits; it addresses a different step; the question calls for Source object definitions.
B:Environment configuration and publish profiles keep target-specific settings separate from the source schema definition; this differs from the Source object definitions requirement described in the scenario.
C:Tags can mark repository states for releases, supporting traceability between commits, built artifacts, and deployments; the required capability here is Source object definitions, which this operation does not supply.
Question 154
A sensitive column should avoid revealing equal plaintext values through identical ciphertext, and server-side equality queries are not required. Which mechanism addresses the requirement?
- TDE key hierarchy
- Randomized Always Encrypted
- Allowlisted MCP tools
- Deterministic Always Encrypted
- Always Encrypted with secure enclaves
Explanation
Correct Answer
B:Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support.
Incorrect Answers
A:TDE protects the database encryption key through configured key hierarchy, so key backup and availability matter for restored data; the missing behavior is Randomized Always Encrypted; this mechanism does something else.
C:An allowlisted set of restricted MCP tools reduces agent capabilities; prompt instructions cannot substitute for backend enforcement; for the stated conditions, the deciding feature is Randomized Always Encrypted rather than this behavior.
D:Deterministic column encryption yields the same ciphertext for equal plaintext with the same key and supports restricted equality comparisons; it addresses a different step; the question calls for Randomized Always Encrypted.
E:Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites; this differs from the Randomized Always Encrypted requirement described in the scenario.
Question 155
A financial allocation transaction must prevent phantom inserts into a protected key range until its operation completes. Which choice best meets these constraints?
- SNAPSHOT isolation
- READ COMMITTED
- SERIALIZABLE isolation
- READ UNCOMMITTED
Explanation
Correct Answer
C:SERIALIZABLE is the strongest standard isolation level and can protect qualifying key ranges from concurrent phantom changes, increasing contention.
Incorrect Answers
A:SNAPSHOT uses transaction-level row versions and checks write conflicts when updating rows another transaction has changed after the snapshot began, so the caller may need to retry; the required capability here is SERIALIZABLE isolation, which this operation does not supply.
B:READ COMMITTED prevents dirty reads, with blocking or row-version behavior depending on the configured implementation; the alternative cannot substitute for SERIALIZABLE isolation under the given constraints.
D:READ UNCOMMITTED allows access to uncommitted changes and can produce inconsistent values, so it is unsuitable for authoritative reports; the missing behavior is SERIALIZABLE isolation; this mechanism does something else.
Question 156
Five relevant documents have been retrieved and the app must package their approved excerpts with the user's question for the generator. Which approach should the team select?
- Context assembly
- Vector retrieval
- Question embedding
- Answer generation
Explanation
Correct Answer
A:Context assembly selects and formats trusted retrieved passages within the token budget before model invocation.
Incorrect Answers
B:Vector-neighbor search ranks stored embeddings by proximity to the query representation and returns source identifiers; it addresses a different step; the question calls for Context assembly.
C:Embedding the user query with a compatible model creates the numeric representation needed for vector similarity search; this differs from the Context assembly requirement described in the scenario.
D:An LLM can generate a response conditioned on retrieved evidence and the user's question, with grounding controls applied; the required capability here is Context assembly, which this operation does not supply.
Question 157
A support engineer must correlate selected diagnostic logs from multiple Azure SQL databases in a shared workspace. Which option satisfies the desired behavior most directly?
- Log Analytics diagnostic queries
- Azure SQL Query Performance Insight
- Query Store runtime report
- Azure Monitor metrics
Explanation
Correct Answer
A:A Log Analytics workspace stores forwarded log records and supports cross-resource KQL analysis where diagnostics are configured.
Incorrect Answers
B:Query Performance Insight provides a focused view of captured SQL query performance metrics and historical trends; the missing behavior is Log Analytics diagnostic queries; this mechanism does something else.
C:Query Store retains statement-level plan and runtime aggregates suitable for comparing the same query across intervals; that function is not interchangeable with Log Analytics diagnostic queries for the requested result.
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; for the stated conditions, the deciding feature is Log Analytics diagnostic queries rather than this behavior.
Question 158
A deployment tool needs a sensitive external service password retrieved under controlled policy at execution time. What should be configured to meet this objective?
- Key Vault secret reference
- Least-privilege deployment principal
- OpenID Connect federation
- Managed identity in hosted runner
Explanation
Correct Answer
A:Fetching secrets from Key Vault centralizes storage and access control, while the pipeline should avoid logging returned values.
Incorrect Answers
B:A narrowly scoped deployment identity limits damage and requires only appropriate SQL and Azure resource permissions for the target workflow; the required capability here is Key Vault secret reference, which this operation does not supply.
C:OIDC workload identity federation lets a trusted workflow exchange a short-lived identity token for authorized Azure credentials; the alternative cannot substitute for Key Vault secret reference under the given constraints.
D:A managed identity provides an Azure-hosted workload with token-based access instead of a persisted username and password; the condition in the stem is resolved by Key Vault secret reference, not by this feature alone.
Question 159
A search service has millions of vectors and prioritizes interactive latency, accepting a measured small loss of recall. What is the most appropriate design decision?
- Metadata predicate pushdown
- Keyword inverted index
- Approximate nearest-neighbor index
- Exact nearest-neighbor scan
Explanation
Correct Answer
C:Approximate-neighbor indexes reduce the amount of distance work by searching a structured vector index, trading some recall for speed.
Incorrect Answers
A:Applying supported metadata predicates early reduces candidate sets and enforces eligibility independently of semantic ranking; that function is not interchangeable with Approximate nearest-neighbor index for the requested result.
B:An inverted full-text index maps terms to matching documents for lexical search over tokenized content; for the stated conditions, the deciding feature is Approximate nearest-neighbor index rather than this behavior.
D:Exact nearest-neighbor search computes distances across all eligible candidates and returns the true nearest results for the chosen metric; this differs from the Approximate nearest-neighbor index requirement described in the scenario.
Question 160
A booking allocator checks for an unclaimed indexed key inside a transaction and then inserts it. It must lock the probed key range and avoid a shared-to-exclusive lock-conversion race without raising the isolation level for every table access. Which table hint combination fits the probe?
- READ_COMMITTED_SNAPSHOT
- READ COMMITTED locking
- SET TRANSACTION ISOLATION LEVEL SNAPSHOT
- WITH (UPDLOCK, HOLDLOCK)
Explanation
Correct Answer
D:HOLDLOCK supplies serializable-style key-range protection for the probed table, while UPDLOCK takes update locks during the read to help avoid conversion deadlocks before the insert. The check and insert must remain in the same transaction with suitable indexing.
Incorrect Answers
A:READ_COMMITTED_SNAPSHOT changes default READ COMMITTED read behavior to statement snapshots; it does not take the update/key-range locks on the booking probe required before insert.
B:Ordinary READ COMMITTED locking protects rows while read locks are held, but does not hold a serializable key-range lock for the full check-and-insert transaction.
C:SNAPSHOT uses row versions for consistent reads and detects update conflicts; it does not reserve an empty insertion range against another concurrent allocator.