Question 420
A simple read/write API needs an Orders resource mapped directly onto a supported SQL database table. Which option satisfies the desired behavior most directly?
- Table-backed entity
- Stored-procedure entity
- View-backed entity
- Explicit relationship configuration
Explanation
Correct Answer
A:A DAB entity can map to a table source with configured permissions for REST or GraphQL operations.
Incorrect Answers
B:DAB can expose supported stored procedures as API entities with operation-specific configuration and permissions; it addresses a different step; the question calls for Table-backed entity.
C:An entity can map to a supported SQL view, with write capabilities subject to view and DAB limitations; this differs from the Table-backed entity requirement described in the scenario.
D:Configured relationships describe how exposed entities connect and control relational navigation in generated GraphQL schemas; the required capability here is Table-backed entity, which this operation does not supply.
Question 421
A supported Azure SQL database must retrieve nearest vectors through a vector-search table expression that can use an approximate index. Which database mechanism should be chosen?
- VECTOR_DISTANCE
- VECTOR_NORMALIZE
- VECTORPROPERTY
- VECTOR_SEARCH
Explanation
Correct Answer
D:VECTOR_SEARCH provides vector-neighbor retrieval and can use supported ANN indexing under the appropriate query form.
Incorrect Answers
A:VECTOR_DISTANCE calculates an exact metric-specific distance and does not automatically use an approximate vector index; the condition in the stem is resolved by VECTOR_SEARCH, not by this feature alone.
B:VECTOR_NORMALIZE produces a norm-adjusted vector for supported vector types and normalization modes; the missing behavior is VECTOR_SEARCH; this mechanism does something else.
C:VECTORPROPERTY reads documented properties of a VECTOR value and is not itself a similarity search function; that function is not interchangeable with VECTOR_SEARCH for the requested result.
Question 422
A customer segmentation query must distribute ordered spending values into approximately four equally sized row groups. Which database mechanism should be chosen?
- RANK()
- NTILE(4)
- PERCENT_RANK()
- ROW_NUMBER()
Explanation
Correct Answer
B:NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row.
Incorrect Answers
A:RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows; this differs from the NTILE(4) requirement described in the scenario.
C:PERCENT_RANK produces a relative rank position rather than the discrete four approximately equal-sized buckets requested by the query.
D:ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules; the condition in the stem is resolved by NTILE(4), not by this feature alone.
Question 423
A team wants to compare two models and must determine which actually retrieves relevant documents for its own customer-support questions. Which SQL capability is the most direct fit?
- Latency and throughput load test
- Evaluation on representative queries
- Smaller embedding model
- Larger embedding model
Explanation
Correct Answer
B:A labeled evaluation set measures real retrieval quality, preventing selection based solely on generic benchmark or model name.
Incorrect Answers
A:Load testing reveals throughput, rate-limit, and response-time behavior under the deployment's expected concurrency; that function is not interchangeable with Evaluation on representative queries for the requested result.
C:A smaller embedding model can reduce inference cost and latency, provided measured retrieval quality remains acceptable; it addresses a different step; the question calls for Evaluation on representative queries.
D:A larger-capacity model may improve representation on difficult content, but benefits must be verified against the target dataset; this differs from the Evaluation on representative queries requirement described in the scenario.
Question 424
A deduplication query must choose one latest record per CustomerId and assign a different ordinal to every tied row. What is the most appropriate design decision?
- RANK()
- ROW_NUMBER()
- NTILE(4)
- DENSE_RANK()
Explanation
Correct Answer
B:ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules.
Incorrect Answers
A:RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows; the alternative cannot substitute for ROW_NUMBER() under the given constraints.
C:NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row; the missing behavior is ROW_NUMBER(); this mechanism does something else.
D:DENSE_RANK numbers distinct ordered values without gaps after ties within the partition; that function is not interchangeable with ROW_NUMBER() for the requested result.
Question 425
Change reviewers need human-readable T-SQL for the proposed deployment without performing any ALTER statements yet. What is the most suitable implementation?
- Script action
- Publish action
- DeployReport action
- Extract action
Explanation
Correct Answer
A:Script produces the deployment operations as SQL text for inspection before a separately authorized execution.
Incorrect Answers
B:Publish compares the DACPAC model with the target and performs required schema operations, with configurable deployment safeguards; this differs from the Script action requirement described in the scenario.
C:DeployReport emits an XML description of deployment changes rather than applying them directly; the required capability here is Script action, which this operation does not supply.
D:Extract captures a database schema into a package suitable for inspection and project initialization, without being a full table-data export; the alternative cannot substitute for Script action under the given constraints.
Question 426
A generated query targets a column removed during yesterday's deployment; the assistant's cached or supplied schema metadata is outdated. Which choice best meets these constraints?
- Unsafe prompt injection in data
- Authentication failure from tool server
- Stale schema context
- Unavailable MCP tool
Explanation
Correct Answer
C:Refreshing schema metadata before generation or execution avoids relying on obsolete object definitions.
Incorrect Answers
A:Data returned from a tool is lower-trust context, and its instructions must not override tool policies or reveal confidential secrets; the missing behavior is Stale schema context; this mechanism does something else.
B:The server's database identity needs valid authentication and the necessary SQL permissions; chat access alone does not grant data access; that function is not interchangeable with Stale schema context for the requested result.
D:Tool discovery depends on the capabilities exposed by the server; adding a prompt instruction cannot create a missing backend tool; it addresses a different step; the question calls for Stale schema context.
Question 427
Before executing a potentially costly query, an engineer wants to inspect the optimizer's proposed operators and cost estimates. What is the most appropriate design decision?
- Actual execution plan
- Estimated execution plan
- Query Store plan history
- Live query statistics
Explanation
Correct Answer
B:An estimated plan shows the optimizer's anticipated strategy without running the full statement and therefore lacks actual runtime counts.
Incorrect Answers
A:An actual plan records the executed query operators and runtime row counts, enabling comparison against estimates; the required capability here is Estimated execution plan, which this operation does not supply.
C:Query Store preserves eligible query texts, plans, and aggregated runtime statistics so plan changes can be correlated with regressions; the condition in the stem is resolved by Estimated execution plan, not by this feature alone.
D:Live Query Statistics can expose progress and operator activity for running supported queries, assisting diagnosis of ongoing execution; the missing behavior is Estimated execution plan; this mechanism does something else.
Question 428
A busy database wants READ COMMITTED statements to use row versions for nonblocking committed reads without rewriting every client transaction to SNAPSHOT. What should be configured to meet this objective?
- READ COMMITTED locking
- SERIALIZABLE
- READ_COMMITTED_SNAPSHOT
- SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Explanation
Correct Answer
C:Enabling READ_COMMITTED_SNAPSHOT changes the database's READ COMMITTED read behavior to statement-level row-version snapshots.
Incorrect Answers
A:Locking READ COMMITTED prevents dirty reads but readers can block behind writers according to lock acquisition; for the stated conditions, the deciding feature is READ_COMMITTED_SNAPSHOT rather than this behavior.
B:SERIALIZABLE applies stronger range-protection behavior at the cost of concurrency, especially for indexed predicates; it addresses a different step; the question calls for READ_COMMITTED_SNAPSHOT.
D:SNAPSHOT isolation gives a version-consistent transaction-level view, unlike statement-level read-committed snapshots; the required capability here is READ_COMMITTED_SNAPSHOT, which this operation does not supply.
Question 429
An untrusted pull-request workflow should not automatically receive write permissions to secrets or deployment environments. Which technique applies to this scenario?
- Required status checks
- Pull-request template checklist
- Restricted workflow permissions
- CODEOWNERS review
Explanation
Correct Answer
C:Limiting workflow token permissions reduces the impact of compromised repository content and avoids unnecessary deployment authority.
Incorrect Answers
A:Required status checks make successful automated validation a condition for merging into a protected branch; the condition in the stem is resolved by Restricted workflow permissions, not by this feature alone.
B:A checklist can prompt reviewers but does not change the write permissions or secret access available to an untrusted workflow run.
D:CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules; for the stated conditions, the deciding feature is Restricted workflow permissions rather than this behavior.
Question 430
Each order stores a JSON items array and the query must join each extracted item back to its source OrderId. Which database mechanism should be chosen?
- CROSS APPLY OPENJSON
- FOR JSON PATH
- JSON_ARRAYAGG
- JSON_OBJECT
Explanation
Correct Answer
A:CROSS APPLY with OPENJSON produces relational item rows correlated to their source order records.
Incorrect Answers
B:FOR JSON PATH converts SELECT output into a path-controlled JSON representation, often suitable for API responses; the required capability here is CROSS APPLY OPENJSON, which this operation does not supply.
C:JSON_ARRAYAGG constructs an aggregate JSON array from group members rather than returning multiple relational rows; the alternative cannot substitute for CROSS APPLY OPENJSON under the given constraints.
D:JSON_OBJECT creates a named-property JSON structure from scalar values and other JSON expressions; the condition in the stem is resolved by CROSS APPLY OPENJSON, not by this feature alone.