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

Question 20

A security architect wants key metadata identifying the external key that protects column encryption keys, with key material kept in an external trust boundary. What is the best primary mechanism to use?

  1. Column encryption key
  2. Always Encrypted-enabled driver
  3. Secure enclave
  4. Column master key

Explanation

Correct Answer

D:

A column master key describes the key-encryption root used to protect column encryption keys; the underlying key should remain outside routine database access.

Incorrect Answers

A:

A column encryption key encrypts Always Encrypted column data, while its encrypted form is stored as database metadata; for the stated conditions, the deciding feature is Column master key rather than this behavior.

B:

A compatible driver with column encryption enabled can perform encryption and decryption using approved key access outside the database engine; it addresses a different step; the question calls for Column master key.

C:

A secure enclave can support approved operations on protected data while isolating cryptographic processing from the rest of the database environment; this differs from the Column master key requirement described in the scenario.

Question 21

A stored procedure needs to build a JSON request envelope with named system and user fields using typed SQL expressions. What should the developer implement?

  1. JSON_OBJECT
  2. STRING_AGG
  3. JSON_ARRAYAGG
  4. FOR JSON PATH
  5. Managed identity authorization

Explanation

Correct Answer

A:

JSON_OBJECT composes named key-value JSON data without fragile manual quote and comma concatenation.

Incorrect Answers

B:

STRING_AGG concatenates values across rows with a selected delimiter, subject to ordering and type considerations; the missing behavior is JSON_OBJECT; this mechanism does something else.

C:

JSON_ARRAYAGG aggregates expressions into JSON arrays using supported SQL engine functionality; that function is not interchangeable with JSON_OBJECT for the requested result.

D:

FOR JSON PATH transforms SQL query results into JSON structures using explicit alias-based paths; for the stated conditions, the deciding feature is JSON_OBJECT rather than this behavior.

E:

Managed identity authentication can authorize a supported outbound model call when the identity has the required destination role; it addresses a different step; the question calls for JSON_OBJECT.

Question 22

A sanctioned hotfix changed a view directly in production, and its final definition must become part of the next version-controlled build. What is the most appropriate design decision?

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

Explanation

Correct Answer

A:

Updating the project from the database can reconcile reviewed schema changes back into declarative source control.

Incorrect Answers

B:

Model dependency validation and schema comparison help reveal impacts that a text-only change review may miss; the required capability here is Import changes into SQL project, which this operation does not supply.

C:

Comparing the deployed database with the approved DACPAC reveals schema drift that normal source-code reviews cannot see; the alternative cannot substitute for Import changes into SQL project under the given constraints.

D:

A drift gate can halt deployment when actual target state deviates materially from the expected controlled baseline; the condition in the stem is resolved by Import changes into SQL project, not by this feature alone.

Question 23

A developer accepts an AI-generated schema change, but another engineer must inspect it before it reaches the protected release branch. What is the right solution for this specific requirement?

  1. Pull-request review
  2. Protected branch rule
  3. SqlPackage deployment report
  4. Database schema comparison

Explanation

Correct Answer

A:

A reviewed pull request introduces human assessment and traceable approvals for database model changes.

Incorrect Answers

B:

Branch protection or rulesets enforce repository merge conditions independently of coding assistant recommendations; for the stated conditions, the deciding feature is Pull-request review rather than this behavior.

C:

A SqlPackage deployment report describes planned differences and supports review without immediately applying destructive changes; it addresses a different step; the question calls for Pull-request review.

D:

Schema comparison shows differences between source models and targets, revealing unintended changes before deployment; this differs from the Pull-request review requirement described in the scenario.

Question 24

A user asks about 'reducing power usage' and should find notes saying 'cut electricity consumption' even without shared keywords. What is the best primary mechanism to use?

  1. Structured relational filter
  2. Full-text search
  3. Hybrid keyword-plus-vector search
  4. Semantic vector search

Explanation

Correct Answer

D:

Vector search compares embedding representations and can retrieve semantic neighbors despite different literal words.

Incorrect Answers

A:

Ordinary indexed SQL predicates are the direct mechanism for precise structured filtering without embedding model inference; the alternative cannot substitute for Semantic vector search under the given constraints.

B:

SQL full-text search indexes terms and supports linguistic predicates such as CONTAINS and FREETEXT rather than numerical vector proximity; the condition in the stem is resolved by Semantic vector search, not by this feature alone.

C:

Hybrid retrieval combines lexical and embedding evidence, capturing both exact-term and conceptual relevance; the missing behavior is Semantic vector search; this mechanism does something else.

Question 25

The corpus will be re-embedded with a newer model and must avoid mixing vectors from incompatible representation spaces. Which feature would solve the stated problem?

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

Explanation

Correct Answer

A:

Recording model version alongside vectors enables matching query embeddings to the correct indexed corpus.

Incorrect Answers

B:

A stable relational source key links vector rows to documents independently of their numeric embedding contents; this differs from the Embedding-model version field requirement described in the scenario.

C:

The VECTOR type stores fixed-dimensional floating-point arrays in an optimized representation compatible with vector functions; the required capability here is Embedding-model version field, which this operation does not supply.

D:

Tenant metadata supports enforced isolation filters; vector similarity alone does not authorize document access; the alternative cannot substitute for Embedding-model version field under the given constraints.

Question 26

A stored procedure update requires focused function-level assertions and a full order-to-inventory test against an integrated test database. Which TWO test categories are required? Choose TWO.

  1. Unit test
  2. Build-time schema validation
  3. Dry-run deployment script
  4. Deployment smoke test
  5. Integration test

Explanation

Correct Answers

A:

A unit test validates one small programmatic behavior against expected results, catching logic regressions early; used in combination with Integration test, it handles a separate requirement of this combined workflow.

E:

An integration test exercises interactions among database objects and dependent components rather than one isolated expression; used in combination with Unit test, it handles a separate requirement of this combined workflow.

Incorrect Answers

B:

Building a database project checks supported schema references and model consistency to catch missing dependencies; that function is not interchangeable with Unit test and Integration test for the requested result.

C:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; for the stated conditions, the deciding feature is Unit test and Integration test rather than this behavior.

D:

A smoke test checks critical post-deployment operations in the target environment and complements static compilation; it addresses a different step; the question calls for Unit test and Integration test.

Question 27

A pipeline must publish a compiled database schema model to an empty or existing SQL database without including ordinary user table rows. What is the best primary mechanism to use?

  1. SDK-style SQL database project
  2. DACPAC
  3. SqlPackage Publish
  4. BACPAC

Explanation

Correct Answer

B:

A DACPAC packages the database schema model for DacFx comparison and deployment; it is not primarily a full data export.

Incorrect Answers

A:

An SDK-style SQL project provides declarative schema files and cross-platform build tooling supported by Microsoft.Build.Sql; the required capability here is DACPAC, which this operation does not supply.

C:

SqlPackage Publish compares the source package with the destination and executes a generated deployment plan when authorized; the condition in the stem is resolved by DACPAC, not by this feature alone.

D:

A BACPAC is a logical export containing schema and data, unlike a schema-only DACPAC; the missing behavior is DACPAC; this mechanism does something else.

Question 28

GitHub Actions must authenticate to Azure without a saved client secret and its production deployment authority must be narrow. Which TWO security practices support this? Choose TWO.

  1. Managed identity in hosted runner
  2. Key Vault secret reference
  3. Least-privilege deployment principal
  4. OpenID Connect federation
  5. Dry-run deployment script

Explanation

Correct Answers

C:

A narrowly scoped deployment identity limits damage and requires only appropriate SQL and Azure resource permissions for the target workflow; used in combination with OpenID Connect federation, it handles a separate requirement of this combined workflow.

D:

OIDC workload identity federation lets a trusted workflow exchange a short-lived identity token for authorized Azure credentials; used in combination with Least-privilege deployment principal, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

A managed identity provides an Azure-hosted workload with token-based access instead of a persisted username and password; for the stated conditions, the deciding feature is Least-privilege deployment principal and OpenID Connect federation rather than this behavior.

B:

Fetching secrets from Key Vault centralizes storage and access control, while the pipeline should avoid logging returned values; it addresses a different step; the question calls for Least-privilege deployment principal and OpenID Connect federation.

E:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; the alternative cannot substitute for Least-privilege deployment principal and OpenID Connect federation under the given constraints.

Question 29

An engineer needs to connect a compatible coding assistant to a company SQL tool endpoint exposed through Model Context Protocol. Which option satisfies the desired behavior most directly?

  1. MCP server configuration
  2. Chat model selection
  3. Chat tool permissions
  4. Repository copilot-instructions.md

Explanation

Correct Answer

A:

Configuring an MCP server endpoint tells an MCP-capable client which tool host it may contact, subject to authentication and policy.

Incorrect Answers

B:

The assistant's model selection controls model behavior in the supported chat tool, not the runtime semantics of deployed T-SQL; the missing behavior is MCP server configuration; this mechanism does something else.

C:

Tool permission and approval settings control reachable actions in the assistant session and need restriction before permitting database access; that function is not interchangeable with MCP server configuration for the requested result.

D:

A repository instruction file can communicate persistent project-specific guidance to compatible GitHub Copilot tooling, without changing database enforcement rules; for the stated conditions, the deciding feature is MCP server configuration rather than this behavior.

Question 30

A query needs a reusable calculation that accepts a tax rate and price and returns one calculated SQL value per call. Which choice best meets these constraints?

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

Explanation

Correct Answer

B:

A scalar function produces a single value and can be invoked in supported expressions, with performance considerations depending on its implementation.

Incorrect Answers

A:

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 Scalar user-defined function requirement described in the scenario.

C:

A table-valued function returns a relational result, and an inline version can expose an optimizer-friendly SELECT expression; the alternative cannot substitute for Scalar user-defined function under the given constraints.

D:

A regular view encapsulates a SELECT definition as a database object and generally resolves its rows from underlying tables when queried; the condition in the stem is resolved by Scalar user-defined function, not by this feature alone.

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