Question 410
A date column is indexed and queries must seek a selected month without applying YEAR() to every stored value. What should be configured to meet this objective?
- Parameterized query
- TOP with ORDER BY
- SARGable date range
- EXISTS semi-join
- MAXRECURSION query hint
Explanation
Correct Answer
C:A half-open range on the raw indexed date can preserve seek opportunities and avoid a non-sargable function on the column.
Incorrect Answers
A:Parameterization separates values from SQL text, reducing injection risk and enabling plan reuse in many cases; the missing behavior is SARGable date range; this mechanism does something else.
B:TOP constrained by ORDER BY selects highest-priority ordered rows, while tie handling requires a suitable secondary key when values match; that function is not interchangeable with SARGable date range for the requested result.
D:EXISTS expresses a presence test and does not expand the outer result into one row per matching child; it addresses a different step; the question calls for SARGable date range.
E:OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion; this differs from the SARGable date range requirement described in the scenario.
Question 411
An infrequently executed parameterized reporting query needs a plan optimized for each supplied value and can tolerate compilation overhead. Which SQL capability is the most direct fit?
- OPTIMIZE FOR UNKNOWN
- OPTION (RECOMPILE)
- Query Store plan forcing
- Parameter-sensitive plan diagnosis
Explanation
Correct Answer
B:RECOMPILE can produce a fresh plan for the current parameter values, trading compilation cost for specialization.
Incorrect Answers
A:OPTIMIZE FOR UNKNOWN directs estimation toward generalized statistics rather than a particular sniffed parameter value; the required capability here is OPTION (RECOMPILE), which this operation does not supply.
C:Query Store plan forcing can stabilize a query on a captured plan, while the DBA must monitor continued validity and suitability; the condition in the stem is resolved by OPTION (RECOMPILE), not by this feature alone.
D:Parameter-sensitive behavior stems from different data selectivities sharing plan assumptions; runtime and plan comparison can reveal the issue; the missing behavior is OPTION (RECOMPILE); this mechanism does something else.
Question 412
A model response contains usage.total_tokens. Billing ingestion treats that field as mandatory and must raise a path error if it is absent, rather than silently return NULL. Which expression enforces that JSON-path behavior?
- ISJSON
- TRY_CONVERT
- External-endpoint permission
- JSON_VALUE(payload, 'strict $.usage.total_tokens')
- OPENJSON
Explanation
Correct Answer
D:JSON_VALUE with a strict JSON path raises an error for a missing required scalar path; lax-path evaluation would normally return NULL for a missing value.
Incorrect Answers
A:ISJSON checks whether text is valid JSON but does not retrieve a mandatory scalar under strict-path error semantics.
B:TRY_CONVERT handles SQL type conversions and invalid formats; it does not enforce JSON-path existence or strict-path failures.
C:External-endpoint permission governs outbound API invocation, not strict parsing of a returned JSON response.
E:OPENJSON can shred objects or arrays into rows, but the request is a single required scalar whose missing path must raise an error.
Question 413
An API returns exactly one selected settings row and must serialize it as a single top-level JSON object rather than an array containing one object. Which option satisfies the desired behavior most directly?
- FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
- JSON_ARRAY
- JSON_ARRAYAGG with NULL ON NULL
- JSON_OBJECT with ABSENT ON NULL
Explanation
Correct Answer
A: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.
Incorrect Answers
B:JSON_ARRAY constructs an array value from its supplied scalar expressions; the missing behavior is FOR JSON PATH, WITHOUT_ARRAY_WRAPPER; this mechanism does something else.
C: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; that function is not interchangeable with FOR JSON PATH, WITHOUT_ARRAY_WRAPPER for the requested result.
D: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; for the stated conditions, the deciding feature is FOR JSON PATH, WITHOUT_ARRAY_WRAPPER rather than this behavior.
Question 414
The API must invoke an approved database routine that performs a narrow business operation rather than exposing unrestricted table updates. Which action is justified by these conditions?
- Explicit relationship configuration
- View-backed entity
- Stored-procedure entity
- Table-backed entity
Explanation
Correct Answer
C:DAB can expose supported stored procedures as API entities with operation-specific configuration and permissions.
Incorrect Answers
A:Configured relationships describe how exposed entities connect and control relational navigation in generated GraphQL schemas; this differs from the Stored-procedure entity requirement described in the scenario.
B:An entity can map to a supported SQL view, with write capabilities subject to view and DAB limitations; the required capability here is Stored-procedure entity, which this operation does not supply.
D:A DAB entity can map to a table source with configured permissions for REST or GraphQL operations; the condition in the stem is resolved by Stored-procedure entity, not by this feature alone.
Question 415
A chat assistant produces a query template that will receive untrusted user-entered search text and must not concatenate it into SQL code. What is the right solution for this specific requirement?
- Parameterize generated SQL
- Synthetic test data
- Scoped temporary credentials
- Schema metadata only
Explanation
Correct Answer
A:Parameterized execution separates user data from executable SQL, reducing injection risk even if the initial query text was AI-assisted.
Incorrect Answers
B:Synthetic records can reproduce structural and edge-case patterns while reducing the need to expose personal production data; for the stated conditions, the deciding feature is Parameterize generated SQL rather than this behavior.
C:Short-lived, narrowly authorized credentials limit exposure relative to reusable administrative secrets embedded in tool configuration; it addresses a different step; the question calls for Parameterize generated SQL.
D:Restricting context to schema metadata can support query drafting without exposing record contents to the model; this differs from the Parameterize generated SQL requirement described in the scenario.
Question 416
A monitoring query begins with one row per device. It must expand the samples JSON array into rows but preserve devices whose array is empty, returning NULL on the sample side. Which table expression supplies this left-preserving expansion?
- JSON_QUERY
- JSON_VALUE
- ISJSON with ARRAY type constraint
- OUTER APPLY OPENJSON(device.Samples)
Explanation
Correct Answer
D:OUTER APPLY preserves each device row when OPENJSON produces no samples, returning NULL on the expansion side; CROSS APPLY would omit that device.
Incorrect Answers
A:JSON_QUERY returns a JSON object or array fragment; it does not expand samples while preserving unmatched device rows.
B:JSON_VALUE retrieves a scalar property rather than a set of readings joined back to the device row.
C:ISJSON with an array type check validates shape; it does not yield one sample row per element or retain empty devices.
Question 417
A SQL Server 2025 log-processing job must extract one code matching a regex and split separately delimited log text into output rows. Which TWO functions should it combine? Choose TWO.
- REGEXP_SUBSTR
- CHARINDEX
- REGEXP_MATCHES
- REGEXP_SPLIT_TO_TABLE
- REGEXP_COUNT
Explanation
Correct Answers
A:REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; used in combination with REGEXP_SPLIT_TO_TABLE, it handles a separate requirement of this combined workflow.
D:REGEXP_SPLIT_TO_TABLE splits input text using a regular-expression delimiter and yields a tabular set of segments; used in combination with REGEXP_SUBSTR, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:CHARINDEX finds the starting position of a literal substring in a string, without regex pattern semantics; this differs from the REGEXP_SUBSTR and REGEXP_SPLIT_TO_TABLE requirement described in the scenario.
C:REGEXP_MATCHES returns matching occurrences as relational rows on supporting versions, unlike a single scalar extraction; the required capability here is REGEXP_SUBSTR and REGEXP_SPLIT_TO_TABLE, which this operation does not supply.
E:REGEXP_COUNT counts matches of a regular expression in a source string; the condition in the stem is resolved by REGEXP_SUBSTR and REGEXP_SPLIT_TO_TABLE, not by this feature alone.
Question 418
A workload repeatedly filters on document properties and needs a purpose-built access path for JSON predicates on a supporting database engine. Which feature would solve the stated problem?
- Native JSON data type
- JSON_VALUE expression
- JSON index
- OPENJSON with a WITH clause
Explanation
Correct Answer
C:A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes.
Incorrect Answers
A:The native json type is intended for JSON documents and supports JSON-oriented operations on platforms where it is available; support must be checked per engine; the missing behavior is JSON index; this mechanism does something else.
B:JSON_VALUE extracts a scalar at the requested JSON path, rather than returning an object or a table of properties; that function is not interchangeable with JSON index for the requested result.
D:OPENJSON shreds JSON arrays or objects into rows, while WITH can define the output columns and their SQL data types; it addresses a different step; the question calls for JSON index.
Question 419
Detail rows must reference a unique alternate code on the header table, not necessarily its declared primary key. Which database mechanism should be chosen?
- Composite natural key
- Foreign key to a candidate key
- Filtered unique index
- Surrogate integer key
Explanation
Correct Answer
B:A foreign key may reference an eligible UNIQUE candidate key, preserving referential integrity without depending on the primary key specifically.
Incorrect Answers
A:A composite key enforces uniqueness over a column combination, matching a many-to-many association's natural identity; the required capability here is Foreign key to a candidate key, which this operation does not supply.
C:A filtered unique index enforces uniqueness within a predicate-defined subset, unlike a table-wide unique constraint; the condition in the stem is resolved by Foreign key to a candidate key, not by this feature alone.
D:A surrogate key decouples row identity from mutable business data and can support compact joins and stable references; the missing behavior is Foreign key to a candidate key; this mechanism does something else.
Question 420
A simple read/write API needs an Orders resource mapped directly onto a supported SQL database table. Which option satisfies the desired behavior most directly?
- Table-backed entity
- Stored-procedure entity
- View-backed entity
- Explicit relationship configuration
Explanation
Correct Answer
A:A DAB entity can map to a table source with configured permissions for REST or GraphQL operations.
Incorrect Answers
B:DAB can expose supported stored procedures as API entities with operation-specific configuration and permissions; it addresses a different step; the question calls for Table-backed entity.
C:An entity can map to a supported SQL view, with write capabilities subject to view and DAB limitations; this differs from the Table-backed entity requirement described in the scenario.
D:Configured relationships describe how exposed entities connect and control relational navigation in generated GraphQL schemas; the required capability here is Table-backed entity, which this operation does not supply.