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

Question 390

A shared Orders table must automatically show each tenant only its own rows even when applications submit broad SELECT statements. Which SQL capability is the most direct fit?

  1. Always Encrypted
  2. Dynamic Data Masking
  3. Transparent Data Encryption
  4. Row-Level Security

Explanation

Correct Answer

D:

Row-Level Security applies predicate logic inside the database to filter or block access to rows according to security context.

Incorrect Answers

A:

Always Encrypted protects selected column values with client-side cryptography and keys outside ordinary server access, subject to query-operation limitations; this differs from the Row-Level Security requirement described in the scenario.

B:

Dynamic Data Masking alters query-result presentation for non-exempt users; it is not encryption or a strong isolation boundary for untrusted querying; the required capability here is Row-Level Security, which this operation does not supply.

C:

Transparent Data Encryption encrypts database storage at rest and decrypts pages for authorized engine operations; it does not hide plaintext from the engine; the alternative cannot substitute for Row-Level Security under the given constraints.

Question 391

A production DACPAC release requires a single reviewed artifact across environments, a mandatory approval before publishing, and a preview of proposed potentially destructive SQL operations. Which THREE release practices should be combined? Choose THREE.

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

Explanation

Correct Answers

A:

A generated deployment script or report exposes DacFx's intended changes without applying them during review; used in combination with Build once, deploy same artifact, Environment approval gate, it handles a separate requirement of this combined workflow.

B:

Approval gates block promotion to sensitive environments until designated reviewers approve the deployment job; used in combination with Build once, deploy same artifact, Dry-run deployment script, it handles a separate requirement of this combined workflow.

E:

Promoting one immutable DACPAC across environments preserves a traceable model artifact while target comparisons remain environment-specific; used in combination with Dry-run deployment script, Environment approval gate, it handles a separate requirement of this combined workflow.

Incorrect Answers

C:

Post-deployment verification tests operational outcomes after schema changes, not merely tool exit codes; it addresses a different step; the question calls for Dry-run deployment script and Environment approval gate and Build once, deploy same artifact.

D:

A smoke test checks critical post-deployment operations in the target environment and complements static compilation; this differs from the Dry-run deployment script and Environment approval gate and Build once, deploy same artifact requirement described in the scenario.

F:

A narrowly scoped deployment identity limits damage and requires only appropriate SQL and Azure resource permissions for the target workflow; the alternative cannot substitute for Dry-run deployment script and Environment approval gate and Build once, deploy same artifact under the given constraints.

Question 392

Monthly partitions should put records at the first instant of a month into the newer partition rather than the previous one. What is the best primary mechanism to use?

  1. RANGE RIGHT boundary definition
  2. Index partition alignment
  3. RANGE LEFT boundary definition
  4. DATE type
  5. Partition elimination
  6. JSON_ARRAYAGG with ORDER BY

Explanation

Correct Answer

A:

RANGE RIGHT assigns the exact boundary value to the partition on its right, matching common month-start lower-bound layouts.

Incorrect Answers

B:

Partition-aligned indexes and compatible structures are essential for metadata-only switch operations in supported configurations; the condition in the stem is resolved by RANGE RIGHT boundary definition, not by this feature alone.

C:

RANGE LEFT associates a boundary value with its left partition; this changes boundary equality membership; the missing behavior is RANGE RIGHT boundary definition; this mechanism does something else.

D:

DATE represents a date without a time-of-day portion, simplifying comparisons that should ignore clock time; that function is not interchangeable with RANGE RIGHT boundary definition for the requested result.

E:

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 RIGHT boundary definition rather than this behavior.

F:

JSON_ARRAYAGG with an ORDER BY clause controls element ordering within its generated JSON array on supported SQL versions; ordinary aggregation without ordering does not guarantee that order; it addresses a different step; the question calls for RANGE RIGHT boundary definition.

Question 393

A claim record needs a default status on insert and, separately, a set-based audit record after a successful modification. Which TWO SQL mechanisms are appropriate? Choose TWO.

  1. INSTEAD OF trigger
  2. DEFAULT constraint
  3. CHECK constraint
  4. AFTER trigger
  5. THROW

Explanation

Correct Answers

B:

A DEFAULT constraint supplies an omitted insertion value and is simpler than an event-triggered procedural program; used in combination with AFTER trigger, it handles a separate requirement of this combined workflow.

D:

An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; used in combination with DEFAULT constraint, it handles a separate requirement of this combined workflow.

Incorrect Answers

A:

An INSTEAD OF trigger substitutes custom DML processing for the triggering operation, often making certain views writable; it addresses a different step; the question calls for DEFAULT constraint and AFTER trigger.

C:

A CHECK constraint is a declarative integrity rule enforced by the engine when qualifying rows are inserted or updated; the required capability here is DEFAULT constraint and AFTER trigger, which this operation does not supply.

E:

THROW explicitly raises or rethrows T-SQL errors and supports propagating failures through calling code; the condition in the stem is resolved by DEFAULT constraint and AFTER trigger, not by this feature alone.

Question 394

A role has inherited SELECT rights, but a particular sensitive view should explicitly reject access for one principal under standard permission rules. Which mechanism addresses the requirement?

  1. GRANT SELECT on schema
  2. DENY permission
  3. GRANT EXECUTE
  4. REVOKE permission

Explanation

Correct Answer

B:

DENY explicitly prevents the named permission, subject to SQL Server's permission-resolution behavior and exceptions.

Incorrect Answers

A:

Schema-level SELECT grants apply to eligible objects within the specified schema scope, reducing per-object grant administration; the missing behavior is DENY permission; this mechanism does something else.

C:

EXECUTE allows a principal to invoke a stored procedure and can be paired with safe module ownership or execution-context design; for the stated conditions, the deciding feature is DENY permission rather than this behavior.

D:

REVOKE removes a previously granted or denied permission entry and allows remaining permissions to determine effective access; it addresses a different step; the question calls for DENY permission.

Question 395

An UPDATE audit trigger needs the previous column values of all updated rows before overwriting them. Which mechanism addresses the requirement?

  1. INSTEAD OF DELETE trigger
  2. AFTER UPDATE trigger
  3. deleted logical table
  4. inserted logical table

Explanation

Correct Answer

C:

The deleted logical table contains prior versions of updated or deleted rows, supporting set-based old-versus-new comparisons.

Incorrect Answers

A:

An INSTEAD OF DELETE trigger can replace a requested deletion with custom SQL logic, including soft-delete behavior; the required capability here is deleted logical table, which this operation does not supply.

B:

An AFTER UPDATE trigger fires in response to UPDATE operations and can inspect affected rowsets, without being a scheduler; the alternative cannot substitute for deleted logical table under the given constraints.

D:

The inserted logical table contains newly inserted or updated row versions available to a DML trigger and should be handled set-wise; the missing behavior is deleted logical table; this mechanism does something else.

Question 396

An external model definition must refer to a named SQL object storing endpoint credential metadata rather than embed authorization values in individual inference statements. Which SQL object fills that role?

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

Explanation

Correct Answer

A:

A database-scoped credential stores or references endpoint authentication data subject to permission and endpoint matching rules.

Incorrect Answers

B:

The embeddings model type indicates that the external definition produces vector representations from supported text input; it addresses a different step; the question calls for DATABASE SCOPED CREDENTIAL.

C:

LOCATION records the target service URI in the external model definition, with provider formatting and TLS constraints; this differs from the DATABASE SCOPED CREDENTIAL requirement described in the scenario.

D:

API_FORMAT identifies the provider-specific request and response contract used by the external model integration; the required capability here is DATABASE SCOPED CREDENTIAL, which this operation does not supply.

Question 397

Field technicians may see only their assigned territory's service rows even when they use the same query as other technicians. Which approach should the team select?

  1. Database permission GRANT
  2. Row-level security policy
  3. CREATE SECURITY POLICY
  4. View-based projection
  5. Endpoint role authorization

Explanation

Correct Answer

B:

An RLS security policy applies row predicates at the database tier rather than depending on every client to implement a filter.

Incorrect Answers

A:

An object-scoped GRANT provides the minimum necessary database operation at the specified object boundary; the condition in the stem is resolved by Row-level security policy, not by this feature alone.

C:

CREATE SECURITY POLICY associates filter and block predicates with a table and controls whether the policy is enabled; that function is not interchangeable with Row-level security policy for the requested result.

D:

Granting SELECT on a narrow view and restricting base-table access limits the visible projection when the execution and ownership model permit it; for the stated conditions, the deciding feature is Row-level security policy rather than this behavior.

E:

Gateway authorization maps authenticated principals to permitted API operations and should complement database enforcement; it addresses a different step; the question calls for Row-level security policy.

Question 398

For each campaign, the analyst wants the number of unique customers who responded, not the number of response events. Which option would you recommend?

  1. SUM(...) OVER
  2. HAVING
  3. COUNT(DISTINCT CustomerId)
  4. GROUPING SETS

Explanation

Correct Answer

C:

COUNT(DISTINCT ...) counts distinct non-NULL values in a group or qualifying result set.

Incorrect Answers

A:

A window aggregate computes over partitions while maintaining input-row granularity rather than grouping detail away; this differs from the COUNT(DISTINCT CustomerId) requirement described in the scenario.

B:

HAVING filters aggregated groups after grouping, unlike a row-level WHERE filter; the required capability here is COUNT(DISTINCT CustomerId), which this operation does not supply.

D:

GROUPING SETS explicitly requests several grouping levels in a single grouped SELECT; the condition in the stem is resolved by COUNT(DISTINCT CustomerId), not by this feature alone.

Question 399

A synchronization platform needs lightweight changed primary keys for one consumer, but a compliance consumer needs full row-change operations for replay. Which TWO SQL change mechanisms fit the different consumers? Choose TWO.

  1. Change Tracking
  2. Change event streaming
  3. Change Data Capture
  4. Azure Functions SQL input binding
  5. Azure Functions SQL trigger

Explanation

Correct Answers

A:

Change Tracking maintains lightweight change versions and keys instead of full historical before-and-after values; used in combination with Change Data Capture, it handles a separate requirement of this combined workflow.

C:

CDC captures data changes in change tables for later consumption, including row images and operation information subject to configuration; used in combination with Change Tracking, it handles a separate requirement of this combined workflow.

Incorrect Answers

B:

Change event streaming sends supported database change events to a configured streaming destination, with platform-specific prerequisites; for the stated conditions, the deciding feature is Change Tracking and Change Data Capture rather than this behavior.

D:

A SQL input binding can supply configured database query results to a supported Azure Function invocation; this differs from the Change Tracking and Change Data Capture requirement described in the scenario.

E:

The SQL trigger binding can drive Azure Functions from supported SQL change-tracking signals, subject to its trigger configuration; the required capability here is Change Tracking and Change Data Capture, which this operation does not supply.

Question 400

A reporting system reads large ranges of amount columns to compute group totals and seeks column compression and batch processing. Which action is justified by these conditions?

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

Explanation

Correct Answer

C:

Columnstore storage is optimized for large analytical scans and compression of similar column values.

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; the alternative cannot substitute for Clustered columnstore index for aggregations under the given constraints.

B:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; the condition in the stem is resolved by Clustered columnstore index for aggregations, not by this feature alone.

D:

An index with HeaderId as a leading key can accelerate lookups and joins from headers to matching details; that function is not interchangeable with Clustered columnstore index for aggregations for the requested result.

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