Microsoft DP-800 (Developing AI-Enabled Database Solutions) Practice Test 41

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?

  1. Indexed view
  2. Filtered index for sparse predicate
  3. Clustered columnstore index for aggregations
  4. 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?

  1. Column encryption key
  2. Always Encrypted-enabled driver
  3. Column master key
  4. Secure enclave
  5. 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?

  1. Latency and throughput load test
  2. Larger embedding model
  3. Smaller embedding model
  4. 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?

  1. Environment-specific publish profile
  2. Source object definitions
  3. Immutable release artifacts
  4. 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?

  1. Azure Functions SQL input binding
  2. Azure Logic Apps SQL connector
  3. Azure Functions SQL output binding
  4. 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?

  1. Randomized Always Encrypted
  2. Always Encrypted with secure enclaves
  3. Deterministic Always Encrypted
  4. 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?

  1. View-backed entity
  2. Stored-procedure entity
  3. Table-backed entity
  4. 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?

  1. DAB in-memory response cache
  2. DAB Redis cache
  3. `$first` page-size control
  4. 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?

  1. JSON_ARRAYAGG
  2. CROSS APPLY OPENJSON
  3. JSON_OBJECT
  4. 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?

  1. Exact serial-number retrieval
  2. Paraphrase retrieval
  3. Boolean regulatory filter
  4. 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?

  1. Parameterized query
  2. TOP with ORDER BY
  3. SARGable date range
  4. EXISTS semi-join
  5. 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.

Developing AI-Enabled Database Solutions (DP-800) Practice Tests