Question 540
A filter needs customers with any matching chargeback while avoiding duplicated customers caused by a join to multiple chargebacks. Which feature would solve the stated problem?
- EXISTS semi-join
- Parameterized query
- TOP with ORDER BY
- SARGable date range
Explanation
Correct Answer
A:EXISTS expresses a presence test and does not expand the outer result into one row per matching child.
Incorrect Answers
B:Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases; it addresses a different step; the question calls for EXISTS semi-join.
C:TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match; this differs from the EXISTS semi-join requirement described in the scenario.
D:A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column; the required capability here is EXISTS semi-join, which this operation does not supply.
Question 541
A report should use a preferred contact phone number and fall back to an alternate number when the preferred value is NULL. What is the best primary mechanism to use?
- COALESCE
- NULLIF
- CASE expression
- TRY_CONVERT
Explanation
Correct Answer
A:COALESCE returns the first non-NULL expression from its ordered arguments.
Incorrect Answers
B:NULLIF returns NULL when its two expressions are equal, often used with division to handle zero denominators; the missing behavior is COALESCE; this mechanism does something else.
C:CASE evaluates conditional branches and yields a scalar expression selected from matching WHEN conditions; that function is not interchangeable with COALESCE for the requested result.
D:TRY_CONVERT attempts type conversion and returns NULL for many invalid values where a regular CONVERT would fail; for the stated conditions, the deciding feature is COALESCE rather than this behavior.
Question 542
A query principal can run SQL but receives permission errors when invoking one registered embedding model; administrators want model-specific access. Which choice best meets these constraints?
- ALTER EXTERNAL MODEL
- GRANT EXECUTE ON EXTERNAL MODEL
- DROP EXTERNAL MODEL
- CREATE EXTERNAL MODEL
Explanation
Correct Answer
B:Executing an external model requires the appropriate model authorization, separable from general table SELECT permissions.
Incorrect Answers
A:ALTER EXTERNAL MODEL changes the stored external model definition, subject to required permissions and supported options; this differs from the GRANT EXECUTE ON EXTERNAL MODEL requirement described in the scenario.
C:DROP EXTERNAL MODEL removes the model metadata object once relevant dependencies and permissions allow it; the alternative cannot substitute for GRANT EXECUTE ON EXTERNAL MODEL under the given constraints.
D:CREATE EXTERNAL MODEL registers an external inference model definition for supported SQL AI functions rather than storing the model weights as table rows; the condition in the stem is resolved by GRANT EXECUTE ON EXTERNAL MODEL, not by this feature alone.
Question 543
A new audit row should receive its insertion timestamp from SYSUTCDATETIME() when the InsertedAt column is omitted, yet explicitly supplied valid timestamps must remain possible. What is the best primary mechanism to use?
- FOREIGN KEY constraint
- DEFAULT constraint
- CHECK constraint
- PRIMARY KEY constraint
Explanation
Correct Answer
B:A DEFAULT expression supplies a value for a column when INSERT omits it or uses DEFAULT; it does not automatically override explicit NULL or an explicitly supplied value.
Incorrect Answers
A:A foreign key checks referential integrity between a referencing column and a candidate key in another or the same table; that function is not interchangeable with DEFAULT constraint for the requested result.
C:A CHECK constraint can compare columns from a row in one Boolean condition and rejects non-NULL expressions evaluated as FALSE, regardless of the inserting application; it addresses a different step; the question calls for DEFAULT constraint.
D:A primary key declares the table's principal identifier and enforces uniqueness plus nonnullability through an associated index; this differs from the DEFAULT constraint requirement described in the scenario.
Question 544
Analysts want the fraction of the first five search hits judged relevant by subject experts. Which technique applies to this scenario?
- Recall@10 metric
- MRR metric
- P95 query latency
- Precision@5 metric
Explanation
Correct Answer
D:Precision@5 quantifies relevance density among the top five, helping assess noisy retrieval.
Incorrect Answers
A:Recall@10 measures how many relevant targets are covered by the top ten results compared with the known relevant set; the alternative cannot substitute for Precision@5 metric under the given constraints.
B:Mean reciprocal rank averages the inverse position of the first relevant hit across test queries; the condition in the stem is resolved by Precision@5 metric, not by this feature alone.
C:The 95th-percentile latency captures slow-tail requests and exposes variability that mean response time conceals; the missing behavior is Precision@5 metric; this mechanism does something else.
Question 545
A team needs a production-supported vector index and VECTOR_SEARCH implementation on current Azure SQL Database. Which option would you recommend?
- Vector candidate pipeline
- Exact SQL vector functions
- Version-specific syntax review
- SQL Server 2025 preview indexing
- Azure SQL generally available vector index
Explanation
Correct Answer
E:Vector indexes and approximate VECTOR_SEARCH are generally available in Azure SQL Database, with version-specific syntax requirements.
Incorrect Answers
A:Vector-neighbor retrieval produces semantic candidate IDs and distance-based order for the second fusion input; it addresses a different step; the question calls for Azure SQL generally available vector index.
B:VECTOR and VECTOR_DISTANCE provide exact vector operations separately from optional approximate index support; this differs from the Azure SQL generally available vector index requirement described in the scenario.
C:Current documentation favors SELECT TOP (N) WITH APPROXIMATE for modern indexes, while older TOP_N syntax remains a compatibility concern; the required capability here is Azure SQL generally available vector index, which this operation does not supply.
D:On SQL Server 2025, vector indexing and VECTOR_SEARCH have preview conditions that differ from generally available Azure SQL support; the alternative cannot substitute for Azure SQL generally available vector index under the given constraints.
Question 546
A transaction uses versioned reads but also updates a row; it must detect and handle a concurrent update committed after that transaction began instead of silently overwriting it. Which database mechanism should be chosen?
- READ COMMITTED
- SERIALIZABLE isolation
- READ COMMITTED SNAPSHOT
- SNAPSHOT isolation
Explanation
Correct Answer
D: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.
Incorrect Answers
A:READ COMMITTED prevents dirty reads, with blocking or row-version behavior depending on the configured implementation; the missing behavior is SNAPSHOT isolation; this mechanism does something else.
B:SERIALIZABLE is the strongest standard isolation level and can protect qualifying key ranges from concurrent phantom changes, increasing contention; that function is not interchangeable with SNAPSHOT isolation for the requested result.
C:READ COMMITTED SNAPSHOT provides statement-level versioned reads, not the transaction-start consistency and update-conflict behavior of SNAPSHOT isolation.
Question 547
A procedure should return one calculated integer count to a calling routine in addition to performing its updates. Which SQL capability is the most direct fit?
- Result set from SELECT
- RETURN status code
- Input parameter
- OUTPUT parameter
Explanation
Correct Answer
D:An OUTPUT parameter returns a variable value to the caller when the invocation and parameter declaration allow output.
Incorrect Answers
A:Executing SELECT within a procedure returns a result set that clients can consume, unlike an integer-only return code; the required capability here is OUTPUT parameter, which this operation does not supply.
B:A stored procedure RETURN value is an integer status mechanism, conventionally used for control or result codes; the alternative cannot substitute for OUTPUT parameter under the given constraints.
C:An input parameter supplies a value for the procedure's execution without embedding a different literal in every call; the condition in the stem is resolved by OUTPUT parameter, not by this feature alone.
Question 548
An AI assistant has proposed a high-impact procedure change. Before approval, the team needs proof of query performance, functional regression coverage, and an evaluation of whether its suggested permissions are excessive. Which THREE reviews are necessary? Choose THREE.
- Review security privileges
- Run unit and integration tests
- Review generated query plans
- Read-only database principal
- Validate against target SQL dialect
- Human approval before tool execution
Explanation
Correct Answers
A:Effective permission analysis identifies the required object-level rights rather than assuming broad database ownership is necessary; used in combination with Review generated query plans, Run unit and integration tests, it handles a separate requirement of this combined workflow.
B:Automated tests exercise database contracts and interactions, detecting regressions that a plausible generated explanation may conceal; used in combination with Review generated query plans, Review security privileges, it handles a separate requirement of this combined workflow.
C:Reviewing actual execution plans and representative workload metrics reveals access paths and cardinality issues that text generation alone cannot verify; used in combination with Review security privileges, Run unit and integration tests, it handles a separate requirement of this combined workflow.
Incorrect Answers
D:A read-only database principal with narrowly scoped SELECT access limits potential writes at the database authorization boundary; the required capability here is Review security privileges and Run unit and integration tests and Review generated query plans, which this operation does not supply.
E:Compiling and testing against the actual target platform identifies unsupported syntax or version-specific behaviors that prompt text cannot guarantee; the alternative cannot substitute for Review security privileges and Run unit and integration tests and Review generated query plans under the given constraints.
F:Requiring human confirmation for sensitive tool calls reduces the risk of autonomous changes and maintains an accountable approval point; the condition in the stem is resolved by Review security privileges and Run unit and integration tests and Review generated query plans, not by this feature alone.
Question 549
An organization cannot use an AI assistance feature until authorized administrators allow the relevant Fabric Copilot configuration. Which SQL capability is the most direct fit?
- Fabric tenant feature settings
- Execute and inspect results
- SQL endpoint authorization
- Copilot in Fabric chat
Explanation
Correct Answer
A:Tenant-level Fabric settings can govern Copilot availability, with scope and capacity prerequisites depending on the specific feature.
Incorrect Answers
B:Running the query against representative data and reviewing results validates behavior; an AI suggestion is not a substitute for execution; the missing behavior is Fabric tenant feature settings; this mechanism does something else.
C:Database authorization still applies when queries are proposed by Copilot, so appropriate grants are required separately; that function is not interchangeable with Fabric tenant feature settings for the requested result.
D:Microsoft Copilot in Fabric offers AI-assisted authoring in supported Fabric workloads and remains subject to tenant and capacity configuration; for the stated conditions, the deciding feature is Fabric tenant feature settings rather than this behavior.
Question 550
A model performs well on one request but the system must support hundreds of concurrent embeddings during nightly indexing. What should the developer implement?
- Smaller embedding model
- Evaluation on representative queries
- Larger embedding model
- Latency and throughput load test
Explanation
Correct Answer
D:Load testing reveals throughput, rate-limit, and response-time behavior under the deployment's expected concurrency.
Incorrect Answers
A:A smaller embedding model can reduce inference cost and latency, provided measured retrieval quality remains acceptable; this differs from the Latency and throughput load test requirement described in the scenario.
B:A labeled evaluation set measures real retrieval quality, preventing selection based solely on generic benchmark or model name; the required capability here is Latency and throughput load test, which this operation does not supply.
C:A larger-capacity model may improve representation on difficult content, but benefits must be verified against the target dataset; the alternative cannot substitute for Latency and throughput load test under the given constraints.