Question 260
A finance query repeats several nested subqueries and the developer needs one named intermediate result reused in a single statement. An analyst needs each transaction's running total within its account ordered by posting date, not one aggregate per account. Which course of action addresses the stated issues?
- Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
- Use a common table expression to structure the query while validating the actual plan for repeated references; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
- Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; do not use a common table expression to structure the query while validating the actual plan for repeated references.
- Use a common table expression to structure the query while validating the actual plan for repeated references; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one.
Explanation
Correct Answer
B:First, a CTE provides readable statement-scoped query structure rather than automatically materializing data. Second, windowed aggregation retains individual rows while calculating a cumulative value.
Incorrect Answers
A:Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance: a CTE provides readable statement-scoped query structure rather than automatically materializing data.
C:Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; do not use a common table expression to structure the query while validating the actual plan for repeated references: use a common table expression to structure the query while validating the actual plan for repeated references.
D:Use a common table expression to structure the query while validating the actual plan for repeated references; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one: windowed aggregation retains individual rows while calculating a cumulative value.
Question 261
A team keeps sensitive vector embeddings indefinitely after deleting the original customer documents under retention policy. A cloud SQL system is moved to a new region without confirming contractual residency obligations and data-flow boundaries. Which response best addresses these needs?
- Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery; assess region, service processing and backup locations against policy before executing the migration.
- Include derived embeddings and search indexes in the deletion and retention workflow and verify removal; assess region, service processing and backup locations against policy before executing the migration.
- Include derived embeddings and search indexes in the deletion and retention workflow and verify removal; use synthetic or masked test data with approved exceptions and enforce environment access separation.
- Assess region, service processing and backup locations against policy before executing the migration; do not Include derived embeddings and search indexes in the deletion and retention workflow and verify removal.
Explanation
Correct Answer
B:First, derived searchable representations can remain sensitive and need lifecycle rules consistent with their source data. Second, deployment geography can alter legal and contractual handling of protected records beyond database performance.
Incorrect Answers
A:Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery; assess region, service processing and backup locations against policy before executing the migration: derived searchable representations can remain sensitive and need lifecycle rules consistent with their source data.
C:Include derived embeddings and search indexes in the deletion and retention workflow and verify removal; use synthetic or masked test data with approved exceptions and enforce environment access separation: deployment geography can alter legal and contractual handling of protected records beyond database performance.
D:Include derived embeddings and search indexes in the deletion and retention workflow and verify removal.
Question 262
A developer optimizes a parameter-sensitive stored query with a hint derived from one tiny test case and it becomes slow for large tenants. Which action best addresses the database requirement?
- Compare representative parameter distributions and review parameter-sensitive plan behavior before pinning hints.
- Evaluate a set-based UPDATE with an appropriate predicate, test transaction size and inspect execution plans.
- Use a sargable range predicate on the original timestamp column and validate the query plan.
- Test representative data and remove or revise unjustified hints based on actual plan and latency measurements.
Explanation
Correct Answer
A:One cached plan may not serve skewed data distributions equally well.
Incorrect Answers
B:Evaluate a set-based UPDATE with an appropriate predicate, test transaction size and inspect execution plans: evaluate a set-based UPDATE with an appropriate predicate, test transaction size and inspect execution plans: set-based SQL can avoid expensive repeated row-by-row work while preserving transactional controls.
C:Use a sargable range predicate on the original timestamp column and validate the query plan: applying a function to the indexed column can prevent an efficient search range.
D:Test representative data and remove or revise unjustified hints based on actual plan and latency measurements: test representative data and remove or revise unjustified hints based on actual plan and latency measurements: optimizer hints can freeze an unsuitable access path as workload conditions change.
Question 263
An application team must address both issues before release. A finance service performs a validated multi-step booking operation and needs one callable database interface with transactional error handling. An audit requirement says modifications to a table must automatically record the old and new keys on every permitted write. Which course of action addresses the stated issues?
- Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; 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; evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks.
Explanation
Correct Answer
B:First, procedures encapsulate database operations and allow callers to execute a controlled multi-step unit. Second, triggers run with table writes and must correctly handle sets of inserted and deleted rows.
Incorrect Answers
A:Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
C: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: triggers run with table writes and must correctly handle sets of inserted and deleted rows.
D:Parameterize the procedure's dynamic SQL and grant the caller only required execution rights; evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks: procedures encapsulate database operations and allow callers to execute a controlled multi-step unit.
Question 264
Issue 1: A 100-page equipment manual is embedded as one vector, and searches for individual error codes return unrelated chapter summaries. Issue 2: A chunking routine breaks contractual clauses midway through sentences and the retrieved text no longer contains the exceptions needed to answer. Issue 3: A document embedding pipeline stores passages without any page, section or document version identifiers and reviewers cannot verify answers. Which complete response addresses the three issues in order?
- Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; and Store stable source provenance and effective-version fields alongside every embedded chunk.
- Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; and 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; Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; and Store stable source provenance and effective-version fields alongside every embedded chunk.
- Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; and Store stable source provenance and effective-version fields alongside every embedded chunk.
Explanation
Correct Answer
D:For issue 1, smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding. For issue 2, chunks must preserve enough context for accurate interpretation, not just fit a token limit. For issue 3, 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; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; and Store stable source provenance and effective-version fields alongside every embedded chunk: smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding.
B:Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; and Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap: rAG evidence must be traceable to its originating authorized document.
C:Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; and Store stable source provenance and effective-version fields alongside every embedded chunk: chunks must preserve enough context for accurate interpretation, not just fit a token limit.
Question 265
A customer-support database must send a JSON object matching a strict response schema, but the chosen language model frequently emits prose and invalid properties. A multilingual support application is tested only with English despite having primary customers in Japanese and Arabic. What is the most appropriate plan for this situation?
- Evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology.
- Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology.
- Compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; do not evaluate a model with reliable structured-output support and validate its schema behavior on representative requests.
- Evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams.
Explanation
Correct Answer
A:First, a generation model must support the output contract required by the application, not merely produce fluent answers. Second, model quality can vary substantially by language, making English-only evaluations inadequate.
Incorrect Answers
B:Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology: a generation model must support the output contract required by the application, not merely produce fluent answers.
C:Evaluate a model with reliable structured-output support and validate its schema behavior on representative requests.
D:Evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams: model quality can vary substantially by language, making English-only evaluations inadequate.
Question 266
An AI-enabled database project needs two corrections. An SQL knowledge index is rebuilt only quarterly, but source articles change daily and users frequently receive obsolete instructions. A deleted policy continues appearing in search because the embedding pipeline handles inserts and updates but ignores deletes. Which course of action addresses the stated issues?
- Implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint; 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.
- Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup; do not implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint.
- Use stable source keys, generation versions and upsert/idempotency controls when processing retries; Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup.
Explanation
Correct Answer
B:First, change capture can identify updated records so embeddings remain aligned with authoritative source content. Second, embedding lifecycle management includes deletion and retention, not only creation and refresh.
Incorrect Answers
A:Implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint; Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer: embedding lifecycle management includes deletion and retention, not only creation and refresh.
C:Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup; do not implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint: implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint.
D:Use stable source keys, generation versions and upsert/idempotency controls when processing retries; Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup: change capture can identify updated records so embeddings remain aligned with authoritative source content.
Question 267
An SQL service raises occasional deadlocks but the alert system triggers only on complete application outages. Which action best addresses the database requirement?
- Redact protected payload fields and restrict telemetry access while preserving correlation identifiers.
- Define actionable Azure Monitor alerts for relevant database and API failure signals with severity-based routing.
- Instrument Application Insights with appropriate dependency tracing and correlate database latency to request spans.
- Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations.
Explanation
Correct Answer
B:Early error and performance alerts can reveal degradations before total service loss.
Incorrect Answers
A:Redact protected payload fields and restrict telemetry access while preserving correlation identifiers; 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; distributed telemetry helps distinguish database delays from application or network stages.
D:Centralize relevant diagnostics in Log Analytics with governed retention and use targeted KQL investigations; central log queries support cross-service incident analysis and protect evidence from local process restarts.
Question 268
A Data API builder endpoint returns too many rows per request and consumers accidentally download the full database table. An administrator changes the DAB entity mapping name but clients receive 404 responses because they still call the old REST route. What is the most appropriate plan for this situation?
- Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; 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; update and validate the entity's published REST path and coordinate a versioned client migration.
- Update and validate the entity's published REST path and coordinate a versioned client migration; do not 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; update and validate the entity's published REST path and coordinate a versioned client migration.
Explanation
Correct Answer
D:First, API pagination bounds response size and reduces accidental unbounded query load. Second, API route names form external contracts and must match the deployed configuration.
Incorrect Answers
A:Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior; Move sensitive connection settings to protected deployment configuration and scope DAB database privileges: aPI route names form external contracts and must match the deployed configuration.
B:Restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; update and validate the entity's published REST path and coordinate a versioned client migration: aPI pagination bounds response size and reduces accidental unbounded query load.
C:Configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
Question 269
A migration adds a NOT NULL column to a table with millions of existing rows and fails because no value is available for old records. What is the most defensible technical decision?
- Review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment.
- 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.
Explanation
Correct Answer
D:Existing data must satisfy new column constraints during deployment, not only future inserts.
Incorrect Answers
A:Review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment: review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment: automated schema comparison can interpret renames as destructive drop-and-create operations.
B:Prepare a validated data backup or forward-fix recovery approach and test it before the change window: prepare a validated data backup or forward-fix recovery approach and test it before the change window: reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
C:Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment: detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment: drift checks identify differences between the source model and the actual target database; Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL.
Question 270
An application team must address both issues before release. A query needs to return a computed tax multiplier for one input amount, and a team proposes a full trigger-based process. A team grants a view SELECT permission and assumes this alone makes rows from every underlying table safe for all tenants. Which response best addresses these needs?
- Use a deterministic scalar function or expression appropriate to the platform and performance requirements; evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- Evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary; do not use a deterministic scalar function or expression appropriate to the platform and performance requirements.
- Choose an appropriate table-valued function and validate its plan behavior and input filtering; evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- Use a deterministic scalar function or expression appropriate to the platform and performance requirements; create a view exposing only approved columns and predicates with appropriate permissions and schema review.
Explanation
Correct Answer
A:First, a scalar calculation produces one result from inputs without needing write-time side effects. Second, view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Incorrect Answers
B:Use a deterministic scalar function or expression appropriate to the platform and performance requirements.
C:Choose an appropriate table-valued function and validate its plan behavior and input filtering; evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: a scalar calculation produces one result from inputs without needing write-time side effects.
D:Use a deterministic scalar function or expression appropriate to the platform and performance requirements; create a view exposing only approved columns and predicates with appropriate permissions and schema review: view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests