Free DP-800 practice test

A system-versioned temporal table automatically maintains a history table that records every row version along with SysStartTime and SysEndTime columns, capturing precisely when each version was valid — entirely managed by the database engine without application changes. Option A (ledger append-only) provides tamper-evidence via blockchain-style hashing but does not automatically store prior versions with time periods; it records all INSERTs but does not automatically snapshot UPDATE/DELETE history by validity period. Option C (in-memory optimized table) is designed to reduce lock contention and latency for high-throughput OLTP; it has no built-in history or time-travel capability. Option D (graph table) models entity relationships as nodes and edges; it has no native mechanism for tracking historical versions of row data over time.

Option B uses the correct MATCH syntax for a cyclic graph pattern: p1 follows p2, and p2 follows p1, expressed as a single chain p1-(follows)->p2-(follows)->p1. This correctly models mutual follows. Option C only checks that p1 follows p2 but does not verify the reverse relationship. Option D is syntactically invalid—AND inside the MATCH clause is not a supported construct in T-SQL graph MATCH; multiple patterns are expressed as a chain or by referencing the same nodes. Option A uses SHORTEST_PATH, which is for finding paths of variable length and is not appropriate for detecting a simple two-hop cycle between known nodes.

Option B is correct: AVG() as a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW computes the average over exactly the current row and the 6 physically preceding rows within the partition, which is the standard T-SQL pattern for a 7-day rolling average when data has one row per day per store. Option A is wrong: a self-join with BETWEEN works functionally but is far less efficient and more verbose than a window function, and is not the idiomatic or 'most appropriate' approach. Option C is wrong: T-SQL window frames use ROWS or RANGE with numeric offsets, not an INTERVAL keyword (that syntax belongs to ANSI SQL / other databases); this would cause a syntax error in Fabric SQL. Option D is wrong: recursive CTEs are designed for hierarchical traversal, not rolling aggregations; they are complex, error-prone, and poorly performant for this use case.

A clustered columnstore index is the optimal choice for analytical workloads that perform large range scans across many columns. It compresses data by column, enabling high compression ratios and batch-mode execution, dramatically reducing I/O for scan-heavy queries. Option A (clustered rowstore) is designed for row-level access patterns and OLTP workloads; it reads entire rows even when only a few columns are needed. Option B (nonclustered rowstore with included column) helps point lookups and narrow scans but still reads row-by-row and performs poorly on wide analytical scans. Option D (nonclustered columnstore) would leave the underlying rowstore heap/clustered index in place, meaning DML operations update both structures; a clustered columnstore replaces the rowstore entirely, which is more appropriate when the workload is predominantly analytical.

Option A is correct: a nonclustered rowstore index on the fact table's join key allows the optimizer to perform an index seek for selective lookups, complementing the columnstore's batch-mode analytical processing when joining to a small dimension. Option B is correct: if the dimension table is small enough, the optimizer can broadcast (replicate) it to all nodes and perform a loop/broadcast join, which is far more efficient than a general hash join for a large-to-small table scenario. Option C is wrong: converting the fact table to a rowstore index would eliminate the batch-mode execution and compression benefits that are critical for a 500M-row analytical table. Option D is wrong: forcing the existing (slow) HASH JOIN would prevent the optimizer from choosing a better algorithm and is counterproductive. Option E is wrong: a filtered columnstore index on a subset of the fact table does not directly address the join key lookup bottleneck described.

A clustered columnstore index (CCI) is ideal for large analytical workloads because it compresses data by column, enabling high compression ratios and very fast aggregation scans over billions of rows. Option A (clustered B-tree on DeviceID) is optimized for point lookups, not analytical range scans. Option B (non-clustered on Timestamp) improves selective row lookups but does not help aggregation performance across the full table. Option D (non-clustered columnstore + clustered B-tree) is a valid pattern for mixed OLTP/analytics, but the workload here is purely analytical with no selective point-lookup requirement, making a CCI alone the best and simplest choice.

REGEXP_LIKE in Fabric SQL database accepts a POSIX-style regular expression. The pattern '^[0-9]{3}-[0-9]{3}-[0-9]{4}$' anchors the match to the full string (^ = start, $ = end), uses character classes [0-9] with quantifiers {3} and {4}, and hyphens as literals — correctly matching exactly the NXX-NXX-XXXX format. Option A uses a valid regex pattern but omits anchors, meaning it would also match phone numbers embedded in longer strings; however it is functionally close. Option B uses T-SQL LIKE syntax ({n} quantifiers are not valid in LIKE patterns), which would fail. Option D uses % wildcard syntax from LIKE within a regex context, which is incorrect; % has no special meaning in regex.

DAB entity permissions are controlled by specifying an actions array. To allow read (paginated GET), create (POST), and update (PATCH/PUT) while blocking delete, the correct value is ["read", "create", "update"]. Option C uses the wildcard "*", which grants all actions including delete — violating the requirement. Option B adds "execute", which grants stored procedure execution rights and is unnecessary here. Option A includes "delete" and excludes "update", the opposite of the stated requirement.

Enabling a system-assigned managed identity on the App Service (option B) creates an Azure AD identity for the service. Granting that identity database permissions via CREATE USER … FROM EXTERNAL PROVIDER and a role assignment (option C) allows the App Service to authenticate using its token—no password needed. Option A still involves a secret in Key Vault (the connection string contains credentials), which doesn't eliminate secrets from the auth flow. Option D would require a SQL login, which is password-based and grants excessive sysadmin privilege. Option E (firewall rule) controls network access, not authentication—it does not remove the need for credentials.

sp_query_store_force_plan is the correct Query Store mechanism to immediately pin a known-good plan (Plan A) for a query, stabilizing performance without any code change. Disabling Query Store (Option A) removes the plan history and telemetry needed for diagnosis and does not fix the regression. Adding RECOMPILE (Option C) forces a new compilation on every execution, which could generate Plan B again and incurs compilation overhead on every call. Dropping and recreating the index (Option D) is a destructive operation, may break other queries, and does not guarantee the optimizer selects the better plan.

Always Encrypted ensures that encryption and decryption happen exclusively on the client side (driver level), so the plaintext data is never exposed to the database engine, DBAs, or service accounts with direct SQL access. Keys stored in Azure Key Vault are never sent to SQL Database. — TDE (C) encrypts data at rest at the storage level but the database engine still decrypts data in memory, so a DBA with query access can read plaintext. — Dynamic Data Masking (A) only obfuscates query results for non-privileged users but does not encrypt the underlying data; privileged users and the engine itself can see plaintext. — ENCRYPTBYKEY (D) performs server-side encryption, meaning the SQL engine handles decryption and a DBA with sufficient permissions can access the symmetric key and decrypt the data.

Azure Functions SQL trigger bindings rely on SQL Server Change Tracking (not CDC) to detect row changes. Two prerequisites must be met: (1) Change Tracking must be enabled at the database level (ALTER DATABASE ... SET CHANGE_TRACKING = ON) and at the table level (ALTER TABLE ... ENABLE CHANGE_TRACKING), and (2) the tracked table must have a primary key, which Change Tracking requires to uniquely identify changed rows. Option B is incorrect; SQL trigger bindings use Change Tracking, not CDC — enabling CDC alone will not make the trigger work. Option D is incorrect; SQL trigger bindings are supported on Azure Functions runtime v4.x (not limited to v1.x). Option C is a plausible concern, but VIEW CHANGE TRACKING is not a separate permission in Azure SQL / Fabric SQL — access to change tracking data is controlled by SELECT permission on the table, not a specific VIEW CHANGE TRACKING grant in this context.

A GraphQL createOrder mutation translates to an INSERT operation on the underlying table. The developer only has SELECT permission, which allows reads but not writes. Granting INSERT on dbo.Orders provides the necessary permission for the mutation. The Fabric SQL Database managed GraphQL endpoint enforces standard SQL permissions on the underlying objects — it does not use separate auto-generated stored procedures that require EXECUTE (Option A). ALTER (Option C) is a DDL permission for modifying table structure, which is not needed for DML mutations. CONTROL (Option D) is maximally permissive and violates least privilege; it is not required for a single mutation operation.

In SDK-style SQL Database Projects, all .sql files in the project folder are automatically included with a 'Build' action by default. However, if a file was explicitly excluded or its build action was manually overridden to 'None', the object will not be compiled and any object referencing it will produce an 'Unresolved reference' error. Correcting the build action to 'Build' resolves the issue. — Option D is incorrect because unresolved references to system objects (e.g., sys.*) require a dacpac reference to master; a reference to a user-defined view in the same project does not. — Option A is incorrect because SDK-style projects fully support views, stored procedures, and all T-SQL object types. — Option B is incorrect; if the table were dropped, the error would reference dbo.Orders, not dbo.vw_ActiveOrders.

DAB supports an in-memory response cache. To enable it, cache.enabled must be set to true at the runtime configuration level. To control how long responses are cached, the entity-level cache.ttl-seconds property specifies the time-to-live in seconds; setting it to 3600 (1 hour) aligns with the data refresh frequency. Option A, increasing pagination.max-page-size, affects how many rows are returned per page but does not influence caching behavior. Option E, rest.request-body-strict, controls validation of request payloads and has no relation to caching. Option B is incorrect; host.mode production enables security headers and stricter settings but is not a prerequisite for enabling caching.

Options B and C are correct. B is correct: 'device won't turn on' vs. 'power failure' is a classic semantic equivalence scenario where vector search excels by capturing meaning beyond exact keywords. C is correct: hybrid search with RRF is ideal when queries are short and ambiguous, because combining keyword and semantic signals improves overall ranking quality. Option A is wrong: searching for an exact phrase like 'force majeure clause' is a keyword task best served by full-text search, not vector search, which may dilute exact-phrase precision. Option D is wrong: an exact regulatory code like 'REG-2024-07B' is a lookup task—full-text or even a simple LIKE/equality query is more reliable than semantic search, which might return conceptually related but non-matching codes. Option E is wrong: ranking only by sales rank requires no text search at all; using full-text search would introduce irrelevant text scores.

Option D is correct. For 2 million rows with a strict recall requirement > 99% and a latency budget of 200 ms, a well-configured ANN vector index (such as DiskANN in Azure SQL Database) with a high efSearch (candidate expansion) parameter achieves near-exact recall while remaining well within the latency budget — an exhaustive scan over 2 million 768-dimensional vectors would be far slower. Option C is wrong — a full table scan with VECTOR_DISTANCE on 2 million rows will far exceed 200 ms latency and is not practical in a production query path; a vector index is highly beneficial at this scale. Option A is wrong — full-text indexes operate on tokenized text, not floating-point embedding vectors; they cannot perform nearest-neighbor search in embedding space. Option B is wrong — deliberately setting efSearch low to target 80% recall violates the > 99% recall requirement stated in the scenario.

Option C is correct. sp_add_external_model (or its equivalent in the Microsoft Fabric AI Functions integration) is the stored procedure used to register an external model endpoint — such as an Azure AI Foundry–hosted embedding or completion model — so that T-SQL functions like ai.generate_embeddings can invoke it. Option A is wrong — sp_invoke_external_rest_endpoint is used to make arbitrary HTTP REST calls from T-SQL, but it is not the procedure that registers a named external model for use by AI functions. Option D is wrong — sp_configure is a server-level configuration procedure used for setting database engine options, not for registering AI models. Option B is wrong — sp_execute_external_script is used to run R or Python scripts via SQL Server Machine Learning Services, which is a different extensibility mechanism entirely.

Index metric alignment (A) is critical: if the index is built with cosine metric but queries use euclidean distance, the ANN graph structure is optimized for the wrong geometry, producing poor recall. DiskANN (B) is designed for high-throughput incremental workloads and does not require a full rebuild when new vectors are inserted, making it suitable for 5,000 inserts/minute. The ef_construction/ef_search (or equivalent) parameters (D) directly govern the recall-speed-build time trade-off and must be tuned to meet the 90% recall and 50 ms latency targets simultaneously. Vector indexes require the VECTOR data type (C is false) — VARBINARY columns are not automatically indexed with vector algorithms. The number of dimensions in the index definition must match the embedding model's output dimension (E is misleading; it does not 'improve' recall — mismatch causes errors). VECTOR_NORMALIZE (F) is useful for converting cosine to inner-product equivalence for normalized vectors, but it is not required by Azure SQL Database's cosine metric implementation, which handles normalization internally.

An AFTER INSERT, UPDATE trigger that calls an Azure Function via sp_invoke_external_rest_endpoint allows near-real-time, event-driven embedding generation without any application code changes, as the trigger fires automatically on data modification. CDC captures row-level changes to a table asynchronously and an Azure Function can process the change feed to regenerate embeddings for only affected rows in near-real-time. A 24-hour SQL Agent job does not provide near-real-time updates and recomputes all embeddings inefficiently. VECTOR_NORMALIZE normalizes an existing vector (scales it to unit length) but cannot generate embeddings from text — embedding generation requires calling an external model. Rebuilding the clustered index has no effect on embedding values. Change Tracking (option F) is also a valid mechanism, but the question asks to select TWO, and CDC (option B) and triggers (option A) are the most direct near-real-time solutions; Change Tracking is correct in principle but the question limits selection to two, making A and B the best pair given the near-real-time and no-app-change requirements.