Question 860
Issue 1: 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. Issue 2: A security predicate reads tenant ID directly from a user-supplied request parameter that clients can change without authentication. Issue 3: A developer tests Row-Level Security only with the administrator account and concludes all customer roles are correctly isolated. Which complete response addresses the three issues in order?
- 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; and 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; and test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries.
- Enforce tenant-aware Row-Level Security predicates using a trusted application session identity and test cross-tenant denial; use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions; and test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries.
- Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions; Derive tenant context from an authenticated trusted identity and prevent clients from spoofing the session context; and test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries.
Explanation
Correct Answer
B:For issue 1, RLS filters effective row access in the database; frontend URL filters alone are insufficient for tenant isolation. For issue 2, a row filter is only as strong as the trustworthy identity value it compares against each row. For issue 3, security validation requires testing the roles subject to the policy, not solely an account that may bypass or override controls.
Incorrect Answers
A: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; and use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions: security validation requires testing the roles subject to the policy, not solely an account that may bypass or override controls.
C:Enforce tenant-aware Row-Level Security predicates using a trusted application session identity and test cross-tenant denial; use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions; and test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries: a row filter is only as strong as the trustworthy identity value it compares against each row.
D:Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions; Derive tenant context from an authenticated trusted identity and prevent clients from spoofing the session context; and test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries: rLS filters effective row access in the database; frontend URL filters alone are insufficient for tenant isolation.
Question 861
Issue 1: A model vendor changes the embedding model's tokenizer, but the pipeline keeps old chunks and declares the retrieval service unchanged. Issue 2: An analyst wants to include an entire customer record in embeddings even though only product-interest text is needed for recommendation similarity. Issue 3: A compliance team cannot explain which embedding model produced a stored vector or when it was last refreshed. Which complete response addresses the three issues in order?
- Reevaluate chunk lengths and retrieval quality under the model update before promoting it; Track token and request usage per refresh job and set suitable budgets and anomaly alerts; and Record model ID, version, source checksum and embedding timestamp with each indexed record.
- Track token and request usage per refresh job and set suitable budgets and anomaly alerts; select task-relevant descriptive fields and exclude protected unrelated customer attributes; and Record model ID, version, source checksum and embedding timestamp with each indexed record.
- Reevaluate chunk lengths and retrieval quality under the model update before promoting it; select task-relevant descriptive fields and exclude protected unrelated customer attributes; and Record model ID, version, source checksum and embedding timestamp with each indexed record.
- Reevaluate chunk lengths and retrieval quality under the model update before promoting it; select task-relevant descriptive fields and exclude protected unrelated customer attributes; and Track token and request usage per refresh job and set suitable budgets and anomaly alerts.
Explanation
Correct Answer
C:For issue 1, tokenizer and model changes can shift effective context limits and retrieval behavior. For issue 2, input selection is part of both retrieval quality and privacy-by-design. For issue 3, version and source lineage are needed to reproduce and audit semantic search behavior.
Incorrect Answers
A:Reevaluate chunk lengths and retrieval quality under the model update before promoting it; Track token and request usage per refresh job and set suitable budgets and anomaly alerts; and Record model ID, version, source checksum and embedding timestamp with each indexed record: input selection is part of both retrieval quality and privacy-by-design.
B:Track token and request usage per refresh job and set suitable budgets and anomaly alerts; select task-relevant descriptive fields and exclude protected unrelated customer attributes; and Record model ID, version, source checksum and embedding timestamp with each indexed record: tokenizer and model changes can shift effective context limits and retrieval behavior.
D:Reevaluate chunk lengths and retrieval quality under the model update before promoting it; select task-relevant descriptive fields and exclude protected unrelated customer attributes; and Track token and request usage per refresh job and set suitable budgets and anomaly alerts: version and source lineage are needed to reproduce and audit semantic search behavior.
Question 862
An AI-enabled database project needs two corrections. An Azure SQL application suddenly has higher latency after its transaction volume triples and resource metrics show sustained compute saturation. A test query is fast with one user but slow with fifty concurrent requests because workloads compete for compute and locks. What is the most appropriate plan for this situation?
- Compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck; Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix.
- Inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration; Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix.
- Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix; do not compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck.
- Compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck; Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity.
Explanation
Correct Answer
A:First, capacity planning should follow CPU, IO and workload measurements rather than an untested tier upgrade. Second, single-user latency does not predict contention or service capacity under production load.
Incorrect Answers
B:Inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration; Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix: capacity planning should follow CPU, IO and workload measurements rather than an untested tier upgrade.
C:Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix; do not compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck: compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck.
D:Compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck; Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity: single-user latency does not predict contention or service capacity under production load.
Question 863
An external model API responds intermittently with throttling, and SQL code retries indefinitely while holding an open business transaction. Which action best addresses the database requirement?
- Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling.
- Bound and back off retries outside long-running critical transactions and handle model errors explicitly.
- Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
Explanation
Correct Answer
C:External inference availability should not monopolize database locks or create uncontrolled request storms.
Incorrect Answers
A:Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint: 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; Bound and back off retries outside long-running critical transactions and handle model errors explicitly.
B:Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling: 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; Bound and back off retries outside long-running critical transactions and handle model errors explicitly.
D:Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content: validate HTTP outcomes and the returned JSON structure before extracting and storing generated content: a successful transport response does not guarantee an unchanged application-level payload contract; Bound and back off retries outside long-running critical transactions and handle model errors explicitly.
Question 864
An account may read rows from its own region but update statements can move rows to a forbidden region and bypass simple read filters. What is the most defensible technical decision?
- Enforce tenant-aware Row-Level Security predicates using a trusted application session identity and test cross-tenant denial.
- Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions.
- 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:A read filter alone may not stop an UPDATE that moves a row across an access boundary.
Incorrect Answers
A:Enforce tenant-aware Row-Level Security predicates using a trusted application session identity and test cross-tenant denial: enforce tenant-aware Row-Level Security predicates using a trusted application session identity and test cross-tenant denial: rLS filters effective row access in the database; frontend URL filters alone are insufficient for tenant isolation; Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions.
C:Derive tenant context from an authenticated trusted identity and prevent clients from spoofing the session context: 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; Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions.
D:Test effective RLS behavior with representative least-privileged identities and denied cross-tenant queries: 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; Use appropriate RLS block predicates as well as filters to constrain unauthorized write-state transitions.
Question 865
An Azure Function responsible for regenerating embeddings sometimes retries after timeout and stores duplicate vectors for one source row. A deleted policy continues appearing in search because the embedding pipeline handles inserts and updates but ignores deletes. What is the most appropriate plan for this situation?
- Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup; do not use stable source keys, generation versions and upsert/idempotency controls when processing retries.
- Use stable source keys, generation versions and upsert/idempotency controls when processing retries; 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; Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup.
- Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer; Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup.
Explanation
Correct Answer
C:First, change-triggered processing may be delivered more than once and must not duplicate index records. Second, embedding lifecycle management includes deletion and retention, not only creation and refresh.
Incorrect Answers
A:Use stable source keys, generation versions and upsert/idempotency controls when processing retries.
B:Use stable source keys, generation versions and upsert/idempotency controls when processing retries; implement an incremental embedding-refresh workflow using supported Change Tracking or CDC and a reliable checkpoint: embedding lifecycle management includes deletion and retention, not only creation and refresh.
D:Move embedding generation to a queued asynchronous process driven by a change signal and idempotent consumer; Propagate deletion tombstones or change records to remove corresponding vectors and verify search index cleanup: change-triggered processing may be delivered more than once and must not duplicate index records.
Question 866
Issue 1: An audit requirement says modifications to a table must automatically record the old and new keys on every permitted write. Issue 2: A developer creates a trigger assuming INSERTED always contains one row, and a bulk load silently omits audit entries for most changed rows. Issue 3: An API calls a stored procedure by concatenating untrusted user input into dynamic SQL and security reviewers find injection risk. Which complete response addresses the three issues in order?
- Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; and create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
- Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; and Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
- Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; and Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
- Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; and Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
Explanation
Correct Answer
C:For issue 1, triggers run with table writes and must correctly handle sets of inserted and deleted rows. For issue 2, T-SQL DML triggers are statement-level, so pseudo-tables may hold many rows. For issue 3, data parameters must not become executable SQL text and procedure access should follow least privilege.
Incorrect Answers
A:Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; and create a parameterized stored procedure with an explicit transaction strategy and documented error behavior: data parameters must not become executable SQL text and procedure access should follow least privilege.
B:Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; and Parameterize the procedure's dynamic SQL and grant the caller only required execution rights: triggers run with table writes and must correctly handle sets of inserted and deleted rows.
D:Evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks; create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; and Parameterize the procedure's dynamic SQL and grant the caller only required execution rights: t-SQL DML triggers are statement-level, so pseudo-tables may hold many rows.
Question 867
A database architect reviews two implementation risks. A database represents employee reporting lines as repeated self-joins, and developers need to traverse multi-hop relationships between people. 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. Which response best addresses these needs?
- 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; 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; evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions.
- Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; do not evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case.
Explanation
Correct Answer
C:First, graph structures represent relationships directly and can simplify traversals that otherwise require repeated joins. Second, external tables expose remote data under a schema while leaving the authoritative storage outside the local table.
Incorrect Answers
A: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: external tables expose remote data under a schema while leaving the authoritative storage outside the local table.
B: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: graph structures represent relationships directly and can simplify traversals that otherwise require repeated joins.
D:Evaluate an eligible external table or supported virtualization option with explicit source credentials and format definitions; do not evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case: evaluate graph node and edge tables with supported pattern queries for the relationship traversal use case.
Question 868
A team uses extremely tiny overlapping chunks and the vector index triples in size while many near-identical snippets dominate results. A document embedding pipeline stores passages without any page, section or document version identifiers and reviewers cannot verify answers. What is the most appropriate plan for this situation?
- Store stable source provenance and effective-version fields alongside every embedded chunk; do not Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap.
- Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; Split the manual into coherent section-level chunks with source IDs and test retrieval granularity.
- Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; Store stable source provenance and effective-version fields alongside every embedded chunk.
- Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; Store stable source provenance and effective-version fields alongside every embedded chunk.
Explanation
Correct Answer
D:First, excessive overlap can raise duplicate retrieval and index cost without improving answer quality. Second, RAG evidence must be traceable to its originating authorized document.
Incorrect Answers
A:Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap.
B:Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap; Split the manual into coherent section-level chunks with source IDs and test retrieval granularity: rAG evidence must be traceable to its originating authorized document.
C:Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; Store stable source provenance and effective-version fields alongside every embedded chunk: excessive overlap can raise duplicate retrieval and index cost without improving answer quality.
Question 869
Issue 1: A healthcare application must store diagnoses so that database administrators cannot read plaintext, while an authorized client can decrypt it. Issue 2: A support report must hide most digits of a stored card number from ordinary readers but privileged roles may still see its original value. Issue 3: A SQL database holds a confidential account field that must remain encrypted at rest while the application controls authorized decryption. Which complete response addresses the three issues in order?
- Evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility; add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone; and implement supported column-level encryption with disciplined key management and verify its query and operational constraints.
- Evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility; configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption; and add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone.
- Add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone; configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption; and implement supported column-level encryption with disciplined key management and verify its query and operational constraints.
- Evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility; configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption; and implement supported column-level encryption with disciplined key management and verify its query and operational constraints.
Explanation
Correct Answer
D:For issue 1, Always Encrypted protects selected sensitive values from the database engine and privileged infrastructure operators when keys remain client-controlled. For issue 2, dynamic masking changes displayed values for lower-privileged queries; it does not encrypt the underlying stored data. For issue 3, 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; add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone; and implement supported column-level encryption with disciplined key management and verify its query and operational constraints: dynamic masking changes displayed values for lower-privileged queries; it does not encrypt the underlying stored data.
B:Evaluate Always Encrypted with appropriately managed client-side keys and verify driver and query compatibility; configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption; and add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone: column encryption protects selected data when at-rest confidentiality and controlled application access are needed.
C:Add appropriate field-level confidentiality and least-privilege controls instead of relying on at-rest storage encryption alone; configure Dynamic Data Masking with explicit permissions and verify it is not being treated as encryption; and implement supported column-level encryption with disciplined key management and verify its query and operational constraints: always Encrypted protects selected sensitive values from the database engine and privileged infrastructure operators when keys remain client-controlled.
Question 870
A query slows after a data load, and its execution plan underestimates rows by several orders of magnitude. Which action best addresses the database requirement?
- Review the plan's spills, cardinality and memory grant behavior and optimize or resource-plan the measured cause.
- Capture an actual execution plan and runtime metrics before targeting the demonstrated operators.
- Inspect statistics freshness and cardinality estimates, then update relevant statistics under a controlled performance test.
- Compare representative plans with and without the hint and remove it if it forces an unsuitable access path.
Explanation
Correct Answer
C:Outdated distribution estimates can lead the optimizer to choose inefficient joins and memory grants.
Incorrect Answers
A:Review the plan's spills, cardinality and memory grant behavior and optimize or resource-plan the measured cause: review the plan's spills, cardinality and memory grant behavior and optimize or resource-plan the measured cause: sort and hash spills often indicate memory-grant or cardinality problems that can dominate latency; Inspect statistics freshness and cardinality estimates, then update relevant statistics under a controlled performance test.
B:Capture an actual execution plan and runtime metrics before targeting the demonstrated operators: capture an actual execution plan and runtime metrics before targeting the demonstrated operators: query tuning should follow measured bottlenecks rather than assuming every slowdown is a missing index; Inspect statistics freshness and cardinality estimates, then update relevant statistics under a controlled performance test.
D:Compare representative plans with and without the hint and remove it if it forces an unsuitable access path: compare representative plans with and without the hint and remove it if it forces an unsuitable access path: hints can override optimizer choices that become more appropriate as data volume or distribution changes; Inspect statistics freshness and cardinality estimates, then update relevant statistics under a controlled performance test.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests