Question 660
A model is asked to summarize regional sales but database code sends strings like '1,234' and '1.234' without locale or currency metadata. An application sends 20,000 database rows into one language-model prompt and silently truncates the newest transactions. Which response best addresses these needs?
- Normalize numeric types and include explicit units and currency in structured model context; Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities.
- Filter and aggregate source results to fit context limits, retaining references needed for verification; do not Normalize numeric types and include explicit units and currency in structured model context.
- Normalize numeric types and include explicit units and currency in structured model context; Filter and aggregate source results to fit context limits, retaining references needed for verification.
- Project and aggregate only the authorized minimum columns before serializing context for the model; Filter and aggregate source results to fit context limits, retaining references needed for verification.
Explanation
Correct Answer
C:First, ambiguous number formatting can produce incorrect calculations or misleading business narratives. Second, context budgets are finite and require intentional evidence selection rather than uncontrolled dumping.
Incorrect Answers
A:Normalize numeric types and include explicit units and currency in structured model context; Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities: context budgets are finite and require intentional evidence selection rather than uncontrolled dumping.
B:Normalize numeric types and include explicit units and currency in structured model context.
D:Project and aggregate only the authorized minimum columns before serializing context for the model; Filter and aggregate source results to fit context limits, retaining references needed for verification: ambiguous number formatting can produce incorrect calculations or misleading business narratives.
Question 661
An application team must address both issues before release. An Always Encrypted column is unreadable after a key rotation because a client cannot resolve the new column encryption key metadata. A database connection string disables TLS certificate validation to fix a hostname mismatch on a production SQL endpoint. What is the most appropriate plan for this situation?
- 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; Fix the trusted certificate name or connection target and keep verified encrypted transport enabled.
- Fix the trusted certificate name or connection target and keep verified encrypted transport enabled; do not 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; replace the shared login with identifiable least-privileged principals and rotate the exposed credential.
Explanation
Correct Answer
B:First, client-side encryption depends on matching key access and metadata, not just a successful SQL connection. Second, skipping certificate validation can turn a configuration mismatch into an interception opportunity.
Incorrect Answers
A: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: client-side encryption depends on matching key access and metadata, not just a successful SQL connection.
C:Fix the trusted certificate name or connection target and keep verified encrypted transport enabled; do not 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.
D: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: skipping certificate validation can turn a configuration mismatch into an interception opportunity.
Question 662
A team repeats country names and tax policy text in millions of invoice rows, creating inconsistent updates when policy wording changes. What should the SQL developer do next?
- Use a computed expression or governed recalculation strategy and enforce a clear source of truth for derived totals.
- Model tenant ownership explicitly and enforce data isolation using suitable keys, security predicates and authorization tests.
- Choose an appropriate history or change-capture design that records transitions with effective timestamps for reporting.
- Normalize the shared reference entity and maintain a foreign-key relationship from each invoice to the authoritative policy record.
Explanation
Correct Answer
D:Separating shared reference data reduces update anomalies and centralizes one-to-many attributes.
Incorrect Answers
A:Use a computed expression or governed recalculation strategy and enforce a clear source of truth for derived totals: use a computed expression or governed recalculation strategy and enforce a clear source of truth for derived totals: duplicated derived values can diverge from transaction lines unless their maintenance is reliable.
B:Model tenant ownership explicitly and enforce data isolation using suitable keys, security predicates and authorization tests: model tenant ownership explicitly and enforce data isolation using suitable keys, security predicates and authorization tests: multi-tenant relational design needs an unambiguous tenant boundary before rows can be safely shared; Normalize the shared reference entity and maintain a foreign-key relationship from each invoice to the authoritative policy record.
C:Choose an appropriate history or change-capture design that records transitions with effective timestamps for reporting: choose an appropriate history or change-capture design that records transitions with effective timestamps for reporting: overwriting current state without history prevents reliable analysis of earlier business status.
Question 663
A uniqueness rule must reject two active rows for the same account while allowing archived versions of the account to coexist. A customer name column is NVARCHAR(20) but authorized legal names of up to 90 characters are being truncated or rejected. Which response best addresses these needs?
- 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.
- 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; 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; do not Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior.
Explanation
Correct Answer
A:First, a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row. Second, data-type size must accommodate the real domain and avoid unauthorized information loss.
Incorrect Answers
B: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: data-type size must accommodate the real domain and avoid unauthorized information loss.
C:Use IS NULL for missing-value predicates and test SQL three-valued logic in the filter; Increase the column length after validating application contracts and storage impact instead of silently shortening the source value: a filtered unique index can enforce uniqueness over the business-active subset rather than over every historical row.
D:Consider a filtered unique index on the active-row predicate after confirming its supported platform behavior.
Question 664
A database architect reviews two implementation risks. An SQL-to-LLM pipeline sends every column of a health table even though the request concerns only anonymous appointment counts. A model is asked to summarize regional sales but database code sends strings like '1,234' and '1.234' without locale or currency metadata. What is the most appropriate plan for this situation?
- Project and aggregate only the authorized minimum columns before serializing context for the model; Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities.
- Normalize numeric types and include explicit units and currency in structured model context; do not Project and aggregate only the authorized minimum columns before serializing context for the model.
- Project and aggregate only the authorized minimum columns before serializing context for the model; Normalize numeric types and include explicit units and currency in structured model context.
- Filter and aggregate source results to fit context limits, retaining references needed for verification; Normalize numeric types and include explicit units and currency in structured model context.
Explanation
Correct Answer
C:First, data minimization should occur before data crosses the external model boundary. Second, ambiguous number formatting can produce incorrect calculations or misleading business narratives.
Incorrect Answers
A:Project and aggregate only the authorized minimum columns before serializing context for the model; Convert authorized relational results to a typed, labeled JSON structure with clear keys and row identities: ambiguous number formatting can produce incorrect calculations or misleading business narratives.
B:Normalize numeric types and include explicit units and currency in structured model context; do not Project and aggregate only the authorized minimum columns before serializing context for the model: project and aggregate only the authorized minimum columns before serializing context for the model.
D:Filter and aggregate source results to fit context limits, retaining references needed for verification; Normalize numeric types and include explicit units and currency in structured model context: data minimization should occur before data crosses the external model boundary.
Question 665
An application team must address both issues before release. A testing environment receives a full production customer dataset, including personal numbers, simply to validate a new stored procedure. A database export for a contractor includes internal authentication tokens along with the permitted reference product table. Which course of action addresses the stated issues?
- Use synthetic or masked test data with approved exceptions and enforce environment access separation; Include derived embeddings and search indexes in the deletion and retention workflow and verify removal.
- Assess region, service processing and backup locations against policy before executing the migration; Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery.
- Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery; do not use synthetic or masked test data with approved exceptions and enforce environment access separation.
- Use synthetic or masked test data with approved exceptions and enforce environment access separation; Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery.
Explanation
Correct Answer
D:First, development testing usually does not require copying complete protected production records. Second, data minimization reduces unnecessary disclosure even when the transfer channel is encrypted.
Incorrect Answers
A:Use synthetic or masked test data with approved exceptions and enforce environment access separation; Include derived embeddings and search indexes in the deletion and retention workflow and verify removal: data minimization reduces unnecessary disclosure even when the transfer channel is encrypted.
B:Assess region, service processing and backup locations against policy before executing the migration; Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery: development testing usually does not require copying complete protected production records.
C:Minimize the export to explicitly authorized columns and remove secrets before encrypted delivery; do not use synthetic or masked test data with approved exceptions and enforce environment access separation: use synthetic or masked test data with approved exceptions and enforce environment access separation.
Question 666
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. A payments team wants a completely non-durable memory table for transactions that must survive server restarts and full recovery. Which response best addresses these needs?
- 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.
- Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; 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; do not evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions.
- Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; reject non-durable storage for financial records and select durable transactional persistence with verified recovery semantics.
Explanation
Correct Answer
A:First, external tables expose remote data under a schema while leaving the authoritative storage outside the local table. Second, critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
Incorrect Answers
B:Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; Benchmark an eligible memory-optimized table and verify platform support, durability choices and query patterns before migration: critical records require durability; an in-memory object optimized for speed must still meet the data-loss objective.
C:Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions.
D:Evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case; 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 667
A security team wants one shared API key for every customer tenant to simplify revocation in a multitenant search service. What is the most defensible technical decision?
- Restrict firewall and private connectivity to approved workloads and validate intended client access.
- Remove and rotate the secret and reference an approved protected configuration source in deployment.
- Use tenant- and workload-scoped identities or credentials with independent rotation and audit capability.
- Scope model invoke permissions and impose budget and use-case controls independently from the private network design.
Explanation
Correct Answer
C:Per-tenant credentials limit blast radius and make unauthorized activity attributable.
Incorrect Answers
A:Restrict firewall and private connectivity to approved workloads and validate intended client access: restrict firewall and private connectivity to approved workloads and validate intended client access: network reachability should be limited to required sources rather than relying solely on login credentials; Use tenant- and workload-scoped identities or credentials with independent rotation and audit capability.
B:Remove and rotate the secret and reference an approved protected configuration source in deployment: remove and rotate the secret and reference an approved protected configuration source in deployment: source control history may retain exposed credentials even if the latest file is later edited; Use tenant- and workload-scoped identities or credentials with independent rotation and audit capability.
D:Scope model invoke permissions and impose budget and use-case controls independently from the private network design: scope model invoke permissions and impose budget and use-case controls independently from the private network design: private connectivity reduces exposure but does not enforce per-model business permission; Use tenant- and workload-scoped identities or credentials with independent rotation and audit capability.
Question 668
An AI-enabled database project needs two corrections. A SQL support search must find both precise error codes and semantically similar descriptions in one result set. A hybrid search returns the same document from lexical and vector channels as two separate results, crowding out other useful sources. Which course of action addresses the stated issues?
- Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; Deduplicate candidates by stable document ID before final ranking and context assembly.
- Deduplicate candidates by stable document ID before final ranking and context assembly; do not Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types.
- Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; Deduplicate candidates by stable document ID before final ranking and context assembly.
- Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable.
Explanation
Correct Answer
A:First, hybrid search covers literal identifier matches and semantic relationships that either method alone can miss. Second, merging result lists must account for shared documents to avoid repeated evidence in the answer context.
Incorrect Answers
B:Deduplicate candidates by stable document ID before final ranking and context assembly; do not Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types: combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types.
C:Evaluate Reciprocal Rank Fusion with suitable rank weights and validate its combined relevance; Deduplicate candidates by stable document ID before final ranking and context assembly: hybrid search covers literal identifier matches and semantic relationships that either method alone can miss.
D:Combine lexical and vector retrieval into a hybrid strategy and evaluate relevance on both query types; use an appropriate fusion or calibrated normalization method rather than assuming unrelated ranking scales are comparable: merging result lists must account for shared documents to avoid repeated evidence in the answer context.
Question 669
An analytics pipeline needs to reuse a parameterized set of invoice rows joined into several different SELECT statements. A team grants a view SELECT permission and assumes this alone makes rows from every underlying table safe for all tenants. Which response best addresses these needs?
- Choose an appropriate table-valued function and validate its plan behavior and input filtering; 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; 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; 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; do not choose an appropriate table-valued function and validate its plan behavior and input filtering.
Explanation
Correct Answer
C:First, a table-valued function supplies a relational row set that callers can compose with other queries. Second, view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Incorrect Answers
A:Choose an appropriate table-valued function and validate its plan behavior and input filtering; create a view exposing only approved columns and predicates with appropriate permissions and schema review: view access alone is not proof of full tenant isolation without evaluating underlying access paths.
B: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: a table-valued function supplies a relational row set that callers can compose with other queries.
D:Choose an appropriate table-valued function and validate its plan behavior and input filtering.
Question 670
Issue 1: A team exposes a private products table through Data API builder but commits an unrestricted production connection string in the public configuration repository. Issue 2: A configured Data API builder entity includes internal audit columns in the REST response even though external users need only public product fields. Issue 3: A Data API builder endpoint returns too many rows per request and consumers accidentally download the full database table. Which complete response addresses the three issues in order?
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration.
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; update and validate the entity's published REST path and coordinate a versioned client migration; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
- Update and validate the entity's published REST path and coordinate a versioned client migration; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
Explanation
Correct Answer
D:For issue 1, Data API builder deployment settings must not expose database credentials or overprivileged access. For issue 2, API projections should expose only explicitly authorized data rather than mirroring an entire table. For issue 3, API pagination bounds response size and reduces accidental unbounded query load.
Incorrect Answers
A:Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration: aPI pagination bounds response size and reduces accidental unbounded query load.
B:Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; update and validate the entity's published REST path and coordinate a versioned client migration; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior: aPI projections should expose only explicitly authorized data rather than mirroring an entire table.
C:Update and validate the entity's published REST path and coordinate a versioned client migration; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior: data API builder deployment settings must not expose database credentials or overprivileged access.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests