Question 170
Issue 1: An API retries a timed-out purchase request and creates two identical orders because the first transaction actually committed. Issue 2: A developer begins a transaction but has code paths that return early without COMMIT or ROLLBACK, leaving locks held. Issue 3: A reporting query reads uncommitted inventory adjustments and publishes stock values later rolled back by another session. Which complete response addresses the three issues in order?
- Add an idempotency key enforced by a unique database rule and return the existing result on safe retries; Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; and choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs.
- Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; Guarantee transaction closure across success and error paths and verify the open transaction state in tests; and choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs.
- Add an idempotency key enforced by a unique database rule and return the existing result on safe retries; Guarantee transaction closure across success and error paths and verify the open transaction state in tests; and choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs.
- Add an idempotency key enforced by a unique database rule and return the existing result on safe retries; Guarantee transaction closure across success and error paths and verify the open transaction state in tests; and Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling.
Explanation
Correct Answer
C:For issue 1, retries must distinguish a completed operation from a new one to avoid duplicate side effects. For issue 2, unfinished transactions retain resources and may block unrelated work. For issue 3, uncommitted data can disappear after rollback and should not become authoritative reporting output.
Incorrect Answers
A:Add an idempotency key enforced by a unique database rule and return the existing result on safe retries; Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; and choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs: unfinished transactions retain resources and may block unrelated work.
B:Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; Guarantee transaction closure across success and error paths and verify the open transaction state in tests; and choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs: retries must distinguish a completed operation from a new one to avoid duplicate side effects.
D:Add an idempotency key enforced by a unique database rule and return the existing result on safe retries; Guarantee transaction closure across success and error paths and verify the open transaction state in tests; and Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling: uncommitted data can disappear after rollback and should not become authoritative reporting output.
Question 171
Issue 1: A coding assistant needs to inspect a dev SQL schema through an MCP server, but an untrusted public endpoint requests administrative credentials. Issue 2: An AI chat session lists several available MCP tools, including one that can modify production data, for a user authorized only to analyze schema. Issue 3: A Copilot agent receives a schema-inspection tool result containing a directive to reveal a secret in the next chat message. Which complete response addresses the three issues in order?
- Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; and Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy.
- Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use; configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; and Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy.
- Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use; and Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy.
- Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; and Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use.
Explanation
Correct Answer
A:For issue 1, MCP tool access crosses a security boundary and must not grant untrusted services database administration. For issue 2, tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording. For issue 3, database and tool output must not be promoted into instructions for privileged assistant behavior.
Incorrect Answers
B:Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use; configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; and Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy: mCP tool access crosses a security boundary and must not grant untrusted services database administration.
C:Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use; and Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy: tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording.
D:Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; and Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use: database and tool output must not be promoted into instructions for privileged assistant behavior.
Question 172
An application team must address both issues before release. A SQL view extracts a scalar field from a JSON payload but occasionally receives invalid JSON and crashes the report. A normalized order table must expand an incoming JSON array into rows for validation and insertion into child records. What is the most appropriate plan for this situation?
- Use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion; do not validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads.
- Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property.
- Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
- Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
Explanation
Correct Answer
C:First, semi-structured input needs validation and predictable failure behavior before scalar values enter authoritative reports. Second, OPENJSON converts array elements into tabular data suitable for relational constraints and joins.
Incorrect Answers
A:Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads.
B:Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property: oPENJSON converts array elements into tabular data suitable for relational constraints and joins.
D:Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion: semi-structured input needs validation and predictable failure behavior before scalar values enter authoritative reports.
Question 173
A finance service performs a validated multi-step booking operation and needs one callable database interface with transactional error handling. Which recommendation best resolves this issue?
- Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks.
- Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements.
- Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
- Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
Explanation
Correct Answer
D:Procedures encapsulate database operations and allow callers to execute a controlled multi-step unit.
Incorrect Answers
A:Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks: evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks: triggers run with table writes and must correctly handle sets of inserted and deleted rows; Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
B:Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements: rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements: t-SQL DML triggers are statement-level, so pseudo-tables may hold many rows.
C:Parameterize the procedure's dynamic SQL and grant the caller only required execution rights: parameterize the procedure's dynamic SQL and grant the caller only required execution rights: data parameters must not become executable SQL text and procedure access should follow least privilege.
Question 174
An application team must address both issues before release. A configured Data API builder entity includes internal audit columns in the REST response even though external users need only public product fields. An administrator changes the DAB entity mapping name but clients receive 404 responses because they still call the old REST route. Which response best addresses these needs?
- Update and validate the entity's published REST path and coordinate a versioned client migration; do not restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role.
- Restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; Move sensitive connection settings to protected deployment configuration and scope DAB database privileges.
- Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; update and validate the entity's published REST path and coordinate a versioned client migration.
- Restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; update and validate the entity's published REST path and coordinate a versioned client migration.
Explanation
Correct Answer
D:First, API projections should expose only explicitly authorized data rather than mirroring an entire table. Second, API route names form external contracts and must match the deployed configuration.
Incorrect Answers
A:Update and validate the entity's published REST path and coordinate a versioned client migration; do not restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role: restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role.
B:Restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; Move sensitive connection settings to protected deployment configuration and scope DAB database privileges: aPI route names form external contracts and must match the deployed configuration.
C:Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; update and validate the entity's published REST path and coordinate a versioned client migration: aPI projections should expose only explicitly authorized data rather than mirroring an entire table.
Question 175
Issue 1: A billing service treats customer reference numbers as contiguous, but automatic identity values occasionally skip numbers after a rolled-back transaction. Issue 2: A developer compares a nullable region column to NULL with an equals sign and reports that the filter never finds missing regions. Issue 3: A uniqueness rule must reject two active rows for the same account while allowing archived versions of the account to coexist. Which complete response addresses the three issues in order?
- Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter; and Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior.
- Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter; and Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior.
- Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter; and Increase the column length after validating application contracts and storage impact instead of silently shortening the source value.
- Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required; Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; and Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior.
Explanation
Correct Answer
B:For issue 1, identity allocation may leave gaps after failed operations, so it does not guarantee consecutive business numbering. For issue 2, NULL denotes unknown or absent data and does not behave like an ordinary comparable value. For issue 3, a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
Incorrect Answers
A:Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter; and Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior: identity allocation may leave gaps after failed operations, so it does not guarantee consecutive business numbering.
C:Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter; and Increase the column length after validating application contracts and storage impact instead of silently shortening the source value: a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
D:Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required; Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; and Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior: nULL denotes unknown or absent data and does not behave like an ordinary comparable value.
Question 176
An SQL service raises occasional deadlocks but the alert system triggers only on complete application outages. A diagnostic pipeline includes full customer query parameters in distributed tracing that many engineers can access. Which course of action addresses the stated issues?
- Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; redact protected payload fields and restrict telemetry access while preserving correlation identifiers.
- Redact protected payload fields and restrict telemetry access while preserving correlation identifiers; do not define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing.
- Define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans.
- Define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; redact protected payload fields and restrict telemetry access while preserving correlation identifiers.
Explanation
Correct Answer
D:First, early error and performance alerts can reveal degradations before total service loss. Second, useful observability should minimize sensitive data captured in logs and traces.
Incorrect Answers
A:Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; redact protected payload fields and restrict telemetry access while preserving correlation identifiers: early error and performance alerts can reveal degradations before total service loss.
B:Define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing.
C:Define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans: useful observability should minimize sensitive data captured in logs and traces.
Question 177
Issue 1: Two procedures update customer and invoice tables in opposite order and repeatedly deadlock during busy periods. Issue 2: A team proposes fixing deadlocks only by extending every command timeout from thirty seconds to ten minutes. Issue 3: An application treats SQL deadlock victim error as a permanent invalid request and silently discards a legitimate business transaction. Which complete response addresses the three issues in order?
- Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; Shorten the blocking transaction and move interactive waiting outside its lock-holding interval; and implement bounded retry with idempotency safeguards for recognized transient deadlock failures.
- Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts; and Shorten the blocking transaction and move interactive waiting outside its lock-holding interval.
- Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts; and implement bounded retry with idempotency safeguards for recognized transient deadlock failures.
- Shorten the blocking transaction and move interactive waiting outside its lock-holding interval; investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts; and implement bounded retry with idempotency safeguards for recognized transient deadlock failures.
Explanation
Correct Answer
C:For issue 1, deadlocks arise when sessions acquire resources in incompatible cycles; consistent ordering reduces the pattern. For issue 2, longer timeouts do not remove cyclic lock dependencies or guarantee a deadlock victim will succeed. For issue 3, a deadlock victim may succeed on retry, but duplicate side effects must be prevented.
Incorrect Answers
A:Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; Shorten the blocking transaction and move interactive waiting outside its lock-holding interval; and implement bounded retry with idempotency safeguards for recognized transient deadlock failures: longer timeouts do not remove cyclic lock dependencies or guarantee a deadlock victim will succeed.
B:Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts; and Shorten the blocking transaction and move interactive waiting outside its lock-holding interval: a deadlock victim may succeed on retry, but duplicate side effects must be prevented.
D:Shorten the blocking transaction and move interactive waiting outside its lock-holding interval; investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts; and implement bounded retry with idempotency safeguards for recognized transient deadlock failures: deadlocks arise when sessions acquire resources in incompatible cycles; consistent ordering reduces the pattern.
Question 178
An application team must address both issues before release. A finance query repeats several nested subqueries and the developer needs one named intermediate result reused in a single statement. A dashboard needs the latest order per customer without collapsing away the associated status and order ID columns. What is the most appropriate plan for this situation?
- Use a common table expression to structure the query while validating the actual plan for repeated references; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
- Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one.
- Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; do not use a common table expression to structure the query while validating the actual plan for repeated references.
- Use a common table expression to structure the query while validating the actual plan for repeated references; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one.
Explanation
Correct Answer
D:First, a CTE provides readable statement-scoped query structure rather than automatically materializing data. Second, window ranking selects a complete latest row per group without an unsafe nonaggregated GROUP BY.
Incorrect Answers
A:Use a common table expression to structure the query while validating the actual plan for repeated references; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance: window ranking selects a complete latest row per group without an unsafe nonaggregated GROUP BY.
B:Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one: a CTE provides readable statement-scoped query structure rather than automatically materializing data.
C:Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; do not use a common table expression to structure the query while validating the actual plan for repeated references: use a common table expression to structure the query while validating the actual plan for repeated references.
Question 179
A ticket-creation API omits creation timestamps and produces null values, even though the database should assign the current time. A purchase-order table has duplicate invoice numbers per supplier, but the same number is legitimately used by different suppliers. Which course of action addresses the stated issues?
- Define the SKU column as NOT NULL and validate existing null records before applying the change; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates.
- Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates.
- Enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; do not configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
- Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted; create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers.
Explanation
Correct Answer
B:First, database defaults fill omitted values consistently without requiring every client to supply the same timestamp logic. Second, composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label.
Incorrect Answers
A:Define the SKU column as NOT NULL and validate existing null records before applying the change; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates: database defaults fill omitted values consistently without requiring every client to supply the same timestamp logic.
C:Enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; do not configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted: configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
D:Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted; create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers: composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label.
Question 180
An Azure-hosted application connects to Azure SQL using a shared password embedded in deployment scripts. A function app can obtain a token but receives database permission denied when it queries an approved stored procedure. Which response best addresses these needs?
- Use an appropriate managed identity and Microsoft Entra authentication with scoped database permissions; grant the managed identity the required database principal and narrowly scoped execution rights after verifying authentication.
- Grant the managed identity the required database principal and narrowly scoped execution rights after verifying authentication; do not use an appropriate managed identity and Microsoft Entra authentication with scoped database permissions.
- Replace the human identity with an approved workload identity and assign minimum operational roles; grant the managed identity the required database principal and narrowly scoped execution rights after verifying authentication.
- Use an appropriate managed identity and Microsoft Entra authentication with scoped database permissions; Adopt a supported passwordless or centrally rotated secret design and test renewal behavior before expiration.
Explanation
Correct Answer
A:First, managed identity avoids storing a reusable service password while retaining identity-based access control. Second, successful token authentication does not by itself grant database object authorization.
Incorrect Answers
B:Grant the managed identity the required database principal and narrowly scoped execution rights after verifying authentication; do not use an appropriate managed identity and Microsoft Entra authentication with scoped database permissions: use an appropriate managed identity and Microsoft Entra authentication with scoped database permissions.
C:Replace the human identity with an approved workload identity and assign minimum operational roles; grant the managed identity the required database principal and narrowly scoped execution rights after verifying authentication: managed identity avoids storing a reusable service password while retaining identity-based access control.
D:Use an appropriate managed identity and Microsoft Entra authentication with scoped database permissions; Adopt a supported passwordless or centrally rotated secret design and test renewal behavior before expiration: successful token authentication does not by itself grant database object authorization.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests