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

Question 70

Audit events need a date and clock time with configurable fractional-second precision rather than merely a calendar date. Which action is justified by these conditions?

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

Explanation

Correct Answer

C:

DATETIME2 represents date and time together with higher supported precision and range than legacy DATETIME.

Incorrect Answers

A:

NVARCHAR stores variable-length Unicode character content and is appropriate when multilingual text must be preserved; this differs from the DATETIME2 requirement described in the scenario.

B:

VARBINARY(MAX) holds variable-length binary data; unlike character types, it does not interpret the bytes using a text encoding; the required capability here is DATETIME2, which this operation does not supply.

D:

Decimal stores fixed-precision, fixed-scale numbers suitable for exact arithmetic within its declared precision and scale; the condition in the stem is resolved by DATETIME2, not by this feature alone.

Question 71

A line-item table is repeatedly joined to a small header table by HeaderId and has no suitable existing access path on that foreign key. What is the most suitable implementation?

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

Explanation

Correct Answer

C:

An index with HeaderId as a leading key can accelerate lookups and joins from headers to matching details.

Incorrect Answers

A:

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; that function is not interchangeable with Nonclustered index on join key for the requested result.

B:

Columnstore storage is optimized for large analytical scans and compression of similar column values; for the stated conditions, the deciding feature is Nonclustered index on join key rather than this behavior.

D:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; this differs from the Nonclustered index on join key requirement described in the scenario.

Question 72

A release service can publish one database schema but should not obtain subscription-wide Owner rights by default. Which technique applies to this scenario?

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

Explanation

Correct Answer

A:

A narrowly scoped deployment identity limits damage and requires only appropriate SQL and Azure resource permissions for the target workflow.

Incorrect Answers

B:

A managed identity provides an Azure-hosted workload with token-based access instead of a persisted username and password; the condition in the stem is resolved by Least-privilege deployment principal, not by this feature alone.

C:

Fetching secrets from Key Vault centralizes storage and access control, while the pipeline should avoid logging returned values; the missing behavior is Least-privilege deployment principal; this mechanism does something else.

D:

OIDC workload identity federation lets a trusted workflow exchange a short-lived identity token for authorized Azure credentials; that function is not interchangeable with Least-privilege deployment principal for the requested result.

Question 73

A managed Azure SQL database needs portal-level identification of expensive queries using performance insights built on captured query data. Which database mechanism should be chosen?

  1. Query Store runtime statistics
  2. Query Store wait statistics
  3. Force known good query plan
  4. Query Performance Insight

Explanation

Correct Answer

D:

Query Performance Insight surfaces query performance trends in Azure SQL to help locate high-impact workloads without a separate profiler trace.

Incorrect Answers

A:

Query Store runtime aggregation preserves historical performance measures across collection windows; it addresses a different step; the question calls for Query Performance Insight.

B:

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

C:

Query Store plan forcing can direct SQL Server toward a previously captured plan while conditions still permit its use; the required capability here is Query Performance Insight, which this operation does not supply.

Question 74

A data model uses a stable internal row identifier even when business references change, and child rows must still enforce a reference to an approved unique header code. Which TWO design choices fit? Choose TWO.

  1. Surrogate integer key
  2. Foreign key to a candidate key
  3. Filtered unique index
  4. Filtered index for sparse predicate
  5. Composite natural key

Explanation

Correct Answers

A:

A surrogate key decouples row identity from mutable business data and can support compact joins and stable references; used in combination with Foreign key to a candidate key, it handles a separate requirement of this combined workflow.

B:

A foreign key may reference an eligible UNIQUE candidate key, preserving referential integrity without depending on the primary key specifically; used in combination with Surrogate integer key, it handles a separate requirement of this combined workflow.

Incorrect Answers

C:

A filtered unique index enforces uniqueness within a predicate-defined subset, unlike a table-wide unique constraint; for the stated conditions, the deciding feature is Surrogate integer key and Foreign key to a candidate key rather than this behavior.

D:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; it addresses a different step; the question calls for Surrogate integer key and Foreign key to a candidate key.

E:

A composite key enforces uniqueness over a column combination, matching a many-to-many association's natural identity; this differs from the Surrogate integer key and Foreign key to a candidate key requirement described in the scenario.

Question 75

Two developers changed the same view definition in different branches, and both intended edits must be reconciled before build. Which feature would solve the stated problem?

  1. Merge conflict resolution
  2. Feature branch
  3. Pull request
  4. Protected main branch

Explanation

Correct Answer

A:

Resolving the conflicting source edits produces one coherent schema definition before a merged database project can be compiled.

Incorrect Answers

B:

A feature branch provides a separate line of changes for review and integration without directly changing the release branch; the alternative cannot substitute for Merge conflict resolution under the given constraints.

C:

A pull request provides a controlled review and merge workflow for database-source changes; the condition in the stem is resolved by Merge conflict resolution, not by this feature alone.

D:

Branch protection or rulesets impose merge policies, required checks, and review conditions in the repository; the missing behavior is Merge conflict resolution; this mechanism does something else.

Question 76

An enrollment row is uniquely identified by the combination of StudentId and CourseId rather than either component by itself. Which option satisfies the desired behavior most directly?

  1. Foreign key to a candidate key
  2. Composite natural key
  3. Surrogate integer key
  4. Filtered unique index

Explanation

Correct Answer

B:

A composite key enforces uniqueness over a column combination, matching a many-to-many association's natural identity.

Incorrect Answers

A:

A foreign key may reference an eligible UNIQUE candidate key, preserving referential integrity without depending on the primary key specifically; for the stated conditions, the deciding feature is Composite natural key rather than this behavior.

C:

A surrogate key decouples row identity from mutable business data and can support compact joins and stable references; this differs from the Composite natural key requirement described in the scenario.

D:

A filtered unique index enforces uniqueness within a predicate-defined subset, unlike a table-wide unique constraint; the required capability here is Composite natural key, which this operation does not supply.

Question 77

The security database team must review any pull request altering schemas under the security/ directory before merge. Which database mechanism should be chosen?

  1. Required status checks
  2. CODEOWNERS review
  3. Restricted workflow permissions
  4. Pull-request approval rule

Explanation

Correct Answer

B:

CODEOWNERS can designate responsible reviewers for repository paths when enforced through branch protection rules.

Incorrect Answers

A:

Required status checks make successful automated validation a condition for merging into a protected branch; the condition in the stem is resolved by CODEOWNERS review, not by this feature alone.

C:

Limiting workflow token permissions reduces the impact of compromised repository content and avoids unnecessary deployment authority; that function is not interchangeable with CODEOWNERS review for the requested result.

D:

Review requirements enforce separation of duties and recorded authorization before merge; for the stated conditions, the deciding feature is CODEOWNERS review rather than this behavior.

Question 78

A deterministic expression for a normalized SKU is queried frequently and the DBA wants SQL Server to store its calculated values physically. What should the developer implement?

  1. Case-sensitive collation
  2. Persisted computed column
  3. Nonpersisted computed column
  4. Binary collation

Explanation

Correct Answer

B:

A PERSISTED computed column materializes the computed result for eligible expressions, with extra storage and update work.

Incorrect Answers

A:

A case-sensitive collation changes comparison and ordering behavior so character case is significant; this differs from the Persisted computed column requirement described in the scenario.

C:

A regular computed column defines an expression evaluated as needed rather than necessarily materialized on disk; the alternative cannot substitute for Persisted computed column under the given constraints.

D:

Binary collations compare encoded character values using binary rules, differing from culture-aware linguistic collations; the condition in the stem is resolved by Persisted computed column, not by this feature alone.

Question 79

The caller expects a small integer indicating an operation status rather than an arbitrary table-shaped response. What should the developer implement?

  1. OUTPUT parameter
  2. Result set from SELECT
  3. Input parameter
  4. RETURN status code

Explanation

Correct Answer

D:

A stored procedure RETURN value is an integer status mechanism, conventionally used for control or result codes.

Incorrect Answers

A:

An OUTPUT parameter returns a variable value to the caller when the invocation and parameter declaration allow output; that function is not interchangeable with RETURN status code for the requested result.

B:

Executing SELECT within a procedure returns a result set that clients can consume, unlike an integer-only return code; for the stated conditions, the deciding feature is RETURN status code rather than this behavior.

C:

An input parameter supplies a value for the procedure's execution without embedding a different literal in every call; it addresses a different step; the question calls for RETURN status code.

Question 80

Continuous delivery must apply a reviewed DACPAC's desired schema to a target Azure SQL database. What should be configured to meet this objective?

  1. Script action
  2. DeployReport action
  3. Extract action
  4. Publish action

Explanation

Correct Answer

D:

Publish compares the DACPAC model with the target and performs required schema operations, with configurable deployment safeguards.

Incorrect Answers

A:

Script produces the deployment operations as SQL text for inspection before a separately authorized execution; the alternative cannot substitute for Publish action under the given constraints.

B:

DeployReport emits an XML description of deployment changes rather than applying them directly; the condition in the stem is resolved by Publish action, not by this feature alone.

C:

Extract captures a database schema into a package suitable for inspection and project initialization, without being a full table-data export; the missing behavior is Publish action; this mechanism does something else.

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