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?
- Azure Monitor metrics
- Deadlock retry policy
- Log Analytics diagnostic queries
- Read/write index tradeoff
- Azure SQL Query Performance Insight
- 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?
- Azure Monitor alerts
- Log Analytics workspace
- SQL auditing
- 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?
- Unique clustered index on the view
- SCHEMABINDING
- Projection view
- 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?
- Query Store runtime statistics
- Query Performance Insight
- Query Store wait statistics
- 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?
- Pre-deployment script
- Post-deployment script
- Build dependency validation
- Permission-negative test
- 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?
- CHAR(2)
- DECIMAL(12,2)
- DATE type
- 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?
- `dab init`
- `$select`
- `$after`
- `$filter`
- `$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?
- ISJSON
- OPENJSON
- TRY_CONVERT
- 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?
- ORDER BY EventTime in OVER clause
- SUM() OVER(PARTITION BY ...)
- PARTITION BY CustomerId
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
- 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?
- GRANT EXECUTE
- GRANT SELECT on schema
- REVOKE permission
- 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?
- Composite natural key
- Foreign key to a candidate key
- Filtered index for sparse predicate
- Surrogate integer key
- 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.