DP-800
Free DP-800 practice questions
10 questions from our DP-800 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.
Question 1 of 10, Design and develop database solutions
An auditing firm requires cryptographic proof that rows in a Transactions table were never modified or deleted after they were inserted. The application only ever inserts rows into this table. Which design meets the requirement with the least overhead?
- AA table protected by an INSTEAD OF UPDATE, DELETE trigger that raises an error
- BAn updatable ledger table with a history table and periodic digest uploads
- CAn append-only ledger table, with database digests stored in immutable storage Correct answer
- DA system-versioned temporal table with a 10-year history retention period
Answer and explanation: An append-only ledger table blocks updates and deletes and chains rows into cryptographic hashes, and storing digests in immutable storage lets auditors verify that nothing was altered. An updatable ledger table also gives tamper-evidence but adds a history table that this insert-only workload does not need. A temporal table records history but offers no cryptographic proof against an administrator altering history. A trigger can be disabled or dropped by a privileged user, so it is not tamper-evident.
Question 2 of 10, Design and develop database solutions
An insurance company must be able to answer the question "What did this policy row look like on 1 March last year?" for regulatory reviews, without writing any audit triggers. Which feature should the developer implement on the Policies table?
- AAn updatable ledger table verified with sys.sp_verify_database_ledger
- BChange Tracking with CHANGE_RETENTION set to 400 days on the database
- CA system-versioned temporal table queried with FOR SYSTEM_TIME AS OF Correct answer
- DChange Data Capture with a cleanup job retaining one year of changes
Answer and explanation: A system-versioned temporal table keeps every prior row version in a history table automatically, and FOR SYSTEM_TIME AS OF returns the row as it was at any point in time. CDC records changes but answering an as-of question requires reconstructing state from change rows and relies on capture jobs. An updatable ledger table proves tamper-evidence; it is not designed for simple point-in-time queries. Change Tracking records only that a row changed, not its previous values.
Question 3 of 10, Design and develop database solutions
An order-entry system on SQL Server 2025 runs heavy OLTP inserts and updates on dbo.Orders. Management now wants live dashboards that aggregate the same table without an ETL delay, and the OLTP workload must keep its existing clustered primary key. What should the developer add?
- AA nightly CREATE TABLE AS SELECT copy of Orders into a separate reporting schema
- BAn indexed view with SCHEMABINDING that pre-aggregates every dashboard metric
- CA nonclustered columnstore index on the columns used by the dashboard queries Correct answer
- DA clustered columnstore index that replaces the existing clustered primary key
Answer and explanation: A nonclustered columnstore index on a rowstore table enables real-time operational analytics: the OLTP path keeps its clustered key while analytic queries use batch-mode columnstore scans. Replacing the clustered key with a clustered columnstore index violates the requirement. An indexed view only serves the exact aggregations it defines and adds maintenance cost to every write. A nightly copy introduces the ETL delay that management wants to avoid.
Question 4 of 10, Design and develop database solutions. Choose 2.
A developer is designing an updatable ledger table in Azure SQL Database for a loan-approval workflow. Which two statements about ledger tables are true? (Choose two.)
- AUpdatable ledger tables discard previous row versions after each verification
- BLedger verification works only when automatic digest storage is disabled
- CAn existing non-ledger table cannot be converted in place into a ledger table Correct answer
- DLedger tables encrypt every column so that DBAs cannot read the data
- EA ledger table cannot later be converted back into a non-ledger table Correct answer
Answer and explanation: Ledger is a property chosen when the table is created; existing tables must be migrated into a new ledger table, and once created a ledger table cannot be turned back into a regular table. Updatable ledger tables keep earlier row versions in a history table, they do not discard them. Verification uses the digests, whether uploaded automatically or provided manually. Ledger provides tamper-evidence, not encryption, so DBAs can still read the data.
Question 5 of 10, Secure, optimize, and deploy database solutions
An Azure SQL database calls an Azure OpenAI embedding deployment through sp_invoke_external_rest_endpoint. The team wants to remove the API key from the database. Which configuration should they use?
- AGrant the server's managed identity the Cognitive Services OpenAI User role and use a credential with IDENTITY = 'Managed Identity' Correct answer
- BStore the API key in a database scoped credential with IDENTITY = 'HTTPEndpointHeaders' and rotate the key monthly
- CPass the API key as a query-string parameter in the @url argument so it is not stored in any credential
- DCreate a SQL login named after the Azure OpenAI resource and map that login to the Contributor role on the Azure OpenAI resource
Answer and explanation: With managed identity, the logical server authenticates to Azure OpenAI using Microsoft Entra ID; the identity needs an appropriate role such as Cognitive Services OpenAI User and the database scoped credential uses IDENTITY = 'Managed Identity'. An HTTPEndpointHeaders credential still stores the API key. Putting the key in the URL exposes it in code and logs. SQL logins cannot be assigned Azure RBAC roles on an Azure OpenAI resource.
Question 6 of 10, Secure, optimize, and deploy database solutions
A security reviewer notes that a user without UNMASK permission can still run queries such as WHERE Salary > 100000 against a masked Salary column and narrow down the actual values. What is the correct conclusion?
- AThe masking function is misconfigured and must be changed from default() to random() to stop inference
- BDynamic Data Masking does not stop inference through ad hoc predicates and must be combined with other controls Correct answer
- CDynamic Data Masking blocks predicates on masked columns, so the user must also hold the UNMASK permission
- DThe user must belong to db_owner, because Dynamic Data Masking prevents inference attacks for every other user
Answer and explanation: Masking only changes the returned values; predicates still evaluate against real data, so users with ad hoc query access can infer values. Microsoft documents this limitation, and DDM should be combined with permissions, auditing or encryption. Switching to random() does not change how predicates work. Predicates on masked columns are not blocked. db_owner members see unmasked data anyway, but inference is possible for any user who can query.
Question 7 of 10, Secure, optimize, and deploy database solutions
A multi-tenant SaaS app connects with a single database user and calls sp_set_session_context with the TenantId after opening each connection. The developer must ensure queries return only the current tenant's rows. What should the developer create?
- AA schema-bound inline TVF that reads SESSION_CONTEXT, applied by a security policy as a FILTER predicate Correct answer
- BA scalar function that the application calls in every query's WHERE clause
- CA DDL trigger that adds a WHERE TenantId clause to each SELECT statement
- DA view per tenant that filters on TenantId, plus a separate database role that is granted SELECT on each view
Answer and explanation: Row-Level Security uses a schema-bound inline TVF as the predicate and a security policy that applies it as a FILTER predicate; reading TenantId from SESSION_CONTEXT works with a shared application user. Per-tenant views do not scale and do not work with one database user. DDL triggers react to schema changes and cannot rewrite queries. Relying on the app to call a function in every query is error-prone and not enforced by the database.
Question 8 of 10, Implement AI capabilities in database solutions
A table holds 30 million document-chunk embeddings and semantic queries must return in well under a second, while a small loss of recall is acceptable. Which design should the developer implement in Azure SQL Database?
- ACreate a DiskANN vector index and query it with VECTOR_SEARCH using TOP (N) WITH APPROXIMATE Correct answer
- BOrder all rows by VECTOR_DISTANCE and add a nonclustered rowstore index on the embedding column too
- CStore the vectors as JSON and create a JSON index on the first ten vector elements
- DPartition the table by month and run VECTOR_DISTANCE against every partition in parallel
Answer and explanation: A DiskANN vector index provides approximate nearest neighbor search, and VECTOR_SEARCH with TOP (N) WITH APPROXIMATE uses it to answer queries over tens of millions of vectors with low latency at a small recall cost. Ordering by VECTOR_DISTANCE scans every row, and a rowstore index on a vector column does not accelerate similarity. Partitioning still computes distances for all rows. A JSON index on a few elements has no relationship to vector similarity.
Question 9 of 10, Implement AI capabilities in database solutions
A team on Azure SQL Database uses the latest vector index version and filters vector search results with WHERE Category = 'Laptops'. Previously, with an earlier index version, such queries sometimes returned fewer rows than requested. Why does the latest version behave better?
- AThe latest version always switches to an exact kNN scan whenever a WHERE clause is present in the query
- BThe latest version builds a separate vector index for each distinct value of every filtered column automatically
- CPredicates are applied during the search through iterative filtering, not after a fixed set of neighbors Correct answer
- DThe latest version requires the TOP_N parameter, which oversamples neighbors before the filter is applied
Answer and explanation: With the latest vector index version, WHERE predicates are applied during the approximate search, so the engine keeps searching until enough qualifying rows are found. Earlier versions post-filtered a fixed set of neighbors, which could return fewer rows. The engine does not build per-value indexes, does not always fall back to kNN when filtering, and TOP_N is deprecated and not supported with the latest index version.
Question 10 of 10, Implement AI capabilities in database solutions
An application must generate embeddings for 5 million rows during an initial load and then a few hundred per minute. Search quality tests show a small embedding model is within 1 percent of a large model on the team's queries. Which choice is MOST cost-effective?
- AUse the large model, because larger vectors always improve recall for every type of workload
- BUse the large model for the initial load and the small model for new rows to balance the cost
- CUse a chat completion model to summarize each row before embedding with the large model
- DUse the small model, which costs less per token and yields smaller vectors to index Correct answer
Answer and explanation: When evaluation shows nearly identical quality, the smaller model reduces inference cost, storage and index size, and speeds up search. Larger vectors do not always improve recall, and here testing shows no meaningful gain. Mixing models produces vectors from different spaces that cannot be compared. Summarizing every row with a chat model adds cost and latency without a demonstrated benefit.