Question 400
A reporting system reads large ranges of amount columns to compute group totals and seeks column compression and batch processing. Which action is justified by these conditions?
- Indexed view
- Filtered index for sparse predicate
- Clustered columnstore index for aggregations
- Nonclustered index on join key
Explanation
Correct Answer
C:Columnstore storage is optimized for large analytical scans and compression of similar column values.
Incorrect Answers
A:An eligible indexed view stores indexed relational results that the SQL engine maintains as qualifying base rows change, at the cost of additional write and index maintenance; the alternative cannot substitute for Clustered columnstore index for aggregations under the given constraints.
B:A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; the condition in the stem is resolved by Clustered columnstore index for aggregations, not by this feature alone.
D:An index with HeaderId as a leading key can accelerate lookups and joins from headers to matching details; that function is not interchangeable with Clustered columnstore index for aggregations for the requested result.
Question 401
An application receives ciphertext from selected columns and needs authorized automatic client-side decryption when fetching rows. Which mechanism addresses the requirement?
- Column encryption key
- Always Encrypted-enabled driver
- Column master key
- Secure enclave
- Transparent Data Encryption
Explanation
Correct Answer
B:A compatible driver with column encryption enabled can perform encryption and decryption using approved key access outside the database engine.
Incorrect Answers
A:A column encryption key encrypts Always Encrypted column data, while its encrypted form is stored as database metadata; it addresses a different step; the question calls for Always Encrypted-enabled driver.
C:A column master key describes the key-encryption root used to protect column encryption keys; the underlying key should remain outside routine database access; the required capability here is Always Encrypted-enabled driver, which this operation does not supply.
D:A secure enclave can support approved operations on protected data while isolating cryptographic processing from the rest of the database environment; the alternative cannot substitute for Always Encrypted-enabled driver under the given constraints.
E:Transparent Data Encryption encrypts database storage at rest and decrypts pages for authorized engine operations; it does not hide plaintext from the engine; the condition in the stem is resolved by Always Encrypted-enabled driver, not by this feature alone.
Question 402
A complex scientific corpus requires improved semantic discrimination and quality tests justify higher model cost and vector storage. Which action is justified by these conditions?
- Latency and throughput load test
- Larger embedding model
- Smaller embedding model
- Evaluation on representative queries
Explanation
Correct Answer
B:A larger-capacity model may improve representation on difficult content, but benefits must be verified against the target dataset.
Incorrect Answers
A:Load testing reveals throughput, rate-limit, and response-time behavior under the deployment's expected concurrency; the missing behavior is Larger embedding model; this mechanism does something else.
C:A smaller embedding model can reduce inference cost and latency, provided measured retrieval quality remains acceptable; for the stated conditions, the deciding feature is Larger embedding model rather than this behavior.
D:A labeled evaluation set measures real retrieval quality, preventing selection based solely on generic benchmark or model name; it addresses a different step; the question calls for Larger embedding model.
Question 403
Staging and production share a schema model but require distinct connection targets and safe deployment options. What should the developer implement?
- Environment-specific publish profile
- Source object definitions
- Immutable release artifacts
- Git tags for releases
Explanation
Correct Answer
A:Environment configuration and publish profiles keep target-specific settings separate from the source schema definition.
Incorrect Answers
B:Declarative .sql object files in a SQL project provide a maintainable source of truth for modeled database objects; the alternative cannot substitute for Environment-specific publish profile under the given constraints.
C:Artifact promotion preserves the reviewed DACPAC across environments, avoiding accidental rebuilds from later commits; the condition in the stem is resolved by Environment-specific publish profile, not by this feature alone.
D:Tags can mark repository states for releases, supporting traceability between commits, built artifacts, and deployments; the missing behavior is Environment-specific publish profile; this mechanism does something else.
Question 404
A downstream warehouse must ingest detailed historical source row changes for replay and reconciliation rather than only current row IDs. What is the right solution for this specific requirement?
- Azure Functions SQL input binding
- Azure Logic Apps SQL connector
- Azure Functions SQL output binding
- CDC-based ingestion service
Explanation
Correct Answer
D:A CDC consumer reads durable change captures and can reconstruct supported insert/update/delete change activity.
Incorrect Answers
A:A SQL input binding can supply configured database query results to a supported Azure Function invocation; for the stated conditions, the deciding feature is CDC-based ingestion service rather than this behavior.
B:Logic Apps offers managed connectors and workflow orchestration for database-triggered or database-driven business processes; it addresses a different step; the question calls for CDC-based ingestion service.
C:A SQL output binding can persist supported output values through configured SQL connectivity and binding semantics; this differs from the CDC-based ingestion service requirement described in the scenario.
Question 405
An approved equality lookup must compare encrypted customer identifiers without exposing plaintext to the server, accepting equality-pattern leakage. Which technique applies to this scenario?
- Randomized Always Encrypted
- Always Encrypted with secure enclaves
- Deterministic Always Encrypted
- TDE key hierarchy
Explanation
Correct Answer
C:Deterministic column encryption yields the same ciphertext for equal plaintext with the same key and supports restricted equality comparisons.
Incorrect Answers
A:Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support; the condition in the stem is resolved by Deterministic Always Encrypted, not by this feature alone.
B:Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites; the missing behavior is Deterministic Always Encrypted; this mechanism does something else.
D:TDE protects the database encryption key through configured key hierarchy, so key backup and availability matter for restored data; for the stated conditions, the deciding feature is Deterministic Always Encrypted rather than this behavior.
Question 406
A reporting API should expose a database-defined join and column projection without giving consumers direct access to all underlying tables. What is the right solution for this specific requirement?
- View-backed entity
- Stored-procedure entity
- Table-backed entity
- Explicit relationship configuration
Explanation
Correct Answer
A:An entity can map to a supported SQL view, with write capabilities subject to view and DAB limitations.
Incorrect Answers
B:DAB can expose supported stored procedures as API entities with operation-specific configuration and permissions; the required capability here is View-backed entity, which this operation does not supply.
C:A DAB entity can map to a table source with configured permissions for REST or GraphQL operations; the alternative cannot substitute for View-backed entity under the given constraints.
D:Configured relationships describe how exposed entities connect and control relational navigation in generated GraphQL schemas; the condition in the stem is resolved by View-backed entity, not by this feature alone.
Question 407
Several API replicas need to share cache state rather than keep unrelated per-process response caches. Which option satisfies the desired behavior most directly?
- DAB in-memory response cache
- DAB Redis cache
- `$first` page-size control
- GraphQL pagination `after`
Explanation
Correct Answer
B:A supported distributed Redis cache stores response entries outside individual runtime processes for multi-instance reuse.
Incorrect Answers
A:DAB response caching can retain eligible read results for configured durations, subject to invalidation and consistency requirements; that function is not interchangeable with DAB Redis cache for the requested result.
C:The first parameter requests the page size; it does not itself identify where the next page begins; it addresses a different step; the question calls for DAB Redis cache.
D:A GraphQL after cursor continues pagination beyond a prior page, subject to the configured query shape; this differs from the DAB Redis cache requirement described in the scenario.
Question 408
A grouped SQL query should return one row per Category with all matching product names combined into a JSON array. Which mechanism addresses the requirement?
- JSON_ARRAYAGG
- CROSS APPLY OPENJSON
- JSON_OBJECT
- FOR JSON PATH
Explanation
Correct Answer
A:JSON_ARRAYAGG constructs an aggregate JSON array from group members rather than returning multiple relational rows.
Incorrect Answers
B:CROSS APPLY with OPENJSON produces relational item rows correlated to their source order records; the condition in the stem is resolved by JSON_ARRAYAGG, not by this feature alone.
C:JSON_OBJECT creates a named-property JSON structure from scalar values and other JSON expressions; the missing behavior is JSON_ARRAYAGG; this mechanism does something else.
D:FOR JSON PATH converts SELECT output into a path-controlled JSON representation, often suitable for API responses; that function is not interchangeable with JSON_ARRAYAGG for the requested result.
Question 409
A user asks 'Why does the fan never stop?' and relevant documents say 'continuous cooling motor operation' without matching keywords. Which feature would solve the stated problem?
- Exact serial-number retrieval
- Paraphrase retrieval
- Boolean regulatory filter
- Combined identifier and symptom retrieval
Explanation
Correct Answer
B:Semantic embedding retrieval handles meaning-based paraphrases that lexical token matching can miss.
Incorrect Answers
A:Full-text or exact structured predicates can preserve the literal identifier, unlike semantic vectors that may cluster unrelated codes; it addresses a different step; the question calls for Paraphrase retrieval.
C:A structured metadata predicate enforces eligibility; neither full-text relevance nor vector closeness is a security filter by itself; the required capability here is Paraphrase retrieval, which this operation does not supply.
D:Hybrid search uses exact-term and semantic candidate lists so both precise identifiers and related descriptions influence ranking; the alternative cannot substitute for Paraphrase retrieval under the given constraints.
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.