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.
Question 41
Three candidates have cosine distances 0.03, 0.24, and 0.61; the engine must rank the closest candidate first. What should be configured to meet this objective?
- Lower cosine distance
- Lower Euclidean distance
- Negative dot-product distance
- Higher inner-product similarity
Explanation
Correct Answer
A:For VECTOR_DISTANCE with cosine metric, smaller distances indicate closer angular similarity, so ascending order is appropriate.
Incorrect Answers
B:Euclidean distance measures separation, so lower values indicate nearer vectors under that metric; this differs from the Lower cosine distance requirement described in the scenario.
C:The supported dot metric returns negative dot-product distance, so ascending output still corresponds to stronger positive dot products; the required capability here is Lower cosine distance, which this operation does not supply.
D:Ordinary dot-product similarity is larger for better alignment under its assumptions, unlike SQL's negative-dot distance convention; the alternative cannot substitute for Lower cosine distance under the given constraints.
Question 42
A sliding-window load fails because index and table layouts do not satisfy partition-switch prerequisites. Which database mechanism should be chosen?
- RANGE RIGHT boundary definition
- Index partition alignment
- RANGE LEFT boundary definition
- Partition elimination
Explanation
Correct Answer
B:Partition-aligned indexes and compatible structures are essential for metadata-only switch operations in supported configurations.
Incorrect Answers
A:RANGE RIGHT assigns the exact boundary value to the partition on its right, matching common month-start lower-bound layouts; the missing behavior is Index partition alignment; this mechanism does something else.
C:RANGE LEFT associates a boundary value with its left partition; this changes boundary equality membership; for the stated conditions, the deciding feature is Index partition alignment rather than this behavior.
D:Partition elimination reduces scanned partitions when predicates allow the optimizer to identify the relevant partition ranges; it addresses a different step; the question calls for Index partition alignment.
Question 43
SQL VECTOR_DISTANCE is configured with the dot metric and the results must be sorted using the database function's distance convention. What should be configured to meet this objective?
- Lower Euclidean distance
- Negative dot-product distance
- Lower cosine distance
- Higher inner-product similarity
Explanation
Correct Answer
B:The supported dot metric returns negative dot-product distance, so ascending output still corresponds to stronger positive dot products.
Incorrect Answers
A:Euclidean distance measures separation, so lower values indicate nearer vectors under that metric; the required capability here is Negative dot-product distance, which this operation does not supply.
C:For VECTOR_DISTANCE with cosine metric, smaller distances indicate closer angular similarity, so ascending order is appropriate; the condition in the stem is resolved by Negative dot-product distance, not by this feature alone.
D:Ordinary dot-product similarity is larger for better alignment under its assumptions, unlike SQL's negative-dot distance convention; the missing behavior is Negative dot-product distance; this mechanism does something else.
Question 44
A customer asks for the exact current balance of invoice 8074, which is stored authoritatively in a transaction table. Which technique applies to this scenario?
- Full-text document retrieval
- Fine-tuning
- Retrieval-augmented generation
- Answer generation
- SQL structured lookup
Explanation
Correct Answer
E:A parameterized structured SQL lookup provides an exact current record without relying on probabilistic text generation.
Incorrect Answers
A:Full-text search can locate matching passages directly; generation adds complexity when the task is simply retrieval; for the stated conditions, the deciding feature is SQL structured lookup rather than this behavior.
B:Fine-tuning adjusts model behavior through training on examples; it is not the usual replacement for retrieving frequently changing knowledge; it addresses a different step; the question calls for SQL structured lookup.
C:RAG retrieves relevant current documents and provides them as context to the generator, keeping knowledge retrieval distinct from model weights; this differs from the SQL structured lookup requirement described in the scenario.
D:An LLM can generate a response conditioned on retrieved evidence and the user's question, with grounding controls applied; the required capability here is SQL structured lookup, which this operation does not supply.
Question 45
An evaluation script needs to rescale stored vectors to a standard norm before comparing their magnitude-sensitive scores. What is the right solution for this specific requirement?
- VECTOR_SEARCH
- VECTOR_NORMALIZE
- VECTORPROPERTY
- VECTOR_DISTANCE
Explanation
Correct Answer
B:VECTOR_NORMALIZE produces a norm-adjusted vector for supported vector types and normalization modes.
Incorrect Answers
A:VECTOR_SEARCH provides vector-neighbor retrieval and can use supported ANN indexing under the appropriate query form; the condition in the stem is resolved by VECTOR_NORMALIZE, not by this feature alone.
C:VECTORPROPERTY reads documented properties of a VECTOR value and is not itself a similarity search function; that function is not interchangeable with VECTOR_NORMALIZE for the requested result.
D:VECTOR_DISTANCE calculates an exact metric-specific distance and does not automatically use an approximate vector index; for the stated conditions, the deciding feature is VECTOR_NORMALIZE rather than this behavior.
Question 46
A document application needs to test phrase membership in one query and retrieve a ranked relational set of lexical matches in another. Which TWO SQL full-text operators are relevant? Choose TWO.
- CONTAINS
- Full-text index
- CREATE VECTOR INDEX
- FREETEXT
- CONTAINSTABLE
Explanation
Correct Answers
A:CONTAINS evaluates full-text search conditions such as words and phrases against indexed text columns; used in combination with CONTAINSTABLE, it handles a separate requirement of this combined workflow.
E:CONTAINSTABLE returns matching keys with a full-text rank value as a relational result set; used in combination with CONTAINS, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:A full-text index maintains searchable term structures required by SQL full-text predicates on supported columns; the required capability here is CONTAINS and CONTAINSTABLE, which this operation does not supply.
C:CREATE VECTOR INDEX builds a vector retrieval index for eligible vector columns, with engine- and version-specific settings; the alternative cannot substitute for CONTAINS and CONTAINSTABLE under the given constraints.
D:FREETEXT performs linguistic search using word-breaking and related term processing for broader lexical matching; the condition in the stem is resolved by CONTAINS and CONTAINSTABLE, not by this feature alone.
Question 47
A public data analyst should access non-sensitive product columns but not the full vendor master table containing internal discounts. Which action is justified by these conditions?
- CREATE SECURITY POLICY
- View-based projection
- Row-level security policy
- Endpoint role authorization
- Database permission GRANT
Explanation
Correct Answer
B:Granting SELECT on a narrow view and restricting base-table access limits the visible projection when the execution and ownership model permit it.
Incorrect Answers
A:CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; that function is not interchangeable with View-based projection for the requested result.
C:An RLS security policy applies row predicates at the database tier rather than depending on every client to implement a filter; it addresses a different step; the question calls for View-based projection.
D:Gateway authorization maps authenticated principals to permitted API operations and should complement database enforcement; this differs from the View-based projection requirement described in the scenario.
E:An object-scoped GRANT provides the minimum necessary database operation at the specified object boundary; the required capability here is View-based projection, which this operation does not supply.
Question 48
The lexical and semantic lists rank candidates differently and must be combined without treating their numeric scores as equivalent. What should be configured to meet this objective?
- Vector candidate pipeline
- RRF fusion stage
- Lexical candidate pipeline
- Semantic vector search
- Final document authorization check
Explanation
Correct Answer
B:RRF converts each result rank into reciprocal contributions and sums them across available lists.
Incorrect Answers
A:Vector-neighbor retrieval produces semantic candidate IDs and distance-based order for the second fusion input; the alternative cannot substitute for RRF fusion stage under the given constraints.
C:Full-text predicates or table-valued search functions can produce lexical matches and optional rank values as one fusion input; the missing behavior is RRF fusion stage; this mechanism does something else.
D:Vector search compares embedding representations and can retrieve semantic neighbors despite different literal words; that function is not interchangeable with RRF fusion stage for the requested result.
E:Authorization-aware source hydration prevents relevant-but-private candidate rows from becoming unauthorized LLM context; for the stated conditions, the deciding feature is RRF fusion stage rather than this behavior.
Question 49
A maintained UDF needs its existing function definition changed without destroying unrelated callers' data. Which action is justified by these conditions?
- DROP VIEW
- CREATE OR ALTER PROCEDURE
- ALTER FUNCTION
- sp_refreshview
Explanation
Correct Answer
C:ALTER FUNCTION changes a function's definition, subject to dependencies and function-kind restrictions.
Incorrect Answers
A:DROP VIEW removes the view object, leaving its underlying tables intact subject to dependency constraints; it addresses a different step; the question calls for ALTER FUNCTION.
B:CREATE OR ALTER for supported modules makes a definition idempotent while preserving the object's logical identity; this differs from the ALTER FUNCTION requirement described in the scenario.
D:sp_refreshview updates metadata for an eligible non-schema-bound view after referenced schema changes, rather than altering source tables; the alternative cannot substitute for ALTER FUNCTION under the given constraints.
Question 50
A telemetry record should be expanded through OPENJSON into multiple sample rows, excluding source rows whose expansion is empty. What is the most appropriate design decision?
- LEFT JOIN
- OUTER APPLY
- CROSS APPLY
- INNER JOIN
Explanation
Correct Answer
C:CROSS APPLY joins each input row to the output of a correlated rowset expression, without retaining empty expansions.
Incorrect Answers
A:A LEFT OUTER JOIN preserves each left-side row and supplies NULL for missing right-side matches; the missing behavior is CROSS APPLY; this mechanism does something else.
B:OUTER APPLY preserves unmatched outer rows while evaluating a correlated table expression for each one; that function is not interchangeable with CROSS APPLY for the requested result.
D:An INNER JOIN returns qualifying pairs only and excludes rows lacking a match under its ON predicate; it addresses a different step; the question calls for CROSS APPLY.