Question 710
A database architect reviews two implementation risks. An API retries a timed-out purchase request and creates two identical orders because the first transaction actually committed. A developer begins a transaction but has code paths that return early without COMMIT or ROLLBACK, leaving locks held. Which course of action addresses the stated issues?
- Choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs; Guarantee transaction closure across success and error paths and verify the open transaction state in tests.
- Guarantee transaction closure across success and error paths and verify the open transaction state in tests; do not add an idempotency key enforced by a unique database rule and return the existing result on safe retries.
- 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.
- 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.
Explanation
Correct Answer
C:First, retries must distinguish a completed operation from a new one to avoid duplicate side effects. Second, unfinished transactions retain resources and may block unrelated work.
Incorrect Answers
A:Choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs; Guarantee transaction closure across success and error paths and verify the open transaction state in tests: retries must distinguish a completed operation from a new one to avoid duplicate side effects.
B:Add an idempotency key enforced by a unique database rule and return the existing result on safe retries.
D: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: unfinished transactions retain resources and may block unrelated work.
Question 711
An application team must address both issues before release. An application needs to describe diagrams from uploaded images but its external model accepts text input only. A lightweight SQL assistant uses a costly large model for simple data categorization and fails its latency and budget goals. Which response best addresses these needs?
- Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements; do not choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams.
- Compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements.
- Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; evaluate a model with reliable structured-output support and validate its schema behavior on representative requests.
- Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements.
Explanation
Correct Answer
D:First, a text-only model cannot inspect actual image content absent from its supported input interface. Second, model size should be justified by tested workload outcomes rather than chosen for prestige alone.
Incorrect Answers
A:Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements; do not choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams: choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams.
B:Compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements: a text-only model cannot inspect actual image content absent from its supported input interface.
C:Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; evaluate a model with reliable structured-output support and validate its schema behavior on representative requests: model size should be justified by tested workload outcomes rather than chosen for prestige alone.
Question 712
A developer creates a trigger assuming INSERTED always contains one row, and a bulk load silently omits audit entries for most changed rows. An API calls a stored procedure by concatenating untrusted user input into dynamic SQL and security reviewers find injection risk. What is the most appropriate plan for this situation?
- Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
- 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.
- Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
- Parameterize the procedure's dynamic SQL and grant the caller only required execution rights; do not Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements.
Explanation
Correct Answer
B:First, T-SQL DML triggers are statement-level, so pseudo-tables may hold many rows. Second, data parameters must not become executable SQL text and procedure access should follow least privilege.
Incorrect Answers
A:Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; Parameterize the procedure's dynamic SQL and grant the caller only required execution rights: t-SQL DML triggers are statement-level, so pseudo-tables may hold many rows.
C:Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; create a parameterized stored procedure with an explicit transaction strategy and documented error behavior: data parameters must not become executable SQL text and procedure access should follow least privilege.
D:Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements.
Question 713
An application team must address both issues before release. A SQL stored procedure needs to call an approved external language model service but developers attempt to use unsupported network calls from ordinary SELECT expressions. An external model API responds intermittently with throttling, and SQL code retries indefinitely while holding an open business transaction. Which course of action addresses the stated issues?
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; 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; 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; do not use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
Explanation
Correct Answer
A:First, sQL-managed external REST invocation has a defined interface and security requirements for outbound model requests. Second, external inference availability should not monopolize database locks or create uncontrolled request storms.
Incorrect Answers
B: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: sQL-managed external REST invocation has a defined interface and security requirements for outbound model requests.
C:Bound and back off retries outside long-running critical transactions and handle model errors explicitly; do not use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling: use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
D:Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint: external inference availability should not monopolize database locks or create uncontrolled request storms.
Question 714
A team changes an indexed view definition but forgets that downstream SQL procedures reference its old column list. Which action best addresses the database requirement?
- 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.
- Run dependency analysis and compatibility tests against all known consumers before publishing the new view.
- Use an expand-and-contract schema migration with temporary compatibility and monitored client cutover.
Explanation
Correct Answer
C:Schema changes must account for dependent objects and client contracts, not only the modified file.
Incorrect Answers
A:Coordinate data contract changes with application validation and deploy in a compatible order: coordinate data contract changes with application validation and deploy in a compatible order: static data is part of the interface between database and application code.
B:Add policy-based diff review and security tests for critical database objects before deployment approval: add policy-based diff review and security tests for critical database objects before deployment approval: compilation does not prove compliance controls remain present after a schema update; Run dependency analysis and compatibility tests against all known consumers before publishing the new view.
D:Use an expand-and-contract schema migration with temporary compatibility and monitored client cutover: use an expand-and-contract schema migration with temporary compatibility and monitored client cutover: backward-compatible deployment stages prevent breaking older application instances during gradual releases.
Question 715
Issue 1: An online order service shows long request waits, and DMV evidence reveals one session keeps an exclusive row lock while awaiting user input. Issue 2: Two procedures update customer and invoice tables in opposite order and repeatedly deadlock during busy periods. Issue 3: A team proposes fixing deadlocks only by extending every command timeout from thirty seconds to ten minutes. Which complete response addresses the three issues in order?
- Shorten the blocking transaction and move interactive waiting outside its lock-holding interval; implement bounded retry with idempotency safeguards for recognized transient deadlock failures; and 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; Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; and investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts.
- 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; 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; Standardize the table access order and review indexes and transaction scope to reduce cyclic lock dependencies; and investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts.
Explanation
Correct Answer
D:For issue 1, locks retained through human interaction unnecessarily serialize otherwise independent requests. For issue 2, deadlocks arise when sessions acquire resources in incompatible cycles; consistent ordering reduces the pattern. For issue 3, longer timeouts do not remove cyclic lock dependencies or guarantee a deadlock victim will succeed.
Incorrect Answers
A:Shorten the blocking transaction and move interactive waiting outside its lock-holding interval; implement bounded retry with idempotency safeguards for recognized transient deadlock failures; and investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts: deadlocks arise when sessions acquire resources in incompatible cycles; consistent ordering reduces the pattern.
B: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; and investigate deadlock graphs and implement targeted transaction, indexing and retry improvements instead of merely raising timeouts: locks retained through human interaction unnecessarily serialize otherwise independent requests.
C: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; 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.
Question 716
A ticket-creation API omits creation timestamps and produces null values, even though the database should assign the current time. Which recommendation best resolves this issue?
- 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.
- Define the SKU column as NOT NULL and validate existing null records before applying the change.
- Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers.
Explanation
Correct Answer
B:Database defaults fill omitted values consistently without requiring every client to supply the same timestamp logic.
Incorrect Answers
A: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: composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label; Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
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; Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
D:Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers: 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; Configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
Question 717
An application team must address both issues before release. A model vendor changes the embedding model's tokenizer, but the pipeline keeps old chunks and declares the retrieval service unchanged. An analyst wants to include an entire customer record in embeddings even though only product-interest text is needed for recommendation similarity. Which response best addresses these needs?
- Reevaluate chunk lengths and retrieval quality under the model update before promoting it; select task-relevant descriptive fields and exclude protected unrelated customer attributes.
- Track token and request usage per refresh job and set suitable budgets and anomaly alerts; select task-relevant descriptive fields and exclude protected unrelated customer attributes.
- Select task-relevant descriptive fields and exclude protected unrelated customer attributes; do not Reevaluate chunk lengths and retrieval quality under the model update before promoting it.
- Reevaluate chunk lengths and retrieval quality under the model update before promoting it; Record model ID, version, source checksum and embedding timestamp with each indexed record.
Explanation
Correct Answer
A:First, tokenizer and model changes can shift effective context limits and retrieval behavior. Second, input selection is part of both retrieval quality and privacy-by-design.
Incorrect Answers
B:Track token and request usage per refresh job and set suitable budgets and anomaly alerts; select task-relevant descriptive fields and exclude protected unrelated customer attributes: tokenizer and model changes can shift effective context limits and retrieval behavior.
C:Select task-relevant descriptive fields and exclude protected unrelated customer attributes; do not Reevaluate chunk lengths and retrieval quality under the model update before promoting it: reevaluate chunk lengths and retrieval quality under the model update before promoting it.
D:Reevaluate chunk lengths and retrieval quality under the model update before promoting it; Record model ID, version, source checksum and embedding timestamp with each indexed record: input selection is part of both retrieval quality and privacy-by-design.
Question 718
Issue 1: A SQL support search must find both precise error codes and semantically similar descriptions in one result set. Issue 2: A hybrid system averages raw BM25 and cosine similarity scores directly even though their scales differ widely. Issue 3: A hybrid search returns the same document from lexical and vector channels as two separate results, crowding out other useful sources. Which complete response addresses the three issues in order?
- Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; and Deduplicate candidates by stable document ID before final ranking and context assembly.
- Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; and Deduplicate candidates by stable document ID before final ranking and context assembly.
- Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; and Deduplicate candidates by stable document ID before final ranking and context assembly.
- Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; and evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance.
Explanation
Correct Answer
B:For issue 1, hybrid search covers literal identifier matches and semantic relationships that either method alone can miss. For issue 2, lexical and vector scoring systems are not automatically on the same numerical scale. For issue 3, merging result lists must account for shared documents to avoid repeated evidence in the answer context.
Incorrect Answers
A:Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; and Deduplicate candidates by stable document ID before final ranking and context assembly: hybrid search covers literal identifier matches and semantic relationships that either method alone can miss.
C:Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; and Deduplicate candidates by stable document ID before final ranking and context assembly: lexical and vector scoring systems are not automatically on the same numerical scale.
D:Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; and evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance: merging result lists must account for shared documents to avoid repeated evidence in the answer context.
Question 719
An AI-enabled database project needs two corrections. A ten-year event table is queried almost exclusively by event date and needs efficient retirement of old monthly data. A large table's partition switch operation fails because its staging table has incompatible index and constraint definitions. Which course of action addresses the stated issues?
- Reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead; 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.
- Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; do not Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process.
- 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.
Explanation
Correct Answer
B:First, range partitioning can support time-based maintenance when predicates and partition boundaries are designed together. Second, efficient partition switching depends on structurally compatible tables and aligned boundaries.
Incorrect Answers
A:Reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead; align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation: range partitioning can support time-based maintenance when predicates and partition boundaries are designed together.
C:Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; do not Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process: partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process.
D: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: efficient partition switching depends on structurally compatible tables and aligned boundaries.
Question 720
Issue 1: An application stores flexible attributes in an NVARCHAR JSON document but queries a specific scalar property thousands of times per second. Issue 2: A service expects a database API to return a nested JSON object with named attributes rather than concatenated free-text strings. Issue 3: A normalized order table must expand an incoming JSON array into rows for validation and insertion into child records. Which complete response addresses the three issues in order?
- Evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
- Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
- 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; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
- Evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads.
Explanation
Correct Answer
A:For issue 1, exposing a searchable JSON property to an index can avoid repeatedly parsing every document during selective filtering. For issue 2, JSON construction functions preserve key/value structure without brittle manual delimiter concatenation. For issue 3, OPENJSON converts array elements into tabular data suitable for relational constraints and joins.
Incorrect Answers
B:Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion: exposing a searchable JSON property to an index can avoid repeatedly parsing every document during selective filtering.
C: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; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion: jSON construction functions preserve key/value structure without brittle manual delimiter concatenation.
D:Evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads: oPENJSON converts array elements into tabular data suitable for relational constraints and joins.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests