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.
Question 561
An AI-enabled database project needs two corrections. AI code suggestions repeatedly violate the team's SQL naming convention and migration rules despite those rules being documented in the repository. A team reviews AI-suggested indexes but cannot explain the expected workload or reproduce the performance benefit. Which response best addresses these needs?
- Require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration; do not create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions.
- Create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions; Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project.
- Create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions; require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration.
- Use an eligible organization-approved model and confirm data-handling controls before submitting the query; require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration.
Explanation
Correct Answer
C:First, repository-level instructions supply relevant context but still require review of generated changes. Second, suggestions are hypotheses until verified against the actual query and update workload.
Incorrect Answers
A:Require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration; do not create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions.
B:Create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions; Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project: suggestions are hypotheses until verified against the actual query and update workload.
D:Use an eligible organization-approved model and confirm data-handling controls before submitting the query; require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration: repository-level instructions supply relevant context but still require review of generated changes.
Question 562
A reporting view exposes a secret token column that is not needed by analysts, despite the analysts having a legitimate read requirement. Which recommendation best resolves this issue?
- Redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
- Grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior.
- Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy.
- Apply compatibility tests and a controlled version or migration path for the procedure contract.
Explanation
Correct Answer
A:Column minimization should be enforced at the data access boundary rather than left to dashboard display choices.
Incorrect Answers
B:Grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior: grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior: whole-database ownership is excessive for calling one approved routine.
C:Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy: restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy: executing a procedure must not become a shortcut around the least-information-needed rule; Redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
D:Apply compatibility tests and a controlled version or migration path for the procedure contract: apply compatibility tests and a controlled version or migration path for the procedure contract: a database programmable-object signature is an API contract that downstream clients may depend upon.
Question 563
A database architect reviews two implementation risks. A partitioned sales table still scans all partitions because analysts wrap the date column in an expression that prevents useful elimination. A team proposes splitting a 20-row reference table into hundreds of partitions to solve slow dashboard joins. Which course of action addresses the stated issues?
- Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process.
- Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead.
- Reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead; do not Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan.
- Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead.
Explanation
Correct Answer
D:First, a partitioning strategy only reduces reads when the optimizer can identify the relevant partitions. Second, partitioning adds management overhead and is not a substitute for addressing an actual join or index problem.
Incorrect Answers
A:Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan; Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process: partitioning adds management overhead and is not a substitute for addressing an actual join or index problem.
B:Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation; reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead: a partitioning strategy only reduces reads when the optimizer can identify the relevant partitions.
C:Reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead; do not Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan: rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan.
Question 564
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. Which action best addresses the database requirement?
- 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.
- 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.
Explanation
Correct Answer
A:Table-valued inputs can reduce round trips and support relational operations on a batch of IDs.
Incorrect Answers
B:Capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details: capture appropriate error metadata and rethrow or return a controlled failure while protecting sensitive details: structured error evidence is necessary for debugging without falsely reporting successful completion.
C:Move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented: move the state-changing operation to a stored procedure or approved event mechanism and keep the function read-oriented: t-SQL user-defined functions have restrictions on database state changes and must not be treated as arbitrary procedures.
D:Rework the condition as a set-based validation and benchmark the trigger under representative batch sizes: rework the condition as a set-based validation and benchmark the trigger under representative batch sizes: set-based handling avoids unnecessary per-row overhead in statement-level trigger execution; Consider a table-valued parameter and a set-based stored procedure while measuring call and plan behavior.
Question 565
A database architect reviews two implementation risks. A developer asks an assistant to rewrite a stored procedure without providing its dependencies and then gets code that references missing tables. A code assistant is asked to optimize a confidential SQL query but the organization's approved model selection policy excludes the current external model. What is the most appropriate plan for this situation?
- Require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration; use an eligible organization-approved model and confirm data-handling controls before submitting the query.
- Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project; use an eligible organization-approved model and confirm data-handling controls before submitting the query.
- Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project; 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; do not Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project.
Explanation
Correct Answer
B:First, context-poor generation may assume database objects that do not exist. Second, model and tool configuration must respect enterprise policy rather than developer convenience.
Incorrect Answers
A:Require a representative benchmark and plan comparison before accepting AI-suggested indexes into a migration; use an eligible organization-approved model and confirm data-handling controls before submitting the query: context-poor generation may assume database objects that do not exist.
C:Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project; create and maintain appropriate GitHub Copilot instruction files referencing the project's schema, naming and deployment conventions: model and tool configuration must respect enterprise policy rather than developer convenience.
D:Use an eligible organization-approved model and confirm data-handling controls before submitting the query; do not Supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project: supply the authorized schema and dependency context before evaluating the generated procedure in an isolated project.
Question 566
Issue 1: A SQL view extracts a scalar field from a JSON payload but occasionally receives invalid JSON and crashes the report. Issue 2: A service expects a database API to return a nested JSON object with named attributes rather than concatenated free-text strings. Issue 3: A normalized order table must expand an incoming JSON array into rows for validation and insertion into child records. Which complete response addresses the three issues in order?
- 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; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
- 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; and 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; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and 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; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion.
Explanation
Correct Answer
D:For issue 1, semi-structured input needs validation and predictable failure behavior before scalar values enter authoritative reports. For issue 2, JSON construction functions preserve key/value structure without brittle manual delimiter concatenation. For issue 3, OPENJSON converts array elements into tabular data suitable for relational constraints and joins.
Incorrect Answers
A: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; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion: jSON construction functions preserve key/value structure without brittle manual delimiter concatenation.
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; and use OPENJSON with an explicit schema for the array entries and validate keys before relational insertion: 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; Construct the response using JSON_OBJECT or the platform's supported JSON construction functions and verify the output schema; and evaluate a supported JSON indexing strategy or an indexed computed extraction for the frequently filtered property: oPENJSON converts array elements into tabular data suitable for relational constraints and joins.
Question 567
Issue 1: A production database calls a model deployment whose version was changed without regression testing and now JSON output fields differ. Issue 2: An AI app embeds permanent administrator tokens directly in the external model definition shared with analysts. Issue 3: A team's staging SQL instance cannot use a newly proposed external model capability because its engine version lacks the required feature. Which complete response addresses the three issues in order?
- Version and validate the external model integration contract before approving the changed deployment; configure the supported external model definition and secure its endpoint authentication under the target SQL platform; and 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; Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility; and check supported platform and version prerequisites before deploying the external model integration.
- 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; and 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; Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility; and configure the supported external model definition and secure its endpoint authentication under the target SQL platform.
Explanation
Correct Answer
B:For issue 1, model endpoint upgrades can change behavior and must be treated as application dependency changes. For issue 2, model access must follow least privilege and avoid exposing reusable high-value credentials. For issue 3, SQL AI features differ by engine edition and release and cannot be assumed portable without verification.
Incorrect Answers
A:Version and validate the external model integration contract before approving the changed deployment; configure the supported external model definition and secure its endpoint authentication under the target SQL platform; and 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.
C: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; and check supported platform and version prerequisites before deploying the external model integration: model endpoint upgrades can change behavior and must be treated as application dependency changes.
D:Version and validate the external model integration contract before approving the changed deployment; Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility; and 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.
Question 568
A team shares raw production error logs containing customer emails with a public model to get a quick explanation of a deadlock. Which recommendation best resolves this issue?
- 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.
- 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.
Explanation
Correct Answer
A:Useful diagnostics do not justify exporting protected customer data into an unauthorized tool.
Incorrect Answers
B:Check the target platform and version documentation and run compatibility tests before accepting generated syntax: check the target platform and version documentation and run compatibility tests before accepting generated syntax: generated statements should not be assumed portable across database versions and services; Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant.
C: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: aI-assisted changes need the same production-readiness evidence as hand-authored database migrations; Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant.
D:Define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment: define the trust model and tenant context explicitly, then test every allowed and denied access case before deployment: security predicates depend on correct identity semantics that cannot be inferred safely from vague instructions; Use approved telemetry handling and redact sensitive identifiers before supplying diagnostic context to an eligible assistant.
Question 569
Issue 1: A reporting team repeats the same approved customer-eligibility SELECT in twelve applications and wants one centrally maintained read definition. Issue 2: A query needs to return a computed tax multiplier for one input amount, and a team proposes a full trigger-based process. Issue 3: A team grants a view SELECT permission and assumes this alone makes rows from every underlying table safe for all tenants. Which complete response addresses the three issues in order?
- Create a view exposing only approved columns and predicates with appropriate permissions and schema review; choose an appropriate table-valued function and validate its plan behavior and input filtering; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- 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; and 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; use a deterministic scalar function or expression appropriate to the platform and performance requirements; and 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; use a deterministic scalar function or expression appropriate to the platform and performance requirements; and choose an appropriate table-valued function and validate its plan behavior and input filtering.
Explanation
Correct Answer
C:For issue 1, views centralize query logic and can restrict presentation but do not replace all underlying access security. For issue 2, a scalar calculation produces one result from inputs without needing write-time side effects. For issue 3, view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Incorrect Answers
A:Create a view exposing only approved columns and predicates with appropriate permissions and schema review; choose an appropriate table-valued function and validate its plan behavior and input filtering; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: a scalar calculation produces one result from inputs without needing write-time side effects.
B: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; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: views centralize query logic and can restrict presentation but do not replace all underlying access security.
D:Create a view exposing only approved columns and predicates with appropriate permissions and schema review; use a deterministic scalar function or expression appropriate to the platform and performance requirements; and choose an appropriate table-valued function and validate its plan behavior and input filtering: view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Question 570
A billing service treats customer reference numbers as contiguous, but automatic identity values occasionally skip numbers after a rolled-back transaction. A uniqueness rule must reject two active rows for the same account while allowing archived versions of the account to coexist. Which response best addresses these needs?
- Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior; do not 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; 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; 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; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter.
Explanation
Correct Answer
C:First, identity allocation may leave gaps after failed operations, so it does not guarantee consecutive business numbering. Second, a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
Incorrect Answers
A:Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior; do not Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required: treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required.
B:Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior: identity allocation may leave gaps after failed operations, so it does not guarantee consecutive business numbering.
D:Treat identity values as surrogate keys rather than gap-free business sequences and use a separately controlled numbering workflow if legally required; use IS NULL for missing-value predicates and test SQL three-valued logic in the filter: a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests