Question 340
An application team must address both issues before release. An external model API responds intermittently with throttling, and SQL code retries indefinitely while holding an open business transaction. A response-processing procedure assumes every HTTP 200 body contains the expected model text even when the provider returns a different schema version. What is the most appropriate plan for this situation?
- Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; do not Bound and back off retries outside long-running critical transactions and handle model errors explicitly.
- Bound and back off retries outside long-running critical transactions and handle model errors explicitly; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
- Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
- Bound and back off retries outside long-running critical transactions and handle model errors explicitly; use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
Explanation
Correct Answer
B:First, external inference availability should not monopolize database locks or create uncontrolled request storms. Second, a successful transport response does not guarantee an unchanged application-level payload contract.
Incorrect Answers
A:Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; do not Bound and back off retries outside long-running critical transactions and handle model errors explicitly: bound and back off retries outside long-running critical transactions and handle model errors explicitly.
C:Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content: external inference availability should not monopolize database locks or create uncontrolled request storms.
D:Bound and back off retries outside long-running critical transactions and handle model errors explicitly; use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling: a successful transport response does not guarantee an unchanged application-level payload contract.
Question 341
A pipeline approves all database changes if the SQL project compiles, even when a change removes an audit trigger. A source-controlled reference table adds a new status code but the application's enum rejects it in production. Which course of action addresses the stated issues?
- Add policy-based diff review and security tests for critical database objects before deployment approval; Coordinate data contract changes with application validation and deploy in a compatible order.
- Run dependency analysis and compatibility tests against all known consumers before publishing the new view; Coordinate data contract changes with application validation and deploy in a compatible order.
- Add policy-based diff review and security tests for critical database objects before deployment approval; use an expand-and-contract schema migration with temporary compatibility and monitored client cutover.
- Coordinate data contract changes with application validation and deploy in a compatible order; do not add policy-based diff review and security tests for critical database objects before deployment approval.
Explanation
Correct Answer
A:First, compilation does not prove compliance controls remain present after a schema update. Second, static data is part of the interface between database and application code.
Incorrect Answers
B:Run dependency analysis and compatibility tests against all known consumers before publishing the new view; Coordinate data contract changes with application validation and deploy in a compatible order: compilation does not prove compliance controls remain present after a schema update.
C:Add policy-based diff review and security tests for critical database objects before deployment approval; use an expand-and-contract schema migration with temporary compatibility and monitored client cutover: static data is part of the interface between database and application code.
D:Add policy-based diff review and security tests for critical database objects before deployment approval.
Question 342
A deployment pipeline publishes a build created from unreviewed branch commits instead of the signed-off release revision. A team wants to deploy the same tested SQL package to staging and production but rebuilds it separately with different dependencies. Which response best addresses these needs?
- Promote the same immutable build artifact through validated environments and use environment-specific secure configuration; do not Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
- Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment; add the specified environment approval gate before the deployment step and retain its audit record.
- Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment; Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
- Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes; Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
Explanation
Correct Answer
C:First, release traceability requires deploying the approved source revision rather than an arbitrary latest build. Second, promoting one artifact reduces untracked differences between tested and released database code.
Incorrect Answers
A:Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
B:Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment; add the specified environment approval gate before the deployment step and retain its audit record: promoting one artifact reduces untracked differences between tested and released database code.
D:Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes; Promote the same immutable build artifact through validated environments and use environment-specific secure configuration: release traceability requires deploying the approved source revision rather than an arbitrary latest build.
Question 343
An AI-enabled database project needs two corrections. A developer optimizes a parameter-sensitive stored query with a hint derived from one tiny test case and it becomes slow for large tenants. A team uses an index hint to force a seek without checking table growth and suddenly the query is slower than the optimizer's alternative. What is the most appropriate plan for this situation?
- Test representative data and remove or revise unjustified hints based on actual plan and latency measurements; do not compare representative parameter distributions and review parameter-sensitive plan behavior before pinning hints.
- Compare representative parameter distributions and review parameter-sensitive plan behavior before pinning hints; test representative data and remove or revise unjustified hints based on actual plan and latency measurements.
- Use a sargable range predicate on the original timestamp column and validate the query plan; test representative data and remove or revise unjustified hints based on actual plan and latency measurements.
- Compare representative parameter distributions and review parameter-sensitive plan behavior before pinning hints; evaluate a set-based UPDATE with an appropriate predicate, test transaction size and inspect execution plans.
Explanation
Correct Answer
B:First, one cached plan may not serve skewed data distributions equally well. Second, optimizer hints can freeze an unsuitable access path as workload conditions change.
Incorrect Answers
A:Compare representative parameter distributions and review parameter-sensitive plan behavior before pinning hints.
C:Use a sargable range predicate on the original timestamp column and validate the query plan; test representative data and remove or revise unjustified hints based on actual plan and latency measurements: one cached plan may not serve skewed data distributions equally well.
D:Compare representative parameter distributions and review parameter-sensitive plan behavior before pinning hints; evaluate a set-based UPDATE with an appropriate predicate, test transaction size and inspect execution plans: optimizer hints can freeze an unsuitable access path as workload conditions change.
Question 344
A database architect reviews two implementation risks. Two order-processing sessions both read an item as available and each attempts to reserve the last unit, overselling inventory. A business requests repeatable values throughout one multi-statement read transaction while other sessions are actively updating the same records. Which course of action addresses the stated issues?
- Keep external calls outside the critical database transaction and commit promptly after the required atomic work; choose an appropriate repeatable or snapshot-based isolation level and test blocking and version-store costs.
- Choose an appropriate repeatable or snapshot-based isolation level and test blocking and version-store costs; do not enforce an atomic conditional update or suitable locking isolation around stock reservation.
- Enforce an atomic conditional update or suitable locking isolation around stock reservation; Avoid READ UNCOMMITTED for authoritative totals and choose an appropriate committed or versioned isolation model.
- Enforce an atomic conditional update or suitable locking isolation around stock reservation; choose an appropriate repeatable or snapshot-based isolation level and test blocking and version-store costs.
Explanation
Correct Answer
D:First, competing reads and writes require coordinated concurrency control to prevent lost updates and overselling. Second, isolation level selection determines whether successive reads can observe intervening committed changes.
Incorrect Answers
A:Keep external calls outside the critical database transaction and commit promptly after the required atomic work; choose an appropriate repeatable or snapshot-based isolation level and test blocking and version-store costs: competing reads and writes require coordinated concurrency control to prevent lost updates and overselling.
B:Enforce an atomic conditional update or suitable locking isolation around stock reservation.
C:Enforce an atomic conditional update or suitable locking isolation around stock reservation; Avoid READ UNCOMMITTED for authoritative totals and choose an appropriate committed or versioned isolation model: isolation level selection determines whether successive reads can observe intervening committed changes.
Question 345
An assistant composes SQL query results into a final JSON response but fails when the model wraps the JSON in Markdown or adds commentary. Which recommendation best resolves this issue?
- Invalidate derived answers and remove the withdrawn source from retrieval and caches under a documented lifecycle process.
- Keep transactional writes behind authenticated rule checks and explicit approved authorization instead of executing generated prose.
- Parse and validate the response against an explicit schema and handle invalid output with bounded recovery.
- Validate citations against actual retrieval IDs and reject unsupported source claims before returning the response.
Explanation
Correct Answer
C:Downstream applications need validated data contracts, not an assumption that fluent text is machine-readable.
Incorrect Answers
A:Invalidate derived answers and remove the withdrawn source from retrieval and caches under a documented lifecycle process: source removal must propagate to generated artifacts and caches, not only the original table.
B:Keep transactional writes behind authenticated rule checks and explicit approved authorization instead of executing generated prose: generated recommendations do not establish identity, eligibility or permission to perform a financial operation.
D:Validate citations against actual retrieval IDs and reject unsupported source claims before returning the response: a citation label is only trustworthy if it resolves to the evidence actually supplied to the model.
Question 346
An application team must address both issues before release. An analyst filters documents by product category after performing global vector search, losing relevant category matches buried below the global top results. A customer-facing search returns identical repetitive passages from multiple copies of the same handbook page. What is the most appropriate plan for this situation?
- Evaluate supported prefiltering or appropriately scoped candidate retrieval before ranking category-specific neighbors; limit candidate count with measured top-k, filtering and reranking while checking answer evidence recall.
- Deduplicate source chunks and apply version-aware grouping before assembling search context; do not evaluate supported prefiltering or appropriately scoped candidate retrieval before ranking category-specific neighbors.
- Inspect the query plan and use the appropriate indexed full-text predicates before increasing database size; Deduplicate source chunks and apply version-aware grouping before assembling search context.
- Evaluate supported prefiltering or appropriately scoped candidate retrieval before ranking category-specific neighbors; Deduplicate source chunks and apply version-aware grouping before assembling search context.
Explanation
Correct Answer
D:First, filter placement can change recall when relevant items are outside an unfiltered top-k set. Second, repeated near-identical evidence wastes model tokens and can bias generated answers toward duplicates.
Incorrect Answers
A:Evaluate supported prefiltering or appropriately scoped candidate retrieval before ranking category-specific neighbors; limit candidate count with measured top-k, filtering and reranking while checking answer evidence recall: repeated near-identical evidence wastes model tokens and can bias generated answers toward duplicates.
B:Evaluate supported prefiltering or appropriately scoped candidate retrieval before ranking category-specific neighbors.
C:Inspect the query plan and use the appropriate indexed full-text predicates before increasing database size; Deduplicate source chunks and apply version-aware grouping before assembling search context: filter placement can change recall when relevant items are outside an unfiltered top-k set.
Question 347
Issue 1: A finance query repeats several nested subqueries and the developer needs one named intermediate result reused in a single statement. Issue 2: An analyst needs each transaction's running total within its account ordered by posting date, not one aggregate per account. Issue 3: A user wants to show all tied top-performing stores, but the report uses ROW_NUMBER and excludes equal-score ties. Which complete response addresses the three issues in order?
- Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
- 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; and use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one.
- 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; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
- 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; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
Explanation
Correct Answer
D:For issue 1, a CTE provides readable statement-scoped query structure rather than automatically materializing data. For issue 2, windowed aggregation retains individual rows while calculating a cumulative value. For issue 3, ranking functions differ in how they handle ties and skipped rank values.
Incorrect Answers
A:Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically: a CTE provides readable statement-scoped query structure rather than automatically materializing data.
B: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; and use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one: ranking functions differ in how they handle ties and skipped rank values.
C: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; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically: windowed aggregation retains individual rows while calculating a cumulative value.
Question 348
A purchase-order table has duplicate invoice numbers per supplier, but the same number is legitimately used by different suppliers. Which action best addresses the database requirement?
- Enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates.
- Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers.
- Define the SKU column as NOT NULL and validate existing null records before applying the change.
- Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
Explanation
Correct Answer
A:Composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label.
Incorrect Answers
B:Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers: a sequence generates numbers independently of table identity insertion, with gaps possible under concurrency or rollback.
C:Define the SKU column as NOT NULL and validate existing null records before applying the change: define the SKU column as NOT NULL and validate existing null records before applying the change: nullability is a schema integrity rule; adding a display placeholder does not make a missing business identifier valid.
D: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: database defaults fill omitted values consistently without requiring every client to supply the same timestamp logic.
Question 349
An online order service shows long request waits, and DMV evidence reveals one session keeps an exclusive row lock while awaiting user input. What is the most defensible technical decision?
- 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.
- Implement bounded retry with idempotency safeguards for recognized transient deadlock failures.
Explanation
Correct Answer
A:Locks retained through human interaction unnecessarily serialize otherwise independent requests.
Incorrect Answers
B:Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies: deadlocks arise when sessions acquire resources in incompatible cycles; consistent ordering reduces the pattern.
C:Investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts: longer timeouts do not remove cyclic lock dependencies or guarantee a deadlock victim will succeed.
D:Implement bounded retry with idempotency safeguards for recognized transient deadlock failures: a deadlock victim may succeed on retry, but duplicate side effects must be prevented.
Question 350
An application treats SQL deadlock victim error as a permanent invalid request and silently discards a legitimate business transaction. Which recommendation best resolves this issue?
- 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.
- Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies.
Explanation
Correct Answer
A:A deadlock victim may succeed on retry, but duplicate side effects must be prevented.
Incorrect Answers
B:Shorten the blocking transaction and move interactive waiting outside its lock-holding interval: locks retained through human interaction unnecessarily serialize otherwise independent requests.
C:Investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts: investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts: longer timeouts do not remove cyclic lock dependencies or guarantee a deadlock victim will succeed.
D:Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies: standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies: deadlocks arise when sessions acquire resources in incompatible cycles; consistent ordering reduces the pattern.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests