Question 70
A response-processing procedure assumes every HTTP 200 body contains the expected model text even when the provider returns a different schema version. Which recommendation best resolves this issue?
- Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
- Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
- Bound and back off retries outside long-running critical transactions and handle model errors explicitly.
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
Explanation
Correct Answer
B:A successful transport response does not guarantee an unchanged application-level payload contract.
Incorrect Answers
A:Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint: structured JSON construction avoids malformed payloads and accidental text-to-syntax injection.
C:Bound and back off retries outside long-running critical transactions and handle model errors explicitly: external inference availability should not monopolize database locks or create uncontrolled request storms.
D:Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling: sQL-managed external REST invocation has a defined interface and security requirements for outbound model requests.
Question 71
A multitenant application uses one orders table and a connection pool but a user can query invoices belonging to other tenants by changing the URL. What should the SQL developer do next?
- Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions.
- Enforce tenant-aware Row-Level Security predicates using a trusted application session identity and test cross-tenant denial.
- Derive tenant context from an authenticated trusted identity and prevent clients from spoofing the session context.
- Test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries.
Explanation
Correct Answer
B:RLS filters effective row access in the database; frontend URL filters alone are insufficient for tenant isolation.
Incorrect Answers
A:Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions: a read filter alone may not stop an UPDATE that moves a row across an access boundary.
C:Derive tenant context from an authenticated trusted identity and prevent clients from spoofing the session context: a row filter is only as strong as the trustworthy identity value it compares against each row.
D:Test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries: security validation requires testing the roles subject to the policy, not solely an account that may bypass or override controls.
Question 72
A SQL database holds a confidential account field that must remain encrypted at rest while the application controls authorized decryption. Which recommendation best resolves this issue?
- Evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility.
- Implement supported column-level encryption with disciplined key management and verify its query and operational constraints.
- Configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption.
- Add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone.
Explanation
Correct Answer
B:Column encryption protects selected data when at-rest confidentiality and controlled application access are needed.
Incorrect Answers
A:Evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility: evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility: always Encrypted protects selected sensitive values from the database engine and privileged infrastructure operators when keys remain client-controlled.
C:Configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption: configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption: dynamic masking changes displayed values for lower-privileged queries; it does not encrypt the underlying stored data.
D:Add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone: at-rest database encryption does not automatically prevent an authorized query engine from returning plaintext to privileged users.
Question 73
A query needs to return a computed tax multiplier for one input amount, and a team proposes a full trigger-based process. Which recommendation best resolves this issue?
- Evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- Use a deterministic scalar function or expression appropriate to the platform and performance requirements.
- 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.
Explanation
Correct Answer
B:A scalar calculation produces one result from inputs without needing write-time side effects.
Incorrect Answers
A: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.
C:Create a view exposing only approved columns and predicates with appropriate permissions and schema review: views centralize query logic and can restrict presentation but do not replace all underlying access security.
D: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.
Question 74
An AI-enabled database project needs two corrections. A billing service treats customer reference numbers as contiguous, but automatic identity values occasionally skip numbers after a rolled-back transaction. A customer name column is NVARCHAR(20) but authorized legal names of up to 90 characters are being truncated or rejected. Which course of action addresses the stated issues?
- Increase the column length after validating application contracts and storage impact instead of silently shortening the source value; do not 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; Increase the column length after validating application contracts and storage impact instead of silently shortening the source value.
- 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.
- 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, data-type size must accommodate the real domain and avoid unauthorized information loss.
Incorrect Answers
A: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; Increase the column length after validating application contracts and storage impact instead of silently shortening the source value: 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: data-type size must accommodate the real domain and avoid unauthorized information loss.
Question 75
An analyst needs each transaction's running total within its account ordered by posting date, not one aggregate per account. A user wants to show all tied top-performing stores, but the report uses ROW_NUMBER and excludes equal-score ties. Which response best addresses these needs?
- Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; do not use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
- Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; use a common table expression to structure the query while validating the actual plan for repeated references.
- Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
- Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use RANK or DENSE_RANK according to tie and gap requirements and order deterministically.
Explanation
Correct Answer
C:First, windowed aggregation retains individual rows while calculating a cumulative value. Second, ranking functions differ in how they handle ties and skipped rank values.
Incorrect Answers
A:Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
B:Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; use a common table expression to structure the query while validating the actual plan for repeated references: ranking functions differ in how they handle ties and skipped rank values.
D:Use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one; use RANK or DENSE_RANK according to tie and gap requirements and order deterministically: windowed aggregation retains individual rows while calculating a cumulative value.
Question 76
An AI-enabled database project needs two corrections. An in-memory transaction workload suffers allocation and lock overhead, and a team proposes memory-optimized tables without checking operational limits. A payments team wants a completely non-durable memory table for transactions that must survive server restarts and full recovery. What is the most appropriate plan for this situation?
- Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; 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.
- Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration; reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
- Reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics; do not Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration.
Explanation
Correct Answer
C:First, in-memory optimization can help selected workloads but carries compatibility and durability considerations. Second, critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
Incorrect Answers
A:Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; 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.
B: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: critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
D:Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration.
Question 77
A semantic-search implementation compares vectors with raw text equality and never computes their numerical similarity. A database stores numeric embedding arrays but the developer needs to check each vector's dimensions before calling a distance function. Which course of action addresses the stated issues?
- 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.
- Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step; inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison.
- Inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison; do not use supported vector distance or search functions on compatible embeddings to rank semantic neighbors.
Explanation
Correct Answer
A:First, semantic similarity is based on vector geometry rather than equality of serialized embedding text. Second, vector dimension validation avoids invalid comparisons and inconsistent index configuration.
Incorrect Answers
B: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: vector dimension validation avoids invalid comparisons and inconsistent index configuration.
C:Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step; inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison: semantic similarity is based on vector geometry rather than equality of serialized embedding text.
D:Use supported vector distance or search functions on compatible embeddings to rank semantic neighbors.
Question 78
A database connection string disables TLS certificate validation to fix a hostname mismatch on a production SQL endpoint. Which recommendation best resolves this issue?
- 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.
- 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.
Explanation
Correct Answer
D:Skipping certificate validation can turn a configuration mismatch into an interception opportunity.
Incorrect Answers
A:Replace the shared login with identifiable least-privileged principals and rotate the exposed credential: individual accountability and secret hygiene are lost when many actors use the same static login.
B:Validate the client key store, key metadata and coordinated rotation path before enabling the new key for production traffic: client-side encryption depends on matching key access and metadata, not just a successful SQL connection.
C:Inspect the user's effective token and all group and database role grants before confirming revocation: effective rights combine multiple principals, so removing one grant may leave another access path.
Question 79
An application changes a view in one branch and a stored procedure in another, causing merge conflicts that the team resolves by dropping one file. A team stores all schema changes only in screenshots of query windows and cannot reproduce the last release in disaster recovery. What is the most appropriate plan for this situation?
- Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow.
- Store the SQL Database Project and migration decisions in version control with reproducible build artifacts; do not Resolve database project conflicts using dependency review and rerun build and integration tests after the merge.
- Add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references; Store the SQL Database Project and migration decisions in version control with reproducible build artifacts.
- Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; Store the SQL Database Project and migration decisions in version control with reproducible build artifacts.
Explanation
Correct Answer
D:First, a textual merge is not sufficient when related database objects have behavioral dependencies. Second, versioned source and artifacts support reproducible database releases rather than undocumented manual changes.
Incorrect Answers
A:Resolve database project conflicts using dependency review and rerun build and integration tests after the merge; Capture intentional changes in source-controlled project files and validate a repeatable schema deployment workflow: versioned source and artifacts support reproducible database releases rather than undocumented manual changes.
B:Resolve database project conflicts using dependency review and rerun build and integration tests after the merge.
C:Add project build and model validation checks to the pull-request pipeline and fail changes on unresolved references; Store the SQL Database Project and migration decisions in version control with reproducible build artifacts: a textual merge is not sufficient when related database objects have behavioral dependencies.
Question 80
An application team must address both issues before release. A stored report uses SELECT * after a schema expansion, unexpectedly exposing confidential columns to an API client. A report uses NOT IN against a subquery containing NULL and returns no rows even though unmatched IDs exist. Which course of action addresses the stated issues?
- Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records; do not select explicitly authorized columns and make schema output changes subject to contract review.
- Define a business-specific ORDER BY with a stable tie-breaker before applying TOP; Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records.
- Select explicitly authorized columns and make schema output changes subject to contract review; Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records.
- Select explicitly authorized columns and make schema output changes subject to contract review; use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists.
Explanation
Correct Answer
C:First, explicit projections avoid accidental information disclosure when the source table grows. Second, NULL in a NOT IN set can make the comparison unknown and unexpectedly filter all rows.
Incorrect Answers
A:Select explicitly authorized columns and make schema output changes subject to contract review.
B:Define a business-specific ORDER BY with a stable tie-breaker before applying TOP; Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records: explicit projections avoid accidental information disclosure when the source table grows.
D:Select explicitly authorized columns and make schema output changes subject to contract review; use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists: nULL in a NOT IN set can make the comparison unknown and unexpectedly filter all rows.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests