Question 30
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. What should the SQL developer do next?
- Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test.
- 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.
- Submit the change through the required pull request and validation process rather than applying the model output directly.
Explanation
Correct Answer
A:Optimization guidance without actual plans and runtime evidence can choose the wrong bottleneck.
Incorrect Answers
B:Verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance: verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance: assistant capability does not bypass Fabric resource authorization.
C:Validate the target engine's supported SQL surface and deploy only code tested against that platform: microsoft SQL experiences share language concepts but can differ in supported database capabilities.
D:Submit the change through the required pull request and validation process rather than applying the model output directly: submit the change through the required pull request and validation process rather than applying the model output directly: aI-assisted authoring does not supersede development governance and approval requirements.
Question 31
A vector index returns a highly similar contract from another tenant despite the query caller lacking permission to see it. Which recommendation best resolves this issue?
- Minimize search telemetry to approved metadata and apply access and retention safeguards.
- Retain provenance through retrieval and answer formatting with stable document and section references.
- Apply authoritative tenant and access-control filters before exposing or using retrieved candidate text.
- Filter results by approved status and effective version metadata before generating policy answers.
Explanation
Correct Answer
C:Search relevance cannot override data authorization, regardless of similarity score.
Incorrect Answers
A:Minimize search telemetry to approved metadata and apply access and retention safeguards: search troubleshooting must not become an uncontrolled copy of sensitive source content.
B:Retain provenance through retrieval and answer formatting with stable document and section references: auditable answers require traceable source locations beyond a plausible title.
D:Filter results by approved status and effective version metadata before generating policy answers: source authority and freshness must be enforced independently of semantic closeness.
Question 32
An operational application needs to accept a table of hundreds of product IDs in one database call instead of opening a connection for every ID. An error handler logs only 'failed' and hides the SQL error number and transaction context needed to diagnose deadlocks. Which course of action addresses the stated issues?
- Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details; do not Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior.
- Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior; 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; 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; Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented.
Explanation
Correct Answer
B:First, table-valued inputs can reduce round trips and support relational operations on a batch of IDs. Second, structured error evidence is necessary for debugging without falsely reporting successful completion.
Incorrect Answers
A:Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior.
C: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: table-valued inputs can reduce round trips and support relational operations on a batch of IDs.
D:Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior; Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented: structured error evidence is necessary for debugging without falsely reporting successful completion.
Question 33
An application team must address both issues before release. A financial audit pipeline must capture each row-level change with old and new values for an incremental warehouse history. An operations workflow needs human approvals after an order status change and spans messaging, audit and external services. Which response best addresses these needs?
- Evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform; evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization.
- Evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps; do not evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform.
- Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps.
- Evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform; evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps.
Explanation
Correct Answer
D:First, CDC records change details useful for reconstructing mutations beyond key-only change indicators. Second, an orchestrated workflow is suited to multi-service approvals that should not run inside a database transaction.
Incorrect Answers
A:Evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform; evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization: an orchestrated workflow is suited to multi-service approvals that should not run inside a database transaction.
B:Evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform.
C:Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps: cDC records change details useful for reconstructing mutations beyond key-only change indicators.
Question 34
A team demonstrates fast ANN performance but has no ground-truth comparison for whether critical safety passages are missed. What should the SQL developer do next?
- Evaluate an ANN vector index with documented recall and latency targets against an exact baseline.
- 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.
- Use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size.
Explanation
Correct Answer
C:A speed result alone does not establish the retrieval quality of approximate search.
Incorrect Answers
A:Evaluate an ANN vector index with documented recall and latency targets against an exact baseline: aNN reduces search work using an index but can omit some exact nearest neighbors.
B:Distinguish exact k-nearest-neighbor search from ANN index approaches and verify target SQL implementation details: the current outline contrasts approximate vector retrieval with exact neighbor search, not interchangeable acronyms.
D:Use exact k-nearest-neighbor evaluation and benchmark its latency against the corpus size: exact KNN examines candidates without approximation, trading more compute for exact ranking.
Question 35
Issue 1: A developer asks Copilot to add row-level security but does not specify tenant identity, causing it to generate a permissive predicate. Issue 2: An AI assistant claims a SQL function exists in every SQL Server version despite documentation showing platform-specific availability. Issue 3: A pull request contains AI-generated schema changes with no migration notes, rollback or risk assessment for existing records. Which complete response addresses the three issues in order?
- Define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment; check the target platform and version documentation and run compatibility tests before accepting generated syntax; and document migration impact and rollback strategy and require standard review and integration tests before merge.
- Define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment; check the target platform and version documentation and run compatibility tests before accepting generated syntax; and use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant.
- Define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment; use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant; and document migration impact and rollback strategy and require standard review and integration tests before merge.
- Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant; check the target platform and version documentation and run compatibility tests before accepting generated syntax; and document migration impact and rollback strategy and require standard review and integration tests before merge.
Explanation
Correct Answer
A:For issue 1, security predicates depend on correct identity semantics that cannot be inferred safely from vague instructions. For issue 2, generated statements should not be assumed portable across database versions and services. For issue 3, AI-assisted changes need the same production-readiness evidence as hand-authored database migrations.
Incorrect Answers
B:Define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment; check the target platform and version documentation and run compatibility tests before accepting generated syntax; and use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant: aI-assisted changes need the same production-readiness evidence as hand-authored database migrations.
C:Define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment; use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant; and 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:Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant; check the target platform and version documentation and run compatibility tests before accepting generated syntax; and document migration impact and rollback strategy and require standard review and integration tests before merge: security predicates depend on correct identity semantics that cannot be inferred safely from vague instructions.
Question 36
Issue 1: An AI chat session lists several available MCP tools, including one that can modify production data, for a user authorized only to analyze schema. Issue 2: A Copilot agent receives a schema-inspection tool result containing a directive to reveal a secret in the next chat message. Issue 3: A developer installs an MCP connector to Fabric without documenting which workspace and datasets it can access. Which complete response addresses the three issues in order?
- Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; and 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; connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; and 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; Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; and connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity.
- 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; and Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use.
Explanation
Correct Answer
D:For issue 1, tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording. For issue 2, database and tool output must not be promoted into instructions for privileged assistant behavior. For issue 3, unscoped tool connections risk exposing more data than the intended development workflow requires.
Incorrect Answers
A:Connect only to an approved authenticated MCP server with scoped read permissions and verified endpoint identity; Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; and 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.
B: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; and Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use: database and tool output must not be promoted into instructions for privileged assistant behavior.
C: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; and 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.
Question 37
A downstream index needs to know which SQL product rows changed since its last successful synchronization, without requiring every historical value. An Azure Function must react to supported SQL table changes to keep a downstream notification channel updated. What is the most appropriate plan for this situation?
- Evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps; configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing.
- Evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization; evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform.
- Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; do not evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization.
- Evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization; configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing.
Explanation
Correct Answer
D:First, change Tracking identifies changed keys and operations without providing complete before-and-after row images. Second, SQL trigger bindings can coordinate database changes with Azure Functions, subject to checkpoint and retry behavior.
Incorrect Answers
A:Evaluate Azure Logic Apps driven by a trusted change signal, with retries, deduplication and approval steps; configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing: change Tracking identifies changed keys and operations without providing complete before-and-after row images.
B:Evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization; evaluate change data capture with appropriate capture instances and retention based on the supported SQL platform: sQL trigger bindings can coordinate database changes with Azure Functions, subject to checkpoint and retry behavior.
C:Configure the SQL trigger binding with supported permissions, leases and reliable idempotent processing; do not evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization: evaluate Change Tracking with retention and version checkpoints for lightweight row-change synchronization.
Question 38
A code assistant is asked to optimize a confidential SQL query but the organization's approved model selection policy excludes the current external model. Which action best addresses the database requirement?
- Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project.
- Require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration.
- Create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions.
- Use an eligible organization-approved model and confirm data-handling controls before submitting the query.
Explanation
Correct Answer
D:Model and tool configuration must respect enterprise policy rather than developer convenience.
Incorrect Answers
A:Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project: context-poor generation may assume database objects that do not exist.
B:Require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration: suggestions are hypotheses until verified against the actual query and update workload.
C:Create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions: repository-level instructions supply relevant context but still require review of generated changes.
Question 39
Issue 1: An in-memory transaction workload suffers allocation and lock overhead, and a team proposes memory-optimized tables without checking operational limits. Issue 2: A team needs to query a governed remote file set as part of a SQL analytical workflow without copying every file into a regular table first. Issue 3: A payments team wants a completely non-durable memory table for transactions that must survive server restarts and full recovery. Which complete response addresses the three issues in order?
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case.
- Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
Explanation
Correct Answer
C:For issue 1, in-memory optimization can help selected workloads but carries compatibility and durability considerations. For issue 2, external tables expose remote data under a schema while leaving the authoritative storage outside the local table. For issue 3, critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
Incorrect Answers
A:Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case: critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
B:Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics: in-memory optimization can help selected workloads but carries compatibility and durability considerations.
D:Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; and reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics: external tables expose remote data under a schema while leaving the authoritative storage outside the local table.
Question 40
An application team must address both issues before release. A shared application database password is emailed to dozens of developers and nobody can identify which user made a change. An Always Encrypted column is unreadable after a key rotation because a client cannot resolve the new column encryption key metadata. What is the most appropriate plan for this situation?
- Replace the shared login with identifiable least-privileged principals and rotate the exposed credential; inspect the user's effective token and all group and database role grants before confirming revocation.
- Fix the trusted certificate name or connection target and keep verified encrypted transport enabled; validate the client key store, key metadata and coordinated rotation path before enabling the new key for production traffic.
- Replace the shared login with identifiable least-privileged principals and rotate the exposed credential; validate the client key store, key metadata and coordinated rotation path before enabling the new key for production traffic.
- Validate the client key store, key metadata and coordinated rotation path before enabling the new key for production traffic; do not replace the shared login with identifiable least-privileged principals and rotate the exposed credential.
Explanation
Correct Answer
C:First, individual accountability and secret hygiene are lost when many actors use the same static login. Second, client-side encryption depends on matching key access and metadata, not just a successful SQL connection.
Incorrect Answers
A:Replace the shared login with identifiable least-privileged principals and rotate the exposed credential; inspect the user's effective token and all group and database role grants before confirming revocation: client-side encryption depends on matching key access and metadata, not just a successful SQL connection.
B:Fix the trusted certificate name or connection target and keep verified encrypted transport enabled; validate the client key store, key metadata and coordinated rotation path before enabling the new key for production traffic: individual accountability and secret hygiene are lost when many actors use the same static login.
D:Replace the shared login with identifiable least-privileged principals and rotate the exposed credential.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests