A query incorrectly uses JSON_VALUE for a complex subdocument and receives NULL
Which SQL design or implementation decision most directly addresses this requirement?
JSON_QUERY returns a JSON fragment such as an array/object while JSON_VALUE returns a scalar.
Regular-expression replacement handles matching text patterns and should be tested for unintended matches.
OPENJSON shreds objects or arrays into rows and typed columns when a schema is supplied.
THROW provides structured error propagation that callers can handle and prevents false-success status reporting.
An AI team wants to measure retriever quality using labeled queries, not just model answer fluency
Which SQL design or implementation decision most directly addresses this requirement?
Answer-generation quality cannot reveal all retrieval failures; ranking metrics provide direct measurement.
Cosine, Euclidean and dot-product measures are not universally interchangeable unless normalization assumptions hold.
Vector properties help validate dimensions and related characteristics before comparison or indexing.
Vector normalization removes magnitude effects for certain comparisons but must match model guidance and the intended ranking metric.
A stored procedure updates stock and order status, but failure in the second update leaves inconsistent data
Which SQL design or implementation decision most directly addresses this requirement?
An explicit transaction and consistent failure policy enforce atomicity across multiple statements.
Views and TVFs are relational query sources; stored procedures are invoked with EXEC and do not act as arbitrary table expressions.
Uncontrolled trigger nesting can cause excess DML, recursion failures, and transactional contention.
Views centralize a logical query definition without storing an independent copy of rows by default.
A service constructs a small API response with named fields directly from SQL expressions
Which SQL design or implementation decision most directly addresses this requirement?
JSON_OBJECT produces correctly structured JSON property/value pairs and avoids manual quoting errors.
Edit distance measures changes required to transform a string and must not be confused with semantic meaning.
APPLY evaluates a right-side table expression per outer row and OUTER APPLY retains parents with no matches.
Graph MATCH expresses edge relationships in a pattern rather than merely comparing flat key columns.
A developer adds raw BM25 scores to cosine similarity numbers without normalizing them
After the team applies the intended design, which observation would most directly verify that the specific requirement was met?
Raw keyword and vector scores may have different ranges and meanings, so naive addition can bias results.
RRF combines positional rankings and avoids directly adding incompatible score scales.
Cosine, Euclidean and dot-product measures are not universally interchangeable unless normalization assumptions hold.
ANN index retrieval can reduce full-scan cost if accuracy requirements permit, while brute-force evaluation remains a reference.
A validation workflow requires an automatic calculation for a new record but the trigger recursively updates the same table
Which SQL design or implementation decision most directly addresses this requirement?
Uncontrolled trigger nesting can cause excess DML, recursion failures, and transactional contention.
An explicit transaction and consistent failure policy enforce atomicity across multiple statements.
INSTEAD OF triggers replace the original DML action and must implement the full intended persistence behavior.
Multistatement TVFs can introduce estimation and optimization limitations, so an inline form is preferred when expressive enough.
An order entry workflow must validate multiple inputs and atomically update several tables
Which SQL design or implementation decision most directly addresses this requirement?
Procedures encapsulate executable database workflows and accept parameters, but caller permissions and atomicity remain design concerns.
Scalar functions return one value and can encapsulate reusable calculation logic, but implementation choice may affect plan quality.
Multistatement TVFs can introduce estimation and optimization limitations, so an inline form is preferred when expressive enough.
Uncontrolled trigger nesting can cause excess DML, recursion failures, and transactional contention.
A health application must prevent database administrators from viewing patient identifiers in plaintext
After the team applies the intended design, which observation would most directly verify that the specific requirement was met?
Always Encrypted keeps sensitive column values inaccessible to parties lacking client-side keys, unlike server-only encryption at rest.
Model-generated tool calls are untrusted and can misuse granted database permissions unless the tool boundary enforces policy.
Dynamic Data Masking changes query presentation for covered low-privilege principals but is not cryptographic encryption.
Overprivileged built-in roles broaden the blast radius of compromises and accidental statements.
A chatbot used for documentation unexpectedly exposes privileged database modification tools
Which SQL design or implementation decision most directly addresses this requirement?
Model Context Protocol tools amplify backend permissions and should be least-privilege for AI workflows.
Instruction files can guide generated suggestions toward local style and safety expectations, but must still be reviewed.
Secrets in repository configuration can be leaked or copied; managed credentials reduce exposure and support rotation.
Copilot features depend on administrative eligibility and workspace settings rather than appearing automatically for every user.
A document retrieval service must search only chunks belonging to the authenticated business account
Which SQL design or implementation decision most directly addresses this requirement?
Vector similarity does not enforce authorization; retrieval still needs tenant/ACL filters.
CDC captures historical change records from the log, unlike key-only change tracking.
Idempotent update/upsert logic prevents duplicate business vectors during retries and parallel workers.
Logic Apps coordinates cross-service actions and retries but does not eliminate the need for idempotency.
An API frequently filters a JSON payload by a customer tier nested inside a document
Which SQL design or implementation decision most directly addresses this requirement?
An indexed computed expression can make frequently queried JSON properties searchable instead of repeatedly parsing and scanning.
Database check predicates reject invalid stored values regardless of which client sent the write.
A primary key uniquely identifies each row and provides a reference target; its underlying index strategy is a separate design choice.
Unicode-capable character types retain characters outside a single legacy code page and require appropriate length planning.
Please check your mailbox for a message from support@examlabs.com and follow the directions.
