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.
Question 201
Release automation has a built DACPAC and needs to reconcile the target database's schema with that model. Which database mechanism should be chosen?
- `dotnet build` of SQL project
- DACPAC
- SqlPackage Publish
- SDK-style SQL database project
- BACPAC
Explanation
Correct Answer
C:SqlPackage Publish compares the source package with the destination and executes a generated deployment plan when authorized.
Incorrect Answers
A:Building an SDK-style SQL project validates its schema model and emits a DACPAC when the build succeeds; it addresses a different step; the question calls for SqlPackage Publish.
B:A DACPAC packages the database schema model for DacFx comparison and deployment; it is not primarily a full data export; this differs from the SqlPackage Publish requirement described in the scenario.
D:An SDK-style SQL project provides declarative schema files and cross-platform build tooling supported by Microsoft.Build.Sql; the alternative cannot substitute for SqlPackage Publish under the given constraints.
E:A BACPAC is a logical export containing schema and data, unlike a schema-only DACPAC; the condition in the stem is resolved by SqlPackage Publish, not by this feature alone.
Question 202
A privacy deletion removes a source document and the search corpus must stop returning its old vector representation. What is the most suitable implementation?
- Update-on-change processing
- Delete propagation
- Idempotent embedding upsert
- Versioned embedding column
Explanation
Correct Answer
B:A deletion-aware sync process removes or invalidates vectors and searchable text tied to the erased source record.
Incorrect Answers
A:Change-driven maintenance processes affected rows and preserves a checkpoint for incremental vector refresh; the missing behavior is Delete propagation; this mechanism does something else.
C:Upsert by a stable document/chunk/version key allows retries without duplicating records, supporting at-least-once processing; for the stated conditions, the deciding feature is Delete propagation rather than this behavior.
D:Storing embedding-model identity or version with each vector permits controlled migration and query-to-corpus compatibility checks; it addresses a different step; the question calls for Delete propagation.
Question 203
A result set must show each invoice line and, without collapsing rows, the total amount for that invoice. What is the right solution for this specific requirement?
- FIRST_VALUE()
- LEAD()
- PARTITION BY CustomerId
- LAG()
- SUM() OVER(PARTITION BY ...)
Explanation
Correct Answer
E:A windowed SUM computes totals across partitions while retaining individual input rows in the output.
Incorrect Answers
A:FIRST_VALUE returns the expression from the first row of the applicable ordered window frame; the required capability here is SUM() OVER(PARTITION BY ...), which this operation does not supply.
B:LEAD reads a following row's value in the specified window partition and order; the alternative cannot substitute for SUM() OVER(PARTITION BY ...) under the given constraints.
C:PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the condition in the stem is resolved by SUM() OVER(PARTITION BY ...), not by this feature alone.
D:LAG accesses a prior row's expression value within the ordered window without a self-join; the missing behavior is SUM() OVER(PARTITION BY ...); this mechanism does something else.
Question 204
A query's duration increased and the team wants historical wait-category evidence associated with the stored query. What should be configured to meet this objective?
- Query Performance Insight
- Query Store wait statistics
- Query Store runtime statistics
- Force known good query plan
Explanation
Correct Answer
B:Query Store wait statistics can attribute supported wait categories to recorded queries, clarifying the performance bottleneck.
Incorrect Answers
A:Query Performance Insight surfaces query performance trends in Azure SQL to help locate high-impact workloads without a separate profiler trace; for the stated conditions, the deciding feature is Query Store wait statistics rather than this behavior.
C:Query Store runtime aggregation preserves historical performance measures across collection windows; this differs from the Query Store wait statistics requirement described in the scenario.
D:Query Store plan forcing can direct SQL Server toward a previously captured plan while conditions still permit its use; the required capability here is Query Store wait statistics, which this operation does not supply.
Question 205
A quarterly report should show the earliest observed price alongside every product's price in that same quarter. Which SQL capability is the most direct fit?
- FIRST_VALUE()
- PARTITION BY CustomerId
- LAG()
- LEAD()
- SUM() OVER(PARTITION BY ...)
Explanation
Correct Answer
A:FIRST_VALUE returns the expression from the first row of the applicable ordered window frame.
Incorrect Answers
B:PARTITION BY divides window computation into independent groups while leaving the original row cardinality intact; the missing behavior is FIRST_VALUE(); this mechanism does something else.
C:LAG accesses a prior row's expression value within the ordered window without a self-join; that function is not interchangeable with FIRST_VALUE() for the requested result.
D:LEAD reads a following row's value in the specified window partition and order; for the stated conditions, the deciding feature is FIRST_VALUE() rather than this behavior.
E:A windowed SUM computes totals across partitions while retaining individual input rows in the output; it addresses a different step; the question calls for FIRST_VALUE().
Question 206
A new database feature requires a known status-code row, and the pipeline must verify the row exists after deployment. Which option would you recommend?
- Post-deployment seed script
- Idempotent MERGE or upsert logic
- Version-controlled reference CSV
- Integration test for referential values
Explanation
Correct Answer
D:A targeted integration or smoke test validates that required reference records are present and usable by related constraints.
Incorrect Answers
A:Controlled post-deployment scripts can maintain reference values idempotently as part of the published database workflow; this differs from the Integration test for referential values requirement described in the scenario.
B:An idempotent upsert checks logical keys and applies necessary changes while avoiding duplicate reference rows across releases; the required capability here is Integration test for referential values, which this operation does not supply.
C:Keeping static values in source control preserves review history, though a deployment step is still needed to apply them; the alternative cannot substitute for Integration test for referential values under the given constraints.
Question 207
A schema migration may involve potentially destructive transformations and the team needs a recoverable database state before approval. What is the best primary mechanism to use?
- Expand-and-contract migration
- Explicit destructive-change review
- Backward-compatible additive change
- Pre-deployment backup
Explanation
Correct Answer
D:An appropriate backup or recovery strategy protects recoverability when deployment scripts cannot simply reverse data loss.
Incorrect Answers
A:Expand-and-contract introduces new schema, migrates users and data, then removes old objects only after dependents move; that function is not interchangeable with Pre-deployment backup for the requested result.
B:A destructive-change gate reviews planned drops and data transformations rather than assuming model synchronization is risk-free; for the stated conditions, the deciding feature is Pre-deployment backup rather than this behavior.
C:An additive compatible migration can allow old and new application versions to coexist during staged cutover; it addresses a different step; the question calls for Pre-deployment backup.
Question 208
A league table must keep equal scores tied but number the following distinct score with no skipped positions. What should the developer implement?
- RANK()
- NTILE(4)
- DENSE_RANK()
- ROW_NUMBER()
Explanation
Correct Answer
C:DENSE_RANK numbers distinct ordered values without gaps after ties within the partition.
Incorrect Answers
A:RANK assigns equal rank to ties and advances the next rank by the number of preceding ranked rows; the alternative cannot substitute for DENSE_RANK() under the given constraints.
B:NTILE divides a sorted window partition into a requested number of numbered buckets with sizes differing by at most one row; the condition in the stem is resolved by DENSE_RANK(), not by this feature alone.
D:ROW_NUMBER supplies a unique sequence number within each window partition according to the specified ORDER BY and tie-breaking rules; that function is not interchangeable with DENSE_RANK() for the requested result.
Question 209
A regulated table must capture old values during UPDATE and DELETE without application-written change logs. What is the most appropriate design decision?
- Graph edge records
- System-versioned history retention
- UNIQUE alternate identifier
- FOREIGN KEY validation
- Ledger digests
- Memory-optimized OLTP row
Explanation
Correct Answer
B:System-versioned temporal storage records prior row versions in a history table while maintaining a current table.
Incorrect Answers
A:Edge tables encode graph relationships between node instances and support relationship-centric queries; it addresses a different step; the question calls for System-versioned history retention.
C:A UNIQUE constraint or index enforces uniqueness for an alternate candidate key without replacing the table's primary identifier; the required capability here is System-versioned history retention, which this operation does not supply.
D:Foreign-key constraints protect relational references independently of the application that writes data; the alternative cannot substitute for System-versioned history retention under the given constraints.
E:Ledger digests help establish tamper-evidence for ledger content; retaining them securely is key to later verification; the condition in the stem is resolved by System-versioned history retention, not by this feature alone.
F:Memory-optimized tables use a specialized engine and must follow feature and schema restrictions rather than conventional disk-based indexing; the missing behavior is System-versioned history retention; this mechanism does something else.
Question 210
A supported solution needs permitted comparisons or transformations that ordinary client-side encrypted columns cannot handle efficiently. What is the best primary mechanism to use?
- Always Encrypted with secure enclaves
- Randomized Always Encrypted
- TDE key hierarchy
- Deterministic Always Encrypted
Explanation
Correct Answer
A:Secure enclaves can enable selected richer operations on encrypted data inside a protected execution environment, with deployment prerequisites.
Incorrect Answers
B:Randomized encryption adds nondeterminism to ciphertext, improving value-pattern privacy at the cost of ordinary equality-query support; that function is not interchangeable with Always Encrypted with secure enclaves for the requested result.
C:TDE protects the database encryption key through configured key hierarchy, so key backup and availability matter for restored data; for the stated conditions, the deciding feature is Always Encrypted with secure enclaves rather than this behavior.
D:Deterministic column encryption yields the same ciphertext for equal plaintext with the same key and supports restricted equality comparisons; it addresses a different step; the question calls for Always Encrypted with secure enclaves.