Question 570
A billing service treats customer reference numbers as contiguous, but automatic identity values occasionally skip numbers after a rolled-back transaction. A uniqueness rule must reject two active rows for the same account while allowing archived versions of the account to coexist. Which response best addresses these needs?
- Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior; do not 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; 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; 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.
Explanation
Correct Answer
C:First, identity allocation may leave gaps after failed operations, so it does not guarantee consecutive business numbering. Second, a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
Incorrect Answers
A:Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior; do not Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required: treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required.
B:Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; 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.
D: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: a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
Question 571
An AI-enabled database project needs two corrections. A testing environment receives a full production customer dataset, including personal numbers, simply to validate a new stored procedure. A cloud SQL system is moved to a new region without confirming contractual residency obligations and data-flow boundaries. What is the most appropriate plan for this situation?
- Use synthetic or masked test data with approved exceptions and enforce environment access separation; Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery.
- Include derived embeddings and search indexes in the deletion and retention workflow and verify removal; assess region, service processing and backup locations against policy before executing the migration.
- Use synthetic or masked test data with approved exceptions and enforce environment access separation; assess region, service processing and backup locations against policy before executing the migration.
- Assess region, service processing and backup locations against policy before executing the migration; do not use synthetic or masked test data with approved exceptions and enforce environment access separation.
Explanation
Correct Answer
C:First, development testing usually does not require copying complete protected production records. Second, deployment geography can alter legal and contractual handling of protected records beyond database performance.
Incorrect Answers
A:Use synthetic or masked test data with approved exceptions and enforce environment access separation; Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery: deployment geography can alter legal and contractual handling of protected records beyond database performance.
B:Include derived embeddings and search indexes in the deletion and retention workflow and verify removal; assess region, service processing and backup locations against policy before executing the migration: development testing usually does not require copying complete protected production records.
D:Use synthetic or masked test data with approved exceptions and enforce environment access separation.
Question 572
A database architect reviews two implementation risks. A reporting view exposes a secret token column that is not needed by analysts, despite the analysts having a legitimate read requirement. A developer modifies a shared stored procedure's parameter semantics without versioning, breaking existing clients when a deployment completes. Which course of action addresses the stated issues?
- Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; apply compatibility tests and a controlled version or migration path for the procedure contract.
- Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior.
- Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy; apply compatibility tests and a controlled version or migration path for the procedure contract.
- Apply compatibility tests and a controlled version or migration path for the procedure contract; do not Redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
Explanation
Correct Answer
A:First, column minimization should be enforced at the data access boundary rather than left to dashboard display choices. Second, a database programmable-object signature is an API contract that downstream clients may depend upon.
Incorrect Answers
B:Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior: a database programmable-object signature is an API contract that downstream clients may depend upon.
C:Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy; apply compatibility tests and a controlled version or migration path for the procedure contract: column minimization should be enforced at the data access boundary rather than left to dashboard display choices.
D:Redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
Question 573
A database architect reviews two implementation risks. A code review finds that a SQL function writes to an audit table as a side effect, contrary to the platform's permitted function semantics. An error handler logs only 'failed' and hides the SQL error number and transaction context needed to diagnose deadlocks. Which response best addresses these needs?
- Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented; Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details.
- Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes; Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details.
- Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented; Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior.
- Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details; do not Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented.
Explanation
Correct Answer
A:First, T-SQL user-defined functions have restrictions on database state changes and must not be treated as arbitrary procedures. Second, structured error evidence is necessary for debugging without falsely reporting successful completion.
Incorrect Answers
B:Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes; Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details: t-SQL user-defined functions have restrictions on database state changes and must not be treated as arbitrary procedures.
C:Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented; Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior: structured error evidence is necessary for debugging without falsely reporting successful completion.
D:Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details; do not Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented: move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented.
Question 574
A developer compares vectors produced by two different embedding models as though their coordinates were directly interchangeable. What is the most defensible technical decision?
- Select an eligible replacement model and perform a controlled full re-embedding and index cutover.
- Evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment.
- Regenerate compatible embeddings and align vector dimensions and index schema with the selected model.
- Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration.
Explanation
Correct Answer
D:Coordinates from unrelated embedding models do not generally share meaningful geometric semantics.
Incorrect Answers
A:Select an eligible replacement model and perform a controlled full re-embedding and index cutover: select an eligible replacement model and perform a controlled full re-embedding and index cutover: mixing incompatible model outputs can degrade search even when dimensions happen to match.
B:Evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment: evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment: embeddings encode similarity, not factual correctness or authorization to answer.
C:Regenerate compatible embeddings and align vector dimensions and index schema with the selected model: regenerate compatible embeddings and align vector dimensions and index schema with the selected model: similarity calculations require consistent vector dimensions and compatible embedding representations; Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration.
Question 575
An application team must address both issues before release. A reporting job reads another session's uncommitted account adjustments and publishes a total that disappears after rollback. Two order-processing sessions both read an item as available and each attempts to reserve the last unit, overselling inventory. Which course of action addresses the stated issues?
- Enforce an atomic conditional update or suitable locking isolation around stock reservation; do not Avoid READ UNCOMMITTED for authoritative totals and choose an appropriate committed or versioned isolation model.
- Keep external calls outside the critical database transaction and commit promptly after the required atomic work; 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; choose an appropriate repeatable or snapshot-based isolation level and test blocking and version-store costs.
- 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.
Explanation
Correct Answer
D:First, dirty reads expose provisional changes that may never become durable business facts. Second, competing reads and writes require coordinated concurrency control to prevent lost updates and overselling.
Incorrect Answers
A:Enforce an atomic conditional update or suitable locking isolation around stock reservation; do not Avoid READ UNCOMMITTED for authoritative totals and choose an appropriate committed or versioned isolation model: avoid READ UNCOMMITTED for authoritative totals and choose an appropriate committed or versioned isolation model.
B:Keep external calls outside the critical database transaction and commit promptly after the required atomic work; enforce an atomic conditional update or suitable locking isolation around stock reservation: dirty reads expose provisional changes that may never become durable business facts.
C:Avoid READ UNCOMMITTED for authoritative totals and choose an appropriate committed or versioned isolation model; 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.
Question 576
A database architect reviews two implementation risks. A team turns on auditing but sends the logs to a destination writable by the same administrators whose actions are being reviewed. An administrator enables verbose SQL audit events and the system unexpectedly captures secret parameter values in unrestricted logs. Which response best addresses these needs?
- Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection; do not Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering.
- Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering; configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access.
- Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring; review captured fields and apply data minimization, access restrictions and retention controls before continuing collection.
- Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering; review captured fields and apply data minimization, access restrictions and retention controls before continuing collection.
Explanation
Correct Answer
D:First, audit evidence is less trustworthy when subjects of the audit can alter or delete it unchecked. Second, observability must not turn sensitive business input into a second uncontrolled data store.
Incorrect Answers
A:Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection; do not Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering: protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering.
B:Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering; configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access: observability must not turn sensitive business input into a second uncontrolled data store.
C:Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring; review captured fields and apply data minimization, access restrictions and retention controls before continuing collection: audit evidence is less trustworthy when subjects of the audit can alter or delete it unchecked.
Question 577
Issue 1: A ten-year event table is queried almost exclusively by event date and needs efficient retirement of old monthly data. Issue 2: A partitioned sales table still scans all partitions because analysts wrap the date column in an expression that prevents useful elimination. Issue 3: A team proposes splitting a 20-row reference table into hundreds of partitions to solve slow dashboard joins. Which complete response addresses the three issues in order?
- Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; and reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead.
- Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process; Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; and align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation.
- Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process; align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; and reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead.
- Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process; Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; and reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead.
Explanation
Correct Answer
D:For issue 1, range partitioning can support time-based maintenance when predicates and partition boundaries are designed together. For issue 2, a partitioning strategy only reduces reads when the optimizer can identify the relevant partitions. For issue 3, partitioning adds management overhead and is not a substitute for addressing an actual join or index problem.
Incorrect Answers
A:Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; and reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead: range partitioning can support time-based maintenance when predicates and partition boundaries are designed together.
B:Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process; Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; and align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation: partitioning adds management overhead and is not a substitute for addressing an actual join or index problem.
C:Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process; align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; and reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead: a partitioning strategy only reduces reads when the optimizer can identify the relevant partitions.
Question 578
An Azure Function must react to supported SQL table changes to keep a downstream notification channel updated. An operations workflow needs human approvals after an order status change and spans messaging, audit and external services. Which course of action addresses the stated issues?
- Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization.
- Evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform; evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps.
- Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps.
- Evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps; do not configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing.
Explanation
Correct Answer
C:First, SQL trigger bindings can coordinate database changes with Azure Functions, subject to checkpoint and retry behavior. Second, an orchestrated workflow is suited to multi-service approvals that should not run inside a database transaction.
Incorrect Answers
A:Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization: an orchestrated workflow is suited to multi-service approvals that should not run inside a database transaction.
B:Evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform; evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps: sQL trigger bindings can coordinate database changes with Azure Functions, subject to checkpoint and retry behavior.
D:Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing.
Question 579
An application team must address both issues before release. A reporting view exposes a secret token column that is not needed by analysts, despite the analysts having a legitimate read requirement. A support user can execute a diagnostic procedure that returns unmasked production card data although their task requires only an error code. Which response best addresses these needs?
- Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy; do not Redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
- Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy.
- Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior.
- Apply compatibility tests and a controlled version or migration path for the procedure contract; restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy.
Explanation
Correct Answer
B:First, column minimization should be enforced at the data access boundary rather than left to dashboard display choices. Second, executing a procedure must not become a shortcut around the least-information-needed rule.
Incorrect Answers
A:Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy; do not Redesign the view to project only approved business columns and restrict direct access to the sensitive base column: redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
C:Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior: executing a procedure must not become a shortcut around the least-information-needed rule.
D:Apply compatibility tests and a controlled version or migration path for the procedure contract; restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy: column minimization should be enforced at the data access boundary rather than left to dashboard display choices.
Question 580
A vector query compares a new model's 1536-dimensional embeddings against a table designed for 768-dimensional vectors. Which recommendation best resolves this issue?
- Evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment.
- Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration.
- Regenerate compatible embeddings and align vector dimensions and index schema with the selected model.
- Select an eligible replacement model and perform a controlled full re-embedding and index cutover.
Explanation
Correct Answer
C:Similarity calculations require consistent vector dimensions and compatible embedding representations.
Incorrect Answers
A:Evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment: evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment: embeddings encode similarity, not factual correctness or authorization to answer; Regenerate compatible embeddings and align vector dimensions and index schema with the selected model.
B:Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration: use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration: coordinates from unrelated embedding models do not generally share meaningful geometric semantics; Regenerate compatible embeddings and align vector dimensions and index schema with the selected model.
D:Select an eligible replacement model and perform a controlled full re-embedding and index cutover: select an eligible replacement model and perform a controlled full re-embedding and index cutover: mixing incompatible model outputs can degrade search even when dimensions happen to match; Regenerate compatible embeddings and align vector dimensions and index schema with the selected model.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests