Question 680
A report needs distinct employee IDs from two sources, but the developer uses UNION ALL and counts duplicates as separate people. A product search assumes ORDER BY inside a subquery guarantees the final result order without an outer ORDER BY. Which course of action addresses the stated issues?
- Normalize timestamps to a common time basis and apply explicit business time-zone conversion for the report; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters.
- Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters.
- Apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters; do not choose UNION or explicit deduplication for the requested unique population after checking key equivalence.
- Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals.
Explanation
Correct Answer
B:First, uNION removes duplicate result rows whereas UNION ALL preserves them. Second, relational result order is not guaranteed without ordering at the final query boundary.
Incorrect Answers
A:Normalize timestamps to a common time basis and apply explicit business time-zone conversion for the report; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters: uNION removes duplicate result rows whereas UNION ALL preserves them.
C:Apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters; do not choose UNION or explicit deduplication for the requested unique population after checking key equivalence: choose UNION or explicit deduplication for the requested unique population after checking key equivalence.
D:Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals: relational result order is not guaranteed without ordering at the final query boundary.
Question 681
Issue 1: A security team wants to query failed database API requests across environments but each service stores logs in separate ephemeral local files. Issue 2: An SQL service raises occasional deadlocks but the alert system triggers only on complete application outages. Issue 3: A diagnostic pipeline includes full customer query parameters in distributed tracing that many engineers can access. Which complete response addresses the three issues in order?
- Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; and redact protected payload fields and restrict telemetry access while preserving correlation identifiers.
- Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; and Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans.
- Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans; define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; and redact protected payload fields and restrict telemetry access while preserving correlation identifiers.
- Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans; and redact protected payload fields and restrict telemetry access while preserving correlation identifiers.
Explanation
Correct Answer
A:For issue 1, central log queries support cross-service incident analysis and protect evidence from local process restarts. For issue 2, early error and performance alerts can reveal degradations before total service loss. For issue 3, useful observability should minimize sensitive data captured in logs and traces.
Incorrect Answers
B:Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; and Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans: useful observability should minimize sensitive data captured in logs and traces.
C:Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans; define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing; and redact protected payload fields and restrict telemetry access while preserving correlation identifiers: central log queries support cross-service incident analysis and protect evidence from local process restarts.
D:Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans; and redact protected payload fields and restrict telemetry access while preserving correlation identifiers: early error and performance alerts can reveal degradations before total service loss.
Question 682
Issue 1: An in-memory transaction workload suffers allocation and lock overhead, and a team proposes memory-optimized tables without checking operational limits. Issue 2: A database represents employee reporting lines as repeated self-joins, and developers need to traverse multi-hop relationships between people. Issue 3: A payments team wants a completely non-durable memory table for transactions that must survive server restarts and full recovery. Which complete response addresses the three issues in order?
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions.
- Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
Explanation
Correct Answer
A:For issue 1, in-memory optimization can help selected workloads but carries compatibility and durability considerations. For issue 2, graph structures represent relationships directly and can simplify traversals that otherwise require repeated joins. For issue 3, critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
Incorrect Answers
B:Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and 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.
C:Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions: critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
D:Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics: in-memory optimization can help selected workloads but carries compatibility and durability considerations.
Question 683
An import includes orders referring to customer IDs that do not exist, and downstream joins silently drop those orders. What is the most defensible technical decision?
- Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it.
- Add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records.
- Define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows.
- Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values.
Explanation
Correct Answer
C:Referential integrity prevents new orphan records at write time rather than relying on every report to detect them.
Incorrect Answers
A:Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it: a primary key establishes unique row identity and prevents null key values, unlike a display-only index.
B:Add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records: a CHECK constraint expresses row-level business rules that a data type alone does not enforce.
D:Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values: exact decimal storage is appropriate for financial values because binary floating-point representation can introduce rounding artifacts.
Question 684
A pricing lookup table differs in every test environment because developers insert static tax categories manually. What should the SQL developer do next?
- 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 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.
Explanation
Correct Answer
B:Source-controlled static records keep environments behaviorally consistent with the deployed model.
Incorrect Answers
A:Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior: add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior: edge-case tests can reveal integrity and error-handling flaws that typical data never exercises; Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts.
C:Add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases: add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases: successful schema execution does not prove the application still meets business integrity requirements; Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts.
D:Add an integration test using a least-privileged application identity against an isolated deployed database: add an integration test using a least-privileged application identity against an isolated deployed database: database object permission and actual connectivity are runtime integration concerns beyond function unit tests; Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts.
Question 685
A subscription table accepts negative durations and arbitrary status strings, producing invalid invoices despite correct column types. Which action best addresses the database requirement?
- Define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows.
- Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values.
- 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.
Explanation
Correct Answer
C:A CHECK constraint expresses row-level business rules that a data type alone does not enforce.
Incorrect Answers
A:Define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows: referential integrity prevents new orphan records at write time rather than relying on every report to detect them.
B:Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values: change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values: exact decimal storage is appropriate for financial values because binary floating-point representation can introduce rounding artifacts.
D: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: a primary key establishes unique row identity and prevents null key values, unlike a display-only index.
Question 686
Issue 1: A team exposes a private products table through Data API builder but commits an unrestricted production connection string in the public configuration repository. Issue 2: A configured Data API builder entity includes internal audit columns in the REST response even though external users need only public product fields. Issue 3: An administrator changes the DAB entity mapping name but clients receive 404 responses because they still call the old REST route. Which complete response addresses the three issues in order?
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; and update and validate the entity's published REST path and coordinate a versioned client migration.
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
- Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration.
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration.
Explanation
Correct Answer
D:For issue 1, Data API builder deployment settings must not expose database credentials or overprivileged access. For issue 2, API projections should expose only explicitly authorized data rather than mirroring an entire table. For issue 3, API route names form external contracts and must match the deployed configuration.
Incorrect Answers
A:Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; and update and validate the entity's published REST path and coordinate a versioned client migration: aPI projections should expose only explicitly authorized data rather than mirroring an entire table.
B:Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior: aPI route names form external contracts and must match the deployed configuration.
C:Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration: data API builder deployment settings must not expose database credentials or overprivileged access.
Question 687
Issue 1: An ANN search is evaluated only on easy common questions and misses rare high-severity product recall notices. Issue 2: An experiment simultaneously changes the embedding model, chunking size and index configuration, so no one knows what improved retrieval. Issue 3: A search latency benchmark measures only SQL execution, while application users wait much longer for a remote embedding call. Which complete response addresses the three issues in order?
- 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; and Measure end-to-end query embedding, candidate retrieval, reranking and response latency.
- 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; and 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; and Measure end-to-end query embedding, candidate retrieval, reranking and response latency.
- 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; and Measure end-to-end query embedding, candidate retrieval, reranking and response latency.
Explanation
Correct Answer
C:For issue 1, search testing must cover rare consequential evidence, not only frequent simple questions. For issue 2, controlled comparisons make attribution of performance changes possible. For issue 3, user-visible search latency includes all pipeline stages rather than one database operation.
Incorrect Answers
A: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; and Measure end-to-end query embedding, candidate retrieval, reranking and response latency: search testing must cover rare consequential evidence, not only frequent simple questions.
B: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; and evaluate retrieval recall and clause-level relevance using labeled queries rather than treating score increases as success: user-visible search latency includes all pipeline stages rather than one database operation.
D: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; and Measure end-to-end query embedding, candidate retrieval, reranking and response latency: controlled comparisons make attribution of performance changes possible.
Question 688
A database architect reviews two implementation risks. A vector index is created for one embedding dimension and a new model produces vectors of incompatible size. A product team chooses an ANN configuration by testing only 20 documents and assumes it will scale to 50 million rows unchanged. What is the most appropriate plan for this situation?
- Benchmark the planned vector index on representative corpus size and query distribution before selection; do not Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration.
- Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration; Benchmark the planned vector index on representative corpus size and query distribution before selection.
- Coordinate schema and query changes through versioned deployment and integration tests before cutover; Benchmark the planned vector index on representative corpus size and query distribution before selection.
- Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration; Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements.
Explanation
Correct Answer
B:First, index definitions and stored vector dimensions must match the selected embedding model. Second, ANN index performance and recall can change materially with data volume and shape.
Incorrect Answers
A:Benchmark the planned vector index on representative corpus size and query distribution before selection; do not Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration: rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration.
C:Coordinate schema and query changes through versioned deployment and integration tests before cutover; Benchmark the planned vector index on representative corpus size and query distribution before selection: index definitions and stored vector dimensions must match the selected embedding model.
D:Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration; Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements: aNN index performance and recall can change materially with data volume and shape.
Question 689
A query uses TOP 10 without ORDER BY and support observes a different group of customers on each execution. What should the SQL developer do next?
- 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.
- Select explicitly authorized columns and make schema output changes subject to contract review.
Explanation
Correct Answer
B:A limited result set is not meaningful or reproducible without a deterministic selection order.
Incorrect Answers
A:Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records: 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: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; Define a business-specific ORDER BY with a stable tie-breaker before applying TOP.
D:Select explicitly authorized columns and make schema output changes subject to contract review: select explicitly authorized columns and make schema output changes subject to contract review: explicit projections avoid accidental information disclosure when the source table grows.
Question 690
A SQL support search must find both precise error codes and semantically similar descriptions in one result set. A team wants to combine independently ranked full-text and vector result lists without calibrating their raw scores. Which response best addresses these needs?
- Deduplicate candidates by stable document ID before final ranking and context assembly; evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance.
- Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; do not Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types.
- 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.
- 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.
Explanation
Correct Answer
C:First, hybrid search covers literal identifier matches and semantic relationships that either method alone can miss. Second, RRF combines ranked positions rather than directly adding incompatible raw scoring units.
Incorrect Answers
A:Deduplicate candidates by stable document ID before final ranking and context assembly; evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance: hybrid search covers literal identifier matches and semantic relationships that either method alone can miss.
B:Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; do not Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types: combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types.
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: rRF combines ranked positions rather than directly adding incompatible raw scoring units.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests