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

Question 580

An organization wants a model to consistently follow a specialized response style across many prompts, not primarily obtain new factual records. Which SQL capability is the most direct fit?

  1. SQL structured lookup
  2. Full-text document retrieval
  3. Fine-tuning
  4. Retrieval-augmented generation

Explanation

Correct Answer

C:

Fine-tuning adjusts model behavior through training on examples; it is not the usual replacement for retrieving frequently changing knowledge.

Incorrect Answers

A:

A parameterized structured SQL lookup provides an exact current record without relying on probabilistic text generation; for the stated conditions, the deciding feature is Fine-tuning rather than this behavior.

B:

Full-text search can locate matching passages directly; generation adds complexity when the task is simply retrieval; it addresses a different step; the question calls for Fine-tuning.

D:

RAG retrieves relevant current documents and provides them as context to the generator, keeping knowledge retrieval distinct from model weights; the required capability here is Fine-tuning, which this operation does not supply.

Question 581

A query became slower after a release, and investigators need to compare its historically chosen plans across time. What should the developer implement?

  1. Estimated execution plan
  2. Query Store runtime statistics
  3. Actual execution plan
  4. Live query statistics
  5. Query Store plan history
  6. Query Store wait statistics

Explanation

Correct Answer

E:

Query Store preserves eligible query texts, plans, and aggregated runtime statistics so plan changes can be correlated with regressions.

Incorrect Answers

A:

An estimated plan shows the optimizer's anticipated strategy without running the full statement and therefore lacks actual runtime counts; the condition in the stem is resolved by Query Store plan history, not by this feature alone.

B:

Query Store runtime aggregation preserves historical performance measures across collection windows; the missing behavior is Query Store plan history; this mechanism does something else.

C:

An actual plan records the executed query operators and runtime row counts, enabling comparison against estimates; that function is not interchangeable with Query Store plan history for the requested result.

D:

Live Query Statistics can expose progress and operator activity for running supported queries, assisting diagnosis of ongoing execution; for the stated conditions, the deciding feature is Query Store plan history rather than this behavior.

F:

Query Store wait statistics can attribute supported wait categories to recorded queries, clarifying the performance bottleneck; this differs from the Query Store plan history requirement described in the scenario.

Question 582

Callers routinely omit the creation timestamp and want SQL Server to set it at insertion rather than sending it from every application. Which action is justified by these conditions?

  1. CHECK (Quantity > 0)
  2. FOREIGN KEY validation
  3. UNIQUE alternate identifier
  4. DEFAULT for CreatedAt

Explanation

Correct Answer

D:

A DEFAULT expression can supply an insertion-time value when the column is not explicitly populated.

Incorrect Answers

A:

A CHECK predicate evaluates the supplied value at write time and rejects rows that violate its Boolean condition; this differs from the DEFAULT for CreatedAt requirement described in the scenario.

B:

Foreign-key constraints protect relational references independently of the application that writes data; the required capability here is DEFAULT for CreatedAt, which this operation does not supply.

C:

A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier; the alternative cannot substitute for DEFAULT for CreatedAt under the given constraints.

Question 583

A log entry may contain several matching token strings and the query should expose the regex matches as a rowset. What should be configured to meet this objective?

  1. REGEXP_INSTR
  2. REGEXP_MATCHES
  3. REGEXP_SPLIT_TO_TABLE
  4. CHARINDEX
  5. REGEXP_SUBSTR

Explanation

Correct Answer

B:

REGEXP_MATCHES returns matching occurrences as relational rows on supporting versions, unlike a single scalar extraction.

Incorrect Answers

A:

REGEXP_INSTR returns a match position for a supplied regular-expression pattern; that function is not interchangeable with REGEXP_MATCHES for the requested result.

C:

REGEXP_SPLIT_TO_TABLE splits input text using a regular-expression delimiter and yields a tabular set of segments; it addresses a different step; the question calls for REGEXP_MATCHES.

D:

CHARINDEX finds the starting position of a literal substring in a string, without regex pattern semantics; this differs from the REGEXP_MATCHES requirement described in the scenario.

E:

REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; the required capability here is REGEXP_MATCHES, which this operation does not supply.

Question 584

An API serves multiple customers and must enforce row ownership even if an endpoint role permits SELECT on the shared table. Which action is justified by these conditions?

  1. Entra JWT authentication
  2. TLS ingress
  3. Database Row-Level Security
  4. DAB authorization roles

Explanation

Correct Answer

C:

SQL Row-Level Security applies within the database and complements, rather than replaces, API operation permissions.

Incorrect Answers

A:

An Entra-compatible authentication provider validates signed tokens for a configured audience and issuer before authorization; the alternative cannot substitute for Database Row-Level Security under the given constraints.

B:

HTTPS/TLS protects requests and responses in transit while leaving entity permissions and SQL grants to separate controls; the condition in the stem is resolved by Database Row-Level Security, not by this feature alone.

D:

DAB entity role permissions limit which API operations authenticated roles can invoke; that function is not interchangeable with Database Row-Level Security for the requested result.

Question 585

A payroll system must keep sensitive column plaintext inaccessible to the database engine during ordinary storage and query processing. What should be configured to meet this objective?

  1. Column encryption key
  2. Transparent Data Encryption
  3. Dynamic Data Masking
  4. Always Encrypted
  5. Row-Level Security

Explanation

Correct Answer

D:

Always Encrypted protects selected column values with client-side cryptography and keys outside ordinary server access, subject to query-operation limitations.

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.

B:

Transparent Data Encryption encrypts database storage at rest and decrypts pages for authorized engine operations; it does not hide plaintext from the engine; this differs from the Always Encrypted requirement described in the scenario.

C:

Dynamic Data Masking alters query-result presentation for non-exempt users; it is not encryption or a strong isolation boundary for untrusted querying; the required capability here is Always Encrypted, which this operation does not supply.

E:

Row-Level Security applies predicate logic inside the database to filter or block access to rows according to security context; the condition in the stem is resolved by Always Encrypted, not by this feature alone.

Question 586

Developers accidentally pasted a connection string into a repository; the team needs automated detection of exposed secrets in changes. What is the right solution for this specific requirement?

  1. Human approval before tool execution
  2. Redaction of sensitive prompt context
  3. Secret scanning in GitHub
  4. Read-only database principal

Explanation

Correct Answer

C:

Secret scanning can identify supported credential patterns in repositories and complements, rather than replaces, credential rotation.

Incorrect Answers

A:

Requiring human confirmation for sensitive tool calls reduces the risk of autonomous changes and maintains an accountable approval point; the missing behavior is Secret scanning in GitHub; this mechanism does something else.

B:

Removing or masking confidential fields before prompt submission minimizes unnecessary exposure through an AI-assisted workflow; that function is not interchangeable with Secret scanning in GitHub for the requested result.

D:

A read-only database principal with narrowly scoped SELECT access limits potential writes at the database authorization boundary; it addresses a different step; the question calls for Secret scanning in GitHub.

Question 587

An unreliable proxy occasionally returns a plain HTML error page where JSON is expected; the procedure must detect malformed payloads before parsing. Which mechanism addresses the requirement?

  1. JSON_VALUE
  2. DATABASE SCOPED CREDENTIAL
  3. TRY_CONVERT
  4. OPENJSON
  5. ISJSON

Explanation

Correct Answer

E:

ISJSON validates JSON text structure so a parser does not assume every HTTPS body is well-formed JSON.

Incorrect Answers

A:

JSON_VALUE retrieves scalar JSON path values from the returned document for SQL processing; the required capability here is ISJSON, which this operation does not supply.

B:

Database-scoped credentials support controlled outbound endpoint authentication, subject to URL, identity, and access rules; the alternative cannot substitute for ISJSON under the given constraints.

C:

TRY_CONVERT attempts an appropriate SQL type conversion and handles many invalid strings without raising a conversion error; the condition in the stem is resolved by ISJSON, not by this feature alone.

D:

OPENJSON parses JSON structures into relational rows and can project explicitly typed columns through WITH; the missing behavior is ISJSON; this mechanism does something else.

Question 588

An EmployeeNumber may appear on only one employee row, independently of the table's primary surrogate key. Which database mechanism should be chosen?

  1. UNIQUE constraint
  2. NOT NULL constraint
  3. ON DELETE CASCADE foreign key action
  4. CHECK with IN predicate

Explanation

Correct Answer

A:

UNIQUE enforces a candidate key distinct from the primary key and prevents duplicate non-NULL key combinations according to platform semantics.

Incorrect Answers

B:

NOT NULL forbids the NULL marker on the designated column, regardless of whether a default exists; it addresses a different step; the question calls for UNIQUE constraint.

C:

A cascading DELETE relationship automatically deletes referencing child rows when the referenced parent is deleted; this differs from the UNIQUE constraint requirement described in the scenario.

D:

A CHECK expression can restrict a column to an enumerated set during writes, without requiring every client to implement the same validation; the required capability here is UNIQUE constraint, which this operation does not supply.

Question 589

A team is creating a Data API builder service from scratch: it must initialize configuration, expose an Orders entity, and run the API locally for validation. Which THREE CLI commands are needed? Choose THREE.

  1. `dab start`
  2. `dab init`
  3. DAB Redis cache
  4. `dab add`
  5. `dab validate`
  6. DAB in-memory response cache

Explanation

Correct Answers

A:

The start command runs the configured DAB service and hosts enabled endpoints for requests; used in combination with `dab add`, `dab init`, it handles a separate requirement of this combined workflow.

B:

The init command scaffolds an initial DAB runtime configuration containing the chosen data-source and endpoint settings; used in combination with `dab add`, `dab start`, it handles a separate requirement of this combined workflow.

D:

The add command registers an entity and its source and permissions in a DAB configuration; used in combination with `dab init`, `dab start`, it handles a separate requirement of this combined workflow.

Incorrect Answers

C:

A supported distributed Redis cache stores response entries outside individual runtime processes for multi-instance reuse; that function is not interchangeable with `dab start` and `dab init` and `dab add` for the requested result.

E:

The validate command checks a DAB configuration against supported validation rules without serving the application; it addresses a different step; the question calls for `dab start` and `dab init` and `dab add`.

F:

DAB response caching can retain eligible read results for configured durations, subject to invalidation and consistency requirements; this differs from the `dab start` and `dab init` and `dab add` requirement described in the scenario.

Question 590

Hundreds of reads are blocked and wait data points to an idle session holding uncommitted update locks; operators need the transaction owner. Which feature would solve the stated problem?

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

Explanation

Correct Answer

D:

Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation.

Incorrect Answers

A:

A shared acquisition order reduces circular-wait opportunities across concurrent operations; this differs from the Find open transaction owner requirement described in the scenario.

B:

READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking; the required capability here is Find open transaction owner, which this operation does not supply.

C:

Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness; the alternative cannot substitute for Find open transaction owner under the given constraints.

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