Question 880
Issue 1: Developers manually change production table schemas but the SQL Database Project no longer represents the deployed model. Issue 2: An application changes a view in one branch and a stored procedure in another, causing merge conflicts that the team resolves by dropping one file. Issue 3: A team stores all schema changes only in screenshots of query windows and cannot reproduce the last release in disaster recovery. Which complete response addresses the three issues in order?
- Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow; Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; and add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references.
- Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow; Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; and 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; and Store the SQL Database Project and migration decisions in version control with reproducible build artifacts.
- Add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references; Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; and Store the SQL Database Project and migration decisions in version control with reproducible build artifacts.
Explanation
Correct Answer
B:For issue 1, schema-as-code relies on the model being the authoritative definition rather than an outdated snapshot. For issue 2, a textual merge is not sufficient when related database objects have behavioral dependencies. For issue 3, versioned source and artifacts support reproducible database releases rather than undocumented manual changes.
Incorrect Answers
A:Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow; Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; and 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.
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; and Store the SQL Database Project and migration decisions in version control with reproducible build artifacts: a textual merge is not sufficient when related database objects have behavioral dependencies.
D:Add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references; Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; and 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.
Question 881
An AI-enabled database project needs two corrections. A legal research service must compare every candidate embedding for a small corpus where exact top neighbors are required for audit. A developer assumes KNN is necessarily a special vector-index type rather than an exact search strategy in the current DP-800 outline. Which course of action addresses the stated issues?
- Use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size; distinguish exact k-nearest-neighbor search from ANN index approaches and verify target SQL implementation details.
- Benchmark ANN recall against exact KNN results on held-out safety-sensitive queries before release; distinguish exact k-nearest-neighbor search from ANN index approaches and verify target SQL implementation details.
- Distinguish exact k-nearest-neighbor search from ANN index approaches and verify target SQL implementation details; do not use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size.
- Use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size; evaluate an ANN vector index with documented recall and latency targets against an exact baseline.
Explanation
Correct Answer
A:First, exact KNN examines candidates without approximation, trading more compute for exact ranking. Second, the current outline contrasts approximate vector retrieval with exact neighbor search, not interchangeable acronyms.
Incorrect Answers
B:Benchmark ANN recall against exact KNN results on held-out safety-sensitive queries before release; distinguish exact k-nearest-neighbor search from ANN index approaches and verify target SQL implementation details: exact KNN examines candidates without approximation, trading more compute for exact ranking.
C:Distinguish exact k-nearest-neighbor search from ANN index approaches and verify target SQL implementation details; do not use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size: use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size.
D:Use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size; evaluate an ANN vector index with documented recall and latency targets against an exact baseline: the current outline contrasts approximate vector retrieval with exact neighbor search, not interchangeable acronyms.
Question 882
An application team must address both issues before release. A database receives daily new embeddings but its vector index is never maintained and search relevance degrades after months of growth. A vector index is created for one embedding dimension and a new model produces vectors of incompatible size. Which response best addresses these needs?
- Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements; 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; do not Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements.
- Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements; Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration.
- Coordinate schema and query changes through versioned deployment and integration tests before cutover; Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration.
Explanation
Correct Answer
C:First, vector indexes require lifecycle management as the indexed corpus changes. Second, index definitions and stored vector dimensions must match the selected embedding model.
Incorrect Answers
A:Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements; 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.
B:Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration; do not Monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements: monitor supported index maintenance and rebuild or refresh mechanisms based on actual platform guidance and measurements.
D:Coordinate schema and query changes through versioned deployment and integration tests before cutover; Rebuild the vector schema and index for the new dimension and re-embed data in a controlled migration: vector indexes require lifecycle management as the indexed corpus changes.
Question 883
Issue 1: An order system stores monetary totals as floating-point values, and end-of-day reconciliation differs by a few cents between reports. Issue 2: An import includes orders referring to customer IDs that do not exist, and downstream joins silently drop those orders. Issue 3: A subscription table accepts negative durations and arbitrary status strings, producing invalid invoices despite correct column types. Which complete response addresses the three issues in order?
- 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; and add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records.
- 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; and 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 a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it; and 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 and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows; and add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records.
Explanation
Correct Answer
A:For issue 1, exact decimal storage is appropriate for financial values because binary floating-point representation can introduce rounding artifacts. For issue 2, referential integrity prevents new orphan records at write time rather than relying on every report to detect them. For issue 3, a CHECK constraint expresses row-level business rules that a data type alone does not enforce.
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; and define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it: a CHECK constraint expresses row-level business rules that a data type alone does not enforce.
C: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; and add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records: referential integrity prevents new orphan records at write time rather than relying on every report to detect them.
D:Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it; define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows; and add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records: exact decimal storage is appropriate for financial values because binary floating-point representation can introduce rounding artifacts.
Question 884
A database architect reviews two implementation risks. A developer compares vectors produced by two different embedding models as though their coordinates were directly interchangeable. A semantic index was built from a model that the organization can no longer invoke, so new documents cannot enter the same vector space. Which course of action addresses the stated issues?
- Select an eligible replacement model and perform a controlled full re-embedding and index cutover; do not use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration.
- Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration; Regenerate compatible embeddings and align vector dimensions and index schema with the selected model.
- Evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment; select an eligible replacement model and perform a controlled full re-embedding and index cutover.
- Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration; select an eligible replacement model and perform a controlled full re-embedding and index cutover.
Explanation
Correct Answer
D:First, coordinates from unrelated embedding models do not generally share meaningful geometric semantics. Second, mixing incompatible model outputs can degrade search even when dimensions happen to match.
Incorrect Answers
A:Select an eligible replacement model and perform a controlled full re-embedding and index cutover; do not use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration: use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration.
B:Use one compatible embedding space for indexed data and queries or plan a complete tested re-embedding migration; Regenerate compatible embeddings and align vector dimensions and index schema with the selected model: mixing incompatible model outputs can degrade search even when dimensions happen to match.
C:Evaluate embedding retrieval on labeled domain queries and measure recall and error patterns before deployment; select an eligible replacement model and perform a controlled full re-embedding and index cutover: coordinates from unrelated embedding models do not generally share meaningful geometric semantics.
Question 885
An AI-enabled database project needs two corrections. An application stores flexible attributes in an NVARCHAR JSON document but queries a specific scalar property thousands of times per second. A normalized order table must expand an incoming JSON array into rows for validation and insertion into child records. Which response best addresses these needs?
- Use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion; do not evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property.
- 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.
- Evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property; use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
- Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
Explanation
Correct Answer
C:First, exposing a searchable JSON property to an index can avoid repeatedly parsing every document during selective filtering. Second, OPENJSON converts array elements into tabular data suitable for relational constraints and joins.
Incorrect Answers
A:Use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion; do not evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property: evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property.
B: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: oPENJSON converts array elements into tabular data suitable for relational constraints and joins.
D:Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; 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.
Question 886
Issue 1: An operator patches a production column manually and the next automated release attempts to reverse the change unexpectedly. Issue 2: A generated deployment plan proposes dropping a populated customer column simply because it was renamed in source. Issue 3: A release contains an irreversible transformation but the deployment plan says rollback is just redeploying the old database project. Which complete response addresses the three issues in order?
- Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment; review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; and Prepare a validated data backup or forward-fix recovery approach and test it before the change window.
- Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; and Prepare a validated data backup or forward-fix recovery approach and test it before the change window.
- Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment; Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; and Prepare a validated data backup or forward-fix recovery approach and test it before the change window.
- Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment; review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; and Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL.
Explanation
Correct Answer
A:For issue 1, drift checks identify differences between the source model and the actual target database. For issue 2, automated schema comparison can interpret renames as destructive drop-and-create operations. For issue 3, reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
Incorrect Answers
B:Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; and Prepare a validated data backup or forward-fix recovery approach and test it before the change window: drift checks identify differences between the source model and the actual target database.
C:Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment; Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; and Prepare a validated data backup or forward-fix recovery approach and test it before the change window: automated schema comparison can interpret renames as destructive drop-and-create operations.
D:Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment; review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; and Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL: reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
Question 887
Issue 1: An audit team needs to reconstruct a customer's recorded address as it appeared last month without overwriting today's address. Issue 2: A regulated workflow needs tamper-evident verification that past financial entries were not silently modified after acceptance. Issue 3: A temporal table's history grows indefinitely and breaches retention policy after every minor order-status correction. Which complete response addresses the three issues in order?
- Explain the difference between temporal history and tamper-evident ledger verification and select controls to match the threat model; evaluate SQL ledger tables and their cryptographic digest verification as part of the audit design; and configure a supported temporal-history retention and cleanup plan aligned with policy and recovery requirements.
- Evaluate a system-versioned temporal table with history retention and query records using supported temporal clauses; evaluate SQL ledger tables and their cryptographic digest verification as part of the audit design; and Explain the difference between temporal history and tamper-evident ledger verification and select controls to match the threat model.
- Evaluate a system-versioned temporal table with history retention and query records using supported temporal clauses; evaluate SQL ledger tables and their cryptographic digest verification as part of the audit design; and configure a supported temporal-history retention and cleanup plan aligned with policy and recovery requirements.
- Evaluate a system-versioned temporal table with history retention and query records using supported temporal clauses; Explain the difference between temporal history and tamper-evident ledger verification and select controls to match the threat model; and configure a supported temporal-history retention and cleanup plan aligned with policy and recovery requirements.
Explanation
Correct Answer
C:For issue 1, temporal history preserves prior row versions for point-in-time analysis without replacing the current operational state. For issue 2, ledger tamper evidence addresses integrity verification, which ordinary row history does not provide by itself. For issue 3, history tables accumulate earlier row versions and need lifecycle governance rather than indefinite default retention.
Incorrect Answers
A:Explain the difference between temporal history and tamper-evident ledger verification and select controls to match the threat model; evaluate SQL ledger tables and their cryptographic digest verification as part of the audit design; and configure a supported temporal-history retention and cleanup plan aligned with policy and recovery requirements: temporal history preserves prior row versions for point-in-time analysis without replacing the current operational state.
B:Evaluate a system-versioned temporal table with history retention and query records using supported temporal clauses; evaluate SQL ledger tables and their cryptographic digest verification as part of the audit design; and Explain the difference between temporal history and tamper-evident ledger verification and select controls to match the threat model: history tables accumulate earlier row versions and need lifecycle governance rather than indefinite default retention.
D:Evaluate a system-versioned temporal table with history retention and query records using supported temporal clauses; Explain the difference between temporal history and tamper-evident ledger verification and select controls to match the threat model; and configure a supported temporal-history retention and cleanup plan aligned with policy and recovery requirements: ledger tamper evidence addresses integrity verification, which ordinary row history does not provide by itself.
Question 888
An application team must address both issues before release. A frontend needs to traverse customer orders as related objects in one authorized GraphQL query but DAB exposes them as unrelated scalar fields. A REST API provides one table but a client needs an approved read-only stored procedure that returns a summarized invoice report. Which response best addresses these needs?
- Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights; do not configure supported DAB entity relationships and GraphQL navigation with restrictive permissions.
- Configure supported DAB entity relationships and GraphQL navigation with restrictive permissions; enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters.
- Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller; Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights.
- Configure supported DAB entity relationships and GraphQL navigation with restrictive permissions; Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights.
Explanation
Correct Answer
D:First, GraphQL relationships require explicit mapping to expose navigable links between database entities. Second, DAB can publish controlled database objects, but procedure authorization remains essential.
Incorrect Answers
A:Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights; do not configure supported DAB entity relationships and GraphQL navigation with restrictive permissions: configure supported DAB entity relationships and GraphQL navigation with restrictive permissions.
B:Configure supported DAB entity relationships and GraphQL navigation with restrictive permissions; enforce supported REST or GraphQL filter inputs and keep dynamic SQL construction out of untrusted client parameters: dAB can publish controlled database objects, but procedure authorization remains essential.
C:Rework the effective identity or tenant predicate and test GraphQL results for each isolated caller; Expose the appropriate stored procedure through supported DAB configuration and grant restricted execution rights: graphQL relationships require explicit mapping to expose navigable links between database entities.
Question 889
A database architect reviews two implementation risks. An application relies on a fixed cosine distance cutoff borrowed from a different embedding model and rejects almost every useful product query. A query result presents a strong nearest neighbor as factual proof of a tax rate without checking its effective source document. What is the most appropriate plan for this situation?
- Verify the matched passage against authoritative approved records before returning the tax fact; do not 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; verify the matched passage against authoritative approved records before returning the tax fact.
- Calibrate thresholds on labeled examples from the actual embedding model and document uncertainty behavior; align the displayed relevance interpretation with the selected distance metric and verify ordering against known examples.
- Implement a relevance/no-answer threshold and abstention or escalation path for low-evidence queries; verify the matched passage against authoritative approved records before returning the tax fact.
Explanation
Correct Answer
B:First, semantic score distributions depend on model and corpus, making transplanted thresholds unreliable. Second, neighbor similarity is not evidence of factual correctness or current legal authority.
Incorrect Answers
A:Verify the matched passage against authoritative approved records before returning the tax fact; do not 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.
C:Calibrate thresholds on labeled examples from the actual embedding model and document uncertainty behavior; align the displayed relevance interpretation with the selected distance metric and verify ordering against known examples: neighbor similarity is not evidence of factual correctness or current legal authority.
D:Implement a relevance/no-answer threshold and abstention or escalation path for low-evidence queries; verify the matched passage against authoritative approved records before returning the tax fact: semantic score distributions depend on model and corpus, making transplanted thresholds unreliable.
Question 890
An Azure SQL application suddenly has higher latency after its transaction volume triples and resource metrics show sustained compute saturation. What should the SQL developer do next?
- 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.
- 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.
Explanation
Correct Answer
A:Capacity planning should follow CPU, IO and workload measurements rather than an untested tier upgrade.
Incorrect Answers
B:Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix: load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix: single-user latency does not predict contention or service capacity under production load; Compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck.
C:Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity: fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity: unbounded client sessions can consume resources without improving transaction throughput; Compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck.
D: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: tempdb pressure can reflect workload design rather than an external network problem; Compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests