Question 290
A nightly ingestion pipeline must embed hundreds of thousands of static documents while enforcing API rate limits and checkpointing independently of write transactions. Which mechanism addresses the requirement?
- AI_GENERATE_EMBEDDINGS
- Database trigger with guarded work
- External batching worker
- Azure Function on change event
Explanation
Correct Answer
C:An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions.
Incorrect Answers
A:AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; the missing behavior is External batching worker; this mechanism does something else.
B:A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously; that function is not interchangeable with External batching worker for the requested result.
D:A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes; it addresses a different step; the question calls for External batching worker.
Question 291
A remote generation endpoint intermittently responds with HTTP 429, and the procedure needs bounded retry or queueing rather than duplicate untracked work. Which option would you recommend?
- SQL structured lookup
- Redact confidential fields
- Limit retrieved context
- Audit retrieved sources
- Citation correctness check
- Retry transient model throttling
Explanation
Correct Answer
F:Rate-limit-aware retries and idempotent request handling reduce failures without overwhelming the inference service.
Incorrect Answers
A:A parameterized structured SQL lookup provides an exact current record without relying on probabilistic text generation; the required capability here is Retry transient model throttling, which this operation does not supply.
B:Redaction and data minimization remove unnecessary secrets or identifiers before leaving the SQL trust boundary; the alternative cannot substitute for Retry transient model throttling under the given constraints.
C:Limiting and ranking context passages keeps input within token budgets and reduces irrelevant material sent to the generator; the condition in the stem is resolved by Retry transient model throttling, not by this feature alone.
D:Logging approved source identifiers and prompt metadata creates a traceable record for investigation without storing unrestricted sensitive content; the missing behavior is Retry transient model throttling; this mechanism does something else.
E:Citation evaluation checks correspondence between produced claims and referenced evidence instead of merely counting links; that function is not interchangeable with Retry transient model throttling for the requested result.
Question 292
The Azure deployment identity signs in successfully but cannot change stored procedures; it needs appropriate database permissions. Which choice best meets these constraints?
- Credential rotation
- Federated GitHub identity
- Protected environment secret
- Database contained user grant
Explanation
Correct Answer
D:Authentication alone does not grant SQL object privileges, so a contained user or equivalent principal must receive deployment rights.
Incorrect Answers
A:Revoking and rotating compromised credentials eliminates continued use of exposed secrets, unlike merely deleting a log message; for the stated conditions, the deciding feature is Database contained user grant rather than this behavior.
B:Federated workload credentials exchange trusted action identity assertions for Azure access, requiring explicit trust configuration; it addresses a different step; the question calls for Database contained user grant.
C:Environment-scoped secret controls restrict availability to authorized workflows, although long-lived passwords should be minimized; this differs from the Database contained user grant requirement described in the scenario.
Question 293
The live test database has changed manually and a developer needs to inspect differences against the project's schema model. What is the right solution for this specific requirement?
- Schema Compare
- SqlPackage Script
- Declarative object file
- SqlPackage DeployReport
Explanation
Correct Answer
A:Schema Compare contrasts schema definitions from two sources and can help identify drift and reconcile them.
Incorrect Answers
B:SqlPackage Script emits deployment SQL instead of immediately publishing the changes to the target database; the missing behavior is Schema Compare; this mechanism does something else.
C:SQL projects store declarative object definitions, which DacFx compares with deployed schemas to derive necessary changes; that function is not interchangeable with Schema Compare for the requested result.
D:DeployReport describes planned deployment changes derived from model comparison and supports approval workflows; for the stated conditions, the deciding feature is Schema Compare rather than this behavior.
Question 294
An API response should include the complete nested `shippingAddress` object from a stored JSON document without flattening it to text fields. Which choice best meets these constraints?
- JSON_VALUE
- JSON_QUERY
- OPENJSON
- ISJSON with ARRAY type constraint
Explanation
Correct Answer
B:JSON_QUERY extracts an object or array fragment and preserves its JSON structure as an expression result.
Incorrect Answers
A:JSON_VALUE extracts a single scalar at a path and is not intended to return entire JSON objects or arrays; this differs from the JSON_QUERY requirement described in the scenario.
C:OPENJSON converts JSON input to a relational rowset and can use an explicit WITH schema for typed extraction; the alternative cannot substitute for JSON_QUERY under the given constraints.
D:ISJSON(value, ARRAY) tests both JSON syntax and the required top-level array shape on SQL versions that support its second type-constraint argument; the condition in the stem is resolved by JSON_QUERY, not by this feature alone.
Question 295
Database administrators must not have ordinary plaintext access to protected health-identifying values at query processing time. Which technique applies to this scenario?
- RLS tenant filtering
- Encrypted sensitive column
- Column-level permissions
- Masked response
Explanation
Correct Answer
B:Always Encrypted places decryption with approved clients and key stores rather than relying only on engine-side masking.
Incorrect Answers
A:A database RLS policy supplies the filtering boundary across application query paths using supported security predicates; that function is not interchangeable with Encrypted sensitive column for the requested result.
C:Column-level GRANT or DENY policies can scope SELECT privileges to designated columns where supported; it addresses a different step; the question calls for Encrypted sensitive column.
D:Dynamic Data Masking changes result visibility according to effective permissions while leaving encrypted-at-rest requirements separate; this differs from the Encrypted sensitive column requirement described in the scenario.
Question 296
A retrieval pipeline compares vectors using a metric whose behavior depends on vector magnitude and wants to control that effect. Which database mechanism should be chosen?
- Metadata-filtered retrieval
- Embedding normalization
- Model dimension compatibility
- VECTOR(1536) column
Explanation
Correct Answer
B:Normalization scales vectors to a consistent norm for appropriate metrics, which can make some similarity comparisons more consistent.
Incorrect Answers
A:Enforcing tenant metadata and database authorization filters during retrieval constrains candidate content independently of vector similarity; the alternative cannot substitute for Embedding normalization under the given constraints.
C:Query and stored vectors must have compatible dimensionality and semantic model space for meaningful distance calculations; the missing behavior is Embedding normalization; this mechanism does something else.
D:VECTOR(n) declares the element count for a vector, so stored values must match the intended embedding dimension; that function is not interchangeable with Embedding normalization for the requested result.
Question 297
A production release detects unexpected objects missing from the database and policy requires investigation before automatic reconciliation. Which feature would solve the stated problem?
- Import changes into SQL project
- Block drifted release
- Review object dependency graph
- Compare live schema to DACPAC
Explanation
Correct Answer
B:A drift gate can halt deployment when actual target state deviates materially from the expected controlled baseline.
Incorrect Answers
A:Updating the project from the database can reconcile reviewed schema changes back into declarative source control; it addresses a different step; the question calls for Block drifted release.
C:Model dependency validation and schema comparison help reveal impacts that a text-only change review may miss; the required capability here is Block drifted release, which this operation does not supply.
D:Comparing the deployed database with the approved DACPAC reveals schema drift that normal source-code reviews cannot see; the alternative cannot substitute for Block drifted release under the given constraints.
Question 298
A procedure must send a set of matching rows and columns directly to an application data reader. What should the developer implement?
- OUTPUT parameter
- Input parameter
- Result set from SELECT
- RETURN status code
Explanation
Correct Answer
C:Executing SELECT within a procedure returns a result set that clients can consume, unlike an integer-only return code.
Incorrect Answers
A:An OUTPUT parameter returns a variable value to the caller when the invocation and parameter declaration allow output; the missing behavior is Result set from SELECT; this mechanism does something else.
B:An input parameter supplies a value for the procedure's execution without embedding a different literal in every call; that function is not interchangeable with Result set from SELECT for the requested result.
D:A stored procedure RETURN value is an integer status mechanism, conventionally used for control or result codes; it addresses a different step; the question calls for Result set from SELECT.
Question 299
Relevant troubleshooting instructions frequently straddle chunk boundaries, so adjacent segments should share a small amount of text. Which database mechanism should be chosen?
- Semantic section chunking
- Fixed-size token chunks
- Hierarchical parent-child chunks
- Overlapping chunks
Explanation
Correct Answer
D:Overlap repeats limited context between adjacent chunks to reduce information loss at boundaries, at added storage and embedding cost.
Incorrect Answers
A:Semantic chunking follows document structure or topic boundaries, making returned units more coherent than arbitrary cuts; the required capability here is Overlapping chunks, which this operation does not supply.
B:Fixed-size chunking divides content into bounded segments, with boundaries that may not respect semantic topics; the alternative cannot substitute for Overlapping chunks under the given constraints.
C:Parent-child chunk mapping keeps fine-grained searchable chunks linked to larger source sections for context expansion; the condition in the stem is resolved by Overlapping chunks, not by this feature alone.
Question 300
A public service needs one controlled order-cancellation operation without arbitrary SQL execution against the underlying database. What is the right solution for this specific requirement?
- Restricted DAB entity permissions
- Stored procedure execution boundary
- Parameterized SQL statements
- Allowlisted MCP tools
Explanation
Correct Answer
B:Exposing a reviewed procedure with EXECUTE permission provides a constrained interface compared with granting direct table writes.
Incorrect Answers
A:DAB entity permissions can limit operations exposed to each role, complementing database-side permissions; for the stated conditions, the deciding feature is Stored procedure execution boundary rather than this behavior.
C:SQL parameters separate supplied values from executable query syntax and are a core mitigation against injection; this differs from the Stored procedure execution boundary requirement described in the scenario.
D:An allowlisted set of restricted MCP tools reduces agent capabilities; prompt instructions cannot substitute for backend enforcement; the required capability here is Stored procedure execution boundary, which this operation does not supply.