Question 550
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 team uses extremely tiny overlapping chunks and the vector index triples in size while many near-identical snippets dominate results. 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?
- 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.
- Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; 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; 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; Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; and Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness.
Explanation
Correct Answer
C:For issue 1, smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding. For issue 2, excessive overlap can raise duplicate retrieval and index cost without improving answer quality. For issue 3, RAG evidence must be traceable to its originating authorized document.
Incorrect Answers
A: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: excessive overlap can raise duplicate retrieval and index cost without improving answer quality.
B:Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; 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: smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding.
D: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 Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness: rAG evidence must be traceable to its originating authorized document.
Question 551
An application team must address both issues before release. An AI chat session lists several available MCP tools, including one that can modify production data, for a user authorized only to analyze schema. A developer installs an MCP connector to Fabric without documenting which workspace and datasets it can access. Which course of action addresses the stated issues?
- Configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity.
- Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use; do not configure tool availability and effective identity to permit read-only schema operations and block write-capable tools.
- Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use.
- Configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use.
Explanation
Correct Answer
D:First, tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording. Second, unscoped tool connections risk exposing more data than the intended development workflow requires.
Incorrect Answers
A:Configure tool availability and effective identity to permit read-only schema operations and block write-capable tools; connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity: unscoped tool connections risk exposing more data than the intended development workflow requires.
B:Configure tool availability and effective identity to permit read-only schema operations and block write-capable tools.
C:Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use: tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording.
Question 552
A database architect reviews two implementation risks. A customer identifier accepts duplicate values even though every integration expects a single permanent row key. An import includes orders referring to customer IDs that do not exist, and downstream joins silently drop those orders. Which response best addresses these needs?
- 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.
- 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.
- Define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows; do not 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; Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values.
Explanation
Correct Answer
A:First, a primary key establishes unique row identity and prevents null key values, unlike a display-only index. Second, referential integrity prevents new orphan records at write time rather than relying on every report to detect them.
Incorrect Answers
B: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: a primary key establishes unique row identity and prevents null key values, unlike a display-only index.
C:Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it.
D: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: referential integrity prevents new orphan records at write time rather than relying on every report to detect them.
Question 553
An operator patches a production column manually and the next automated release attempts to reverse the change unexpectedly. 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 appropriate plan for this situation?
- 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.
- 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.
- 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.
- Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; do not Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment.
Explanation
Correct Answer
B:First, drift checks identify differences between the source model and the actual target database. Second, existing data must satisfy new column constraints during deployment, not only future inserts.
Incorrect Answers
A: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: 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; review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment: existing data must satisfy new column constraints during deployment, not only future inserts.
D:Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; do not 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.
Question 554
Issue 1: An analyst assumes Copilot in Fabric will automatically query a private lakehouse owned by another division without a permission grant. Issue 2: A team wants an AI assistant to explain why a T-SQL view is slow but provides only an error-free query text and no execution evidence. Issue 3: A model proposes changing a schema to satisfy a prompt, but the Fabric project's pull-request policy requires two reviewers. Which complete response addresses the three issues in order?
- Verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance; Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; and validate the target engine's supported SQL surface and deploy only code tested against that platform.
- Validate the target engine's supported SQL surface and deploy only code tested against that platform; Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; and Submit the change through the required pull request and validation process rather than applying the model output directly.
- Verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance; validate the target engine's supported SQL surface and deploy only code tested against that platform; and Submit the change through the required pull request and validation process rather than applying the model output directly.
- Verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance; Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; and Submit the change through the required pull request and validation process rather than applying the model output directly.
Explanation
Correct Answer
D:For issue 1, assistant capability does not bypass Fabric resource authorization. For issue 2, optimization guidance without actual plans and runtime evidence can choose the wrong bottleneck. For issue 3, AI-assisted authoring does not supersede development governance and approval requirements.
Incorrect Answers
A:Verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance; Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; and validate the target engine's supported SQL surface and deploy only code tested against that platform: aI-assisted authoring does not supersede development governance and approval requirements.
B:Validate the target engine's supported SQL surface and deploy only code tested against that platform; Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; and Submit the change through the required pull request and validation process rather than applying the model output directly: assistant capability does not bypass Fabric resource authorization.
C:Verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance; validate the target engine's supported SQL surface and deploy only code tested against that platform; and Submit the change through the required pull request and validation process rather than applying the model output directly: optimization guidance without actual plans and runtime evidence can choose the wrong bottleneck.
Question 555
An error handler logs only 'failed' and hides the SQL error number and transaction context needed to diagnose deadlocks. A trigger intended to enforce a spending limit uses cursor loops over every inserted row and stalls during hourly bulk loads. Which response best addresses these needs?
- Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details; Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior.
- Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes; do not Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details.
- Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented; Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes.
- Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details; Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes.
Explanation
Correct Answer
D:First, structured error evidence is necessary for debugging without falsely reporting successful completion. Second, set-based handling avoids unnecessary per-row overhead in statement-level trigger execution.
Incorrect Answers
A:Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details; Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior: set-based handling avoids unnecessary per-row overhead in statement-level trigger execution.
B:Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details.
C:Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented; Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes: structured error evidence is necessary for debugging without falsely reporting successful completion.
Question 556
An AI-enabled database project needs two corrections. An analytics query repeatedly scans billions of historical sales rows to aggregate a few numeric columns by month. A table gains five overlapping indexes and update latency doubles because each insert must maintain redundant access paths. What is the most appropriate plan for this situation?
- Inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs; do not evaluate a columnstore index against the batch-oriented analytical workload and measure compression and scan improvements.
- Evaluate a columnstore index against the batch-oriented analytical workload and measure compression and scan improvements; inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs.
- Test a composite nonclustered index whose leading keys match the selective query filters and review its execution plan; inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs.
- Evaluate a columnstore index against the batch-oriented analytical workload and measure compression and scan improvements; keep an appropriate rowstore lookup index for selective invoice requests and benchmark it with actual predicates.
Explanation
Correct Answer
B:First, columnstore storage is suited to large analytical scans and aggregation, subject to workload-specific testing. Second, additional indexes have write and storage costs, so more indexes are not automatically better for mixed workloads.
Incorrect Answers
A:Inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs; do not evaluate a columnstore index against the batch-oriented analytical workload and measure compression and scan improvements: evaluate a columnstore index against the batch-oriented analytical workload and measure compression and scan improvements.
C:Test a composite nonclustered index whose leading keys match the selective query filters and review its execution plan; inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs: columnstore storage is suited to large analytical scans and aggregation, subject to workload-specific testing.
D:Evaluate a columnstore index against the batch-oriented analytical workload and measure compression and scan improvements; keep an appropriate rowstore lookup index for selective invoice requests and benchmark it with actual predicates: additional indexes have write and storage costs, so more indexes are not automatically better for mixed workloads.
Question 557
Issue 1: An external model API responds intermittently with throttling, and SQL code retries indefinitely while holding an open business transaction. Issue 2: A model endpoint requires a structured JSON request but a stored procedure concatenates user text directly into raw JSON, breaking on quotation marks. Issue 3: A response-processing procedure assumes every HTTP 200 body contains the expected model text even when the provider returns a different schema version. Which complete response addresses the three issues in order?
- Bound and back off retries outside long-running critical transactions and handle model errors explicitly; Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; and use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
- 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; and 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; use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; and 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; Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; and validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
Explanation
Correct Answer
D:For issue 1, external inference availability should not monopolize database locks or create uncontrolled request storms. For issue 2, structured JSON construction avoids malformed payloads and accidental text-to-syntax injection. For issue 3, a successful transport response does not guarantee an unchanged application-level payload contract.
Incorrect Answers
A:Bound and back off retries outside long-running critical transactions and handle model errors explicitly; Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint; and 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.
B: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; and validate HTTP outcomes and the returned JSON structure before extracting and storing generated content: external inference availability should not monopolize database locks or create uncontrolled request storms.
C:Bound and back off retries outside long-running critical transactions and handle model errors explicitly; use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; and 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 558
An application team must address both issues before release. A semantic-search implementation compares vectors with raw text equality and never computes their numerical similarity. A team uses a distance threshold copied from a cosine model with an unnormalized Euclidean embedding dataset and gets inconsistent results. Which response best addresses these needs?
- Use supported vector distance or search functions on compatible embeddings to rank semantic neighbors; inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison.
- Use supported vector distance or search functions on compatible embeddings to rank semantic neighbors; 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; do not use supported vector distance or search functions on compatible embeddings to rank semantic neighbors.
- Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step; Calibrate distance metrics and normalization against the actual embedding model and representative relevance judgments.
Explanation
Correct Answer
B:First, semantic similarity is based on vector geometry rather than equality of serialized embedding text. Second, different metrics and vector scales produce different score distributions and cannot share thresholds blindly.
Incorrect Answers
A:Use supported vector distance or search functions on compatible embeddings to rank semantic neighbors; inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison: different metrics and vector scales produce different score distributions and cannot share thresholds blindly.
C:Calibrate distance metrics and normalization against the actual embedding model and representative relevance judgments; do not 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.
D:Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step; Calibrate distance metrics and normalization against the actual embedding model and representative relevance judgments: semantic similarity is based on vector geometry rather than equality of serialized embedding text.
Question 559
An AI app embeds permanent administrator tokens directly in the external model definition shared with analysts. A team's staging SQL instance cannot use a newly proposed external model capability because its engine version lacks the required feature. What is the most appropriate plan for this situation?
- Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility; configure the supported external model definition and secure its endpoint authentication under the target SQL platform.
- Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility; check supported platform and version prerequisites before deploying the external model integration.
- Version and validate the external model integration contract before approving the changed deployment; check supported platform and version prerequisites before deploying the external model integration.
- Check supported platform and version prerequisites before deploying the external model integration; do not Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility.
Explanation
Correct Answer
B:First, model access must follow least privilege and avoid exposing reusable high-value credentials. Second, SQL AI features differ by engine edition and release and cannot be assumed portable without verification.
Incorrect Answers
A:Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility; configure the supported external model definition and secure its endpoint authentication under the target SQL platform: sQL AI features differ by engine edition and release and cannot be assumed portable without verification.
C:Version and validate the external model integration contract before approving the changed deployment; check supported platform and version prerequisites before deploying the external model integration: model access must follow least privilege and avoid exposing reusable high-value credentials.
D:Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility.
Question 560
An AI assistant claims a SQL function exists in every SQL Server version despite documentation showing platform-specific availability. A pull request contains AI-generated schema changes with no migration notes, rollback or risk assessment for existing records. Which course of action addresses the stated issues?
- Check the target platform and version documentation and run compatibility tests before accepting generated syntax; document migration impact and rollback strategy and require standard review and integration tests before merge.
- Document migration impact and rollback strategy and require standard review and integration tests before merge; do not check the target platform and version documentation and run compatibility tests before accepting generated syntax.
- Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant; document migration impact and rollback strategy and require standard review and integration tests before merge.
- Check the target platform and version documentation and run compatibility tests before accepting generated syntax; define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment.
Explanation
Correct Answer
A:First, generated statements should not be assumed portable across database versions and services. Second, AI-assisted changes need the same production-readiness evidence as hand-authored database migrations.
Incorrect Answers
B:Check the target platform and version documentation and run compatibility tests before accepting generated syntax.
C:Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant; document migration impact and rollback strategy and require standard review and integration tests before merge: generated statements should not be assumed portable across database versions and services.
D:Check the target platform and version documentation and run compatibility tests before accepting generated syntax; define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment: aI-assisted changes need the same production-readiness evidence as hand-authored database migrations.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests