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

Question 440

A reconciliation SELECT has a complex intermediate result that should be given a readable name for use within one following SQL statement. What is the right solution for this specific requirement?

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

Explanation

Correct Answer

D:

A nonrecursive common table expression defines a named query expression in a WITH clause, scoped to the immediately following statement.

Incorrect Answers

A:

A recursive CTE combines anchor and recursive members to iteratively produce reachable levels of a hierarchy; the alternative cannot substitute for Nonrecursive CTE under the given constraints.

B:

The anchor member emits the initial rows of a recursive CTE; those rows seed later recursive expansion; the condition in the stem is resolved by Nonrecursive CTE, not by this feature alone.

C:

OPTION (MAXRECURSION n) can cap recursive CTE iterations on supported SQL engines, helping control runaway recursive expansion; the missing behavior is Nonrecursive CTE; this mechanism does something else.

Question 441

A change processor may retry the same message after a timeout and must not generate duplicate searchable chunk rows. Which approach should the team select?

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

Explanation

Correct Answer

D:

Upsert by a stable document/chunk/version key allows retries without duplicating records, supporting at-least-once processing.

Incorrect Answers

A:

A deletion-aware sync process removes or invalidates vectors and searchable text tied to the erased source record; it addresses a different step; the question calls for Idempotent embedding upsert.

B:

Change-driven maintenance processes affected rows and preserves a checkpoint for incremental vector refresh; this differs from the Idempotent embedding upsert requirement described in the scenario.

C:

Storing embedding-model identity or version with each vector permits controlled migration and query-to-corpus compatibility checks; the required capability here is Idempotent embedding upsert, which this operation does not supply.

Question 442

A ranking workflow needs a table of full-text matches and their relative RANK values for subsequent joins. Which action is justified by these conditions?

  1. CONTAINSTABLE
  2. Full-text index
  3. FREETEXT
  4. LIKE predicate

Explanation

Correct Answer

A:

CONTAINSTABLE returns matching keys with a full-text rank value as a relational result set.

Incorrect Answers

B:

A full-text index maintains searchable term structures required by SQL full-text predicates on supported columns; that function is not interchangeable with CONTAINSTABLE for the requested result.

C:

FREETEXT performs linguistic search using word-breaking and related term processing for broader lexical matching; for the stated conditions, the deciding feature is CONTAINSTABLE rather than this behavior.

D:

LIKE can filter rows by textual patterns but supplies no full-text ranking table with a RANK column for joining.

Question 443

Auditors need to ask what a customer address looked like at 15:00 yesterday after several updates were applied. Which SQL capability is the most direct fit?

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

Explanation

Correct Answer

C:

Temporal tables maintain current and history rows with system-maintained validity periods, allowing FOR SYSTEM_TIME point-in-time queries.

Incorrect Answers

A:

An external table supplies database metadata for accessing external data; it does not turn the remote data into ordinary local stored rows; the required capability here is System-versioned temporal table, which this operation does not supply.

B:

SQL graph node and edge tables represent entities and relationships, enabling MATCH-based queries over connected records; the alternative cannot substitute for System-versioned temporal table under the given constraints.

D:

An updatable ledger table retains audit history with digest-based verification of data integrity rather than merely exposing prior row versions; the missing behavior is System-versioned temporal table; this mechanism does something else.

Question 444

New documents require embedding through a registered SQL model, but a serverless process must start the refresh after tracked table changes. Which TWO techniques fit the pipeline? Choose TWO.

  1. MODEL_TYPE = EMBEDDINGS
  2. External batching worker
  3. Database trigger with guarded work
  4. AI_GENERATE_EMBEDDINGS
  5. Azure Function on change event

Explanation

Correct Answers

D:

AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; used in combination with Azure Function on change event, it handles a separate requirement of this combined workflow.

E:

A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes; used in combination with AI_GENERATE_EMBEDDINGS, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

The embeddings model type indicates that the external definition produces vector representations from supported text input; for the stated conditions, the deciding feature is AI_GENERATE_EMBEDDINGS and Azure Function on change event rather than this behavior.

B:

An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions; it addresses a different step; the question calls for AI_GENERATE_EMBEDDINGS and Azure Function on change event.

C:

A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously; this differs from the AI_GENERATE_EMBEDDINGS and Azure Function on change event requirement described in the scenario.

Question 445

Each retrieved embedding must be traceable to its current source document for permission checks, updates, and citation display. What should be configured to meet this objective?

  1. VECTOR(n) column
  2. DocumentId foreign key
  3. TenantId metadata field
  4. Embedding-model version field

Explanation

Correct Answer

B:

A stable relational source key links vector rows to documents independently of their numeric embedding contents.

Incorrect Answers

A:

The VECTOR type stores fixed-dimensional floating-point arrays in an optimized representation compatible with vector functions; the condition in the stem is resolved by DocumentId foreign key, not by this feature alone.

C:

Tenant metadata supports enforced isolation filters; vector similarity alone does not authorize document access; that function is not interchangeable with DocumentId foreign key for the requested result.

D:

Recording model version alongside vectors enables matching query embeddings to the correct indexed corpus; for the stated conditions, the deciding feature is DocumentId foreign key rather than this behavior.

Question 446

A deployment workflow authenticates to Azure with an environment-bound federation relationship instead of any stored password. What is the most suitable implementation?

  1. Credential rotation
  2. Protected environment secret
  3. Database contained user grant
  4. Environment-specific publish profile
  5. Federated GitHub identity

Explanation

Correct Answer

E:

Federated workload credentials exchange trusted action identity assertions for Azure access, requiring explicit trust configuration.

Incorrect Answers

A:

Revoking and rotating compromised credentials eliminates continued use of exposed secrets, unlike merely deleting a log message; this differs from the Federated GitHub identity requirement described in the scenario.

B:

Environment-scoped secret controls restrict availability to authorized workflows, although long-lived passwords should be minimized; the required capability here is Federated GitHub identity, which this operation does not supply.

C:

Authentication alone does not grant SQL object privileges, so a contained user or equivalent principal must receive deployment rights; the alternative cannot substitute for Federated GitHub identity under the given constraints.

D:

Environment configuration and publish profiles keep target-specific settings separate from the source schema definition; the condition in the stem is resolved by Federated GitHub identity, not by this feature alone.

Question 447

After an incident, investigators must correlate observed access statements with principals and timestamps rather than change database permission policy. What is the right solution for this specific requirement?

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

Explanation

Correct Answer

B:

Examining retained audit records supports forensic reconstruction; it does not itself prevent future unauthorized requests.

Incorrect Answers

A:

SQL audit specifications define audited action groups or object actions to be captured by the audit mechanism; that function is not interchangeable with Review stored audit events for the requested result.

C:

Azure resource diagnostic settings route supported log and metric categories to configured destinations and require appropriate permissions; it addresses a different step; the question calls for Review stored audit events.

D:

TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations; this differs from the Review stored audit events requirement described in the scenario.

E:

A metric or log alert rule evaluates collected telemetry and can initiate actions when a defined condition is met; the required capability here is Review stored audit events, which this operation does not supply.

Question 448

A stored procedure references a renamed column and the pipeline should catch the broken reference before generating a release DACPAC. Which database mechanism should be chosen?

  1. DACPAC dependency-aware publish
  2. Post-deployment script
  3. Pre-deployment script
  4. Build dependency validation

Explanation

Correct Answer

D:

Building the SQL project validates supported references against the modeled database schema.

Incorrect Answers

A:

DacFx's deployment plan accounts for model dependencies when generating schema changes for the target; the alternative cannot substitute for Build dependency validation under the given constraints.

B:

A post-deployment script runs after main schema changes and can populate approved reference data; the condition in the stem is resolved by Build dependency validation, not by this feature alone.

C:

A pre-deployment script executes at the designated stage of database project publishing and must be designed safely and idempotently; the missing behavior is Build dependency validation; this mechanism does something else.

Question 449

A small corpus of ten thousand vectors requires a provably precise top-five nearest-neighbor ordering for evaluation. Which option satisfies the desired behavior most directly?

  1. Keyword inverted index
  2. Approximate nearest-neighbor index
  3. Metadata predicate pushdown
  4. Exact nearest-neighbor scan

Explanation

Correct Answer

D:

Exact nearest-neighbor search computes distances across all eligible candidates and returns the true nearest results for the chosen metric.

Incorrect Answers

A:

An inverted full-text index maps terms to matching documents for lexical search over tokenized content; it addresses a different step; the question calls for Exact nearest-neighbor scan.

B:

Approximate-neighbor indexes reduce the amount of distance work by searching a structured vector index, trading some recall for speed; this differs from the Exact nearest-neighbor scan requirement described in the scenario.

C:

Applying supported metadata predicates early reduces candidate sets and enforces eligibility independently of semantic ranking; the required capability here is Exact nearest-neighbor scan, which this operation does not supply.

Question 450

An API receives tokens issued by the right tenant but intended for a different service; it must reject them for this API. What is the best primary mechanism to use?

  1. Managed identity to database
  2. CREATE SECURITY POLICY
  3. Inline table-valued predicate function
  4. TLS termination
  5. Per-role API operation policy
  6. Validate JWT audience

Explanation

Correct Answer

F:

Checking a token's audience prevents treating tokens minted for unrelated resources as authorization to this endpoint.

Incorrect Answers

A:

A managed identity supports token-based database sign-in if the SQL platform and database principal are configured appropriately; the missing behavior is Validate JWT audience; this mechanism does something else.

B:

CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; that function is not interchangeable with Validate JWT audience for the requested result.

C:

RLS uses predicate functions to express per-row access rules evaluated by security policies, often with SCHEMABINDING; for the stated conditions, the deciding feature is Validate JWT audience rather than this behavior.

D:

TLS secures transport confidentiality and peer authentication, while application roles still govern allowed operations; it addresses a different step; the question calls for Validate JWT audience.

E:

Role-level operation restrictions at the API boundary can separate read capabilities from write mutations without confusing authentication with authorization; this differs from the Validate JWT audience requirement described in the scenario.

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