Question 810
A database architect reviews two implementation risks. A team runs a schema release while long-running migrations are still active from the previous deployment. A deployment pipeline publishes a build created from unreviewed branch commits instead of the signed-off release revision. Which response best addresses these needs?
- Promote the same immutable build artifact through validated environments and use environment-specific secure configuration; Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
- Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes; add the specified environment approval gate before the deployment step and retain its audit record.
- Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment; do not add safe concurrency controls and deployment-state checks to avoid overlapping schema changes.
- Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes; Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
Explanation
Correct Answer
D:First, parallel DDL changes can conflict and leave a database in an indeterminate release state. Second, release traceability requires deploying the approved source revision rather than an arbitrary latest build.
Incorrect Answers
A:Promote the same immutable build artifact through validated environments and use environment-specific secure configuration; Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment: parallel DDL changes can conflict and leave a database in an indeterminate release state.
B:Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes; add the specified environment approval gate before the deployment step and retain its audit record: release traceability requires deploying the approved source revision rather than an arbitrary latest build.
C:Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment; do not add safe concurrency controls and deployment-state checks to avoid overlapping schema changes: add safe concurrency controls and deployment-state checks to avoid overlapping schema changes.
Question 811
A database architect reviews two implementation risks. A table trigger synchronously calls an external embedding endpoint for every order update and writes now stall during model outages. An Azure Function responsible for regenerating embeddings sometimes retries after timeout and stores duplicate vectors for one source row. What is the most appropriate plan for this situation?
- Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup; use stable source keys, generation versions and upsert/idempotency controls when processing retries.
- 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.
- Use stable source keys, generation versions and upsert/idempotency controls when processing retries; do not Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer.
- Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer; use stable source keys, generation versions and upsert/idempotency controls when processing retries.
Explanation
Correct Answer
D:First, external inference latency should not extend critical database write transactions unnecessarily. Second, change-triggered processing may be delivered more than once and must not duplicate index records.
Incorrect Answers
A:Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup; use stable source keys, generation versions and upsert/idempotency controls when processing retries: external inference latency should not extend critical database write transactions unnecessarily.
B: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: change-triggered processing may be delivered more than once and must not duplicate index records.
C:Use stable source keys, generation versions and upsert/idempotency controls when processing retries; do not Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer: move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer.
Question 812
A high-traffic transaction table repeatedly retrieves one invoice by unique invoice ID, while a proposed full columnstore design slows point lookups. Which action best addresses the database requirement?
- Keep an appropriate rowstore lookup index for selective invoice requests and benchmark it with actual predicates.
- 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.
- Test a composite nonclustered index whose leading keys match the selective query filters and review its execution plan.
Explanation
Correct Answer
A:Selective operational lookups benefit from a narrow access path rather than automatically replacing it with analytical column storage.
Incorrect Answers
B:Inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs: inspect index usage and consolidate or remove redundant indexes after measuring read and write trade-offs: additional indexes have write and storage costs, so more indexes are not automatically better for mixed workloads; Keep an appropriate rowstore lookup index for selective invoice requests and benchmark it with actual predicates.
C: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: columnstore storage is suited to large analytical scans and aggregation, subject to workload-specific testing; Keep an appropriate rowstore lookup index for selective invoice requests and benchmark it with actual predicates.
D:Test a composite nonclustered index whose leading keys match the selective query filters and review its execution plan: test a composite nonclustered index whose leading keys match the selective query filters and review its execution plan: index key ordering affects seeks and read volume; an index unrelated to the WHERE pattern may not improve the query; Keep an appropriate rowstore lookup index for selective invoice requests and benchmark it with actual predicates.
Question 813
A vector-search API exposed through Data API builder permits unauthenticated requests to retrieve private customer records. What should the SQL developer do next?
- Constrain the MCP tool to approved operations and identities and treat retrieved instructions as untrusted data.
- Move model credentials to an approved secret or managed identity boundary and restrict network and caller access.
- Apply supported query depth, complexity, rate and resource controls alongside entity permissions.
- Require validated authentication and per-entity authorization before exposing the API to clients.
Explanation
Correct Answer
D:An HTTP endpoint does not inherit business authorization automatically from having a database connection.
Incorrect Answers
A:Constrain the MCP tool to approved operations and identities and treat retrieved instructions as untrusted data: constrain the MCP tool to approved operations and identities and treat retrieved instructions as untrusted data: tool-output text must not become authority to execute privileged database actions; Require validated authentication and per-entity authorization before exposing the API to clients.
B:Move model credentials to an approved secret or managed identity boundary and restrict network and caller access: move model credentials to an approved secret or managed identity boundary and restrict network and caller access: external model connections need credential isolation and least privilege like any other remote service; Require validated authentication and per-entity authorization before exposing the API to clients.
C:Apply supported query depth, complexity, rate and resource controls alongside entity permissions: apply supported query depth, complexity, rate and resource controls alongside entity permissions: graphQL authorization alone does not prevent denial of service from unbounded query execution; Require validated authentication and per-entity authorization before exposing the API to clients.
Question 814
A billing service treats customer reference numbers as contiguous, but automatic identity values occasionally skip numbers after a rolled-back transaction. Which action best addresses the database requirement?
- Use IS NULL for missing-value predicates and test SQL three-valued logic in the filter.
- Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior.
- Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required.
- Increase the column length after validating application contracts and storage impact instead of silently shortening the source value.
Explanation
Correct Answer
C:Identity allocation may leave gaps after failed operations, so it does not guarantee consecutive business numbering.
Incorrect Answers
A:Use IS NULL for missing-value predicates and test SQL three-valued logic in the filter: use IS NULL for missing-value predicates and test SQL three-valued logic in the filter: nULL denotes unknown or absent data and does not behave like an ordinary comparable value; Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required.
B:Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior: consider a filtered unique index on the active-row predicate after confirming its supported platform behavior: a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row; Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required.
D:Increase the column length after validating application contracts and storage impact instead of silently shortening the source value: increase the column length after validating application contracts and storage impact instead of silently shortening the source value: data-type size must accommodate the real domain and avoid unauthorized information loss; Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required.
Question 815
Issue 1: A SQL service sends regulated document text to an external embedding provider without checking its processing region or retention terms. Issue 2: A SQL chatbot mixes private tenant embeddings into a global search pool with no filtering before ranking results. Issue 3: An application stores embeddings as opaque strings and sorts them lexicographically to guess which document is most similar. Which complete response addresses the three issues in order?
- Keep the content as untrusted model input and block instructions that request unrelated privileged operations; enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets; and Store vectors in a supported vector representation and use appropriate distance or search functions for similarity.
- Validate the model provider's residency, processing and data-retention terms before sending the protected text; enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets; and Store vectors in a supported vector representation and use appropriate distance or search functions for similarity.
- Validate the model provider's residency, processing and data-retention terms before sending the protected text; keep the content as untrusted model input and block instructions that request unrelated privileged operations; and Store vectors in a supported vector representation and use appropriate distance or search functions for similarity.
- Validate the model provider's residency, processing and data-retention terms before sending the protected text; enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets; and keep the content as untrusted model input and block instructions that request unrelated privileged operations.
Explanation
Correct Answer
B:For issue 1, semantic model processing is an external data flow that must satisfy the source classification rules. For issue 2, vector similarity does not override access-control boundaries in multitenant databases. For issue 3, vector search requires numerical vector operations, not ordinary alphabetic comparison of serialized coordinates.
Incorrect Answers
A:Keep the content as untrusted model input and block instructions that request unrelated privileged operations; enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets; and Store vectors in a supported vector representation and use appropriate distance or search functions for similarity: semantic model processing is an external data flow that must satisfy the source classification rules.
C:Validate the model provider's residency, processing and data-retention terms before sending the protected text; keep the content as untrusted model input and block instructions that request unrelated privileged operations; and Store vectors in a supported vector representation and use appropriate distance or search functions for similarity: vector similarity does not override access-control boundaries in multitenant databases.
D:Validate the model provider's residency, processing and data-retention terms before sending the protected text; enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets; and keep the content as untrusted model input and block instructions that request unrelated privileged operations: vector search requires numerical vector operations, not ordinary alphabetic comparison of serialized coordinates.
Question 816
Issue 1: A database report filters by AccountId and a recent date range, but the table lacks any useful index for those predicates. Issue 2: A table has a covering index for reads but batch inserts have become slow because dozens of redundant included columns must be maintained. Issue 3: A dashboard joins on a string column whose collation differs from the other table, causing an implicit conversion in the plan. Which complete response addresses the three issues in order?
- 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; and align the join data types and compatible collations where safe, then rerun the actual plan.
- 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; and 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; replace non-sargable extraction with a bounded date range appropriate to the required period; and 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; review index usage and trim redundant included columns or overlapping indexes after benchmarking writes; and align the join data types and compatible collations where safe, then rerun the actual plan.
Explanation
Correct Answer
A:For issue 1, appropriate leading index keys can reduce reads for selective account-scoped time queries. For issue 2, covering indexes can avoid lookups but carry storage and write amplification costs. For issue 3, implicit conversions can prevent efficient seek operations and complicate index use.
Incorrect Answers
B: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; and replace non-sargable extraction with a bounded date range appropriate to the required period: implicit conversions can prevent efficient seek operations and complicate index use.
C:Test a composite access index aligned to AccountId and the date range and inspect its seek and maintenance costs; replace non-sargable extraction with a bounded date range appropriate to the required period; and align the join data types and compatible collations where safe, then rerun the actual plan: covering indexes can avoid lookups but carry storage and write amplification costs.
D:Replace non-sargable extraction with a bounded date range appropriate to the required period; review index usage and trim redundant included columns or overlapping indexes after benchmarking writes; and align the join data types and compatible collations where safe, then rerun the actual plan: appropriate leading index keys can reduce reads for selective account-scoped time queries.
Question 817
A team wants to deploy the same tested SQL package to staging and production but rebuilds it separately with different dependencies. What should the SQL developer do next?
- Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes.
- Add the specified environment approval gate before the deployment step and retain its audit record.
- Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
- Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
Explanation
Correct Answer
D:Promoting one artifact reduces untracked differences between tested and released database code.
Incorrect Answers
A:Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes: add safe concurrency controls and deployment-state checks to avoid overlapping schema changes: parallel DDL changes can conflict and leave a database in an indeterminate release state; Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
B:Add the specified environment approval gate before the deployment step and retain its audit record: add the specified environment approval gate before the deployment step and retain its audit record: model validation and business authorization are separate conditions for a production release; Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
C:Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment: bind the production artifact to the reviewed commit or release tag and verify provenance at deployment: release traceability requires deploying the approved source revision rather than an arbitrary latest build; Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
Question 818
An application team must address both issues before release. A developer pastes a production database connection string into an AI coding chat to explain a connection error. An assistant suggests a migration that drops a critical table to fix a small constraint error, and the team wants to run it automatically. Which course of action addresses the stated issues?
- Restrict the extension and repository scopes using approved configuration and audit its permissions; require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL.
- Remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls; require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL.
- Require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL; do not remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls.
- Remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls; test generated statements with representative fixtures and verify access behavior before accepting them into source control.
Explanation
Correct Answer
B:First, AI assistance is not authorization to disclose live credentials or sensitive connection information. Second, generated database code can be syntactically valid while causing destructive business changes.
Incorrect Answers
A:Restrict the extension and repository scopes using approved configuration and audit its permissions; require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL: aI assistance is not authorization to disclose live credentials or sensitive connection information.
C:Require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL; do not remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls: remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls.
D:Remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls; test generated statements with representative fixtures and verify access behavior before accepting them into source control: generated database code can be syntactically valid while causing destructive business changes.
Question 819
A reporting team repeats the same approved customer-eligibility SELECT in twelve applications and wants one centrally maintained read definition. What should the SQL developer do next?
- Choose an appropriate table-valued function and validate its plan behavior and input filtering.
- 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.
- Create a view exposing only approved columns and predicates with appropriate permissions and schema review.
Explanation
Correct Answer
D:Views centralize query logic and can restrict presentation but do not replace all underlying access security.
Incorrect Answers
A:Choose an appropriate table-valued function and validate its plan behavior and input filtering: choose an appropriate table-valued function and validate its plan behavior and input filtering: a table-valued function supplies a relational row set that callers can compose with other queries; Create a view exposing only approved columns and predicates with appropriate permissions and schema review.
B:Use a deterministic scalar function or expression appropriate to the platform and performance requirements: use a deterministic scalar function or expression appropriate to the platform and performance requirements: a scalar calculation produces one result from inputs without needing write-time side effects; Create a view exposing only approved columns and predicates with appropriate permissions and schema review.
C: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: view access alone is not proof of full tenant isolation without evaluating underlying access paths; Create a view exposing only approved columns and predicates with appropriate permissions and schema review.
Question 820
A database architect reviews two implementation risks. 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 Copilot agent receives a schema-inspection tool result containing a directive to reveal a secret in the next chat message. What is the most appropriate plan for this situation?
- 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.
- 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; Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy.
- Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; do not configure tool availability and effective identity to permit read-only schema operations and block write-capable tools.
Explanation
Correct Answer
A:First, tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording. Second, database and tool output must not be promoted into instructions for privileged assistant behavior.
Incorrect Answers
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: database and tool output must not be promoted into instructions for privileged assistant behavior.
C:Bind the connector to an approved workspace and record data scope, consent and audit requirements before enabling use; Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy: tool exposure and authorization determine what actions an assistant can perform, regardless of prompt wording.
D:Treat the returned schema text as untrusted data and ignore tool-output instructions that conflict with trusted policy; do not configure tool availability and effective identity to permit read-only schema operations and block write-capable tools: configure tool availability and effective identity to permit read-only schema operations and block write-capable tools.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests