Question 570
Deployment scripts must update an existing procedure body or create it if absent without manually branching on existence. Which option satisfies the desired behavior most directly?
- ALTER FUNCTION
- CREATE OR ALTER PROCEDURE
- DROP VIEW
- sp_refreshview
Explanation
Correct Answer
B:CREATE OR ALTER for supported modules makes a definition idempotent while preserving the object's logical identity.
Incorrect Answers
A:ALTER FUNCTION changes a function's definition, subject to dependencies and function-kind restrictions; the missing behavior is CREATE OR ALTER PROCEDURE; this mechanism does something else.
C:DROP VIEW removes the view object, leaving its underlying tables intact subject to dependency constraints; for the stated conditions, the deciding feature is CREATE OR ALTER PROCEDURE rather than this behavior.
D:sp_refreshview updates metadata for an eligible non-schema-bound view after referenced schema changes, rather than altering source tables; it addresses a different step; the question calls for CREATE OR ALTER PROCEDURE.
Question 571
Every change to production database scripts requires at least one independent human reviewer not committing the change. Which approach should the team select?
- Explicit destructive-change review
- Restricted workflow permissions
- CODEOWNERS review
- Required status checks
- Pull-request approval rule
Explanation
Correct Answer
E:Review requirements enforce separation of duties and recorded authorization before merge.
Incorrect Answers
A:A destructive-change gate reviews planned drops and data transformations rather than assuming model synchronization is risk-free; the required capability here is Pull-request approval rule, which this operation does not supply.
B:Limiting workflow token permissions reduces the impact of compromised repository content and avoids unnecessary deployment authority; the alternative cannot substitute for Pull-request approval rule under the given constraints.
C:CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules; the condition in the stem is resolved by Pull-request approval rule, not by this feature alone.
D:Required status checks make successful automated validation a condition for merging into a protected branch; the missing behavior is Pull-request approval rule; this mechanism does something else.
Question 572
A column rename must occur without breaking clients that deploy at different times; the team needs an intermediate compatibility period. Which option satisfies the desired behavior most directly?
- Backward-compatible additive change
- Explicit destructive-change review
- Expand-and-contract migration
- Pre-deployment backup
Explanation
Correct Answer
C:Expand-and-contract introduces new schema, migrates users and data, then removes old objects only after dependents move.
Incorrect Answers
A:An additive compatible migration can allow old and new application versions to coexist during staged cutover; for the stated conditions, the deciding feature is Expand-and-contract migration rather than this behavior.
B:A destructive-change gate reviews planned drops and data transformations rather than assuming model synchronization is risk-free; it addresses a different step; the question calls for Expand-and-contract migration.
D:An appropriate backup or recovery strategy protects recoverability when deployment scripts cannot simply reverse data loss; the required capability here is Expand-and-contract migration, which this operation does not supply.
Question 573
A database stores strictly ASCII-compatible product codes whose lengths vary and does not need Unicode semantics for that column. Which option satisfies the desired behavior most directly?
- DECIMAL(12,2)
- CHAR(2)
- VARCHAR(80)
- DATE type
Explanation
Correct Answer
C:VARCHAR holds variable-length non-Unicode text subject to the column's collation/code page, with less storage than NVARCHAR for many such values.
Incorrect Answers
A:DECIMAL provides exact base-ten precision and a declared scale appropriate to financial arithmetic; the condition in the stem is resolved by VARCHAR(80), not by this feature alone.
B:CHAR stores fixed-length non-Unicode character data, with padding semantics for shorter supplied values; the missing behavior is VARCHAR(80); this mechanism does something else.
D:DATE represents a date without a time-of-day portion, simplifying comparisons that should ignore clock time; for the stated conditions, the deciding feature is VARCHAR(80) rather than this behavior.
Question 574
Users report intermittent slow vector searches despite reasonable average timings; tail performance must be measured. Which technique applies to this scenario?
- P95 query latency
- Precision@5 metric
- Post-retrieval reranking
- MRR metric
- Recall@10 metric
Explanation
Correct Answer
A:The 95th-percentile latency captures slow-tail requests and exposes variability that mean response time conceals.
Incorrect Answers
B:Precision@5 quantifies relevance density among the top five, helping assess noisy retrieval; the required capability here is P95 query latency, which this operation does not supply.
C:A reranking stage refines top candidates after initial retrieval, reducing cost compared with full-corpus cross-encoding; the alternative cannot substitute for P95 query latency under the given constraints.
D:Mean reciprocal rank averages the inverse position of the first relevant hit across test queries; the condition in the stem is resolved by P95 query latency, not by this feature alone.
E:Recall@10 measures how many relevant targets are covered by the top ten results compared with the known relevant set; the missing behavior is P95 query latency; this mechanism does something else.
Question 575
An Azure-hosted API needs to request a SQL access token without storing client secrets in its deployment configuration. What is the right solution for this specific requirement?
- Microsoft Entra authentication
- Managed identity
- Least-privilege database role
- Contained database user
Explanation
Correct Answer
B:A managed identity gives an eligible Azure workload an automatically managed service identity, with database access still requiring grants.
Incorrect Answers
A:Microsoft Entra authentication supports identity-provider tokens for compatible SQL connections and database principal authorization; that function is not interchangeable with Managed identity for the requested result.
C:A custom database role can aggregate narrowly defined GRANT permissions and simplify managed membership; it addresses a different step; the question calls for Managed identity.
D:A contained database user represents an identity at database scope and can receive database permissions or role membership; this differs from the Managed identity requirement described in the scenario.
Question 576
A frequently called procedure needs a less value-specific compiled estimate instead of relying on one atypical initial parameter. Which mechanism addresses the requirement?
- Query Store plan forcing
- Parameter-sensitive plan diagnosis
- OPTION (RECOMPILE)
- OPTIMIZE FOR UNKNOWN
Explanation
Correct Answer
D:OPTIMIZE FOR UNKNOWN directs estimation toward generalized statistics rather than a particular sniffed parameter value.
Incorrect Answers
A:Query Store plan forcing can stabilize a query on a captured plan, while the DBA must monitor continued validity and suitability; the alternative cannot substitute for OPTIMIZE FOR UNKNOWN under the given constraints.
B:Parameter-sensitive behavior stems from different data selectivities sharing plan assumptions; runtime and plan comparison can reveal the issue; the condition in the stem is resolved by OPTIMIZE FOR UNKNOWN, not by this feature alone.
C:RECOMPILE can produce a fresh plan for the current parameter values, trading compilation cost for specialization; the missing behavior is OPTIMIZE FOR UNKNOWN; this mechanism does something else.
Question 577
An invoice API needs SQL to suppress other tenants' rows on SELECT and reject attempts to insert records marked with another tenant's identifier. Which TWO RLS predicates are appropriate? Choose TWO.
- GRANT SELECT on view
- Column master key
- Masked column definition
- RLS block predicate
- RLS filter predicate
Explanation
Correct Answers
D:An RLS block predicate can reject changes that would violate a row-access policy during specified DML operations; used in combination with RLS filter predicate, it handles a separate requirement of this combined workflow.
E:An RLS filter predicate limits visible rows for eligible operations at the table's security-policy boundary; used in combination with RLS block predicate, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:Object-level SELECT permissions can be granted on the view while base-table privileges remain constrained, with ownership chaining considered; it addresses a different step; the question calls for RLS block predicate and RLS filter predicate.
B:A column master key describes the key-encryption root used to protect column encryption keys; the underlying key should remain outside routine database access; this differs from the RLS block predicate and RLS filter predicate requirement described in the scenario.
C:A masking function on the column changes the result shown to principals lacking UNMASK, leaving the underlying value unchanged; the required capability here is RLS block predicate and RLS filter predicate, which this operation does not supply.
Question 578
An application principal must call an approved stored procedure while direct modification of protected tables remains unavailable. Which database mechanism should be chosen?
- EXECUTE AS execution context
- EXECUTE permission
- Module signing
- Ownership chaining
Explanation
Correct Answer
B:Granting EXECUTE on the procedure permits calling that module without granting unrestricted base-table DML, subject to ownership and execution context.
Incorrect Answers
A:EXECUTE AS switches a supported module's permission-checking context, and should be used with explicit least-privilege considerations; the missing behavior is EXECUTE permission; this mechanism does something else.
C:Module signing can associate certificate-based permissions with module execution, avoiding blanket grants to all application principals; for the stated conditions, the deciding feature is EXECUTE permission rather than this behavior.
D:Ownership chaining can allow access through a module when ownership and permission conditions are satisfied; cross-owner paths are different; it addresses a different step; the question calls for EXECUTE permission.
Question 579
A hash or sort operator spills data to tempdb because the query received insufficient memory for its intermediate state. Which mechanism addresses the requirement?
- I/O latency monitoring
- Wait-type analysis
- CPU-bound query analysis
- Memory grant spill review
Explanation
Correct Answer
D:Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb.
Incorrect Answers
A:I/O metrics and file latency telemetry distinguish storage pressure and physical read cost from other query delays; the required capability here is Memory grant spill review, which this operation does not supply.
B:Categorizing waits helps identify the contended resource, but wait evidence should be correlated with relevant sessions and plans; the alternative cannot substitute for Memory grant spill review under the given constraints.
C:CPU usage and operator analysis reveal expensive computations or inefficient row processing independently of storage wait times; the condition in the stem is resolved by Memory grant spill review, not by this feature alone.
Question 580
An organization wants a model to consistently follow a specialized response style across many prompts, not primarily obtain new factual records. Which SQL capability is the most direct fit?
- SQL structured lookup
- Full-text document retrieval
- Fine-tuning
- Retrieval-augmented generation
Explanation
Correct Answer
C:Fine-tuning adjusts model behavior through training on examples; it is not the usual replacement for retrieving frequently changing knowledge.
Incorrect Answers
A:A parameterized structured SQL lookup provides an exact current record without relying on probabilistic text generation; for the stated conditions, the deciding feature is Fine-tuning rather than this behavior.
B:Full-text search can locate matching passages directly; generation adds complexity when the task is simply retrieval; it addresses a different step; the question calls for Fine-tuning.
D:RAG retrieves relevant current documents and provides them as context to the generator, keeping knowledge retrieval distinct from model weights; the required capability here is Fine-tuning, which this operation does not supply.