Question 480
An approved SQL RAG procedure already has the database permissions for outbound calls. It must send a prepared JSON prompt with a SQL REST procedure, using a named database-scoped endpoint credential specified by policy rather than inline secret headers or a managed identity. Which TWO components meet these specific requirements? Choose TWO.
- External-endpoint permission
- Managed identity authorization
- sp_invoke_external_rest_endpoint
- DATABASE SCOPED CREDENTIAL
- STRING_AGG
Explanation
Correct Answers
C:sp_invoke_external_rest_endpoint invokes HTTPS endpoints with configured payload, headers, timeout, and credentials where supported; used in combination with DATABASE SCOPED CREDENTIAL, it handles a separate requirement of this combined workflow.
D:A DATABASE SCOPED CREDENTIAL provides SQL-managed endpoint authentication material referenced by outbound calls, satisfying the specified named-object policy instead of embedding a literal key.
Incorrect Answers
A:EXECUTE ANY EXTERNAL ENDPOINT is the relevant database permission for calling the supported external REST procedure; the alternative cannot substitute for sp_invoke_external_rest_endpoint and DATABASE SCOPED CREDENTIAL under the given constraints.
B:Managed identity authentication can authorize a supported outbound model call when the identity has the required destination role; the condition in the stem is resolved by sp_invoke_external_rest_endpoint and DATABASE SCOPED CREDENTIAL, not by this feature alone.
E:STRING_AGG concatenates values across rows with a selected delimiter, subject to ordering and type considerations; for the stated conditions, the deciding feature is sp_invoke_external_rest_endpoint and DATABASE SCOPED CREDENTIAL rather than this behavior.
Question 481
A REST client wants each Orders response limited to 25 results while still being able to request subsequent pages. What should be configured to meet this objective?
- `$first` page-size control
- DAB in-memory response cache
- DAB Redis cache
- GraphQL pagination `after`
Explanation
Correct Answer
A:The first parameter requests the page size; it does not itself identify where the next page begins.
Incorrect Answers
B:DAB response caching can retain eligible read results for configured durations, subject to invalidation and consistency requirements; this differs from the `$first` page-size control requirement described in the scenario.
C:A supported distributed Redis cache stores response entries outside individual runtime processes for multi-instance reuse; the required capability here is `$first` page-size control, which this operation does not supply.
D:A GraphQL after cursor continues pagination beyond a prior page, subject to the configured query shape; the alternative cannot substitute for `$first` page-size control under the given constraints.
Question 482
Callers need a parameterized relational expression returning a table of transactions for a supplied tenant identifier. Which technique applies to this scenario?
- Table-valued function
- Stored procedure
- Scalar user-defined function
- View
Explanation
Correct Answer
A:A table-valued function returns a relational result, and an inline version can expose an optimizer-friendly SELECT expression.
Incorrect Answers
B:A stored procedure can encapsulate multi-statement DML and transactions, accept input parameters, and report status or rowsets to application callers; that function is not interchangeable with Table-valued function for the requested result.
C:A scalar function produces a single value and can be invoked in supported expressions, with performance considerations depending on its implementation; for the stated conditions, the deciding feature is Table-valued function rather than this behavior.
D:A regular view encapsulates a SELECT definition as a database object and generally resolves its rows from underlying tables when queried; it addresses a different step; the question calls for Table-valued function.
Question 483
An old external model reference is retired and should be removed after dependent SQL objects are migrated away. What should be configured to meet this objective?
- GRANT EXECUTE ON EXTERNAL MODEL
- ALTER EXTERNAL MODEL
- CREATE EXTERNAL MODEL
- DROP EXTERNAL MODEL
Explanation
Correct Answer
D:DROP EXTERNAL MODEL removes the model metadata object once relevant dependencies and permissions allow it.
Incorrect Answers
A:Executing an external model requires the appropriate model authorization, separable from general table SELECT permissions; the required capability here is DROP EXTERNAL MODEL, which this operation does not supply.
B:ALTER EXTERNAL MODEL changes the stored external model definition, subject to required permissions and supported options; the alternative cannot substitute for DROP EXTERNAL MODEL under the given constraints.
C:CREATE EXTERNAL MODEL registers an external inference model definition for supported SQL AI functions rather than storing the model weights as table rows; the condition in the stem is resolved by DROP EXTERNAL MODEL, not by this feature alone.
Question 484
Schema deployment succeeds, but the team needs to verify existing data survives the change and constraints still work. Which choice best meets these constraints?
- Permission-negative test
- Boundary-value test
- Positive-path unit test
- Migration integration test
Explanation
Correct Answer
D:Integration tests after migration exercise live data transformations, object dependencies, and application-critical behavior.
Incorrect Answers
A:Negative authorization tests verify forbidden operations fail with the actual application principal rather than assuming intended grants; for the stated conditions, the deciding feature is Migration integration test rather than this behavior.
B:Boundary tests target conditions at and near validation thresholds where logic defects commonly occur; it addresses a different step; the question calls for Migration integration test.
C:Positive-path unit tests validate intended output and state changes against known inputs; this differs from the Migration integration test requirement described in the scenario.
Question 485
Index configuration and query scoring must use an appropriate compatible similarity metric for their target embeddings. Which choice best meets these constraints?
- Rebuild embeddings after model change
- Monitor recall against exact baseline
- Matching vector dimension
- Consistent distance metric
Explanation
Correct Answer
D:Metric consistency preserves meaningful ranking and avoids evaluating an index under unintended distance assumptions.
Incorrect Answers
A: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 Consistent distance metric, not by this feature alone.
B:Exact-neighbor evaluation provides a reference for estimating recall lost by approximate-index strategies; the missing behavior is Consistent distance metric; this mechanism does something else.
C:Compatible vector dimensions are required for distance operations; embedding spaces also need consistent model semantics; that function is not interchangeable with Consistent distance metric for the requested result.
Question 486
A sensor report must preserve missing readings as explicit JSON null elements in an aggregated array so downstream consumers can distinguish gaps from deleted positions. Which feature would solve the stated problem?
- JSON_OBJECT with ABSENT ON NULL
- JSON_ARRAY
- FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
- JSON_ARRAYAGG with NULL ON NULL
Explanation
Correct Answer
D:JSON_ARRAYAGG with NULL ON NULL retains SQL NULL inputs as JSON null elements; its usual ABSENT ON NULL behavior would omit those array slots.
Incorrect Answers
A:JSON_OBJECT with ABSENT ON NULL suppresses keys whose SQL expressions are NULL; its default NULL ON NULL behavior would instead emit those keys with JSON null values; this differs from the JSON_ARRAYAGG with NULL ON NULL requirement described in the scenario.
B:JSON_ARRAY constructs an array value from its supplied scalar expressions; the required capability here is JSON_ARRAYAGG with NULL ON NULL, which this operation does not supply.
C:FOR JSON PATH with WITHOUT_ARRAY_WRAPPER removes the enclosing JSON array for a single-row result; use it only when cardinality and JSON validity are controlled; the alternative cannot substitute for JSON_ARRAYAGG with NULL ON NULL under the given constraints.
Question 487
A search solution must fuse independent ranking lists yet preserve high priority for exact diagnostic keywords in the lexical branch. Which TWO methods directly support these requirements? Choose TWO.
- VECTOR(n) column
- Reciprocal Rank Fusion
- Weighted score normalization
- Vector-only ranking
- Keyword-only ranking
Explanation
Correct Answers
B:RRF adds reciprocal contributions based on each result's rank position, making lexical and vector rankings comparable without raw-score calibration; used in combination with Keyword-only ranking, it handles a separate requirement of this combined workflow.
E:Lexical ranking rewards occurrences of specified terms, supporting exact identifiers that embeddings may not distinguish reliably; used in combination with Reciprocal Rank Fusion, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:The VECTOR type stores fixed-dimensional floating-point arrays in an optimized representation compatible with vector functions; that function is not interchangeable with Reciprocal Rank Fusion and Keyword-only ranking for the requested result.
C:Normalizing and weighting scores can combine modalities, but requires understanding score distributions and calibration; it addresses a different step; the question calls for Reciprocal Rank Fusion and Keyword-only ranking.
D:Vector-only ranking uses embedding proximity and may miss exact special identifiers that lexical retrieval handles better; this differs from the Reciprocal Rank Fusion and Keyword-only ranking requirement described in the scenario.
Question 488
Security officers must investigate which automated database tools an assistant invoked during a schema-design session. Which choice best meets these constraints?
- Review tool audit trail
- Repository access logs
- Least-privilege MCP tools
- Scoped instruction file
Explanation
Correct Answer
A:Recording tool-call identities, operations, and outcomes supports accountability and incident investigation independently of conversation text.
Incorrect Answers
B:Repository access logs show activity against source files; they do not record which SQL tools an MCP or assistant client invoked during a session.
C:Exposing a constrained metadata inspection tool reduces the actions the client can request compared with a full arbitrary-SQL tool; the missing behavior is Review tool audit trail; this mechanism does something else.
D:Appropriately scoped Copilot instruction files can provide targeted coding conventions without imposing the same guidance everywhere; that function is not interchangeable with Review tool audit trail for the requested result.
Question 489
A SQL API embeds an already valid nested object from a JSON column into a larger FOR JSON result and must prevent the nested fragment from becoming an escaped string. Which option would you recommend?
- JSON_QUERY to preserve a JSON fragment
- JSON_MODIFY with append
- JSON_OBJECT
- STRING_ESCAPE
Explanation
Correct Answer
A: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.
Incorrect Answers
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; this differs from the JSON_QUERY to preserve a JSON fragment requirement described in the scenario.
C:JSON_OBJECT builds object-shaped JSON from key/value expressions and handles escaping more reliably than manual string concatenation; the required capability here is JSON_QUERY to preserve a JSON fragment, which this operation does not supply.
D:STRING_ESCAPE escapes string characters; on its own, it cannot mark prevalidated JSON as a nested fragment for a FOR JSON result.
Question 490
A shared DAB endpoint needs Reader/Editor operation restrictions and a second SQL-level barrier that filters records by tenant even for broad API queries. Which TWO authorization layers are needed? Choose TWO.
- Entra JWT authentication
- TLS ingress
- Local DAB runtime
- Database Row-Level Security
- DAB authorization roles
Explanation
Correct Answers
D:SQL Row-Level Security applies within the database and complements, rather than replaces, API operation permissions; used in combination with DAB authorization roles, it handles a separate requirement of this combined workflow.
E:DAB entity role permissions limit which API operations authenticated roles can invoke; used in combination with Database Row-Level Security, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:An Entra-compatible authentication provider validates signed tokens for a configured audience and issuer before authorization; the missing behavior is Database Row-Level Security and DAB authorization roles; this mechanism does something else.
B:HTTPS/TLS protects requests and responses in transit while leaving entity permissions and SQL grants to separate controls; that function is not interchangeable with Database Row-Level Security and DAB authorization roles for the requested result.
C:Running DAB locally supports iterative testing and debugging using its configuration and accessible development data source; for the stated conditions, the deciding feature is Database Row-Level Security and DAB authorization roles rather than this behavior.