Free DP-800 Practice Questions With Full Rationale

Correct: A. A computed column built from JSON_VALUE, indexed directly, lets the query engine use an index seek instead of parsing JSON on every row for every execution — the standard fix for hot JSON-path filters.

• Why not B: A full platform migration is disproportionate when the JSON column feature already supports indexed computed columns for exactly this case.
• Why not C: Application-layer caching masks the symptom for repeat queries but does nothing for the underlying scan cost on first execution or cache misses.
• Why not D: More memory does not remove the per-row JSON parsing cost; the query still re-parses the JSON on every scan.

Correct: B. Ledger tables maintain a cryptographically verifiable history, so any tampering — including by someone with administrative access — is detectable, which is the specific regulatory requirement described.

• Why not A: Temporal tables track historical row versions for point-in-time queries, but they do not provide cryptographic tamper-evidence against a privileged user.
• Why not C: In-memory tables optimize for throughput and latency, not tamper-evidence.
• Why not D: External tables reference data stored outside the database and have no bearing on tamper-evidence for local transaction records.

Correct: C. JARO_WINKLER_DISTANCE is a fuzzy string-matching function designed specifically to score how similar two strings are, which is exactly what near-duplicate name detection needs.

• Why not A: OPENJSON shreds JSON text into rows and columns — unrelated to string similarity.
• Why not B: REGEXP_LIKE tests for an exact pattern match; it cannot score approximate similarity between two arbitrary strings.
• Why not D: MATCH is the graph-query operator for traversing node and edge tables, not for comparing string similarity.

Correct: D. Connecting to an MCP (Model Context Protocol) server endpoint, including for a Fabric lakehouse, is exactly how GitHub Copilot gets live, structured context about a specific data source instead of relying on general knowledge.

• Why not A: A local text-pattern search of scripts has no connection to a live lakehouse and cannot see current schema or data.
• Why not B: Works but does not scale, must be repeated every session, and is the manual workaround for what MCP is built to automate.
• Why not C: Skipping the security review of AI-assisted tools is a compliance risk unrelated to the actual goal of live schema context.

Correct: A. Dynamic Data Masking obscures a column's value in query results for specified users without changing the stored data, which is exactly the display-only masking described.

• Why not B: Always Encrypted would prevent the application from reading the plaintext value at all for authorized processes too, breaking the described workflow.
• Why not C: Row-Level Security restricts which entire rows are visible, not how a specific column value is displayed.
• Why not D: Revoking access entirely removes the column from the query results altogether rather than showing a masked value.

Correct: B. Row-Level Security enforces a predicate at the database level that filters which rows any given query can return, exactly matching a database-enforced, per-tenant row-visibility requirement.

• Why not A: Encrypting the customer ID column protects that value's confidentiality but does not filter which rows a query returns.
• Why not C: Masking hides a value in the result set; it does not stop a query from returning another tenant's rows.
• Why not D: A shared login with no distinction is the opposite of tenant isolation and would rely entirely on application logic the scenario says can't be trusted alone.

Correct: C. Query Store and DMVs are the built-in diagnostic tools for identifying blocking sessions and deadlock chains — the necessary first step before choosing a fix such as an isolation-level change.

• Why not A: Adding hardware without diagnosis does not address a locking/blocking pattern and skips the diagnostic step the scenario calls for.
• Why not B: Index rebuilds address fragmentation, not a concurrency/locking problem between two specific transactions.
• Why not D: A graph table changes the data model for relationship queries; it has no bearing on transaction blocking.

Correct: D. SQL Database Projects provide source-controlled, buildable database models with schema drift detection and configurable branching/PR/approval policies — the exact CI/CD workflow described.

• Why not A: A shared login removes the accountability and review trail the scenario explicitly requires.
• Why not B: An email-based approval is not source-controlled, not automatically validated, and cannot detect drift from out-of-band changes.
• Why not C: Direct production changes with after-the-fact documentation is the exact anti-pattern schema drift detection exists to catch.

Correct: A. An Azure Function bound to a SQL trigger fires immediately when the underlying row changes, giving near-real-time embedding updates without any polling interval.

• Why not B: A nightly batch job introduces up to 24 hours of staleness, which fails the "within seconds" requirement.
• Why not C: Manual, user-triggered re-embedding does not scale and leaves most changes permanently stale until someone happens to notice.
• Why not D: Hourly polling is far slower than the seconds-level freshness the scenario requires, even though Change Tracking is a valid mechanism for less time-sensitive cases.

Correct: B. ANN is designed to trade a small amount of accuracy for speed and scalability, which is exactly the tradeoff acceptable at multi-million-row scale with a sub-second requirement.

• Why not A: ENN guarantees exact results but scales worse — the scenario explicitly accepts approximation in exchange for speed, which points away from ENN.
• Why not C: Full-text search alone matches keywords, not semantic similarity between embeddings, so it does not answer a similarity-search requirement.
• Why not D: Regex pattern matching on product names has no relationship to vector embedding similarity.

Correct: C. This is the documented RAG-in-T-SQL sequence: convert structured data to JSON, invoke the external model through sp_invoke_external_rest_endpoint, then extract the model's response — matching the "send results to language model, extract response" objective directly.

• Why not A: VECTOR_SEARCH retrieves similar rows; it does not generate a natural-language summary without a subsequent call to a language model.
• Why not B: Embeddings alone produce vectors for similarity search, not a natural-language summary — an actual model call is required for that.
• Why not D: Always Encrypted is a data-protection feature, not a mechanism for generating or returning a summary.