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

Question 140

An assistant needs table and column names to write a join but must not receive any raw patient or payroll rows. What is the right solution for this specific requirement?

  1. Synthetic test data
  2. Parameterize generated SQL
  3. Schema metadata only
  4. Scoped temporary credentials

Explanation

Correct Answer

C:

Restricting context to schema metadata can support query drafting without exposing record contents to the model.

Incorrect Answers

A:

Synthetic records can reproduce structural and edge-case patterns while reducing the need to expose personal production data; for the stated conditions, the deciding feature is Schema metadata only rather than this behavior.

B:

Parameterized execution separates user data from executable SQL, reducing injection risk even if the initial query text was AI-assisted; it addresses a different step; the question calls for Schema metadata only.

D:

Short-lived, narrowly authorized credentials limit exposure relative to reusable administrative secrets embedded in tool configuration; the required capability here is Schema metadata only, which this operation does not supply.

Question 141

A SQL developer assistant needs only catalog discovery, not a generic unrestricted execution endpoint. Which feature would solve the stated problem?

  1. SQL style examples
  2. Review tool audit trail
  3. Least-privilege MCP tools
  4. Scoped instruction file

Explanation

Correct Answer

C:

Exposing a constrained metadata inspection tool reduces the actions the client can request compared with a full arbitrary-SQL tool.

Incorrect Answers

A:

Giving concise project examples helps steer suggestions but cannot guarantee conformance without linting and review; the condition in the stem is resolved by Least-privilege MCP tools, not by this feature alone.

B:

Recording tool-call identities, operations, and outcomes supports accountability and incident investigation independently of conversation text; the missing behavior is Least-privilege MCP tools; this mechanism does something else.

D:

Appropriately scoped Copilot instruction files can provide targeted coding conventions without imposing the same guidance everywhere; for the stated conditions, the deciding feature is Least-privilege MCP tools rather than this behavior.

Question 142

An Azure-hosted service must use enterprise token-based SQL sign-in and avoid embedding a service password in application settings. Which TWO identity capabilities support the design? Choose TWO.

  1. Least-privilege database role
  2. Azure Monitor alerts
  3. Managed identity
  4. Microsoft Entra authentication
  5. Contained database user

Explanation

Correct Answers

C:

A managed identity gives an eligible Azure workload an automatically managed service identity, with database access still requiring grants; used in combination with Microsoft Entra authentication, it handles a separate requirement of this combined workflow.

D:

Microsoft Entra authentication supports identity-provider tokens for compatible SQL connections and database principal authorization; used in combination with Managed identity, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

A custom database role can aggregate narrowly defined GRANT permissions and simplify managed membership; this differs from the Managed identity and Microsoft Entra authentication requirement described in the scenario.

B:

Azure Monitor evaluates selected signals against alert rules and can notify or trigger responses; it is not a row-authorization mechanism; the required capability here is Managed identity and Microsoft Entra authentication, which this operation does not supply.

E:

A contained database user represents an identity at database scope and can receive database permissions or role membership; the missing behavior is Managed identity and Microsoft Entra authentication; this mechanism does something else.

Question 143

After an authorized hotfix changed production directly, the team needs to detect divergence from its DACPAC and then bring the reviewed object definition back into Git. Which TWO actions are appropriate? Choose TWO.

  1. Import changes into SQL project
  2. Key Vault secret reference
  3. Review object dependency graph
  4. Block drifted release
  5. Compare live schema to DACPAC

Explanation

Correct Answers

A:

Updating the project from the database can reconcile reviewed schema changes back into declarative source control; used in combination with Compare live schema to DACPAC, it handles a separate requirement of this combined workflow.

E:

Comparing the deployed database with the approved DACPAC reveals schema drift that normal source-code reviews cannot see; used in combination with Import changes into SQL project, it handles a separate requirement of this combined workflow.

Incorrect Answers

B:

Fetching secrets from Key Vault centralizes storage and access control, while the pipeline should avoid logging returned values; for the stated conditions, the deciding feature is Import changes into SQL project and Compare live schema to DACPAC rather than this behavior.

C:

Model dependency validation and schema comparison help reveal impacts that a text-only change review may miss; it addresses a different step; the question calls for Import changes into SQL project and Compare live schema to DACPAC.

D:

A drift gate can halt deployment when actual target state deviates materially from the expected controlled baseline; this differs from the Import changes into SQL project and Compare live schema to DACPAC requirement described in the scenario.

Question 144

A dashboard repeatedly reads a precomputed aggregate while base transaction rows change; it needs the aggregate index to be maintained with underlying SQL modifications. Which option would you recommend?

  1. Nonclustered index on join key
  2. Indexed view
  3. Clustered columnstore index for aggregations
  4. Filtered index for sparse predicate

Explanation

Correct Answer

B:

An eligible indexed view stores indexed relational results that the SQL engine maintains as qualifying base rows change, at the cost of additional write and index maintenance.

Incorrect Answers

A:

An index with HeaderId as a leading key can accelerate lookups and joins from headers to matching details; the alternative cannot substitute for Indexed view under the given constraints.

C:

Columnstore storage is optimized for large analytical scans and compression of similar column values; the missing behavior is Indexed view; this mechanism does something else.

D:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; that function is not interchangeable with Indexed view for the requested result.

Question 145

A supported Always Encrypted deployment needs selected richer operations and in-place cryptographic work inside a protected server-side computation boundary. Which option would you recommend?

  1. Secure enclave
  2. Transparent Data Encryption
  3. Column encryption key
  4. Always Encrypted-enabled driver
  5. Column master key
  6. Masked column definition

Explanation

Correct Answer

A:

A secure enclave can support approved operations on protected data while isolating cryptographic processing from the rest of the database environment.

Incorrect Answers

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 Secure enclave requirement described in the scenario.

C:

A column encryption key encrypts Always Encrypted column data, while its encrypted form is stored as database metadata; the required capability here is Secure enclave, which this operation does not supply.

D:

A compatible driver with column encryption enabled can perform encryption and decryption using approved key access outside the database engine; the alternative cannot substitute for Secure enclave under the given constraints.

E:

A column master key describes the key-encryption root used to protect column encryption keys; the underlying key should remain outside routine database access; the condition in the stem is resolved by Secure enclave, not by this feature alone.

F:

A masking function on the column changes the result shown to principals lacking UNMASK, leaving the underlying value unchanged; the missing behavior is Secure enclave; this mechanism does something else.

Question 146

An existing MCP client already supports tool discovery. The SQL backend now needs a component that advertises tools to that client and an independent server-side authorization check to refuse destructive calls even if the client requests them. Which TWO components implement those distinct duties? Choose TWO.

  1. Database service identity
  2. MCP client
  3. MCP tool server
  4. Authorization policy on tool calls
  5. SQL endpoint authorization

Explanation

Correct Answers

C:

An MCP server exposes tool capabilities to a compatible client; the tool's database permissions still depend on its backend identity; used in combination with Authorization policy on tool calls, it handles a separate requirement of this combined workflow.

D:

Tool-side authorization and command restrictions should enforce permitted operations independently of natural-language prompt instructions; used in combination with MCP tool server, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

A managed or workload identity can acquire authorized tokens for compatible SQL access, requiring appropriate database grants; the missing behavior is MCP tool server and Authorization policy on tool calls; this mechanism does something else.

B:

An MCP client connects to configured servers and consumes advertised capabilities while respecting its security controls; that function is not interchangeable with MCP tool server and Authorization policy on tool calls for the requested result.

E:

Database authorization still applies when queries are proposed by Copilot, so appropriate grants are required separately; this differs from the MCP tool server and Authorization policy on tool calls requirement described in the scenario.

Question 147

A production migration affecting encrypted columns must require an authorized human signoff after build and testing pass. What should be configured to meet this objective?

  1. Build once, deploy same artifact
  2. Deployment smoke test
  3. Post-release verification
  4. Dry-run deployment script
  5. Environment approval gate

Explanation

Correct Answer

E:

Approval gates block promotion to sensitive environments until designated reviewers approve the deployment job.

Incorrect Answers

A:

Promoting one immutable DACPAC across environments preserves a traceable model artifact while target comparisons remain environment-specific; the required capability here is Environment approval gate, which this operation does not supply.

B:

A smoke test checks critical post-deployment operations in the target environment and complements static compilation; the alternative cannot substitute for Environment approval gate under the given constraints.

C:

Post-deployment verification tests operational outcomes after schema changes, not merely tool exit codes; the condition in the stem is resolved by Environment approval gate, not by this feature alone.

D:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; the missing behavior is Environment approval gate; this mechanism does something else.

Question 148

Audit policy requires that the same bytes approved in pre-production are used for the actual production migration. Which option would you recommend?

  1. Source object definitions
  2. Git tags for releases
  3. Immutable release artifacts
  4. Environment-specific publish profile
  5. Protected environment secret

Explanation

Correct Answer

C:

Artifact promotion preserves the reviewed DACPAC across environments, avoiding accidental rebuilds from later commits.

Incorrect Answers

A:

Declarative .sql object files in a SQL project provide a maintainable source of truth for modeled database objects; for the stated conditions, the deciding feature is Immutable release artifacts rather than this behavior.

B:

Tags can mark repository states for releases, supporting traceability between commits, built artifacts, and deployments; it addresses a different step; the question calls for Immutable release artifacts.

D:

Environment configuration and publish profiles keep target-specific settings separate from the source schema definition; the required capability here is Immutable release artifacts, which this operation does not supply.

E:

Environment-scoped secret controls restrict availability to authorized workflows, although long-lived passwords should be minimized; the alternative cannot substitute for Immutable release artifacts under the given constraints.

Question 149

After defining monthly boundaries, the DBA must map those partitions to the appropriate filegroups. What is the most appropriate design decision?

  1. Aligned partitioned index
  2. Partition scheme
  3. Partition switching
  4. Partition function

Explanation

Correct Answer

B:

A partition scheme assigns the partitions defined by a function to filegroups and can be referenced by tables and indexes.

Incorrect Answers

A:

Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries; the condition in the stem is resolved by Partition scheme, not by this feature alone.

C:

ALTER TABLE SWITCH changes partition ownership through metadata when structures, indexes and constraints satisfy switching prerequisites; that function is not interchangeable with Partition scheme for the requested result.

D:

A partition function defines boundary points and maps data values to logical partitions without itself choosing storage destinations; for the stated conditions, the deciding feature is Partition scheme rather than this behavior.

Question 150

A skewed predicate targets a small subset whose distribution differs sharply from the full table, and estimates need subset-specific data. What should the developer implement?

  1. Index physical maintenance
  2. Actual vs estimated row comparison
  3. Filtered statistics
  4. UPDATE STATISTICS

Explanation

Correct Answer

C:

Filtered statistics capture distributions for rows matching a predicate and can improve estimates for compatible queries.

Incorrect Answers

A:

Appropriate index rebuild or reorganize actions address physical layout while statistics alone cannot fix page fragmentation; this differs from the Filtered statistics requirement described in the scenario.

B:

Comparing estimated and actual rows at plan operators reveals estimation errors that can explain inappropriate operator choices; the required capability here is Filtered statistics, which this operation does not supply.

D:

Updating statistics refreshes cardinality information used during plan selection, though it does not directly rebuild every index; the condition in the stem is resolved by Filtered statistics, not by this feature alone.

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