Question 610
A database stores numeric embedding arrays but the developer needs to check each vector's dimensions before calling a distance function. A developer changes from cosine to dot-product scoring but assumes normalization is irrelevant to ranking outcomes. What is the most appropriate plan for this situation?
- Inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison; determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step.
- 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.
- Determine whether the embedding model requires normalization and evaluate VECTOR_NORMALIZE or an equivalent supported step; do not inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison.
- 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.
Explanation
Correct Answer
A:First, vector dimension validation avoids invalid comparisons and inconsistent index configuration. Second, dot-product scores can depend on vector magnitudes unless normalization and model assumptions are controlled.
Incorrect Answers
B: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: vector dimension validation avoids invalid comparisons and inconsistent index configuration.
C:Inspect vector properties using supported VECTORPROPERTY or equivalent metadata checks before comparison.
D: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: dot-product scores can depend on vector magnitudes unless normalization and model assumptions are controlled.
Question 611
A deployment pipeline publishes a build created from unreviewed branch commits instead of the signed-off release revision. Which action best addresses the database requirement?
- Add the specified environment approval gate before the deployment step and retain its audit record.
- Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes.
- Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
- Promote the same immutable build artifact through validated environments and use environment-specific secure configuration.
Explanation
Correct Answer
C:Release traceability requires deploying the approved source revision rather than an arbitrary latest build.
Incorrect Answers
A:Add the specified environment approval gate before the deployment step and retain its audit record: add the specified environment approval gate before the deployment step and retain its audit record: model validation and business authorization are separate conditions for a production release.
B:Add safe concurrency controls and deployment-state checks to avoid overlapping schema changes: add safe concurrency controls and deployment-state checks to avoid overlapping schema changes: parallel DDL changes can conflict and leave a database in an indeterminate release state.
D:Promote the same immutable build artifact through validated environments and use environment-specific secure configuration: promote the same immutable build artifact through validated environments and use environment-specific secure configuration: promoting one artifact reduces untracked differences between tested and released database code; Bind the production artifact to the reviewed commit or release tag and verify provenance at deployment.
Question 612
A SQL service sends regulated document text to an external embedding provider without checking its processing region or retention terms. What should the SQL developer do next?
- Keep the content as untrusted model input and block instructions that request unrelated privileged operations.
- Store vectors in a supported vector representation and use appropriate distance or search functions for similarity.
- Validate the model provider's residency, processing and data-retention terms before sending the protected text.
- Enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets.
Explanation
Correct Answer
C:Semantic model processing is an external data flow that must satisfy the source classification rules.
Incorrect Answers
A:Keep the content as untrusted model input and block instructions that request unrelated privileged operations: keep the content as untrusted model input and block instructions that request unrelated privileged operations: source documents and user text cannot grant authorization to access application secrets.
B:Store vectors in a supported vector representation and use appropriate distance or search functions for similarity: store vectors in a supported vector representation and use appropriate distance or search functions for similarity: vector search requires numerical vector operations, not ordinary alphabetic comparison of serialized coordinates.
D:Enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets: enforce tenant and document authorization during candidate retrieval and revalidate before returning source snippets: vector similarity does not override access-control boundaries in multitenant databases; Validate the model provider's residency, processing and data-retention terms before sending the protected text.
Question 613
A database import accepts product IDs only when they follow two uppercase letters and six digits, but a LIKE check misses invalid punctuation. A directory deduplication review needs to rank near-matching contact names for human adjudication, not declare all near matches identical. What is the most appropriate plan for this situation?
- Use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds; do not apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers.
- Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs.
- Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds.
- Evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold; use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds.
Explanation
Correct Answer
C:First, anchored regular expressions validate the full identifier shape instead of only a loose substring. Second, fuzzy matching provides graded similarity, not proof that two people or entities are identical.
Incorrect Answers
A:Use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds; do not apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers: apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers.
B:Apply a supported REGEXP_LIKE pattern with explicit anchors and test representative valid and invalid identifiers; use REGEXP_REPLACE with an anchored prefix expression and validate unmatched values before updating stored IDs: fuzzy matching provides graded similarity, not proof that two people or entities are identical.
D:Evaluate EDIT_DISTANCE with domain-appropriate preprocessing and a tested acceptance threshold; use a supported fuzzy similarity measure such as JARO_WINKLER_DISTANCE in candidate scoring and retain manual review thresholds: anchored regular expressions validate the full identifier shape instead of only a loose substring.
Question 614
A production database calls a model deployment whose version was changed without regression testing and now JSON output fields differ. Which action best addresses the database requirement?
- 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.
- 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.
Explanation
Correct Answer
A:Model endpoint upgrades can change behavior and must be treated as application dependency changes.
Incorrect Answers
B:Scope invocation credentials through approved secure identity or secret management and restrict configuration visibility: scope invocation credentials through approved secure identity or secret management and restrict configuration visibility: model access must follow least privilege and avoid exposing reusable high-value credentials; Version and validate the external model integration contract before approving the changed deployment.
C:Check supported platform and version prerequisites before deploying the external model integration: check supported platform and version prerequisites before deploying the external model integration: sQL AI features differ by engine edition and release and cannot be assumed portable without verification; Version and validate the external model integration contract before approving the changed deployment.
D:Configure the supported external model definition and secure its endpoint authentication under the target SQL platform: configure the supported external model definition and secure its endpoint authentication under the target SQL platform: external model integration requires explicit endpoint and credential configuration rather than mere table existence; Version and validate the external model integration contract before approving the changed deployment.
Question 615
An application team must address both issues before release. A customer identifier accepts duplicate values even though every integration expects a single permanent row key. A subscription table accepts negative durations and arbitrary status strings, producing invalid invoices despite correct column types. Which response best addresses these needs?
- Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it; add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records.
- Add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records; do not define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it.
- Define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows; add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records.
- Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it; Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values.
Explanation
Correct Answer
A:First, a primary key establishes unique row identity and prevents null key values, unlike a display-only index. Second, a CHECK constraint expresses row-level business rules that a data type alone does not enforce.
Incorrect Answers
B:Add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records; do not define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it: define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it.
C:Define and validate a FOREIGN KEY from orders to customers after reconciling orphaned rows; add an appropriate CHECK constraint for duration and permitted states while cleaning existing invalid records: a primary key establishes unique row identity and prevents null key values, unlike a display-only index.
D:Define a PRIMARY KEY on a stable non-null identifier and validate existing duplicates before enforcing it; Change the amount column to an appropriately sized DECIMAL precision and scale after validating existing values: a CHECK constraint expresses row-level business rules that a data type alone does not enforce.
Question 616
An application team must address both issues before release. An application account is granted db_owner just to execute one data-entry stored procedure. A reporting view exposes a secret token column that is not needed by analysts, despite the analysts having a legitimate read requirement. What is the most appropriate plan for this situation?
- Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; do not 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; 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; apply compatibility tests and a controlled version or migration path for the procedure contract.
- Grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior; Redesign the view to project only approved business columns and restrict direct access to the sensitive base column.
Explanation
Correct Answer
D:First, whole-database ownership is excessive for calling one approved routine. Second, column minimization should be enforced at the data access boundary rather than left to dashboard display choices.
Incorrect Answers
A:Redesign the view to project only approved business columns and restrict direct access to the sensitive base column; do not 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.
B:Restrict the procedure's output and execution permissions and validate the effective identity against the data classification policy; Redesign the view to project only approved business columns and restrict direct access to the sensitive base column: whole-database ownership is excessive for calling one approved routine.
C:Grant the narrow EXECUTE permission needed for the procedure and review its underlying access behavior; apply compatibility tests and a controlled version or migration path for the procedure contract: column minimization should be enforced at the data access boundary rather than left to dashboard display choices.
Question 617
An application team must address both issues before release. A team wants an AI assistant to explain why a T-SQL view is slow but provides only an error-free query text and no execution evidence. A model proposes changing a schema to satisfy a prompt, but the Fabric project's pull-request policy requires two reviewers. Which course of action addresses the stated issues?
- Validate the target engine's supported SQL surface and deploy only code tested against that platform; Submit the change through the required pull request and validation process rather than applying the model output directly.
- Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; Submit the change through the required pull request and validation process rather than applying the model output directly.
- Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance.
- Submit the change through the required pull request and validation process rather than applying the model output directly; do not Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test.
Explanation
Correct Answer
B:First, optimization guidance without actual plans and runtime evidence can choose the wrong bottleneck. Second, AI-assisted authoring does not supersede development governance and approval requirements.
Incorrect Answers
A:Validate the target engine's supported SQL surface and deploy only code tested against that platform; Submit the change through the required pull request and validation process rather than applying the model output directly: optimization guidance without actual plans and runtime evidence can choose the wrong bottleneck.
C:Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test; verify workspace, lakehouse and identity entitlements before expecting source-grounded assistance: aI-assisted authoring does not supersede development governance and approval requirements.
D:Attach authorized plan and workload context and treat the suggested tuning changes as hypotheses to test.
Question 618
An AI-enabled database project needs two corrections. A finance service performs a validated multi-step booking operation and needs one callable database interface with transactional error handling. An API calls a stored procedure by concatenating untrusted user input into dynamic SQL and security reviewers find injection risk. Which response best addresses these needs?
- Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
- Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; Parameterize the procedure's dynamic SQL and grant the caller only required execution rights.
- Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks.
- Parameterize the procedure's dynamic SQL and grant the caller only required execution rights; do not create a parameterized stored procedure with an explicit transaction strategy and documented error behavior.
Explanation
Correct Answer
B:First, procedures encapsulate database operations and allow callers to execute a controlled multi-step unit. Second, data parameters must not become executable SQL text and procedure access should follow least privilege.
Incorrect Answers
A:Rewrite the trigger with set-based joins to INSERTED and DELETED and test multirow statements; Parameterize the procedure's dynamic SQL and grant the caller only required execution rights: procedures encapsulate database operations and allow callers to execute a controlled multi-step unit.
C:Create a parameterized stored procedure with an explicit transaction strategy and documented error behavior; evaluate a well-scoped AFTER trigger or native auditing feature and test multirow operations and recursion risks: data parameters must not become executable SQL text and procedure access should follow least privilege.
D:Parameterize the procedure's dynamic SQL and grant the caller only required execution rights; do not 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.
Question 619
A database architect reviews two implementation risks. An analyst wants to include an entire customer record in embeddings even though only product-interest text is needed for recommendation similarity. A compliance team cannot explain which embedding model produced a stored vector or when it was last refreshed. What is the most appropriate plan for this situation?
- Record model ID, version, source checksum and embedding timestamp with each indexed record; do not select task-relevant descriptive fields and exclude protected unrelated customer attributes.
- Select task-relevant descriptive fields and exclude protected unrelated customer attributes; Reevaluate chunk lengths and retrieval quality under the model update before promoting it.
- Select task-relevant descriptive fields and exclude protected unrelated customer attributes; 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; Record model ID, version, source checksum and embedding timestamp with each indexed record.
Explanation
Correct Answer
C:First, input selection is part of both retrieval quality and privacy-by-design. Second, version and source lineage are needed to reproduce and audit semantic search behavior.
Incorrect Answers
A:Record model ID, version, source checksum and embedding timestamp with each indexed record; do not select task-relevant descriptive fields and exclude protected unrelated customer attributes: select task-relevant descriptive fields and exclude protected unrelated customer attributes.
B:Select task-relevant descriptive fields and exclude protected unrelated customer attributes; Reevaluate chunk lengths and retrieval quality under the model update before promoting it: version and source lineage are needed to reproduce and audit semantic search behavior.
D:Track token and request usage per refresh job and set suitable budgets and anomaly alerts; 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.
Question 620
A migration adds a NOT NULL column to a table with millions of existing rows and fails because no value is available for old records. A release contains an irreversible transformation but the deployment plan says rollback is just redeploying the old database project. Which course of action addresses the stated issues?
- Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; Prepare a validated data backup or forward-fix recovery approach and test it before the change window.
- Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment.
- Review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; Prepare a validated data backup or forward-fix recovery approach and test it before the change window.
- Prepare a validated data backup or forward-fix recovery approach and test it before the change window; do not Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL.
Explanation
Correct Answer
A:First, existing data must satisfy new column constraints during deployment, not only future inserts. Second, reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
Incorrect Answers
B:Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL; Detect schema drift against the SQL Database Project and reconcile approved exceptions before publishing deployment: reverting a schema definition cannot recreate data already deleted or transformed irreversibly.
C:Review the schema diff and implement an explicit data-preserving rename or migration before allowing deployment; Prepare a validated data backup or forward-fix recovery approach and test it before the change window: existing data must satisfy new column constraints during deployment, not only future inserts.
D:Stage the change with a backfill or safe default and validate constraints before enforcing NOT NULL.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests