Question 190
Analysts need to query data held in an external source through a relational definition without initially loading all source rows locally. What is the best primary mechanism to use?
- Graph node and edge tables
- External table
- Updatable ledger table
- System-versioned temporal table
Explanation
Correct Answer
B:An external table supplies database metadata for accessing external data; it does not turn the remote data into ordinary local stored rows.
Incorrect Answers
A:SQL graph node and edge tables represent entities and relationships, enabling MATCH-based queries over connected records; this differs from the External table requirement described in the scenario.
C:An updatable ledger table retains audit history with digest-based verification of data integrity rather than merely exposing prior row versions; the alternative cannot substitute for External table under the given constraints.
D:Temporal tables maintain current and history rows with system-maintained validity periods, allowing FOR SYSTEM_TIME point-in-time queries; the condition in the stem is resolved by External table, not by this feature alone.
Question 191
Inserts or updates through an updatable filtered view must not result in rows disappearing from that same view's defined predicate. Which choice best meets these constraints?
- WITH CHECK OPTION
- SCHEMABINDING
- Indexed view
- Projection view
Explanation
Correct Answer
A:WITH CHECK OPTION requires changes made through the view to remain visible under the view's WHERE condition when supported.
Incorrect Answers
B:SCHEMABINDING binds an eligible view to referenced objects and prevents incompatible schema changes until the binding is addressed; for the stated conditions, the deciding feature is WITH CHECK OPTION rather than this behavior.
C:An indexed view materializes indexed results once the required unique clustered index and schema prerequisites are met; it addresses a different step; the question calls for WITH CHECK OPTION.
D:A view can project selected columns and simplify controlled access when base-table permissions are restricted appropriately; this differs from the WITH CHECK OPTION requirement described in the scenario.
Question 192
A model service returns a JSON object containing an array of choices, and SQL must extract structured rows from it. What is the best primary mechanism to use?
- ISJSON
- TRY_CONVERT
- JSON_VALUE
- OPENJSON
Explanation
Correct Answer
D:OPENJSON parses JSON structures into relational rows and can project explicitly typed columns through WITH.
Incorrect Answers
A:ISJSON validates JSON text structure so a parser does not assume every HTTPS body is well-formed JSON; the alternative cannot substitute for OPENJSON under the given constraints.
B:TRY_CONVERT attempts an appropriate SQL type conversion and handles many invalid strings without raising a conversion error; the condition in the stem is resolved by OPENJSON, not by this feature alone.
C:JSON_VALUE retrieves scalar JSON path values from the returned document for SQL processing; the missing behavior is OPENJSON; this mechanism does something else.
Question 193
A checkout transaction must atomically reserve stock and write a payment marker, while a separate edit screen must detect stale updates without long-held locks. Which TWO distinct mechanisms fit? Choose TWO.
- Optimistic rowversion check
- Memory grant spill review
- Atomic transaction
- Deadlock retry policy
- Pessimistic transaction locking
Explanation
Correct Answers
A:Comparing the original ROWVERSION token in UPDATE's WHERE clause allows a zero-row result to signal a stale write; used in combination with Atomic transaction, it handles a separate requirement of this combined workflow.
C:A database transaction groups multiple modifications into one commit-or-rollback unit, preserving all-or-nothing behavior; used in combination with Optimistic rowversion check, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:Spill warnings and memory-grant data in an execution plan reveal operators writing intermediate work to tempdb; this differs from the Optimistic rowversion check and Atomic transaction requirement described in the scenario.
D:Retrying the complete idempotent transaction after a bounded delay can address transient deadlock victims without hiding genuine application defects; the alternative cannot substitute for Optimistic rowversion check and Atomic transaction under the given constraints.
E:Appropriate transaction isolation and update locks can serialize conflicting modifications during the critical reservation step; the condition in the stem is resolved by Optimistic rowversion check and Atomic transaction, not by this feature alone.
Question 194
After heavy page splitting, measured index fragmentation and page density justify physical index maintenance rather than adding new logical predicates. Which SQL capability is the most direct fit?
- Index maintenance on fragmentation
- Included index columns
- Filtered index predicate
- Composite index key order
Explanation
Correct Answer
A:Reorganizing or rebuilding appropriate indexes addresses physical fragmentation and may improve access efficiency, but should follow measured need.
Incorrect Answers
B:INCLUDE columns can satisfy output projections without expanding the seek key, often making a nonclustered index covering; that function is not interchangeable with Index maintenance on fragmentation for the requested result.
C:A WHERE filter on a nonclustered index narrows the indexed rowset to qualifying values when query predicates are compatible; for the stated conditions, the deciding feature is Index maintenance on fragmentation rather than this behavior.
D:Leading index-key columns determine which predicates can support a seek; key order matters for equality and range access; it addresses a different step; the question calls for Index maintenance on fragmentation.
Question 195
A dashboard needs a running sum from the first ordered row through the current row, counting physical rows rather than groups of equal sort values. What should be configured to meet this objective?
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
- ORDER BY EventTime in OVER clause
- PARTITION BY CustomerId
Explanation
Correct Answer
A:A ROWS frame defined this way forms a growing row-based window and gives predictable cumulative aggregation with a deterministic ORDER BY.
Incorrect Answers
B:This ROWS window frame contains at most the current row and two prior ordered rows for moving calculations; the alternative cannot substitute for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW under the given constraints.
C:ORDER BY inside OVER establishes the function's logical row ordering; the outer query's ORDER BY controls presentation separately; the condition in the stem is resolved by ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not by this feature alone.
D:PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the missing behavior is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; this mechanism does something else.
Question 196
A payment procedure must catch a runtime failure, determine whether its transaction can still commit, and propagate an appropriate error after handling it. Which THREE T-SQL mechanisms directly support these requirements? Choose THREE.
- Multi-statement table-valued function
- SET XACT_ABORT ON
- AFTER trigger
- XACT_STATE()
- TRY...CATCH
- THROW
Explanation
Correct Answers
D:XACT_STATE reports transaction state, distinguishing a doomed transaction from one that can still commit; used in combination with THROW, TRY...CATCH, it handles a separate requirement of this combined workflow.
E:TRY...CATCH structures error handling into protected and handler blocks for catchable execution errors; used in combination with THROW, XACT_STATE(), it handles a separate requirement of this combined workflow.
F:THROW explicitly raises or rethrows T-SQL errors and supports propagating failures through calling code; used in combination with TRY...CATCH, XACT_STATE(), it handles a separate requirement of this combined workflow.
Incorrect Answers
A:A multi-statement TVF declares a table return variable and populates it with multiple statements, subject to different optimizer behavior than inline TVFs; for the stated conditions, the deciding feature is XACT_STATE() and TRY...CATCH and THROW rather than this behavior.
B:XACT_ABORT ON makes specified runtime failures abort and roll back a transaction, affecting the error-handling behavior of the batch; it addresses a different step; the question calls for XACT_STATE() and TRY...CATCH and THROW.
C:An AFTER DML trigger executes in response to an eligible change and can inspect the inserted and deleted logical rowsets; this differs from the XACT_STATE() and TRY...CATCH and THROW requirement described in the scenario.
Question 197
A developer finished a set of T-SQL schema edits and needs discussion, automated checks, and a recorded merge decision. What should be configured to meet this objective?
- Merge conflict resolution
- Protected main branch
- Pull request
- Feature branch
Explanation
Correct Answer
C:A pull request provides a controlled review and merge workflow for database-source changes.
Incorrect Answers
A:Resolving the conflicting source edits produces one coherent schema definition before a merged database project can be compiled; the condition in the stem is resolved by Pull request, not by this feature alone.
B:Branch protection or rulesets impose merge policies, required checks, and review conditions in the repository; the missing behavior is Pull request; this mechanism does something else.
D:A feature branch provides a separate line of changes for review and integration without directly changing the release branch; for the stated conditions, the deciding feature is Pull request rather than this behavior.
Question 198
A development team needs a portable export of a SQL database schema plus table data for a data-bearing import. What should the developer implement?
- SDK-style SQL database project
- SqlPackage Publish
- DACPAC
- BACPAC
Explanation
Correct Answer
D:A BACPAC is a logical export containing schema and data, unlike a schema-only DACPAC.
Incorrect Answers
A:An SDK-style SQL project provides declarative schema files and cross-platform build tooling supported by Microsoft.Build.Sql; this differs from the BACPAC requirement described in the scenario.
B:SqlPackage Publish compares the source package with the destination and executes a generated deployment plan when authorized; the required capability here is BACPAC, which this operation does not supply.
C:A DACPAC packages the database schema model for DacFx comparison and deployment; it is not primarily a full data export; the alternative cannot substitute for BACPAC under the given constraints.
Question 199
A query filters on the same date column used as a table's partition key, and the optimizer should skip unrelated partitions. Which option satisfies the desired behavior most directly?
- Partition elimination
- RANGE RIGHT boundary definition
- Index partition alignment
- RANGE LEFT boundary definition
Explanation
Correct Answer
A:Partition elimination reduces scanned partitions when predicates allow the optimizer to identify the relevant partition ranges.
Incorrect Answers
B:RANGE RIGHT assigns the exact boundary value to the partition on its right, matching common month-start lower-bound layouts; for the stated conditions, the deciding feature is Partition elimination rather than this behavior.
C:Partition-aligned indexes and compatible structures are essential for metadata-only switch operations in supported configurations; it addresses a different step; the question calls for Partition elimination.
D:RANGE LEFT associates a boundary value with its left partition; this changes boundary equality membership; this differs from the Partition elimination requirement described in the scenario.
Question 200
An operation performs a transaction, writes several tables, and returns informational rows without needing to appear in a FROM clause. Which approach should the team select?
- Scalar function
- Stored procedure
- Multi-statement table-valued function
- Inline table-valued function
Explanation
Correct Answer
B:A stored procedure is an executable unit suited to procedural database operations; it is not directly a relational table expression.
Incorrect Answers
A:A scalar UDF returns a single scalar value, although calling patterns and implementation determine its performance; the alternative cannot substitute for Stored procedure under the given constraints.
C:A multi-statement TVF declares a table return variable and populates it with multiple statements, subject to different optimizer behavior than inline TVFs; the missing behavior is Stored procedure; this mechanism does something else.
D:An inline TVF returns the result of a single SELECT definition and is commonly optimized similarly to a parameterized view; that function is not interchangeable with Stored procedure for the requested result.