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

Question 200

An operation performs a transaction, writes several tables, and returns informational rows without needing to appear in a FROM clause. Which approach should the team select?

  1. Scalar function
  2. Stored procedure
  3. Multi-statement table-valued function
  4. Inline table-valued function

Explanation

Correct Answer

B:

A stored procedure is an executable unit suited to procedural database operations; it is not directly a relational table expression.

Incorrect Answers

A:

A scalar UDF returns a single scalar value, although calling patterns and implementation determine its performance; the alternative cannot substitute for Stored procedure under the given constraints.

C:

A multi-statement TVF declares a table return variable and populates it with multiple statements, subject to different optimizer behavior than inline TVFs; the missing behavior is Stored procedure; this mechanism does something else.

D:

An inline TVF returns the result of a single SELECT definition and is commonly optimized similarly to a parameterized view; that function is not interchangeable with Stored procedure for the requested result.

Question 201

Release automation has a built DACPAC and needs to reconcile the target database's schema with that model. Which database mechanism should be chosen?

  1. `dotnet build` of SQL project
  2. DACPAC
  3. SqlPackage Publish
  4. SDK-style SQL database project
  5. BACPAC

Explanation

Correct Answer

C:

SqlPackage Publish compares the source package with the destination and executes a generated deployment plan when authorized.

Incorrect Answers

A:

Building an SDK-style SQL project validates its schema model and emits a DACPAC when the build succeeds; it addresses a different step; the question calls for SqlPackage Publish.

B:

A DACPAC packages the database schema model for DacFx comparison and deployment; it is not primarily a full data export; this differs from the SqlPackage Publish requirement described in the scenario.

D:

An SDK-style SQL project provides declarative schema files and cross-platform build tooling supported by Microsoft.Build.Sql; the alternative cannot substitute for SqlPackage Publish under the given constraints.

E:

A BACPAC is a logical export containing schema and data, unlike a schema-only DACPAC; the condition in the stem is resolved by SqlPackage Publish, not by this feature alone.

Question 202

A privacy deletion removes a source document and the search corpus must stop returning its old vector representation. What is the most suitable implementation?

  1. Update-on-change processing
  2. Delete propagation
  3. Idempotent embedding upsert
  4. Versioned embedding column

Explanation

Correct Answer

B:

A deletion-aware sync process removes or invalidates vectors and searchable text tied to the erased source record.

Incorrect Answers

A:

Change-driven maintenance processes affected rows and preserves a checkpoint for incremental vector refresh; the missing behavior is Delete propagation; this mechanism does something else.

C:

Upsert by a stable document/chunk/version key allows retries without duplicating records, supporting at-least-once processing; for the stated conditions, the deciding feature is Delete propagation rather than this behavior.

D:

Storing embedding-model identity or version with each vector permits controlled migration and query-to-corpus compatibility checks; it addresses a different step; the question calls for Delete propagation.

Question 203

A result set must show each invoice line and, without collapsing rows, the total amount for that invoice. What is the right solution for this specific requirement?

  1. FIRST_VALUE()
  2. LEAD()
  3. PARTITION BY CustomerId
  4. LAG()
  5. SUM() OVER(PARTITION BY ...)

Explanation

Correct Answer

E:

A windowed SUM computes totals across partitions while retaining individual input rows in the output.

Incorrect Answers

A:

FIRST_VALUE returns the expression from the first row of the applicable ordered window frame; the required capability here is SUM() OVER(PARTITION BY ...), which this operation does not supply.

B:

LEAD reads a following row's value in the specified window partition and order; the alternative cannot substitute for SUM() OVER(PARTITION BY ...) under the given constraints.

C:

PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the condition in the stem is resolved by SUM() OVER(PARTITION BY ...), not by this feature alone.

D:

LAG accesses a prior row's expression value within the ordered window without a self-join; the missing behavior is SUM() OVER(PARTITION BY ...); this mechanism does something else.

Question 204

A query's duration increased and the team wants historical wait-category evidence associated with the stored query. What should be configured to meet this objective?

  1. Query Performance Insight
  2. Query Store wait statistics
  3. Query Store runtime statistics
  4. Force known good query plan

Explanation

Correct Answer

B:

Query Store wait statistics can attribute supported wait categories to recorded queries, clarifying the performance bottleneck.

Incorrect Answers

A:

Query Performance Insight surfaces query performance trends in Azure SQL to help locate high-impact workloads without a separate profiler trace; for the stated conditions, the deciding feature is Query Store wait statistics rather than this behavior.

C:

Query Store runtime aggregation preserves historical performance measures across collection windows; this differs from the Query Store wait statistics requirement described in the scenario.

D:

Query Store plan forcing can direct SQL Server toward a previously captured plan while conditions still permit its use; the required capability here is Query Store wait statistics, which this operation does not supply.

Question 205

A quarterly report should show the earliest observed price alongside every product's price in that same quarter. Which SQL capability is the most direct fit?

  1. FIRST_VALUE()
  2. PARTITION BY CustomerId
  3. LAG()
  4. LEAD()
  5. SUM() OVER(PARTITION BY ...)

Explanation

Correct Answer

A:

FIRST_VALUE returns the expression from the first row of the applicable ordered window frame.

Incorrect Answers

B:

PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the missing behavior is FIRST_VALUE(); this mechanism does something else.

C:

LAG accesses a prior row's expression value within the ordered window without a self-join; that function is not interchangeable with FIRST_VALUE() for the requested result.

D:

LEAD reads a following row's value in the specified window partition and order; for the stated conditions, the deciding feature is FIRST_VALUE() rather than this behavior.

E:

A windowed SUM computes totals across partitions while retaining individual input rows in the output; it addresses a different step; the question calls for FIRST_VALUE().

Question 206

A new database feature requires a known status-code row, and the pipeline must verify the row exists after deployment. Which option would you recommend?

  1. Post-deployment seed script
  2. Idempotent MERGE or upsert logic
  3. Version-controlled reference CSV
  4. Integration test for referential values

Explanation

Correct Answer

D:

A targeted integration or smoke test validates that required reference records are present and usable by related constraints.

Incorrect Answers

A:

Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow; this differs from the Integration test for referential values requirement described in the scenario.

B:

An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases; the required capability here is Integration test for referential values, which this operation does not supply.

C:

Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; the alternative cannot substitute for Integration test for referential values under the given constraints.

Question 207

A schema migration may involve potentially destructive transformations and the team needs a recoverable database state before approval. What is the best primary mechanism to use?

  1. Expand-and-contract migration
  2. Explicit destructive-change review
  3. Backward-compatible additive change
  4. Pre-deployment backup

Explanation

Correct Answer

D:

An appropriate backup or recovery strategy protects recoverability when deployment scripts cannot simply reverse data loss.

Incorrect Answers

A:

Expand-and-contract introduces new schema, migrates users and data, then removes old objects only after dependents move; that function is not interchangeable with Pre-deployment backup for the requested result.

B:

A destructive-change gate reviews planned drops and data transformations rather than assuming model synchronization is risk-free; for the stated conditions, the deciding feature is Pre-deployment backup rather than this behavior.

C:

An additive compatible migration can allow old and new application versions to coexist during staged cutover; it addresses a different step; the question calls for Pre-deployment backup.

Question 208

A league table must keep equal scores tied but number the following distinct score with no skipped positions. What should the developer implement?

  1. RANK()
  2. NTILE(4)
  3. DENSE_RANK()
  4. ROW_NUMBER()

Explanation

Correct Answer

C:

DENSE_RANK numbers distinct ordered values without gaps after ties within the partition.

Incorrect Answers

A:

RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows; the alternative cannot substitute for DENSE_RANK() under the given constraints.

B:

NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row; the condition in the stem is resolved by DENSE_RANK(), not by this feature alone.

D:

ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules; that function is not interchangeable with DENSE_RANK() for the requested result.

Question 209

A regulated table must capture old values during UPDATE and DELETE without application-written change logs. What is the most appropriate design decision?

  1. Graph edge records
  2. System-versioned history retention
  3. UNIQUE alternate identifier
  4. FOREIGN KEY validation
  5. Ledger digests
  6. Memory-optimized OLTP row

Explanation

Correct Answer

B:

System-versioned temporal storage records prior row versions in a history table while maintaining a current table.

Incorrect Answers

A:

Edge tables encode graph relationships between node instances and support relationship-centric queries; it addresses a different step; the question calls for System-versioned history retention.

C:

A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier; the required capability here is System-versioned history retention, which this operation does not supply.

D:

Foreign-key constraints protect relational references independently of the application that writes data; the alternative cannot substitute for System-versioned history retention under the given constraints.

E:

Ledger digests help establish tamper-evidence for ledger content; retaining them securely is key to later verification; the condition in the stem is resolved by System-versioned history retention, not by this feature alone.

F:

Memory-optimized tables use a specialized engine and must follow feature and schema restrictions rather than conventional disk-based indexing; the missing behavior is System-versioned history retention; this mechanism does something else.

Question 210

A supported solution needs permitted comparisons or transformations that ordinary client-side encrypted columns cannot handle efficiently. What is the best primary mechanism to use?

  1. Always Encrypted with secure enclaves
  2. Randomized Always Encrypted
  3. TDE key hierarchy
  4. Deterministic Always Encrypted

Explanation

Correct Answer

A:

Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites.

Incorrect Answers

B:

Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support; that function is not interchangeable with Always Encrypted with secure enclaves for the requested result.

C:

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 Always Encrypted with secure enclaves rather than this behavior.

D:

Deterministic column encryption yields the same ciphertext for equal plaintext with the same key and supports restricted equality comparisons; it addresses a different step; the question calls for Always Encrypted with secure enclaves.

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