Question 650
A model endpoint requires a structured JSON request but a stored procedure concatenates user text directly into raw JSON, breaking on quotation marks. A response-processing procedure assumes every HTTP 200 body contains the expected model text even when the provider returns a different schema version. Which course of action addresses the stated issues?
- Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; do not Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
- Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
- Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
- Bound and back off retries outside long-running critical transactions and handle model errors explicitly; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
Explanation
Correct Answer
C:First, structured JSON construction avoids malformed payloads and accidental text-to-syntax injection. Second, a successful transport response does not guarantee an unchanged application-level payload contract.
Incorrect Answers
A:Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
B:Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling: a successful transport response does not guarantee an unchanged application-level payload contract.
D:Bound and back off retries outside long-running critical transactions and handle model errors explicitly; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content: structured JSON construction avoids malformed payloads and accidental text-to-syntax injection.
Question 651
A dashboard joins on a string column whose collation differs from the other table, causing an implicit conversion in the plan. Which recommendation best resolves this issue?
- Align the join data types and compatible collations where safe, then rerun the actual plan.
- Replace non-sargable extraction with a bounded date range appropriate to the required period.
- Test a composite access index aligned to AccountId and the date range and inspect its seek and maintenance costs.
- Review index usage and trim redundant included columns or overlapping indexes after benchmarking writes.
Explanation
Correct Answer
A:Implicit conversions can prevent efficient seek operations and complicate index use.
Incorrect Answers
B:Replace non-sargable extraction with a bounded date range appropriate to the required period: a range against the stored date column can support efficient index access instead of calculating a function for every row.
C:Test a composite access index aligned to AccountId and the date range and inspect its seek and maintenance costs: appropriate leading index keys can reduce reads for selective account-scoped time queries.
D:Review index usage and trim redundant included columns or overlapping indexes after benchmarking writes: covering indexes can avoid lookups but carry storage and write amplification costs.
Question 652
An application team must address both issues before release. An employee directory embedding includes Social Security numbers and access tokens unrelated to the approved people-search workflow. A contact-search index stores embeddings of full long customer notes but omits tenant and source identifiers from the retrieval record. What is the most appropriate plan for this situation?
- Exclude sensitive irrelevant fields and classify embedding data before generating or storing vector representations; Embed relevant descriptive fields while retaining stable IDs as separate metadata for authoritative lookup.
- Retain tenant ownership and provenance metadata with each vector for authorization and source verification; do not Exclude sensitive irrelevant fields and classify embedding data before generating or storing vector representations.
- Select the current approved policy text and preserve version and status metadata alongside each embedding; retain tenant ownership and provenance metadata with each vector for authorization and source verification.
- Exclude sensitive irrelevant fields and classify embedding data before generating or storing vector representations; retain tenant ownership and provenance metadata with each vector for authorization and source verification.
Explanation
Correct Answer
D:First, embeddings can encode sensitive information and should reflect data-minimization requirements. Second, a similarity score cannot establish which tenant owns a row or whether the source is permitted.
Incorrect Answers
A:Exclude sensitive irrelevant fields and classify embedding data before generating or storing vector representations; Embed relevant descriptive fields while retaining stable IDs as separate metadata for authoritative lookup: a similarity score cannot establish which tenant owns a row or whether the source is permitted.
B:Exclude sensitive irrelevant fields and classify embedding data before generating or storing vector representations.
C:Select the current approved policy text and preserve version and status metadata alongside each embedding; retain tenant ownership and provenance metadata with each vector for authorization and source verification: embeddings can encode sensitive information and should reflect data-minimization requirements.
Question 653
Issue 1: A policy chatbot answers from stale model weights even though an approved handbook changes weekly. Issue 2: A developer wants to retrain a foundation model to remember three SQL procedure names instead of making the approved schema available to the assistant. Issue 3: An internal RAG system produces confident responses to questions absent from its authorized corpus. Which complete response addresses the three issues in order?
- Use retrieval-augmented generation with the current authorized policy corpus and source citations; Retrieve the authoritative current balance through a governed data query and only use generation for explanation; and require the model to acknowledge missing evidence and route unsupported answers for human review.
- 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; and require the model to acknowledge missing evidence and route unsupported answers for human review.
- Use retrieval-augmented generation with the current authorized policy corpus and source citations; Provide versioned authorized schema context and retrieval rather than disproportionate model training; and require the model to acknowledge missing evidence and route unsupported answers for human review.
- Use retrieval-augmented generation with the current authorized policy corpus and source citations; Provide versioned authorized schema context and retrieval rather than disproportionate model training; and Retrieve the authoritative current balance through a governed data query and only use generation for explanation.
Explanation
Correct Answer
C:For issue 1, RAG can connect responses to changing external knowledge without retraining model weights for every revision. For issue 2, small evolving factual references are better supplied at request time than baked into model weights. For issue 3, retrieval augmentation does not justify fabricating facts when no suitable evidence is found.
Incorrect Answers
A:Use retrieval-augmented generation with the current authorized policy corpus and source citations; Retrieve the authoritative current balance through a governed data query and only use generation for explanation; and require the model to acknowledge missing evidence and route unsupported answers for human review: small evolving factual references are better supplied at request time than baked into model weights.
B: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; and require the model to acknowledge missing evidence and route unsupported answers for human review: rAG can connect responses to changing external knowledge without retraining model weights for every revision.
D:Use retrieval-augmented generation with the current authorized policy corpus and source citations; Provide versioned authorized schema context and retrieval rather than disproportionate model training; and Retrieve the authoritative current balance through a governed data query and only use generation for explanation: retrieval augmentation does not justify fabricating facts when no suitable evidence is found.
Question 654
An AI-enabled database project needs two corrections. A team exposes a private products table through Data API builder but commits an unrestricted production connection string in the public configuration repository. An administrator changes the DAB entity mapping name but clients receive 404 responses because they still call the old REST route. Which response best addresses these needs?
- 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; do not 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; 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; update and validate the entity's published REST path and coordinate a versioned client migration.
Explanation
Correct Answer
D:First, Data API builder deployment settings must not expose database credentials or overprivileged access. Second, 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; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role: aPI route names form external contracts and must match the deployed configuration.
B:Update and validate the entity's published REST path and coordinate a versioned client migration; do not Move sensitive connection settings to protected deployment configuration and scope DAB database privileges: move sensitive connection settings to protected deployment configuration and scope DAB database privileges.
C: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: data API builder deployment settings must not expose database credentials or overprivileged access.
Question 655
A developer changes from cosine to dot-product scoring but assumes normalization is irrelevant to ranking outcomes. What should the SQL developer do next?
- Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step.
- Inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison.
- Calibrate distance metrics and normalization against the actual embedding model and representative relevance judgments.
- Use supported vector distance or search functions on compatible embeddings to rank semantic neighbors.
Explanation
Correct Answer
A:Dot-product scores can depend on vector magnitudes unless normalization and model assumptions are controlled.
Incorrect Answers
B:Inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison: inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison: vector dimension validation avoids invalid comparisons and inconsistent index configuration.
C:Calibrate distance metrics and normalization against the actual embedding model and representative relevance judgments: calibrate distance metrics and normalization against the actual embedding model and representative relevance judgments: different metrics and vector scales produce different score distributions and cannot share thresholds blindly; Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step.
D:Use supported vector distance or search functions on compatible embeddings to rank semantic neighbors: use supported vector distance or search functions on compatible embeddings to rank semantic neighbors: semantic similarity is based on vector geometry rather than equality of serialized embedding text.
Question 656
Issue 1: A dashboard needs the latest order per customer without collapsing away the associated status and order ID columns. Issue 2: An analyst needs each transaction's running total within its account ordered by posting date, not one aggregate per account. Issue 3: A user wants to show all tied top-performing stores, but the report uses ROW_NUMBER and excludes equal-score ties. Which complete response addresses the three issues in order?
- Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; and 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; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
- Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use a common table expression to structure the query while validating the actual plan for repeated references; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
- 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; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
Explanation
Correct Answer
B:For issue 1, window ranking selects a complete latest row per group without an unsafe nonaggregated GROUP BY. For issue 2, windowed aggregation retains individual rows while calculating a cumulative value. For issue 3, ranking functions differ in how they handle ties and skipped rank values.
Incorrect Answers
A:Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; and use a common table expression to structure the query while validating the actual plan for repeated references: ranking functions differ in how they handle ties and skipped rank values.
C:Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use a common table expression to structure the query while validating the actual plan for repeated references; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically: windowed aggregation retains individual rows while calculating a cumulative value.
D: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; and use RANK or DENSE_RANK according to tie and gap requirements and order deterministically: window ranking selects a complete latest row per group without an unsafe nonaggregated GROUP BY.
Question 657
A source-controlled reference table adds a new status code but the application's enum rejects it in production. Which recommendation best resolves this issue?
- Coordinate data contract changes with application validation and deploy in a compatible order.
- Run dependency analysis and compatibility tests against all known consumers before publishing the new view.
- Add policy-based diff review and security tests for critical database objects before deployment approval.
- Use an expand-and-contract schema migration with temporary compatibility and monitored client cutover.
Explanation
Correct Answer
A:Static data is part of the interface between database and application code.
Incorrect Answers
B:Run dependency analysis and compatibility tests against all known consumers before publishing the new view: run dependency analysis and compatibility tests against all known consumers before publishing the new view: schema changes must account for dependent objects and client contracts, not only the modified file.
C:Add policy-based diff review and security tests for critical database objects before deployment approval: add policy-based diff review and security tests for critical database objects before deployment approval: compilation does not prove compliance controls remain present after a schema update.
D:Use an expand-and-contract schema migration with temporary compatibility and monitored client cutover: backward-compatible deployment stages prevent breaking older application instances during gradual releases.
Question 658
An application team must address both issues before release. An application stores flexible attributes in an NVARCHAR JSON document but queries a specific scalar property thousands of times per second. A SQL view extracts a scalar field from a JSON payload but occasionally receives invalid JSON and crashes the report. What is the most appropriate plan for this situation?
- Use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion; 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; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema.
- Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; 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.
Explanation
Correct Answer
D:First, exposing a searchable JSON property to an index can avoid repeatedly parsing every document during selective filtering. Second, semi-structured input needs validation and predictable failure behavior before scalar values enter authoritative reports.
Incorrect Answers
A:Use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion; validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads: exposing a searchable JSON property to an index can avoid repeatedly parsing every document during selective filtering.
B:Evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema: semi-structured input needs validation and predictable failure behavior before scalar values enter authoritative reports.
C:Validate JSON input and use appropriate supported JSON extraction with error handling for malformed payloads; 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.
Question 659
An application team must address both issues before release. A generated deployment plan proposes dropping a populated customer column simply because it was renamed in source. A release contains an irreversible transformation but the deployment plan says rollback is just redeploying the old database project. Which course of action addresses the stated issues?
- Review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; 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; Prepare a validated data backup or forward-fix recovery approach and test it before the change window.
- 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.
- Prepare a validated data backup or forward-fix recovery approach and test it before the change window; do not review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment.
Explanation
Correct Answer
C:First, automated schema comparison can interpret renames as destructive drop-and-create operations. Second, reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
Incorrect Answers
A:Review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment: reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
B:Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; 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:Prepare a validated data backup or forward-fix recovery approach and test it before the change window; do not 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.
Question 660
A model is asked to summarize regional sales but database code sends strings like '1,234' and '1.234' without locale or currency metadata. An application sends 20,000 database rows into one language-model prompt and silently truncates the newest transactions. Which response best addresses these needs?
- Normalize numeric types and include explicit units and currency in structured model context; Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities.
- Filter and aggregate source results to fit context limits, retaining references needed for verification; do not Normalize numeric types and include explicit units and currency in structured model context.
- Normalize numeric types and include explicit units and currency in structured model context; Filter and aggregate source results to fit context limits, retaining references needed for verification.
- Project and aggregate only the authorized minimum columns before serializing context for the model; Filter and aggregate source results to fit context limits, retaining references needed for verification.
Explanation
Correct Answer
C:First, ambiguous number formatting can produce incorrect calculations or misleading business narratives. Second, context budgets are finite and require intentional evidence selection rather than uncontrolled dumping.
Incorrect Answers
A:Normalize numeric types and include explicit units and currency in structured model context; Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities: context budgets are finite and require intentional evidence selection rather than uncontrolled dumping.
B:Normalize numeric types and include explicit units and currency in structured model context.
D:Project and aggregate only the authorized minimum columns before serializing context for the model; Filter and aggregate source results to fit context limits, retaining references needed for verification: ambiguous number formatting can produce incorrect calculations or misleading business narratives.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests