Question 100
A similarity query accidentally compares a new 768-dimensional query vector with a corpus of 1536-dimensional vectors and must be corrected. What should be configured to meet this objective?
- VECTOR(1536) column
- Embedding normalization
- Model dimension compatibility
- Metadata-filtered retrieval
Explanation
Correct Answer
C:Query and stored vectors must have compatible dimensionality and semantic model space for meaningful distance calculations.
Incorrect Answers
A:VECTOR(n) declares the element count for a vector, so stored values must match the intended embedding dimension; for the stated conditions, the deciding feature is Model dimension compatibility rather than this behavior.
B:Normalization scales vectors to a consistent norm for appropriate metrics, which can make some similarity comparisons more consistent; it addresses a different step; the question calls for Model dimension compatibility.
D:Enforcing tenant metadata and database authorization filters during retrieval constrains candidate content independently of vector similarity; the required capability here is Model dimension compatibility, which this operation does not supply.
Question 101
For each device event, analysts must show both the previous measurement and the following measurement without self-joining the stream. Which TWO window functions should be used? Choose TWO.
- FIRST_VALUE()
- LAG()
- SUM() OVER(PARTITION BY ...)
- LEAD()
- PARTITION BY CustomerId
Explanation
Correct Answers
B:LAG accesses a prior row's expression value within the ordered window without a self-join; used in combination with LEAD(), it handles a separate requirement of this combined workflow.
D:LEAD reads a following row's value in the specified window partition and order; used in combination with LAG(), it handles a separate requirement of this combined workflow.
Incorrect Answers
A:FIRST_VALUE returns the expression from the first row of the applicable ordered window frame; the condition in the stem is resolved by LAG() and LEAD(), not by this feature alone.
C:A windowed SUM computes totals across partitions while retaining individual input rows in the output; that function is not interchangeable with LAG() and LEAD() for the requested result.
E:PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; it addresses a different step; the question calls for LAG() and LEAD().
Question 102
An extraction task needs the location of a regex pattern's match within a longer comment, not the matched text. What is the most appropriate design decision?
- REGEXP_INSTR
- REGEXP_REPLACE
- REGEXP_LIKE
- REGEXP_COUNT
Explanation
Correct Answer
A:REGEXP_INSTR returns a match position for a supplied regular-expression pattern.
Incorrect Answers
B:REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms; the required capability here is REGEXP_INSTR, which this operation does not supply.
C:REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; the alternative cannot substitute for REGEXP_INSTR under the given constraints.
D:REGEXP_COUNT counts matches of a regular expression in a source string; the condition in the stem is resolved by REGEXP_INSTR, not by this feature alone.
Question 103
A feature-search service compares two absolute-position vectors and must treat the candidate with distance 1 as closer than distance 4. What is the right solution for this specific requirement?
- Negative dot-product distance
- Higher inner-product similarity
- Lower Euclidean distance
- Lower cosine distance
Explanation
Correct Answer
C:Euclidean distance measures separation, so lower values indicate nearer vectors under that metric.
Incorrect Answers
A:The supported dot metric returns negative dot-product distance, so ascending output still corresponds to stronger positive dot products; that function is not interchangeable with Lower Euclidean distance for the requested result.
B:Ordinary dot-product similarity is larger for better alignment under its assumptions, unlike SQL's negative-dot distance convention; for the stated conditions, the deciding feature is Lower Euclidean distance rather than this behavior.
D:For VECTOR_DISTANCE with cosine metric, smaller distances indicate closer angular similarity, so ascending order is appropriate; this differs from the Lower Euclidean distance requirement described in the scenario.
Question 104
A query fails because new query embeddings have different dimensionality from the stored VECTOR column and must use a compatible model output. Which approach should the team select?
- Consistent distance metric
- Rebuild embeddings after model change
- Monitor recall against exact baseline
- Matching vector dimension
Explanation
Correct Answer
D:Compatible vector dimensions are required for distance operations; embedding spaces also need consistent model semantics.
Incorrect Answers
A:Metric consistency preserves meaningful ranking and avoids evaluating an index under unintended distance assumptions; the alternative cannot substitute for Matching vector dimension under the given constraints.
B:Re-embedding source content into a consistent model space is necessary because equal dimension does not imply semantic compatibility; the condition in the stem is resolved by Matching vector dimension, not by this feature alone.
C:Exact-neighbor evaluation provides a reference for estimating recall lost by approximate-index strategies; the missing behavior is Matching vector dimension; this mechanism does something else.
Question 105
The DAB endpoint receives credentials and customer records over untrusted networks and must encrypt the client-to-API transport. Which approach should the team select?
- DAB authorization roles
- TLS ingress
- Entra JWT authentication
- Database Row-Level Security
Explanation
Correct Answer
B:HTTPS/TLS protects requests and responses in transit while leaving entity permissions and SQL grants to separate controls.
Incorrect Answers
A:DAB entity role permissions limit which API operations authenticated roles can invoke; it addresses a different step; the question calls for TLS ingress.
C:An Entra-compatible authentication provider validates signed tokens for a configured audience and issuer before authorization; the required capability here is TLS ingress, which this operation does not supply.
D:SQL Row-Level Security applies within the database and complements, rather than replaces, API operation permissions; the alternative cannot substitute for TLS ingress under the given constraints.
Question 106
A privileged procedure needs narrowly added permissions without broadly impersonating an administrative login for every caller. Which approach should the team select?
- EXECUTE AS execution context
- Module signing
- EXECUTE permission
- Ownership chaining
Explanation
Correct Answer
B:Module signing can associate certificate-based permissions with module execution, avoiding blanket grants to all application principals.
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 Module signing; this mechanism does something else.
C:Granting EXECUTE on the procedure permits calling that module without granting unrestricted base-table DML, subject to ownership and execution context; for the stated conditions, the deciding feature is Module signing 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 Module signing.
Question 107
An activity document stores an array of status events; a write must add one event at the end without reconstructing unrelated JSON properties. Which choice best meets these constraints?
- JSON_ARRAYAGG with ORDER BY
- JSON_MODIFY with append
- JSON_OBJECT
- External data access
- JSON_QUERY to preserve a JSON fragment
Explanation
Correct Answer
B:JSON_MODIFY with an append path adds a value to a JSON array and returns the revised document, leaving other properties in the input intact.
Incorrect Answers
A:JSON_ARRAYAGG with an ORDER BY clause controls element ordering within its generated JSON array on supported SQL versions; ordinary aggregation without ordering does not guarantee that order; the required capability here is JSON_MODIFY with append, which this operation does not supply.
C:JSON_OBJECT builds object-shaped JSON from key/value expressions and handles escaping more reliably than manual string concatenation; the condition in the stem is resolved by JSON_MODIFY with append, not by this feature alone.
D:An external table exposes remote data through metadata and suitable connectivity without copying it into a regular local table first; the missing behavior is JSON_MODIFY with append; this mechanism does something else.
E:JSON_QUERY can mark an extracted object or array as a JSON fragment, letting FOR JSON embed that fragment as JSON instead of quoting and escaping it as a plain string; that function is not interchangeable with JSON_MODIFY with append for the requested result.
Question 108
A large notes column will be queried repeatedly with full-text predicates and needs the corresponding tokenized search structure. What is the most suitable implementation?
- CONTAINSTABLE
- Full-text index
- FREETEXT
- CONTAINS
Explanation
Correct Answer
B:A full-text index maintains searchable term structures required by SQL full-text predicates on supported columns.
Incorrect Answers
A:CONTAINSTABLE returns matching keys with a full-text rank value as a relational result set; for the stated conditions, the deciding feature is Full-text index rather than this behavior.
C:FREETEXT performs linguistic search using word-breaking and related term processing for broader lexical matching; this differs from the Full-text index requirement described in the scenario.
D:CONTAINS evaluates full-text search conditions such as words and phrases against indexed text columns; the required capability here is Full-text index, which this operation does not supply.
Question 109
A bulk embedding load encounters HTTP 429 responses from the model provider and needs controlled retry rather than rapid repeated calls. Which database mechanism should be chosen?
- Managed identity to Azure AI
- Endpoint rate-limit handling
- Restricted model EXECUTE rights
- Sensitive-data minimization
Explanation
Correct Answer
B:Backoff, rate-limit-aware batching, and idempotent checkpoints handle transient throttling while protecting upstream model capacity.
Incorrect Answers
A:Managed identity authentication can replace static secrets for supported endpoints when the workload identity receives the required service roles; the condition in the stem is resolved by Endpoint rate-limit handling, not by this feature alone.
C:Model-specific EXECUTE permissions can limit who invokes the external model, separately from SELECT on its source tables; that function is not interchangeable with Endpoint rate-limit handling for the requested result.
D:Minimizing model inputs to approved fields limits unnecessary disclosure and reduces retention and compliance risk; for the stated conditions, the deciding feature is Endpoint rate-limit handling rather than this behavior.
Question 110
An application must detect that a row changed after it was read so it can reject a stale optimistic update. Which feature would solve the stated problem?
- ROWVERSION column
- SEQUENCE object
- Change Tracking
- IDENTITY property
Explanation
Correct Answer
A:ROWVERSION is an automatically changing binary token for row modifications, useful in equality comparisons for optimistic concurrency and not a clock timestamp.
Incorrect Answers
B:A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR; the required capability here is ROWVERSION column, which this operation does not supply.
C:Change Tracking can tell a synchronization consumer which rows changed since a version; it does not, by itself, enforce a compare-and-swap update against the row version originally read.
D:IDENTITY automatically produces values for an inserted column, although applications must not assume gap-free numbering or transactional sequences; the condition in the stem is resolved by ROWVERSION column, not by this feature alone.