Question 310
An analyst needs identifiers present in both a billing query and a support-ticket query without manually joining their tables. What is the right solution for this specific requirement?
- UNION ALL
- UNION
- INTERSECT
- EXCEPT
Explanation
Correct Answer
C:INTERSECT returns distinct compatible rows appearing in both input result sets.
Incorrect Answers
A:UNION ALL concatenates the result sets without removing duplicate rows, unlike UNION's distinct behavior; this differs from the INTERSECT requirement described in the scenario.
B:UNION performs set combination with duplicate elimination across compatible query projections; the required capability here is INTERSECT, which this operation does not supply.
D:EXCEPT returns distinct rows from the first input that are not found in the second input; the condition in the stem is resolved by INTERSECT, not by this feature alone.
Question 311
A customer API stores JSON. A dashboard needs one nested scalar postal code while an ETL step must turn a JSON item array into typed rows. Which TWO SQL techniques serve the distinct tasks? Choose TWO.
- JSON_VALUE expression
- JSON index
- Native JSON data type
- Aligned partitioned index
- OPENJSON with a WITH clause
Explanation
Correct Answers
A:JSON_VALUE extracts a scalar at the requested JSON path, rather than returning an object or a table of properties; used in combination with OPENJSON with a WITH clause, it handles a separate requirement of this combined workflow.
E:OPENJSON shreds JSON arrays or objects into rows, while WITH can define the output columns and their SQL data types; used in combination with JSON_VALUE expression, it handles a separate requirement of this combined workflow.
Incorrect Answers
B:A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; for the stated conditions, the deciding feature is JSON_VALUE expression and OPENJSON with a WITH clause rather than this behavior.
C:The native json type is intended for JSON documents and supports JSON-oriented operations on platforms where it is available; support must be checked per engine; it addresses a different step; the question calls for JSON_VALUE expression and OPENJSON with a WITH clause.
D:Aligning indexes to the table's partitioning supports partition maintenance operations whose prerequisites require matching structure and boundaries; this differs from the JSON_VALUE expression and OPENJSON with a WITH clause requirement described in the scenario.
Question 312
Each InvoiceLine must reference an existing Invoice, and a line with a nonexistent parent identifier must be rejected. Which choice best meets these constraints?
- FOREIGN KEY constraint
- DECIMAL(19,4)
- CHECK constraint
- DEFAULT constraint
- PRIMARY KEY constraint
Explanation
Correct Answer
A:A foreign key checks referential integrity between a referencing column and a candidate key in another or the same table.
Incorrect Answers
B: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 FOREIGN KEY constraint, not by this feature alone.
C:A CHECK constraint can compare columns from a row in one Boolean condition and rejects non-NULL expressions evaluated as FALSE, regardless of the inserting application; the missing behavior is FOREIGN KEY constraint; this mechanism does something else.
D:A DEFAULT expression supplies a value for a column when INSERT omits it or uses DEFAULT; it does not automatically override explicit NULL or an explicitly supplied value; that function is not interchangeable with FOREIGN KEY constraint for the requested result.
E:A primary key declares the table's principal identifier and enforces uniqueness plus nonnullability through an associated index; for the stated conditions, the deciding feature is FOREIGN KEY constraint rather than this behavior.
Question 313
An analyst can read most fields of a Customer table but must not select a specific sensitive TaxIdentifier column. Which mechanism addresses the requirement?
- Masked response
- Encrypted sensitive column
- Column-level permissions
- RLS tenant filtering
Explanation
Correct Answer
C:Column-level GRANT or DENY policies can scope SELECT privileges to designated columns where supported.
Incorrect Answers
A:Dynamic Data Masking changes result visibility according to effective permissions while leaving encrypted-at-rest requirements separate; it addresses a different step; the question calls for Column-level permissions.
B:Always Encrypted places decryption with approved clients and key stores rather than relying only on engine-side masking; this differs from the Column-level permissions requirement described in the scenario.
D:A database RLS policy supplies the filtering boundary across application query paths using supported security predicates; the alternative cannot substitute for Column-level permissions under the given constraints.
Question 314
A security team wants Azure SQL diagnostic logs forwarded to a selected Log Analytics destination for retention and search. What is the most appropriate design decision?
- Alert on suspicious pattern
- Diagnostic setting export
- Review stored audit events
- Audit specification
Explanation
Correct Answer
B:Azure resource diagnostic settings route supported log and metric categories to configured destinations and require appropriate permissions.
Incorrect Answers
A:A metric or log alert rule evaluates collected telemetry and can initiate actions when a defined condition is met; the missing behavior is Diagnostic setting export; this mechanism does something else.
C:Examining retained audit records supports forensic reconstruction; it does not itself prevent future unauthorized requests; for the stated conditions, the deciding feature is Diagnostic setting export rather than this behavior.
D:SQL audit specifications define audited action groups or object actions to be captured by the audit mechanism; it addresses a different step; the question calls for Diagnostic setting export.
Question 315
A nested batch wants a named rollback point for part of its work while preserving the possibility of retaining earlier work if the transaction remains committable. Which feature would solve the stated problem?
- BEGIN TRY / BEGIN CATCH
- SAVE TRANSACTION
- XACT_STATE()
- THROW without arguments
Explanation
Correct Answer
B:SAVE TRANSACTION establishes a savepoint inside an active transaction, allowing partial rollback when the transaction's state permits it.
Incorrect Answers
A:TRY...CATCH provides a structured handler for many run-time T-SQL errors, allowing recovery code to run in the CATCH branch; the required capability here is SAVE TRANSACTION, which this operation does not supply.
C:XACT_STATE() returns -1 for an uncommittable active transaction, permitting only rollback and read operations until that transaction is ended; the condition in the stem is resolved by SAVE TRANSACTION, not by this feature alone.
D:THROW without arguments inside CATCH rethrows the caught exception, preserving its original error details rather than inventing a replacement exception; the missing behavior is SAVE TRANSACTION; this mechanism does something else.
Question 316
A data-changing routine should automatically terminate and roll back its transaction for many runtime statement errors rather than continuing. Which option satisfies the desired behavior most directly?
- SET XACT_ABORT ON
- TRY...CATCH
- XACT_STATE()
- THROW
Explanation
Correct Answer
A:XACT_ABORT ON makes specified runtime failures abort and roll back a transaction, affecting the error-handling behavior of the batch.
Incorrect Answers
B:TRY...CATCH structures error handling into protected and handler blocks for catchable execution errors; it addresses a different step; the question calls for SET XACT_ABORT ON.
C:XACT_STATE reports transaction state, distinguishing a doomed transaction from one that can still commit; this differs from the SET XACT_ABORT ON requirement described in the scenario.
D:THROW explicitly raises or rethrows T-SQL errors and supports propagating failures through calling code; the required capability here is SET XACT_ABORT ON, which this operation does not supply.
Question 317
A financial application must make tampering with historical database changes cryptographically evident to later reviewers. What is the best primary mechanism to use?
- System-versioned temporal table
- JSON index
- Updatable ledger table
- External table
- Graph node and edge tables
Explanation
Correct Answer
C:An updatable ledger table retains audit history with digest-based verification of data integrity rather than merely exposing prior row versions.
Incorrect Answers
A: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 Updatable ledger table, not by this feature alone.
B:A JSON index accelerates supported searches over JSON paths; it does not replace validation or parsing logic for arbitrary payload shapes; the missing behavior is Updatable ledger table; this mechanism does something else.
D:An external table supplies database metadata for accessing external data; it does not turn the remote data into ordinary local stored rows; for the stated conditions, the deciding feature is Updatable ledger table rather than this behavior.
E:SQL graph node and edge tables represent entities and relationships, enabling MATCH-based queries over connected records; it addresses a different step; the question calls for Updatable ledger table.
Question 318
A nightly UPDATE modifies the whole history table in one transaction, causing broad blocking; it can be divided into safe smaller batches. Which mechanism addresses the requirement?
- Consistent lock ordering
- Reduce lock footprint
- Find open transaction owner
- Enable appropriate row versioning
Explanation
Correct Answer
B:Appropriate batching reduces simultaneous locks and transaction duration while preserving data correctness.
Incorrect Answers
A:A shared acquisition order reduces circular-wait opportunities across concurrent operations; this differs from the Reduce lock footprint requirement described in the scenario.
C:Tracing blockers to a specific active transaction identifies the lock holder and avoids random query cancellation; the alternative cannot substitute for Reduce lock footprint under the given constraints.
D:READ_COMMITTED_SNAPSHOT changes supported read behavior to row versions and can reduce reader/writer blocking; the condition in the stem is resolved by Reduce lock footprint, not by this feature alone.
Question 319
Multiple applications need a reusable named SELECT projection that always reads the current underlying tables without independently copying the data. What is the right solution for this specific requirement?
- Scalar user-defined function
- View
- Table-valued function
- Stored procedure
Explanation
Correct Answer
B:A regular view encapsulates a SELECT definition as a database object and generally resolves its rows from underlying tables when queried.
Incorrect Answers
A:A scalar function produces a single value and can be invoked in supported expressions, with performance considerations depending on its implementation; that function is not interchangeable with View for the requested result.
C:A table-valued function returns a relational result, and an inline version can expose an optimizer-friendly SELECT expression; it addresses a different step; the question calls for View.
D:A stored procedure can encapsulate multi-statement DML and transactions, accept input parameters, and report status or rowsets to application callers; this differs from the View requirement described in the scenario.
Question 320
A supported modern T-SQL pipeline must reject strings that fail a regular-expression pattern and also normalize recurring whitespace through regex replacement. Which TWO functions are needed? Choose TWO.
- REGEXP_COUNT
- REGEXP_LIKE
- REGEXP_REPLACE
- REGEXP_SUBSTR
- REGEXP_INSTR
Explanation
Correct Answers
B:REGEXP_LIKE evaluates whether input text matches a regular expression and returns a predicate result; used in combination with REGEXP_REPLACE, it handles a separate requirement of this combined workflow.
C:REGEXP_REPLACE substitutes regex-matched substrings and returns the transformed text on supported platforms; used in combination with REGEXP_LIKE, it handles a separate requirement of this combined workflow.
Incorrect Answers
A:REGEXP_COUNT counts matches of a regular expression in a source string; the alternative cannot substitute for REGEXP_LIKE and REGEXP_REPLACE under the given constraints.
D:REGEXP_SUBSTR extracts text matched by a regular expression rather than returning a Boolean match test; that function is not interchangeable with REGEXP_LIKE and REGEXP_REPLACE for the requested result.
E:REGEXP_INSTR returns a match position for a supplied regular-expression pattern; for the stated conditions, the deciding feature is REGEXP_LIKE and REGEXP_REPLACE rather than this behavior.