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

Question 1

A troubleshooting prompt contains customer account fields that are prohibited from being transmitted to an external model service. Which prompt-handling control prevents their disclosure before submission?

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

Explanation

Correct Answer

D:

Redacting or removing confidential prompt fields before submitting the request keeps prohibited customer data from being sent to an external model.

Incorrect Answers

A:

Requiring human confirmation for sensitive tool calls reduces the risk of autonomous changes and maintains an accountable approval point; it addresses a different step; the question calls for Redaction of sensitive prompt context.

B:

A read-only database principal with narrowly scoped SELECT access limits potential writes at the database authorization boundary; this differs from the Redaction of sensitive prompt context requirement described in the scenario.

C:

Secret scanning can identify supported credential patterns in repositories and complements, rather than replaces, credential rotation; the required capability here is Redaction of sensitive prompt context, which this operation does not supply.

Question 2

A compliance review needs cryptographic evidence that an authorized actor has not retroactively rewritten protected ledger history. Which SQL capability is the most direct fit?

  1. Graph MATCH predicate
  2. Ledger verification
  3. FOR SYSTEM_TIME AS OF
  4. External data access

Explanation

Correct Answer

B:

Ledger verification uses generated digests and recorded history to detect tampering; it is not simply a time-travel query.

Incorrect Answers

A:

MATCH expresses a graph traversal pattern over node and edge tables rather than a plain scalar comparison; the missing behavior is Ledger verification; this mechanism does something else.

C:

FOR SYSTEM_TIME AS OF retrieves the temporal version valid at a requested instant using the system-maintained period columns; for the stated conditions, the deciding feature is Ledger verification rather than this behavior.

D:

An external table exposes remote data through metadata and suitable connectivity without copying it into a regular local table first; it addresses a different step; the question calls for Ledger verification.

Question 3

A model endpoint speaks the Azure OpenAI request protocol and the SQL definition must build compatible request payloads. Which approach should the team select?

  1. MODEL_TYPE = EMBEDDINGS
  2. LOCATION parameter
  3. API_FORMAT parameter
  4. DATABASE SCOPED CREDENTIAL

Explanation

Correct Answer

C:

API_FORMAT identifies the provider-specific request and response contract used by the external model integration.

Incorrect Answers

A:

The embeddings model type indicates that the external definition produces vector representations from supported text input; the required capability here is API_FORMAT parameter, which this operation does not supply.

B:

LOCATION records the target service URI in the external model definition, with provider formatting and TLS constraints; the alternative cannot substitute for API_FORMAT parameter under the given constraints.

D:

A database-scoped credential stores or references endpoint authentication data subject to permission and endpoint matching rules; the missing behavior is API_FORMAT parameter; this mechanism does something else.

Question 4

A developer's project uses SQL syntax supported only by a newer engine, and validation must check against the intended deployment platform. What is the best primary mechanism to use?

  1. Database reference
  2. `dotnet build` of SQL project
  3. Target platform configuration
  4. Static analysis rule

Explanation

Correct Answer

C:

SQL project target-platform settings influence build-time compatibility checks for supported database features and syntax.

Incorrect Answers

A:

Database references supply schema models for external dependencies and help SQL project builds validate cross-database object references; for the stated conditions, the deciding feature is Target platform configuration rather than this behavior.

B:

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 Target platform configuration.

D:

SQL code analysis can inspect modeled schema and code against configured rules as part of validation, complementing runtime tests; the required capability here is Target platform configuration, which this operation does not supply.

Question 5

A partition specification intentionally treats each listed boundary value as the final included value of the lower partition. What should the developer implement?

  1. RANGE LEFT boundary definition
  2. Index partition alignment
  3. RANGE RIGHT boundary definition
  4. Partition elimination

Explanation

Correct Answer

A:

RANGE LEFT associates a boundary value with its left partition; this changes boundary equality membership.

Incorrect Answers

B:

Partition-aligned indexes and compatible structures are essential for metadata-only switch operations in supported configurations; the missing behavior is RANGE LEFT boundary definition; this mechanism does something else.

C:

RANGE RIGHT assigns the exact boundary value to the partition on its right, matching common month-start lower-bound layouts; that function is not interchangeable with RANGE LEFT boundary definition for the requested result.

D:

Partition elimination reduces scanned partitions when predicates allow the optimizer to identify the relevant partition ranges; for the stated conditions, the deciding feature is RANGE LEFT boundary definition rather than this behavior.

Question 6

Customer names can include Arabic, Japanese, and accented characters and the design calls for a variable-length Unicode string. Which SQL capability is the most direct fit?

  1. VARBINARY(MAX)
  2. DECIMAL(19,4)
  3. DATETIME2
  4. NVARCHAR(200)

Explanation

Correct Answer

D:

NVARCHAR stores variable-length Unicode character content and is appropriate when multilingual text must be preserved.

Incorrect Answers

A:

VARBINARY(MAX) holds variable-length binary data; unlike character types, it does not interpret the bytes using a text encoding; this differs from the NVARCHAR(200) requirement described in the scenario.

B:

Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale; the required capability here is NVARCHAR(200), which this operation does not supply.

C:

DATETIME2 represents date and time together with higher supported precision and range than legacy DATETIME; the alternative cannot substitute for NVARCHAR(200) under the given constraints.

Question 7

An API release introduced a plan regression and the DBA needs to compare retained plan choices and determine whether an earlier plan could be tested or forced for the specific statement. Which choice best meets these constraints?

  1. Azure Monitor metrics
  2. Query Store
  3. Log Analytics
  4. Application Insights

Explanation

Correct Answer

B:

Query Store retains query plans and runtime histories, allowing statement-level regression comparison and supported plan-forcing remediation that generic request tracing lacks.

Incorrect Answers

A:

Azure Monitor captures supported resource metrics and evaluates metric-based alert rules against them; that function is not interchangeable with Query Store for the requested result.

C:

Log Analytics provides searchable aggregated telemetry when the relevant services emit data to that workspace; it addresses a different step; the question calls for Query Store.

D:

Application Insights collects application telemetry and supports investigation of failed or slow service requests; this differs from the Query Store requirement described in the scenario.

Question 8

Every inserted support ticket needs a database-generated increasing numeric surrogate value associated with the ticket row. What should be configured to meet this objective?

  1. ROWVERSION column
  2. SEQUENCE object
  3. NEWID()
  4. IDENTITY property

Explanation

Correct Answer

D:

IDENTITY automatically produces values for an inserted column, although applications must not assume gap-free numbering or transactional sequences.

Incorrect Answers

A:

ROWVERSION is an automatically changing binary token for row modifications, useful in equality comparisons for optimistic concurrency and not a clock timestamp; the alternative cannot substitute for IDENTITY property under the given constraints.

B:

A SEQUENCE is a separately defined number generator that can be consumed from different tables and statements using NEXT VALUE FOR; the condition in the stem is resolved by IDENTITY property, not by this feature alone.

C:

NEWID produces a GUID-shaped uniqueidentifier; it is not a monotonically increasing table identity; the missing behavior is IDENTITY property; this mechanism does something else.

Question 9

A bill-of-materials query must repeatedly walk component-to-parent relationships until the reachable hierarchy is exhausted. Which mechanism addresses the requirement?

  1. Anchor member
  2. Nonrecursive CTE
  3. MAXRECURSION query hint
  4. Recursive CTE

Explanation

Correct Answer

D:

A recursive CTE combines anchor and recursive members to iteratively produce reachable levels of a hierarchy.

Incorrect Answers

A:

The anchor member emits the initial rows of a recursive CTE; those rows seed later recursive expansion; it addresses a different step; the question calls for Recursive CTE.

B:

A nonrecursive common table expression defines a named query expression in a WITH clause, scoped to the immediately following statement; this differs from the Recursive CTE requirement described in the scenario.

C:

OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion; the required capability here is Recursive CTE, which this operation does not supply.

Question 10

Hundreds of documents share identical legal footers that dominate the text and weaken semantic discrimination. Which feature would solve the stated problem?

  1. Exclude secret columns
  2. Normalize noisy boilerplate
  3. Title plus body
  4. Preserve source identifiers separately

Explanation

Correct Answer

B:

Removing repeated boilerplate from embedding inputs can increase the signal from content that differentiates documents.

Incorrect Answers

A:

Excluding secret or irrelevant columns reduces data exposure and prevents confidential values from affecting retrieval features; the missing behavior is Normalize noisy boilerplate; this mechanism does something else.

C:

Including the title and relevant body in a controlled embedding input gives the representation both subject identity and contextual detail; for the stated conditions, the deciding feature is Normalize noisy boilerplate rather than this behavior.

D:

Storing stable document and chunk identifiers alongside vectors supports traceability, updates, filtering, and safe content retrieval; it addresses a different step; the question calls for Normalize noisy boilerplate.

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