Question 30
A query needs a reusable calculation that accepts a tax rate and price and returns one calculated SQL value per call. Which choice best meets these constraints?
- Stored procedure
- Scalar user-defined function
- Table-valued function
- View
Explanation
Correct Answer
B:A scalar function produces a single value and can be invoked in supported expressions, with performance considerations depending on its implementation.
Incorrect Answers
A:A stored procedure can encapsulate multi-statement DML and transactions, accept input parameters, and report status or rowsets to application callers; this differs from the Scalar user-defined function requirement described in the scenario.
C:A table-valued function returns a relational result, and an inline version can expose an optimizer-friendly SELECT expression; the alternative cannot substitute for Scalar user-defined function under the given constraints.
D:A regular view encapsulates a SELECT definition as a database object and generally resolves its rows from underlying tables when queried; the condition in the stem is resolved by Scalar user-defined function, not by this feature alone.
Question 31
Data API builder must protect credentials and record contents against network eavesdropping while clients call its REST endpoint. What should the developer implement?
- HTTPS/TLS endpoint
- Managed identity for model endpoint
- Azure Key Vault
- Azure Monitor alerts
- Scoped token authorization
Explanation
Correct Answer
A:TLS encrypts data in transit and authenticates the server endpoint, distinct from authorization to specific database rows.
Incorrect Answers
B:A managed identity can obtain service tokens for supported model endpoints when assigned the appropriate resource permissions; for the stated conditions, the deciding feature is HTTPS/TLS endpoint rather than this behavior.
C:Key Vault secures secrets and cryptographic materials through authenticated access and audit controls instead of embedding values in repository files; it addresses a different step; the question calls for HTTPS/TLS endpoint.
D:Azure Monitor evaluates selected signals against alert rules and can notify or trigger responses; it is not a row-authorization mechanism; this differs from the HTTPS/TLS endpoint requirement described in the scenario.
E:Server-side token validation and authorization check claims and permissions rather than treating any well-formed bearer token as sufficient; the required capability here is HTTPS/TLS endpoint, which this operation does not supply.
Question 32
An existing corpus's vectors all use a retired embedding model and must be regenerated consistently with a newly selected model. Which approach should the team select?
- Change Data Capture
- Azure Functions SQL trigger
- Change Tracking
- Scheduled full rebuild
Explanation
Correct Answer
D:A controlled rebuild recomputes vectors across the corpus, often into a new index or version, rather than mixing incompatible embedding spaces.
Incorrect Answers
A:CDC records change operations and row details for configured tables, supporting event processing and replay; the alternative cannot substitute for Scheduled full rebuild under the given constraints.
B:The SQL trigger binding can invoke a function for detected database changes and let the function handle inference and vector updates; the condition in the stem is resolved by Scheduled full rebuild, not by this feature alone.
C:Change Tracking identifies changed rows and versions efficiently, making it useful for incremental re-embedding without storing full change history; the missing behavior is Scheduled full rebuild; this mechanism does something else.
Question 33
A nested-loop join is unexpectedly expensive, and investigators need to determine whether cardinality underestimation drove the decision. Which action is justified by these conditions?
- UPDATE STATISTICS
- Index physical maintenance
- Actual vs estimated row comparison
- Filtered statistics
Explanation
Correct Answer
C:Comparing estimated and actual rows at plan operators reveals estimation errors that can explain inappropriate operator choices.
Incorrect Answers
A:Updating statistics refreshes cardinality information used during plan selection, though it does not directly rebuild every index; it addresses a different step; the question calls for Actual vs estimated row comparison.
B:Appropriate index rebuild or reorganize actions address physical layout while statistics alone cannot fix page fragmentation; this differs from the Actual vs estimated row comparison requirement described in the scenario.
D:Filtered statistics capture distributions for rows matching a predicate and can improve estimates for compatible queries; the alternative cannot substitute for Actual vs estimated row comparison under the given constraints.
Question 34
A data integration requires comparisons based on code-point or binary ordering rules rather than linguistic equivalence. Which mechanism addresses the requirement?
- Nonpersisted computed column
- Case-sensitive collation
- Persisted computed column
- Binary collation
Explanation
Correct Answer
D:Binary collations compare encoded character values using binary rules, differing from culture-aware linguistic collations.
Incorrect Answers
A:A regular computed column defines an expression evaluated as needed rather than necessarily materialized on disk; the missing behavior is Binary collation; this mechanism does something else.
B:A case-sensitive collation changes comparison and ordering behavior so character case is significant; that function is not interchangeable with Binary collation for the requested result.
C:A PERSISTED computed column materializes the computed result for eligible expressions, with extra storage and update work; for the stated conditions, the deciding feature is Binary collation rather than this behavior.
Question 35
An analyst wants a supported vector query to fail rather than silently fall back to exact execution when no eligible ANN index plan exists. What is the most appropriate design decision?
- SELECT TOP (N) WITH APPROXIMATE
- CREATE VECTOR INDEX
- VECTOR_DISTANCE exact ordering
- FORCE_ANN_ONLY hint
Explanation
Correct Answer
D:FORCE_ANN_ONLY requests the approximate index strategy and has prerequisites including an appropriate index and approximate top-k query.
Incorrect Answers
A:TOP with APPROXIMATE and an ordered vector-search distance permits eligible approximate index-based retrieval in current supported versions; the required capability here is FORCE_ANN_ONLY hint, which this operation does not supply.
B:CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; the alternative cannot substitute for FORCE_ANN_ONLY hint under the given constraints.
C:Sorting by VECTOR_DISTANCE calculates exact distances and can benchmark recall against indexed approximate results; the condition in the stem is resolved by FORCE_ANN_ONLY hint, not by this feature alone.
Question 36
Generated T-SQL uses a function not implemented in the target engine edition; the developer needs to catch incompatibility before deployment. What is the most suitable implementation?
- Validate against target SQL dialect
- Review generated query plans
- Review security privileges
- Run unit and integration tests
Explanation
Correct Answer
A:Compiling and testing against the actual target platform identifies unsupported syntax or version-specific behaviors that prompt text cannot guarantee.
Incorrect Answers
B:Reviewing actual execution plans and representative workload metrics reveals access paths and cardinality issues that text generation alone cannot verify; it addresses a different step; the question calls for Validate against target SQL dialect.
C:Effective permission analysis identifies the required object-level rights rather than assuming broad database ownership is necessary; this differs from the Validate against target SQL dialect requirement described in the scenario.
D:Automated tests exercise database contracts and interactions, detecting regressions that a plausible generated explanation may conceal; the required capability here is Validate against target SQL dialect, which this operation does not supply.
Question 37
Auditors need to understand why encrypted database files require correct certificate or key protection for successful restore. Which option would you recommend?
- Deterministic Always Encrypted
- Randomized Always Encrypted
- TDE key hierarchy
- Always Encrypted with secure enclaves
Explanation
Correct Answer
C:TDE protects the database encryption key through configured key hierarchy, so key backup and availability matter for restored data.
Incorrect Answers
A:Deterministic column encryption yields the same ciphertext for equal plaintext with the same key and supports restricted equality comparisons; the condition in the stem is resolved by TDE key hierarchy, not by this feature alone.
B:Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support; the missing behavior is TDE key hierarchy; this mechanism does something else.
D:Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites; for the stated conditions, the deciding feature is TDE key hierarchy rather than this behavior.
Question 38
Exact search over millions of vectors exceeds latency budgets and the team wants to measure indexed candidate search. What is the most appropriate design decision?
- Distance-computation cost
- ANN index evaluation
- Filter-selectivity assessment
- Top-k selection
Explanation
Correct Answer
B:ANN indexing can reduce comparisons and improve speed, subject to measured recall and platform support.
Incorrect Answers
A:Exhaustive distance calculations scale with candidate count and vector dimension, creating CPU and sorting pressure; this differs from the ANN index evaluation requirement described in the scenario.
C:Selective eligibility predicates and supported filter execution behavior affect vector retrieval work and correctness; the alternative cannot substitute for ANN index evaluation under the given constraints.
D:Limiting k reduces result volume and downstream processing while preserving the intended nearest-neighbor objective; the condition in the stem is resolved by ANN index evaluation, not by this feature alone.
Question 39
An exact nearest-neighbor SELECT should return the seven smallest cosine-distance values deterministically from a filtered corpus. What should be configured to meet this objective?
- Hybrid union of candidate IDs
- Post-retrieval reranking
- Approximate top-k search
- TOP with ordered distance
- Reciprocal-rank contribution
Explanation
Correct Answer
D:Ordering by the computed distance ascending and selecting TOP limits results to nearest eligible vectors under exact scoring.
Incorrect Answers
A:Joining or unioning candidate IDs from both sources allows duplicate documents to receive contributions from multiple ranking lists; that function is not interchangeable with TOP with ordered distance for the requested result.
B:A reranking stage refines top candidates after initial retrieval, reducing cost compared with full-corpus cross-encoding; for the stated conditions, the deciding feature is TOP with ordered distance rather than this behavior.
C:Approximate TOP with VECTOR_SEARCH can leverage supported vector indexes for low-latency ANN retrieval; it addresses a different step; the question calls for TOP with ordered distance.
E:RRF sums reciprocal-rank terms for each ranking list where a document appears, so missing-list contributions are simply absent; the required capability here is TOP with ordered distance, which this operation does not supply.
Question 40
A validation routine detects a forbidden state and must raise an error that reaches the caller with correct exception semantics. What is the most appropriate design decision?
- THROW
- AFTER trigger
- XACT_STATE()
- SET XACT_ABORT ON
- TRY...CATCH
Explanation
Correct Answer
A:THROW explicitly raises or rethrows T-SQL errors and supports propagating failures through calling code.
Incorrect Answers
B:An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; the condition in the stem is resolved by THROW, not by this feature alone.
C:XACT_STATE reports transaction state, distinguishing a doomed transaction from one that can still commit; the missing behavior is THROW; this mechanism does something else.
D:XACT_ABORT ON makes specified runtime failures abort and roll back a transaction, affecting the error-handling behavior of the batch; that function is not interchangeable with THROW for the requested result.
E:TRY...CATCH structures error handling into protected and handler blocks for catchable execution errors; for the stated conditions, the deciding feature is THROW rather than this behavior.