Question 210
A developer places a long network call inside a table trigger, causing ordinary inserts to stall when the remote service slows. What is the most defensible technical decision?
- Move external side effects to a reliable asynchronous integration path and keep the trigger transaction short.
- Make the calculation's assumptions explicit and validate session-sensitive behavior in repeatable integration tests.
- Use TRY/CATCH with transaction rollback and consistent failure signaling so partial business changes are not reported as committed.
- Review whether controlled procedures or a suitable INSTEAD OF trigger provide a supported update interface.
Explanation
Correct Answer
A:Long-running work inside write triggers extends transaction duration and raises contention and failure risk.
Incorrect Answers
B:Make the calculation's assumptions explicit and validate session-sensitive behavior in repeatable integration tests: reusable database logic should not silently depend on varying client session state.
C:Use TRY/CATCH with transaction rollback and consistent failure signaling so partial business changes are not reported as committed: transactional failure handling must preserve atomicity and communicate that the operation did not complete.
D:Review whether controlled procedures or a suitable INSTEAD OF trigger provide a supported update interface: complex views can lose straightforward update semantics and need explicit mapping of writes.
Question 211
A REST API provides one table but a client needs an approved read-only stored procedure that returns a summarized invoice report. Which recommendation best resolves this issue?
- Configure supported DAB entity relationships and GraphQL navigation with restrictive permissions.
- Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller.
- Enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters.
- Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights.
Explanation
Correct Answer
D:DAB can publish controlled database objects, but procedure authorization remains essential.
Incorrect Answers
A:Configure supported DAB entity relationships and GraphQL navigation with restrictive permissions: configure supported DAB entity relationships and GraphQL navigation with restrictive permissions: graphQL relationships require explicit mapping to expose navigable links between database entities.
B:Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller: aPI authentication is insufficient if underlying database authorization returns data outside the caller's scope.
C:Enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters: enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters: structured API filters constrain requests without granting clients direct SQL execution.
Question 212
An AI-enabled database project needs two corrections. Developers manually change production table schemas but the SQL Database Project no longer represents the deployed model. A team stores all schema changes only in screenshots of query windows and cannot reproduce the last release in disaster recovery. Which course of action addresses the stated issues?
- Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; Store the SQL Database Project and migration decisions in version control with reproducible build artifacts.
- Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow; Store the SQL Database Project and migration decisions in version control with reproducible build artifacts.
- Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow; add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references.
- Store the SQL Database Project and migration decisions in version control with reproducible build artifacts; do not Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow.
Explanation
Correct Answer
B:First, schema-as-code relies on the model being the authoritative definition rather than an outdated snapshot. Second, versioned source and artifacts support reproducible database releases rather than undocumented manual changes.
Incorrect Answers
A:Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; Store the SQL Database Project and migration decisions in version control with reproducible build artifacts: schema-as-code relies on the model being the authoritative definition rather than an outdated snapshot.
C:Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow; add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references: versioned source and artifacts support reproducible database releases rather than undocumented manual changes.
D:Store the SQL Database Project and migration decisions in version control with reproducible build artifacts; do not Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow: capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow.
Question 213
A deleted policy continues appearing in search because the embedding pipeline handles inserts and updates but ignores deletes. Which recommendation best resolves this issue?
- Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer.
- Implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint.
- Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup.
- Use stable source keys, generation versions and upsert/idempotency controls when processing retries.
Explanation
Correct Answer
C:Embedding lifecycle management includes deletion and retention, not only creation and refresh.
Incorrect Answers
A:Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer: external inference latency should not extend critical database write transactions unnecessarily.
B:Implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint: change capture can identify updated records so embeddings remain aligned with authoritative source content.
D:Use stable source keys, generation versions and upsert/idempotency controls when processing retries: change-triggered processing may be delivered more than once and must not duplicate index records.
Question 214
An application team must address both issues before release. A hybrid system averages raw BM25 and cosine similarity scores directly even though their scales differ widely. A hybrid search returns the same document from lexical and vector channels as two separate results, crowding out other useful sources. What is the most appropriate plan for this situation?
- Use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; Deduplicate candidates by stable document ID before final ranking and context assembly.
- Use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types.
- Deduplicate candidates by stable document ID before final ranking and context assembly; do not use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable.
- Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; Deduplicate candidates by stable document ID before final ranking and context assembly.
Explanation
Correct Answer
A:First, lexical and vector scoring systems are not automatically on the same numerical scale. Second, merging result lists must account for shared documents to avoid repeated evidence in the answer context.
Incorrect Answers
B:Use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable; Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types: merging result lists must account for shared documents to avoid repeated evidence in the answer context.
C:Use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable.
D:Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; 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.
Question 215
Issue 1: A service receives a JSON array of feature flags and needs one relational row for each item for validation. Issue 2: An API must produce one JSON array of product codes per invoice group rather than comma-joined text that fails on embedded commas. Issue 3: A database query must read the integer priority from a JSON document and filter on it, but the developer currently compares full JSON text. Which complete response addresses the three issues in order?
- Parse the array with OPENJSON and an explicit expected schema before applying policy checks; use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; and Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering.
- Parse the array with OPENJSON and an explicit expected schema before applying policy checks; use a supported JSON path or containment predicate with explicit validation of path and value semantics; and Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering.
- Use a supported JSON path or containment predicate with explicit validation of path and value semantics; use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; and Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering.
- Parse the array with OPENJSON and an explicit expected schema before applying policy checks; use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; and use a supported JSON path or containment predicate with explicit validation of path and value semantics.
Explanation
Correct Answer
A:For issue 1, JSON expansion converts structured array elements into rows instead of brittle string splits. For issue 2, JSON array construction represents elements as data rather than ambiguous delimiter-separated text. For issue 3, JSON property extraction distinguishes one scalar from a serialized document.
Incorrect Answers
B:Parse the array with OPENJSON and an explicit expected schema before applying policy checks; use a supported JSON path or containment predicate with explicit validation of path and value semantics; and Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering: jSON array construction represents elements as data rather than ambiguous delimiter-separated text.
C:Use a supported JSON path or containment predicate with explicit validation of path and value semantics; use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; and Extract the scalar with an appropriate JSON function and cast or validate its numeric type before filtering: jSON expansion converts structured array elements into rows instead of brittle string splits.
D:Parse the array with OPENJSON and an explicit expected schema before applying policy checks; use the platform's JSON_ARRAYAGG capability and validate output types and ordering expectations; and use a supported JSON path or containment predicate with explicit validation of path and value semantics: jSON property extraction distinguishes one scalar from a serialized document.
Question 216
A document embedding pipeline stores passages without any page, section or document version identifiers and reviewers cannot verify answers. Which action best addresses the database requirement?
- Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap.
- Split the manual into coherent section-level chunks with source IDs and test retrieval granularity.
- Store stable source provenance and effective-version fields alongside every embedded chunk.
- Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness.
Explanation
Correct Answer
C:RAG evidence must be traceable to its originating authorized document.
Incorrect Answers
A:Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap: excessive overlap can raise duplicate retrieval and index cost without improving answer quality.
B:Split the manual into coherent section-level chunks with source IDs and test retrieval granularity: smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding.
D:Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness: chunks must preserve enough context for accurate interpretation, not just fit a token limit.
Question 217
An application team must address both issues before release. An order system stores monetary totals as floating-point values, and end-of-day reconciliation differs by a few cents between reports. A customer identifier accepts duplicate values even though every integration expects a single permanent row key. What is the most appropriate plan for this situation?
- Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values; define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it.
- Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values; define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows.
- Add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records; define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it.
- Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it; do not Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values.
Explanation
Correct Answer
A:First, exact decimal storage is appropriate for financial values because binary floating-point representation can introduce rounding artifacts. Second, a primary key establishes unique row identity and prevents null key values, unlike a display-only index.
Incorrect Answers
B:Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values; define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows: a primary key establishes unique row identity and prevents null key values, unlike a display-only index.
C:Add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records; define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it: exact decimal storage is appropriate for financial values because binary floating-point representation can introduce rounding artifacts.
D:Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values.
Question 218
An index change increases average semantic similarity scores but users complain that exact warranty exceptions disappear from results. An experiment simultaneously changes the embedding model, chunking size and index configuration, so no one knows what improved retrieval. Which course of action addresses the stated issues?
- Evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success; Version and vary one retrieval component at a time where practical, with repeatable baseline queries.
- Measure end-to-end query embedding, candidate retrieval, reranking and response latency; Version and vary one retrieval component at a time where practical, with repeatable baseline queries.
- Evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success; add edge cases and no-answer items to the evaluation corpus and check recall for high-impact documents.
- Version and vary one retrieval component at a time where practical, with repeatable baseline queries; do not evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success.
Explanation
Correct Answer
A:First, higher similarity numbers do not guarantee that required authoritative passages are retrieved. Second, controlled comparisons make attribution of performance changes possible.
Incorrect Answers
B:Measure end-to-end query embedding, candidate retrieval, reranking and response latency; Version and vary one retrieval component at a time where practical, with repeatable baseline queries: higher similarity numbers do not guarantee that required authoritative passages are retrieved.
C:Evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success; add edge cases and no-answer items to the evaluation corpus and check recall for high-impact documents: controlled comparisons make attribution of performance changes possible.
D:Evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success.
Question 219
An AI-enabled database project needs two corrections. A ticket-creation API omits creation timestamps and produces null values, even though the database should assign the current time. A catalog model permits a new product row without a required SKU even though the integration never supports unidentified items. Which response best addresses these needs?
- 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.
- 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.
- Define the SKU column as NOT NULL and validate existing null records before applying the change; do not 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; define the SKU column as NOT NULL and validate existing null records before applying the change.
Explanation
Correct Answer
A:First, database defaults fill omitted values consistently without requiring every client to supply the same timestamp logic. Second, nullability is a schema integrity rule; adding a display placeholder does not make a missing business identifier valid.
Incorrect Answers
B: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: nullability is a schema integrity rule; adding a display placeholder does not make a missing business identifier valid.
C:Define the SKU column as NOT NULL and validate existing null records before applying the change; 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:Enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; define the SKU column as NOT NULL and validate existing null records before applying the change: database defaults fill omitted values consistently without requiring every client to supply the same timestamp logic.
Question 220
A database architect reviews two implementation risks. A team raises maximum database connections indiscriminately while each client holds idle sessions and SQL workers become exhausted. A warehouse-style SQL workload repeatedly allocates huge temporary sort results and experiences tempdb contention. What is the most appropriate plan for this situation?
- Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity; inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration.
- Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity; compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck.
- Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix; inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration.
- Inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration; do not Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity.
Explanation
Correct Answer
A:First, unbounded client sessions can consume resources without improving transaction throughput. Second, tempdb pressure can reflect workload design rather than an external network problem.
Incorrect Answers
B:Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity; compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck: tempdb pressure can reflect workload design rather than an external network problem.
C:Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix; inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration: unbounded client sessions can consume resources without improving transaction throughput.
D:Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests