Question 380
A release needs to insert or update a few stable ISO country-code rows after schema changes without treating business transactions as source code. Which action is justified by these conditions?
- Version-controlled reference CSV
- Integration test for referential values
- Post-deployment seed script
- Idempotent MERGE or upsert logic
Explanation
Correct Answer
C:Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow.
Incorrect Answers
A:Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; for the stated conditions, the deciding feature is Post-deployment seed script rather than this behavior.
B:A targeted integration or smoke test validates that required reference records are present and usable by related constraints; it addresses a different step; the question calls for Post-deployment seed script.
D:An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases; the required capability here is Post-deployment seed script, which this operation does not supply.
Question 381
Each customer requires the top two recent orders produced by a correlated table expression, omitting customers without any such orders. Which technique applies to this scenario?
- CROSS APPLY
- Scalar correlated subquery
- NOT EXISTS correlated subquery
- EXISTS correlated subquery
Explanation
Correct Answer
A:CROSS APPLY evaluates a table expression per outer row and includes only outer rows whose applied expression yields results.
Incorrect Answers
B:A scalar correlated subquery can compute one related value per outer row, provided the expression returns at most one value; the missing behavior is CROSS APPLY; this mechanism does something else.
C:NOT EXISTS uses an anti-semi-join existence test and avoids the problematic NULL behavior of some NOT IN subqueries; that function is not interchangeable with CROSS APPLY for the requested result.
D:A correlated EXISTS predicate evaluates whether a related row exists for each outer record without materializing duplicate outer records; for the stated conditions, the deciding feature is CROSS APPLY rather than this behavior.
Question 382
A cleanup routine must replace all occurrences matching a pattern, such as runs of whitespace, in one source string. Which feature would solve the stated problem?
- REGEXP_LIKE
- REGEXP_INSTR
- REGEXP_COUNT
- REGEXP_REPLACE
Explanation
Correct Answer
D:REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms.
Incorrect Answers
A:REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; this differs from the REGEXP_REPLACE requirement described in the scenario.
B:REGEXP_INSTR returns a match position for a supplied regular-expression pattern; the required capability here is REGEXP_REPLACE, which this operation does not supply.
C:REGEXP_COUNT counts matches of a regular expression in a source string; the alternative cannot substitute for REGEXP_REPLACE under the given constraints.
Question 383
An ingestion process receives an array of line items as JSON and must produce typed relational rows for subsequent joins. What is the right solution for this specific requirement?
- OPENJSON with a WITH clause
- JSON index
- JSON_VALUE expression
- Native JSON data type
- Aligned partitioned index
Explanation
Correct Answer
A:OPENJSON shreds JSON arrays or objects into rows, while WITH can define the output columns and their SQL data types.
Incorrect Answers
B:A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; for the stated conditions, the deciding feature is OPENJSON with a WITH clause rather than this behavior.
C:JSON_VALUE extracts a scalar at the requested JSON path, rather than returning an object or a table of properties; it addresses a different step; the question calls for OPENJSON with a WITH clause.
D:The native json type is intended for JSON documents and supports JSON-oriented operations on platforms where it is available; support must be checked per engine; this differs from the OPENJSON with a WITH clause requirement described in the scenario.
E:Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries; the required capability here is OPENJSON with a WITH clause, which this operation does not supply.
Question 384
A small low-volume table needs a tightly controlled reaction to a write and the team accepts the trigger's transactional overhead. Which database mechanism should be chosen?
- AI_GENERATE_EMBEDDINGS
- Database trigger with guarded work
- Azure Function on change event
- External batching worker
Explanation
Correct Answer
B:A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously.
Incorrect Answers
A:AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; the alternative cannot substitute for Database trigger with guarded work under the given constraints.
C:A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes; the missing behavior is Database trigger with guarded work; this mechanism does something else.
D:An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions; that function is not interchangeable with Database trigger with guarded work for the requested result.
Question 385
A labeled search test knows 20 relevant documents and needs the proportion appearing in the first ten retrieved candidates. Which database mechanism should be chosen?
- Recall@10 metric
- Precision@5 metric
- P95 query latency
- MRR metric
Explanation
Correct Answer
A:Recall@10 measures how many relevant targets are covered by the top ten results compared with the known relevant set.
Incorrect Answers
B:Precision@5 quantifies relevance density among the top five, helping assess noisy retrieval; this differs from the Recall@10 metric requirement described in the scenario.
C:The 95th-percentile latency captures slow-tail requests and exposes variability that mean response time conceals; the required capability here is Recall@10 metric, which this operation does not supply.
D:Mean reciprocal rank averages the inverse position of the first relevant hit across test queries; the alternative cannot substitute for Recall@10 metric under the given constraints.
Question 386
An existing approved model object must be updated to use a new compatible endpoint configuration without renaming the callers' model identifier. Which choice best meets these constraints?
- ALTER EXTERNAL MODEL
- GRANT EXECUTE ON EXTERNAL MODEL
- CREATE EXTERNAL MODEL
- DROP EXTERNAL MODEL
Explanation
Correct Answer
A:ALTER EXTERNAL MODEL changes the stored external model definition, subject to required permissions and supported options.
Incorrect Answers
B:Executing an external model requires the appropriate model authorization, separable from general table SELECT permissions; that function is not interchangeable with ALTER EXTERNAL MODEL for the requested result.
C:CREATE EXTERNAL MODEL registers an external inference model definition for supported SQL AI functions rather than storing the model weights as table rows; for the stated conditions, the deciding feature is ALTER EXTERNAL MODEL rather than this behavior.
D:DROP EXTERNAL MODEL removes the model metadata object once relevant dependencies and permissions allow it; it addresses a different step; the question calls for ALTER EXTERNAL MODEL.
Question 387
A fleet of app versions runs during rollout, so the schema should introduce a nullable field before old clients are retired. Which technique applies to this scenario?
- Pre-deployment backup
- Backward-compatible additive change
- Expand-and-contract migration
- Explicit destructive-change review
Explanation
Correct Answer
B:An additive compatible migration can allow old and new application versions to coexist during staged cutover.
Incorrect Answers
A:An appropriate backup or recovery strategy protects recoverability when deployment scripts cannot simply reverse data loss; the required capability here is Backward-compatible additive change, which this operation does not supply.
C:Expand-and-contract introduces new schema, migrates users and data, then removes old objects only after dependents move; the condition in the stem is resolved by Backward-compatible additive change, not by this feature alone.
D:A destructive-change gate reviews planned drops and data transformations rather than assuming model synchronization is risk-free; the missing behavior is Backward-compatible additive change; this mechanism does something else.
Question 388
Only three percent of tickets have Status = 'Open'; the application's critical query looks exclusively at open tickets and the index must stay small. Which mechanism addresses the requirement?
- Nonclustered covering index
- Clustered rowstore index
- Clustered columnstore index
- Filtered nonclustered index
Explanation
Correct Answer
D:A filtered index covers only rows that satisfy its WHERE predicate, reducing maintenance and size while accelerating appropriately matched selective queries.
Incorrect Answers
A:A nonclustered index can use CustomerId as its key and INCLUDE the projected fields, satisfying the query from the secondary index rather than fetching every base row; for the stated conditions, the deciding feature is Filtered nonclustered index rather than this behavior.
B:A clustered B-tree determines the rowstore table's data organization and supports efficient key-based access, but it is not a compressed analytics-oriented layout; it addresses a different step; the question calls for Filtered nonclustered index.
C:Clustered columnstore organizes and compresses values by column and uses batch-oriented processing for scanning and aggregating large analytical data sets; this differs from the Filtered nonclustered index requirement described in the scenario.
Question 389
A compliance requirement covers INSERT and DELETE activity against a narrow set of financial objects; the DBA must configure which actions to record. What is the most suitable implementation?
- Alert on suspicious pattern
- Audit specification
- Diagnostic setting export
- Review stored audit events
Explanation
Correct Answer
B:SQL audit specifications define audited action groups or object actions to be captured by the audit mechanism.
Incorrect Answers
A:A metric or log alert rule evaluates collected telemetry and can initiate actions when a defined condition is met; the condition in the stem is resolved by Audit specification, not by this feature alone.
C:Azure resource diagnostic settings route supported log and metric categories to configured destinations and require appropriate permissions; that function is not interchangeable with Audit specification for the requested result.
D:Examining retained audit records supports forensic reconstruction; it does not itself prevent future unauthorized requests; for the stated conditions, the deciding feature is Audit specification rather than this behavior.
Question 390
A shared Orders table must automatically show each tenant only its own rows even when applications submit broad SELECT statements. Which SQL capability is the most direct fit?
- Always Encrypted
- Dynamic Data Masking
- Transparent Data Encryption
- Row-Level Security
Explanation
Correct Answer
D:Row-Level Security applies predicate logic inside the database to filter or block access to rows according to security context.
Incorrect Answers
A:Always Encrypted protects selected column values with client-side cryptography and keys outside ordinary server access, subject to query-operation limitations; this differs from the Row-Level Security requirement described in the scenario.
B:Dynamic Data Masking alters query-result presentation for non-exempt users; it is not encryption or a strong isolation boundary for untrusted querying; the required capability here is Row-Level Security, which this operation does not supply.
C:Transparent Data Encryption encrypts database storage at rest and decrypts pages for authorized engine operations; it does not hide plaintext from the engine; the alternative cannot substitute for Row-Level Security under the given constraints.