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

Question 520

A developer wants managed Azure SQL portal charts showing top queries by resource use during a known slow period. Which feature would solve the stated problem?

  1. Azure Monitor metrics
  2. Deadlock retry policy
  3. Log Analytics diagnostic queries
  4. Read/write index tradeoff
  5. Azure SQL Query Performance Insight
  6. Query Store runtime report

Explanation

Correct Answer

E:

Query Performance Insight provides a focused view of captured SQL query performance metrics and historical trends.

Incorrect Answers

A:

Azure Monitor resource metrics expose time-series consumption and pressure signals for capacity planning, but do not replace Query Store for statement-level plan diagnosis; the alternative cannot substitute for Azure SQL Query Performance Insight under the given constraints.

B:

Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects; the condition in the stem is resolved by Azure SQL Query Performance Insight, not by this feature alone.

C:

A Log Analytics workspace stores forwarded log records and supports cross-resource KQL analysis where diagnostics are configured; the missing behavior is Azure SQL Query Performance Insight; this mechanism does something else.

D:

Each secondary index adds maintenance and storage costs, so query benefit must be balanced against write amplification; that function is not interchangeable with Azure SQL Query Performance Insight for the requested result.

F:

Query Store retains statement-level plan and runtime aggregates suitable for comparing the same query across intervals; it addresses a different step; the question calls for Azure SQL Query Performance Insight.

Question 521

Operators want a notification when SQL database CPU or failed-connection metrics cross thresholds. What is the best primary mechanism to use?

  1. Azure Monitor alerts
  2. Log Analytics workspace
  3. SQL auditing
  4. Query Store

Explanation

Correct Answer

A:

Azure Monitor evaluates selected signals against alert rules and can notify or trigger responses; it is not a row-authorization mechanism.

Incorrect Answers

B:

Log Analytics stores supported telemetry for KQL-based investigation when diagnostics are configured to send records there; this differs from the Azure Monitor alerts requirement described in the scenario.

C:

SQL auditing records configured database or server actions to an audit target, supporting investigations rather than blocking the statements themselves; the required capability here is Azure Monitor alerts, which this operation does not supply.

D:

Query Store persists execution-plan and runtime-performance information for eligible queries rather than serving as an authentication audit log; the alternative cannot substitute for Azure Monitor alerts under the given constraints.

Question 522

An aggregate view already satisfies SCHEMABINDING, SET-option, determinism, and other indexed-view prerequisites. Which index must be created first to materialize and maintain that view?

  1. Unique clustered index on the view
  2. SCHEMABINDING
  3. Projection view
  4. WITH CHECK OPTION

Explanation

Correct Answer

A:

A SQL Server indexed view is materialized by creating a unique clustered index on a qualifying schemabound view before optional additional nonclustered indexes.

Incorrect Answers

B:

SCHEMABINDING is a prerequisite for an indexed view, but the view is not materialized solely by specifying this view option.

C:

A projection view selects columns without physically materializing results or maintaining an indexed aggregate.

D:

WITH CHECK OPTION restricts certain updates through an updatable view so they remain visible; it does not create the view's first unique clustered index.

Question 523

Query Store proves a query regressed because the optimizer picked a different plan; the DBA wants to stabilize it on a validated earlier plan. Which option would you recommend?

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

Explanation

Correct Answer

D:

Query Store plan forcing can direct SQL Server toward a previously captured plan while conditions still permit its use.

Incorrect Answers

A:

Query Store runtime aggregation preserves historical performance measures across collection windows; the required capability here is Force known good query plan, which this operation does not supply.

B:

Query Performance Insight surfaces query performance trends in Azure SQL to help locate high-impact workloads without a separate profiler trace; the alternative cannot substitute for Force known good query plan under the given constraints.

C:

Query Store wait statistics can attribute supported wait categories to recorded queries, clarifying the performance bottleneck; the condition in the stem is resolved by Force known good query plan, not by this feature alone.

Question 524

A controlled migration needs to perform a prerequisite data cleanup before the main model-driven schema alterations. Which choice best meets these constraints?

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

Explanation

Correct Answer

A:

A pre-deployment script executes at the designated stage of database project publishing and must be designed safely and idempotently.

Incorrect Answers

B:

A post-deployment script runs after main schema changes and can populate approved reference data; it addresses a different step; the question calls for Pre-deployment script.

C:

Building the SQL project validates supported references against the modeled database schema; this differs from the Pre-deployment script requirement described in the scenario.

D:

Negative authorization tests verify forbidden operations fail with the actual application principal rather than assuming intended grants; the required capability here is Pre-deployment script, which this operation does not supply.

E:

DacFx's deployment plan accounts for model dependencies when generating schema changes for the target; the alternative cannot substitute for Pre-deployment script under the given constraints.

Question 525

A contract table stores only the calendar date of expiration; storing hours and minutes would be meaningless. What is the most appropriate design decision?

  1. CHAR(2)
  2. DECIMAL(12,2)
  3. DATE type
  4. VARCHAR(80)

Explanation

Correct Answer

C:

DATE represents a date without a time-of-day portion, simplifying comparisons that should ignore clock time.

Incorrect Answers

A:

CHAR stores fixed-length non-Unicode character data, with padding semantics for shorter supplied values; the condition in the stem is resolved by DATE type, not by this feature alone.

B:

DECIMAL provides exact base-ten precision and a declared scale appropriate to financial arithmetic; the missing behavior is DATE type; this mechanism does something else.

D:

VARCHAR holds variable-length non-Unicode text subject to the column's collation/code page, with less storage than NVARCHAR for many such values; for the stated conditions, the deciding feature is DATE type rather than this behavior.

Question 526

A mobile client wants only Id and Status properties from the Orders REST endpoint, not the entire entity projection. Which approach should the team select?

  1. `dab init`
  2. `$select`
  3. `$after`
  4. `$filter`
  5. `$orderby`

Explanation

Correct Answer

B:

The REST select parameter chooses output fields for the request and reduces unneeded projection.

Incorrect Answers

A:

The init command scaffolds an initial DAB runtime configuration containing the chosen data-source and endpoint settings; this differs from the `$select` requirement described in the scenario.

C:

The after parameter advances keyset-style pagination using a continuation value supplied by DAB; the alternative cannot substitute for `$select` under the given constraints.

D:

The DAB REST filter query parameter applies server-side row filtering according to the configured entity's capabilities; the condition in the stem is resolved by `$select`, not by this feature alone.

E:

The REST orderby parameter requests sorting by eligible exposed fields and directions; the missing behavior is `$select`; this mechanism does something else.

Question 527

After JSON_VALUE has extracted a model's confidence field as text, ingestion must convert '0.875' to DECIMAL(4,3) and produce NULL for malformed or overflowing strings rather than throw a conversion error. Which T-SQL function fits?

  1. ISJSON
  2. OPENJSON
  3. TRY_CONVERT
  4. JSON_VALUE

Explanation

Correct Answer

C:

TRY_CONVERT attempts an appropriate SQL type conversion and handles many invalid strings without raising a conversion error.

Incorrect Answers

A:

ISJSON validates JSON text structure so a parser does not assume every HTTPS body is well-formed JSON; that function is not interchangeable with TRY_CONVERT for the requested result.

B:

OPENJSON parses JSON structures into relational rows and can project explicitly typed columns through WITH; for the stated conditions, the deciding feature is TRY_CONVERT rather than this behavior.

D:

JSON_VALUE retrieves scalar JSON path values from the returned document for SQL processing; this differs from the TRY_CONVERT requirement described in the scenario.

Question 528

A window function must define which event is earlier or later inside each device's time series, independent of the final display order. Which option would you recommend?

  1. ORDER BY EventTime in OVER clause
  2. SUM() OVER(PARTITION BY ...)
  3. PARTITION BY CustomerId
  4. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  5. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Explanation

Correct Answer

A:

ORDER BY inside OVER establishes the function's logical row ordering; the outer query's ORDER BY controls presentation separately.

Incorrect Answers

B:

A windowed SUM computes totals across partitions while retaining individual input rows in the output; the condition in the stem is resolved by ORDER BY EventTime in OVER clause, not by this feature alone.

C:

PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the missing behavior is ORDER BY EventTime in OVER clause; this mechanism does something else.

D:

This ROWS window frame contains at most the current row and two prior ordered rows for moving calculations; that function is not interchangeable with ORDER BY EventTime in OVER clause for the requested result.

E:

A ROWS frame defined this way forms a growing row-based window and gives predictable cumulative aggregation with a deterministic ORDER BY; for the stated conditions, the deciding feature is ORDER BY EventTime in OVER clause rather than this behavior.

Question 529

A reporting principal requires reads of all present and future appropriate objects in the Reporting schema without individual table grants. What is the most appropriate design decision?

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

Explanation

Correct Answer

B:

Schema-level SELECT grants apply to eligible objects within the specified schema scope, reducing per-object grant administration.

Incorrect Answers

A:

EXECUTE allows a principal to invoke a stored procedure and can be paired with safe module ownership or execution-context design; it addresses a different step; the question calls for GRANT SELECT on schema.

C:

REVOKE removes a previously granted or denied permission entry and allows remaining permissions to determine effective access; the required capability here is GRANT SELECT on schema, which this operation does not supply.

D:

DENY explicitly prevents the named permission, subject to SQL Server's permission-resolution behavior and exceptions; the alternative cannot substitute for GRANT SELECT on schema under the given constraints.

Question 530

A table allows many inactive historical aliases but requires active aliases to be unique among active rows only. What should the developer implement?

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

Explanation

Correct Answer

E:

A filtered unique index enforces uniqueness within a predicate-defined subset, unlike a table-wide unique constraint.

Incorrect Answers

A:

A composite key enforces uniqueness over a column combination, matching a many-to-many association's natural identity; the missing behavior is Filtered unique index; this mechanism does something else.

B:

A foreign key may reference an eligible UNIQUE candidate key, preserving referential integrity without depending on the primary key specifically; that function is not interchangeable with Filtered unique index for the requested result.

C:

A filtered index indexes just the matching subset and can avoid maintaining entries for the dominant non-escalated rows; for the stated conditions, the deciding feature is Filtered unique index rather than this behavior.

D:

A surrogate key decouples row identity from mutable business data and can support compact joins and stable references; it addresses a different step; the question calls for Filtered unique index.

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