Free DP-800 Practice Test – 20 Questions with Explanations

Two stored procedures deadlock repeatedly during peak order processing. One procedure updates Orders and then Inventory, while the other updates Inventory and then Orders. The deadlock graph shows both sessions holding one key lock and waiting on the other object.
What is the most likely cause?

A finance team stores invoices for several legal entities in one database. Auditors require that ordinary finance users see only rows for their entity, while a dedicated audit role must review all entities without disabling protection for everyone else.

Which design best satisfies the exhibit requirements?

A posting procedure writes to a base table and an audit table in the same transaction. After a recent code review, the team noticed that failures inside the CATCH block could still leave transaction handling incorrect.

Which change is required?

A legal research team is testing filtered vector retrieval for a high-stakes workload where missing relevant documents is worse than accepting moderately higher latency. The filter is highly selective, and the team wants the configuration that best protects recall quality without abandoning indexed search unnecessarily.

Which run is the best fit?

A team needs a graph-native query that returns a path of people 1-3 hops away from a starting person. They want to keep the query in MATCH syntax, support arbitrary-length traversal, and return ordered path output rather than direct projection from path-participating aliases.

Which exhibit row is the only valid design for this requirement?

A platform team is standardizing secure Azure SQL access across hosted services. Their baseline is passwordless connectivity for app-to-database traffic and proper database authorization instead of management-plane assumptions.
Which two actions align best with that baseline? (Select TWO.)

A platform stores billions of transaction rows and rotates data monthly by using a sliding-window strategy. Several ingest tables must share the same generated business number, so IDENTITY on a single table is not sufficient. The team wants the cleanest implementation order with the fewest redefinition steps later.

What is the correct order?

A customer-support application uses a SQL Server view to expose only open service tickets to a support team. The team must be able to update rows through the view, and inserts or updates through that view must not create rows that fall outside the visible filter.
Which two design choices should you use? (Select TWO.)

A database team is standardizing reusable rowset logic for pricing and entitlement checks in SQL Server. They want a function that can be joined with base tables and prefer relational logic that stays optimizer-friendly. They are reviewing design statements before finalizing the pattern.
Which two statements support choosing an inline table-valued function? (Select TWO.)

A checkout process inserts many order lines in one statement when a cart is committed. The team created a trigger to maintain the order subtotal, but some orders end up with understated totals after bulk inserts.

Which change is the best fit?

A company supports multiple feature branches and deploys to Azure SQL Database from a SQL Database Project. The release manager wants one design that catches object-level regressions early, validates the DACPAC against a production-like target before promotion, and avoids platform mismatches slipping through the pipeline.
Which approach is the best overall design?

A team is adopting SQL Database Projects in Visual Studio Code and wants the simplest release flow for updating a target database from source control. They want the sequence that matches the documented project workflow rather than a manual script-first process.

What is the correct order?

A billing team needs one result set that returns the latest invoice per customer, a running account balance over transaction time, and a territory sales rank without collapsing the underlying rows. They want the mappings that most naturally fit window-function-based query design.
Which combination is the best fit?

A platform team wants one deployment design for all environments. They require central rotation of an Azure SQL deployment secret, no secret values in source control, and no YAML edits when an existing secret value changes.

Which design best satisfies the exhibit requirements?

An order-processing team stores each order payload as JSON in an nvarchar(max) column. They need a query that returns one row per line item for downstream joins, while also filtering only orders whose customer tier is a single scalar value inside the payload.
Which approach is the best fit?

A payment application stores account identifiers and government-issued numbers in Azure SQL Database. Database administrators must be able to tune queries and manage backups, but they must not be able to view plaintext values, and the application may later need richer comparisons on protected columns without handing the engine unrestricted key access.
Which encryption design is the best fit?

An architect is reviewing four candidate workloads that each need a specialized table design. The team wants to reserve in-memory technology for the workload that most strongly benefits from high-throughput transactional access and very hot transient data, rather than using it as a generic performance label.

Which workload is the strongest fit for a memory-optimized table?

A team is standardizing search guidance for AI-enabled workloads in SQL database in Microsoft Fabric. They want one mapping set that correctly distinguishes exact search from ANN search and also captures the compatibility rule between vector indexes and query metrics.
Which combination is the best fit?

A team versions a SQL Database Project in Git and needs consistent deployment of small lookup tables such as order statuses and regions. The data changes infrequently, must be reviewable in pull requests, and should deploy repeatedly without creating duplicate rows.

What is the best conclusion?

A support knowledge solution stores article text and embeddings so it can support both exact keyword lookups and semantic retrieval. Users often search with product codes plus natural-language symptoms, and the team wants one final ranked list that rewards documents appearing near the top of both retrieval methods.
Which ranking approach should the team implement?