Question 420
A finance service performs a validated multi-step booking operation and needs one callable database interface with transactional error handling. A developer creates a trigger assuming INSERTED always contains one row, and a bulk load silently omits audit entries for most changed rows. Which response best addresses these needs?
- Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; do not create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
- Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks.
- Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; 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.
Explanation
Correct Answer
C:First, procedures encapsulate database operations and allow callers to execute a controlled multi-step unit. Second, T-SQL DML triggers are statement-level, so pseudo-tables may hold many rows.
Incorrect Answers
A:Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; do not create a parameterized stored procedure with an explicit transaction strategy and documented error behavior: create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
B:Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks: t-SQL DML triggers are statement-level, so pseudo-tables may hold many rows.
D: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: procedures encapsulate database operations and allow callers to execute a controlled multi-step unit.
Question 421
A developer tests a stored procedure only for normal happy-path input while nulls and duplicate IDs cause production failures. A pipeline runs unit tests against mocked queries but never checks whether the application can access the deployed stored procedure. What is the most appropriate plan for this situation?
- Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior; add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases.
- Add an integration test using a least-privileged application identity against an isolated deployed database; do not add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior.
- Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts; add an integration test using a least-privileged application identity against an isolated deployed database.
- Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior; add an integration test using a least-privileged application identity against an isolated deployed database.
Explanation
Correct Answer
D:First, edge-case tests can reveal integrity and error-handling flaws that typical data never exercises. Second, database object permission and actual connectivity are runtime integration concerns beyond function unit tests.
Incorrect Answers
A:Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior; add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases: database object permission and actual connectivity are runtime integration concerns beyond function unit tests.
B:Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior.
C:Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts; add an integration test using a least-privileged application identity against an isolated deployed database: edge-case tests can reveal integrity and error-handling flaws that typical data never exercises.
Question 422
A database architect reviews two implementation risks. A finance tool needs the exact latest account balance but the team suggests asking a language model to invent an estimate from prior statements. A developer wants to retrain a foundation model to remember three SQL procedure names instead of making the approved schema available to the assistant. Which course of action addresses the stated issues?
- Provide versioned authorized schema context and retrieval rather than disproportionate model training; do not Retrieve the authoritative current balance through a governed data query and only use generation for explanation.
- Retrieve the authoritative current balance through a governed data query and only use generation for explanation; Provide versioned authorized schema context and retrieval rather than disproportionate model training.
- Require the model to acknowledge missing evidence and route unsupported answers for human review; Provide versioned authorized schema context and retrieval rather than disproportionate model training.
- Retrieve the authoritative current balance through a governed data query and only use generation for explanation; use retrieval-augmented generation with the current authorized policy corpus and source citations.
Explanation
Correct Answer
B:First, dynamic transaction values should come from the source database rather than model prediction. Second, small evolving factual references are better supplied at request time than baked into model weights.
Incorrect Answers
A:Retrieve the authoritative current balance through a governed data query and only use generation for explanation.
C:Require the model to acknowledge missing evidence and route unsupported answers for human review; Provide versioned authorized schema context and retrieval rather than disproportionate model training: dynamic transaction values should come from the source database rather than model prediction.
D:Retrieve the authoritative current balance through a governed data query and only use generation for explanation; use retrieval-augmented generation with the current authorized policy corpus and source citations: small evolving factual references are better supplied at request time than baked into model weights.
Question 423
A fraud analyst needs to find accounts connected through two or more ownership relationships in SQL graph tables. What should the SQL developer do next?
- Use a correlated subquery or a suitable window comparison keyed by supplier and verify exclusion rules.
- Use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices.
- Enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule.
- Use the supported MATCH graph traversal syntax and verify edge direction and path semantics against test data.
Explanation
Correct Answer
D:Graph patterns express relationship traversal without assuming foreign keys alone reveal multi-hop links.
Incorrect Answers
A:Use a correlated subquery or a suitable window comparison keyed by supplier and verify exclusion rules: use a correlated subquery or a suitable window comparison keyed by supplier and verify exclusion rules: a correlated predicate evaluates each invoice against its relevant supplier group rather than a global average; Use the supported MATCH graph traversal syntax and verify edge direction and path semantics against test data.
B:Use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices: use an appropriate LEFT JOIN with grouped counts that distinguish missing rows from actual invoices: an outer join preserves unmatched customers while aggregate logic accounts for zero related records; Use the supported MATCH graph traversal syntax and verify edge direction and path semantics against test data.
C:Enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule: enforce a single-row selection with an appropriate aggregate or deterministic TOP condition after clarifying the business rule: scalar subqueries must return no more than one value for each outer row; Use the supported MATCH graph traversal syntax and verify edge direction and path semantics against test data.
Question 424
A vector search returns a numerically smaller distance for one passage, but the UI labels it as 'higher distance means more similar'. What should the SQL developer do next?
- Verify the matched passage against authoritative approved records before returning the tax fact.
- Align the displayed relevance interpretation with the selected distance metric and verify ordering against known examples.
- Calibrate thresholds on labeled examples from the actual embedding model and document uncertainty behavior.
- Implement a relevance/no-answer threshold and abstention or escalation path for low-evidence queries.
Explanation
Correct Answer
B:Distance metrics typically rank lower values as closer, unlike some similarity scores where larger values are better.
Incorrect Answers
A:Verify the matched passage against authoritative approved records before returning the tax fact: verify the matched passage against authoritative approved records before returning the tax fact: neighbor similarity is not evidence of factual correctness or current legal authority; Align the displayed relevance interpretation with the selected distance metric and verify ordering against known examples.
C:Calibrate thresholds on labeled examples from the actual embedding model and document uncertainty behavior: calibrate thresholds on labeled examples from the actual embedding model and document uncertainty behavior: semantic score distributions depend on model and corpus, making transplanted thresholds unreliable; Align the displayed relevance interpretation with the selected distance metric and verify ordering against known examples.
D:Implement a relevance/no-answer threshold and abstention or escalation path for low-evidence queries: implement a relevance/no-answer threshold and abstention or escalation path for low-evidence queries: forcing a nearest result does not mean relevant evidence exists; Align the displayed relevance interpretation with the selected distance metric and verify ordering against known examples.
Question 425
An application team must address both issues before release. A SQL application cannot call its approved remote model because the model's endpoint and credentials are missing from database configuration. A production database calls a model deployment whose version was changed without regression testing and now JSON output fields differ. Which course of action addresses the stated issues?
- Configure the supported external model definition and secure its endpoint authentication under the target SQL platform; Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility.
- Check supported platform and version prerequisites before deploying the external model integration; Version and validate the external model integration contract before approving the changed deployment.
- Version and validate the external model integration contract before approving the changed deployment; do not configure the supported external model definition and secure its endpoint authentication under the target SQL platform.
- Configure the supported external model definition and secure its endpoint authentication under the target SQL platform; Version and validate the external model integration contract before approving the changed deployment.
Explanation
Correct Answer
D:First, external model integration requires explicit endpoint and credential configuration rather than mere table existence. Second, model endpoint upgrades can change behavior and must be treated as application dependency changes.
Incorrect Answers
A:Configure the supported external model definition and secure its endpoint authentication under the target SQL platform; Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility: model endpoint upgrades can change behavior and must be treated as application dependency changes.
B:Check supported platform and version prerequisites before deploying the external model integration; Version and validate the external model integration contract before approving the changed deployment: external model integration requires explicit endpoint and credential configuration rather than mere table existence.
C:Version and validate the external model integration contract before approving the changed deployment; do not configure the supported external model definition and secure its endpoint authentication under the target SQL platform: configure the supported external model definition and secure its endpoint authentication under the target SQL platform.
Question 426
Issue 1: A reporting table needs sequential business reference numbers independent of any particular table's identity column. Issue 2: A purchase-order table has duplicate invoice numbers per supplier, but the same number is legitimately used by different suppliers. Issue 3: A catalog model permits a new product row without a required SKU even though the integration never supports unidentified items. Which complete response addresses the three issues in order?
- Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers; configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted; and 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; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; and 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; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; and 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; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; and configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted.
Explanation
Correct Answer
C:For issue 1, a sequence generates numbers independently of table identity insertion, with gaps possible under concurrency or rollback. For issue 2, composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label. For issue 3, nullability is a schema integrity rule; adding a display placeholder does not make a missing business identifier valid.
Incorrect Answers
A:Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers; configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted; and define the SKU column as NOT NULL and validate existing null records before applying the change: composite uniqueness protects the intended business key without falsely requiring global uniqueness of an invoice label.
B: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; and define the SKU column as NOT NULL and validate existing null records before applying the change: a sequence generates numbers independently of table identity insertion, with gaps possible under concurrency or rollback.
D:Create and use a SEQUENCE with defined allocation and concurrency expectations for the external reference numbers; enforce a composite UNIQUE constraint on supplier ID and invoice number after resolving same-supplier duplicates; and configure an appropriate DEFAULT expression on the created-at column and test insert behavior when the field is omitted: nullability is a schema integrity rule; adding a display placeholder does not make a missing business identifier valid.
Question 427
An application team must address both issues before release. A database represents employee reporting lines as repeated self-joins, and developers need to traverse multi-hop relationships between people. A payments team wants a completely non-durable memory table for transactions that must survive server restarts and full recovery. What is the most appropriate plan for this situation?
- Reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics; do not evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case.
- Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration.
Explanation
Correct Answer
C:First, graph structures represent relationships directly and can simplify traversals that otherwise require repeated joins. Second, critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
Incorrect Answers
A:Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case.
B:Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics: graph structures represent relationships directly and can simplify traversals that otherwise require repeated joins.
D:Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration: critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
Question 428
A stored report uses SELECT * after a schema expansion, unexpectedly exposing confidential columns to an API client. Which action best addresses the database requirement?
- Select explicitly authorized columns and make schema output changes subject to contract review.
- Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records.
- Define a business-specific ORDER BY with a stable tie-breaker before applying TOP.
- Use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists.
Explanation
Correct Answer
A:Explicit projections avoid accidental information disclosure when the source table grows.
Incorrect Answers
B:Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records: nULL in a NOT IN set can make the comparison unknown and unexpectedly filter all rows.
C:Define a business-specific ORDER BY with a stable tie-breaker before applying TOP: define a business-specific ORDER BY with a stable tie-breaker before applying TOP: a limited result set is not meaningful or reproducible without a deterministic selection order.
D:Use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists: use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists: parameterization separates user data from executable SQL syntax.
Question 429
An index change increases average semantic similarity scores but users complain that exact warranty exceptions disappear from results. What is the most defensible technical decision?
- Add edge cases and no-answer items to the evaluation corpus and check recall for high-impact documents.
- Evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success.
- 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.
Explanation
Correct Answer
B:Higher similarity numbers do not guarantee that required authoritative passages are retrieved.
Incorrect Answers
A:Add edge cases and no-answer items to the evaluation corpus and check recall for high-impact documents: add edge cases and no-answer items to the evaluation corpus and check recall for high-impact documents: search testing must cover rare consequential evidence, not only frequent simple questions.
C:Measure end-to-end query embedding, candidate retrieval, reranking and response latency: user-visible search latency includes all pipeline stages rather than one database operation.
D:Version and vary one retrieval component at a time where practical, with repeatable baseline queries: version and vary one retrieval component at a time where practical, with repeatable baseline queries: controlled comparisons make attribution of performance changes possible.
Question 430
Issue 1: An application account is granted db_owner just to execute one data-entry stored procedure. Issue 2: A developer modifies a shared stored procedure's parameter semantics without versioning, breaking existing clients when a deployment completes. Issue 3: A support user can execute a diagnostic procedure that returns unmasked production card data although their task requires only an error code. Which complete response addresses the three issues in order?
- Grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior; Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; and restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy.
- 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; and 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; and 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; apply compatibility tests and a controlled version or migration path for the procedure contract; and restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy.
Explanation
Correct Answer
C:For issue 1, whole-database ownership is excessive for calling one approved routine. For issue 2, a database programmable-object signature is an API contract that downstream clients may depend upon. For issue 3, executing a procedure must not become a shortcut around the least-information-needed rule.
Incorrect Answers
A:Grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior; Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; and restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy: a database programmable-object signature is an API contract that downstream clients may depend upon.
B: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; and Redesign the view to project only approved business columns and restrict direct access to the sensitive base column: executing a procedure must not become a shortcut around the least-information-needed rule.
D: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; and restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy: whole-database ownership is excessive for calling one approved routine.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests