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

Question 310

An analyst needs identifiers present in both a billing query and a support-ticket query without manually joining their tables. What is the right solution for this specific requirement?

  1. UNION ALL
  2. UNION
  3. INTERSECT
  4. EXCEPT

Explanation

Correct Answer

C:

INTERSECT returns distinct compatible rows appearing in both input result sets.

Incorrect Answers

A:

UNION ALL concatenates the result sets without removing duplicate rows, unlike UNION's distinct behavior; this differs from the INTERSECT requirement described in the scenario.

B:

UNION performs set combination with duplicate elimination across compatible query projections; the required capability here is INTERSECT, which this operation does not supply.

D:

EXCEPT returns distinct rows from the first input that are not found in the second input; the condition in the stem is resolved by INTERSECT, not by this feature alone.

Question 311

A customer API stores JSON. A dashboard needs one nested scalar postal code while an ETL step must turn a JSON item array into typed rows. Which TWO SQL techniques serve the distinct tasks? Choose TWO.

  1. JSON_VALUE expression
  2. JSON index
  3. Native JSON data type
  4. Aligned partitioned index
  5. OPENJSON with a WITH clause

Explanation

Correct Answers

A:

JSON_VALUE extracts a scalar at the requested JSON path, rather than returning an object or a table of properties; used in combination with OPENJSON with a WITH clause, it handles a separate requirement of this combined workflow.

E:

OPENJSON shreds JSON arrays or objects into rows, while WITH can define the output columns and their SQL data types; used in combination with JSON_VALUE expression, it handles a separate requirement of this combined workflow.

Incorrect Answers

B:

A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; for the stated conditions, the deciding feature is JSON_VALUE expression and OPENJSON with a WITH clause rather than this behavior.

C:

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; it addresses a different step; the question calls for JSON_VALUE expression and OPENJSON with a WITH clause.

D:

Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries; this differs from the JSON_VALUE expression and OPENJSON with a WITH clause requirement described in the scenario.

Question 312

Each InvoiceLine must reference an existing Invoice, and a line with a nonexistent parent identifier must be rejected. Which choice best meets these constraints?

  1. FOREIGN KEY constraint
  2. DECIMAL(19,4)
  3. CHECK constraint
  4. DEFAULT constraint
  5. PRIMARY KEY constraint

Explanation

Correct Answer

A:

A foreign key checks referential integrity between a referencing column and a candidate key in another or the same table.

Incorrect Answers

B:

Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale; the condition in the stem is resolved by FOREIGN KEY constraint, not by this feature alone.

C:

A CHECK constraint can compare columns from a row in one Boolean condition and rejects non-NULL expressions evaluated as FALSE, regardless of the inserting application; the missing behavior is FOREIGN KEY constraint; this mechanism does something else.

D:

A DEFAULT expression supplies a value for a column when INSERT omits it or uses DEFAULT; it does not automatically override explicit NULL or an explicitly supplied value; that function is not interchangeable with FOREIGN KEY constraint for the requested result.

E:

A primary key declares the table's principal identifier and enforces uniqueness plus nonnullability through an associated index; for the stated conditions, the deciding feature is FOREIGN KEY constraint rather than this behavior.

Question 313

An analyst can read most fields of a Customer table but must not select a specific sensitive TaxIdentifier column. Which mechanism addresses the requirement?

  1. Masked response
  2. Encrypted sensitive column
  3. Column-level permissions
  4. RLS tenant filtering

Explanation

Correct Answer

C:

Column-level GRANT or DENY policies can scope SELECT privileges to designated columns where supported.

Incorrect Answers

A:

Dynamic Data Masking changes result visibility according to effective permissions while leaving encrypted-at-rest requirements separate; it addresses a different step; the question calls for Column-level permissions.

B:

Always Encrypted places decryption with approved clients and key stores rather than relying only on engine-side masking; this differs from the Column-level permissions requirement described in the scenario.

D:

A database RLS policy supplies the filtering boundary across application query paths using supported security predicates; the alternative cannot substitute for Column-level permissions under the given constraints.

Question 314

A security team wants Azure SQL diagnostic logs forwarded to a selected Log Analytics destination for retention and search. What is the most appropriate design decision?

  1. Alert on suspicious pattern
  2. Diagnostic setting export
  3. Review stored audit events
  4. Audit specification

Explanation

Correct Answer

B:

Azure resource diagnostic settings route supported log and metric categories to configured destinations and require appropriate permissions.

Incorrect Answers

A:

A metric or log alert rule evaluates collected telemetry and can initiate actions when a defined condition is met; the missing behavior is Diagnostic setting export; this mechanism does something else.

C:

Examining retained audit records supports forensic reconstruction; it does not itself prevent future unauthorized requests; for the stated conditions, the deciding feature is Diagnostic setting export rather than this behavior.

D:

SQL audit specifications define audited action groups or object actions to be captured by the audit mechanism; it addresses a different step; the question calls for Diagnostic setting export.

Question 315

A nested batch wants a named rollback point for part of its work while preserving the possibility of retaining earlier work if the transaction remains committable. Which feature would solve the stated problem?

  1. BEGIN TRY / BEGIN CATCH
  2. SAVE TRANSACTION
  3. XACT_STATE()
  4. THROW without arguments

Explanation

Correct Answer

B:

SAVE TRANSACTION establishes a savepoint inside an active transaction, allowing partial rollback when the transaction's state permits it.

Incorrect Answers

A:

TRY...CATCH provides a structured handler for many run-time T-SQL errors, allowing recovery code to run in the CATCH branch; the required capability here is SAVE TRANSACTION, which this operation does not supply.

C:

XACT_STATE() returns -1 for an uncommittable active transaction, permitting only rollback and read operations until that transaction is ended; the condition in the stem is resolved by SAVE TRANSACTION, not by this feature alone.

D:

THROW without arguments inside CATCH rethrows the caught exception, preserving its original error details rather than inventing a replacement exception; the missing behavior is SAVE TRANSACTION; this mechanism does something else.

Question 316

A data-changing routine should automatically terminate and roll back its transaction for many runtime statement errors rather than continuing. Which option satisfies the desired behavior most directly?

  1. SET XACT_ABORT ON
  2. TRY...CATCH
  3. XACT_STATE()
  4. THROW

Explanation

Correct Answer

A:

XACT_ABORT ON makes specified runtime failures abort and roll back a transaction, affecting the error-handling behavior of the batch.

Incorrect Answers

B:

TRY...CATCH structures error handling into protected and handler blocks for catchable execution errors; it addresses a different step; the question calls for SET XACT_ABORT ON.

C:

XACT_STATE reports transaction state, distinguishing a doomed transaction from one that can still commit; this differs from the SET XACT_ABORT ON requirement described in the scenario.

D:

THROW explicitly raises or rethrows T-SQL errors and supports propagating failures through calling code; the required capability here is SET XACT_ABORT ON, which this operation does not supply.

Question 317

A financial application must make tampering with historical database changes cryptographically evident to later reviewers. What is the best primary mechanism to use?

  1. System-versioned temporal table
  2. JSON index
  3. Updatable ledger table
  4. External table
  5. Graph node and edge tables

Explanation

Correct Answer

C:

An updatable ledger table retains audit history with digest-based verification of data integrity rather than merely exposing prior row versions.

Incorrect Answers

A:

Temporal tables maintain current and history rows with system-maintained validity periods, allowing FOR SYSTEM_TIME point-in-time queries; the condition in the stem is resolved by Updatable ledger table, not by this feature alone.

B:

A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; the missing behavior is Updatable ledger table; this mechanism does something else.

D:

An external table supplies database metadata for accessing external data; it does not turn the remote data into ordinary local stored rows; for the stated conditions, the deciding feature is Updatable ledger table rather than this behavior.

E:

SQL graph node and edge tables represent entities and relationships, enabling MATCH-based queries over connected records; it addresses a different step; the question calls for Updatable ledger table.

Question 318

A nightly UPDATE modifies the whole history table in one transaction, causing broad blocking; it can be divided into safe smaller batches. Which mechanism addresses the requirement?

  1. Consistent lock ordering
  2. Reduce lock footprint
  3. Find open transaction owner
  4. Enable appropriate row versioning

Explanation

Correct Answer

B:

Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness.

Incorrect Answers

A:

A shared acquisition order reduces circular-wait opportunities across concurrent operations; this differs from the Reduce lock footprint requirement described in the scenario.

C:

Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation; the alternative cannot substitute for Reduce lock footprint under the given constraints.

D:

READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking; the condition in the stem is resolved by Reduce lock footprint, not by this feature alone.

Question 319

Multiple applications need a reusable named SELECT projection that always reads the current underlying tables without independently copying the data. What is the right solution for this specific requirement?

  1. Scalar user-defined function
  2. View
  3. Table-valued function
  4. Stored procedure

Explanation

Correct Answer

B:

A regular view encapsulates a SELECT definition as a database object and generally resolves its rows from underlying tables when queried.

Incorrect Answers

A:

A scalar function produces a single value and can be invoked in supported expressions, with performance considerations depending on its implementation; that function is not interchangeable with View for the requested result.

C:

A table-valued function returns a relational result, and an inline version can expose an optimizer-friendly SELECT expression; it addresses a different step; the question calls for View.

D:

A stored procedure can encapsulate multi-statement DML and transactions, accept input parameters, and report status or rowsets to application callers; this differs from the View requirement described in the scenario.

Question 320

A supported modern T-SQL pipeline must reject strings that fail a regular-expression pattern and also normalize recurring whitespace through regex replacement. Which TWO functions are needed? Choose TWO.

  1. REGEXP_COUNT
  2. REGEXP_LIKE
  3. REGEXP_REPLACE
  4. REGEXP_SUBSTR
  5. REGEXP_INSTR

Explanation

Correct Answers

B:

REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; used in combination with REGEXP_REPLACE, it handles a separate requirement of this combined workflow.

C:

REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms; used in combination with REGEXP_LIKE, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

REGEXP_COUNT counts matches of a regular expression in a source string; the alternative cannot substitute for REGEXP_LIKE and REGEXP_REPLACE under the given constraints.

D:

REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; that function is not interchangeable with REGEXP_LIKE and REGEXP_REPLACE for the requested result.

E:

REGEXP_INSTR returns a match position for a supplied regular-expression pattern; for the stated conditions, the deciding feature is REGEXP_LIKE and REGEXP_REPLACE rather than this behavior.

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