Question 210
A supported solution needs permitted comparisons or transformations that ordinary client-side encrypted columns cannot handle efficiently. What is the best primary mechanism to use?
- Always Encrypted with secure enclaves
- Randomized Always Encrypted
- TDE key hierarchy
- Deterministic Always Encrypted
Explanation
Correct Answer
A:Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites.
Incorrect Answers
B:Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support; that function is not interchangeable with Always Encrypted with secure enclaves for the requested result.
C:TDE protects the database encryption key through configured key hierarchy, so key backup and availability matter for restored data; for the stated conditions, the deciding feature is Always Encrypted with secure enclaves 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 Always Encrypted with secure enclaves.
Question 211
A developer is redesigning a stored procedure and should isolate unreviewed schema changes from the protected main branch. Which mechanism addresses the requirement?
- Merge conflict resolution
- Pull request
- Feature branch
- Protected main branch
Explanation
Correct Answer
C:A feature branch provides a separate line of changes for review and integration without directly changing the release branch.
Incorrect Answers
A:Resolving the conflicting source edits produces one coherent schema definition before a merged database project can be compiled; the required capability here is Feature branch, which this operation does not supply.
B:A pull request provides a controlled review and merge workflow for database-source changes; the alternative cannot substitute for Feature branch under the given constraints.
D:Branch protection or rulesets impose merge policies, required checks, and review conditions in the repository; the missing behavior is Feature branch; this mechanism does something else.
Question 212
For one SQL query whose latency increased, a DBA needs historical categories of waits attributed to that query, not an audit record of who signed in. Which capability exposes the needed wait breakdown?
- SQL auditing
- Log Analytics workspace
- Azure Monitor alerts
- Query Store wait statistics
Explanation
Correct Answer
D:Query Store can retain per-query wait statistics, helping distinguish locking, I/O, and other wait categories for the affected query in supported configurations.
Incorrect Answers
A:SQL auditing records configured security and data-access events; it does not organize historical wait categories per Query Store statement.
B:A Log Analytics workspace receives selected logs and telemetry; this does not by itself substitute for SQL Query Store wait attribution at the query level.
C:Azure Monitor alert rules detect thresholds or signals but do not provide the stored Query Store per-statement wait breakdown requested here.
Question 213
A pull request changes an indexed view; it must not merge unless SQL project compilation and tests pass. What should be configured to meet this objective?
- Required status checks
- Pull-request approval rule
- Restricted workflow permissions
- CODEOWNERS review
Explanation
Correct Answer
A:Required status checks make successful automated validation a condition for merging into a protected branch.
Incorrect Answers
B:Review requirements enforce separation of duties and recorded authorization before merge; the missing behavior is Required status checks; this mechanism does something else.
C:Limiting workflow token permissions reduces the impact of compromised repository content and avoids unnecessary deployment authority; that function is not interchangeable with Required status checks for the requested result.
D:CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules; for the stated conditions, the deciding feature is Required status checks rather than this behavior.
Question 214
A legacy deployment integration still requires a credential, but only approved production jobs should be able to read it. What should the developer implement?
- Credential rotation
- Database contained user grant
- Protected environment secret
- Federated GitHub identity
Explanation
Correct Answer
C:Environment-scoped secret controls restrict availability to authorized workflows, although long-lived passwords should be minimized.
Incorrect Answers
A:Revoking and rotating compromised credentials eliminates continued use of exposed secrets, unlike merely deleting a log message; this differs from the Protected environment secret requirement described in the scenario.
B:Authentication alone does not grant SQL object privileges, so a contained user or equivalent principal must receive deployment rights; the required capability here is Protected environment secret, which this operation does not supply.
D:Federated workload credentials exchange trusted action identity assertions for Azure access, requiring explicit trust configuration; the condition in the stem is resolved by Protected environment secret, not by this feature alone.
Question 215
Read-heavy reports are slow and storage latency counters rise, suggesting data access waits rather than merely bad SQL syntax. What is the most appropriate design decision?
- Wait-type analysis
- CPU-bound query analysis
- Memory grant spill review
- I/O latency monitoring
Explanation
Correct Answer
D:I/O metrics and file latency telemetry distinguish storage pressure and physical read cost from other query delays.
Incorrect Answers
A:Categorizing waits helps identify the contended resource, but wait evidence should be correlated with relevant sessions and plans; that function is not interchangeable with I/O latency monitoring for the requested result.
B:CPU usage and operator analysis reveal expensive computations or inefficient row processing independently of storage wait times; for the stated conditions, the deciding feature is I/O latency monitoring rather than this behavior.
C:Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb; it addresses a different step; the question calls for I/O latency monitoring.
Question 216
A vector result must link back to its authorized source row and citation metadata without relying on reconstructing identity from embedding numbers. Which choice best meets these constraints?
- Title plus body
- Normalize noisy boilerplate
- Exclude secret columns
- Preserve source identifiers separately
Explanation
Correct Answer
D:Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval.
Incorrect Answers
A:Including the title and relevant body in a controlled embedding input gives the representation both subject identity and contextual detail; the alternative cannot substitute for Preserve source identifiers separately under the given constraints.
B:Removing repeated boilerplate from embedding inputs can increase the signal from content that differentiates documents; the condition in the stem is resolved by Preserve source identifiers separately, not by this feature alone.
C:Excluding secret or irrelevant columns reduces data exposure and prevents confidential values from affecting retrieval features; the missing behavior is Preserve source identifiers separately; this mechanism does something else.
Question 217
An integration service wants a newly generated uniqueidentifier value to use as a correlation identifier across services. Which action is justified by these conditions?
- SEQUENCE object
- ROWVERSION column
- IDENTITY property
- NEWID()
Explanation
Correct Answer
D:NEWID produces a GUID-shaped uniqueidentifier; it is not a monotonically increasing table identity.
Incorrect Answers
A:A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR; it addresses a different step; the question calls for NEWID().
B:ROWVERSION is an automatically changing binary token for row modifications, useful in equality comparisons for optimistic concurrency and not a clock timestamp; this differs from the NEWID() requirement described in the scenario.
C:IDENTITY automatically produces values for an inserted column, although applications must not assume gap-free numbering or transactional sequences; the required capability here is NEWID(), which this operation does not supply.
Question 218
A service must set the JSON property $.shipping.delivered to true inside a stored JSON value while retaining other properties. What is the most suitable implementation?
- OPENJSON WITH
- JSON_MODIFY
- JSON_PATH_EXISTS
- JSON_CONTAINS
Explanation
Correct Answer
B:JSON_MODIFY updates content at a specified JSON path and returns the modified document expression.
Incorrect Answers
A:An OPENJSON WITH clause maps JSON paths to named typed relational columns during rowset extraction; the missing behavior is JSON_MODIFY; this mechanism does something else.
C:JSON_PATH_EXISTS checks whether an SQL/JSON path exists, avoiding the need to return the path's value; for the stated conditions, the deciding feature is JSON_MODIFY rather than this behavior.
D:JSON_CONTAINS evaluates containment semantics for JSON values rather than merely extracting a scalar for presentation; it addresses a different step; the question calls for JSON_MODIFY.
Question 219
A chatbot frequently retrieves loosely related passages; the team needs the percentage of its first five hits that are actually relevant. What is the most appropriate design decision?
- Embedding drift check
- Precision at k
- Recall at k
- Latency percentile
Explanation
Correct Answer
B:Precision@k measures relevance within the top-k returned results rather than the total pool's coverage.
Incorrect Answers
A:Periodic evaluation on recent representative queries can detect semantic quality drift and trigger reindexing or model comparison; the required capability here is Precision at k, which this operation does not supply.
C:Recall@k measures the fraction of relevant items retrieved in a top-k result and reveals missed evidence; the condition in the stem is resolved by Precision at k, not by this feature alone.
D:Latency percentiles expose tail-response behavior that mean timings can conceal in production; the missing behavior is Precision at k; this mechanism does something else.
Question 220
A module must perform controlled work using a designated execution principal instead of the caller's full original context. What is the most appropriate design decision?
- Ownership chaining
- EXECUTE AS execution context
- Module signing
- EXECUTE permission
- INSTEAD OF DELETE trigger
Explanation
Correct Answer
B:EXECUTE AS switches a supported module's permission-checking context, and should be used with explicit least-privilege considerations.
Incorrect Answers
A:Ownership chaining can allow access through a module when ownership and permission conditions are satisfied; cross-owner paths are different; for the stated conditions, the deciding feature is EXECUTE AS execution context rather than this behavior.
C:Module signing can associate certificate-based permissions with module execution, avoiding blanket grants to all application principals; this differs from the EXECUTE AS execution context requirement described in the scenario.
D:Granting EXECUTE on the procedure permits calling that module without granting unrestricted base-table DML, subject to ownership and execution context; the required capability here is EXECUTE AS execution context, which this operation does not supply.
E:An INSTEAD OF DELETE trigger can replace a requested deletion with custom SQL logic, including soft-delete behavior; the alternative cannot substitute for EXECUTE AS execution context under the given constraints.