DP-800 Practice Questions — Page 1

Question 1

A catalog table stores flexible product attributes such as dimensions and regional labels. The reporting team must query common attributes efficiently.

Which design should you recommend?

A.Store frequently queried attributes in relational columns and reserve JSON for less-structured fields.
B.Move every attribute into a single nvarchar(max) field.
C.Avoid indexing because attributes might change later.
D.Store prices only in application memory.

Question 2

Open question ↗

A fact table stores ten years of monthly sales data. The business retains only the last 36 months in the primary database, and old partitions must be archived with minimal downtime.

DRAG DROP - Arrange the actions in the correct order.

```mermaid

flowchart LR

S1[Create a partition function and partition scheme]

S2[Align the fact table to the partition scheme]

S1 --> S2

S3[Load current data into the correct partitions]

S2 --> S3

S4[Switch out and archive old partitions as part of the retention process]

S3 --> S4

```

Question 3

Open question ↗

A logistics company needs to enforce valid shipment states and prevent orphaned delivery records.

Which table design should you recommend?

A.Use primary keys, foreign keys, and check constraints to enforce valid relationships and states.
B.Store all deliveries in a free-form notes column.
C.Allow null foreign keys so inserts always succeed.
D.Duplicate shipment rows across multiple tables without constraints.

Question 4

Open question ↗

Multiple tables need values from a shared numbering sequence for externally visible document identifiers.

Which feature should you recommend?

A.A SEQUENCE object
B.A temporary table
C.A DDL trigger
D.An unindexed GUID text column managed manually

Question 5

Open question ↗

You are designing an Azure SQL Database solution for a retail company. The database must store orders, order lines, customers, and product catalogs. Finance reports depend on accurate historical totals, and the development team wants to minimize data anomalies when orders are updated.

Which table design should you recommend?

A.Store customer address details in the Orders table so each order record is fully self-contained.
B.Normalize orders, customers, and products into separate tables and enforce relationships by using primary keys and foreign keys.
C.Store all order information as JSON documents in a single table to simplify application code.
D.Duplicate product price and tax rules in every order row and update them by using scheduled batch jobs.

Question 6

Open question ↗

You manage an Azure SQL Database that supports a busy ecommerce site. Queries frequently filter by `OrderDate` and `CustomerId`, while nightly reports aggregate totals by month. Insert performance must remain acceptable during business hours.

Which two actions should you recommend? Each correct answer presents part of the solution.

A.Create a clustered index aligned to the main lookup pattern and add nonclustered indexes for common selective predicates.
B.Add a separate heap copy of the Orders table for reporting queries.
C.Review index maintenance overhead and avoid creating unused overlapping indexes.
D.Replace relational indexes with JSON payload storage for all order attributes.

Question 7

Open question ↗

You must store the full change history for customer credit limits and account status. Auditors need to reconstruct values as they existed on any day in the past, and the solution must avoid custom history code in the application layer.

HOTSPOT - Select the best option for each requirement.

```mermaid

flowchart TD

A1[Preserve row versions automatically] --> B1[System-versioned temporal tables]

A2[Reconstruct values at a point in time] --> B2[FOR SYSTEM_TIME queries]

```

Question 8

Open question ↗

A finance application calls a stored procedure that posts invoice payments. The procedure must validate input, update multiple tables atomically, and return a consistent error message if validation fails.

Which implementation should you recommend?

A.Use a stored procedure with TRY...CATCH, explicit transactions, and THROW for error handling.
B.Generate ad hoc UPDATE statements in the application for each table.
C.Use a trigger on every table to infer the payment workflow.
D.Store the invoice workflow in JSON and process it asynchronously without validation.

Question 9

Open question ↗

A maintenance operation processes large batches and must be restartable if it fails midway.

Which design should you recommend?

A.Break the operation into modular programmable units with clear transaction boundaries.
B.Execute one unbounded transaction for the entire weekend.
C.Disable logging during the run.
D.Run multiple conflicting scripts manually at the same time.

Question 10

Open question ↗

A nightly data load writes audit rows whenever key tables change. The audit logic must capture who changed the row and when, but it must not create uncontrolled recursion.

Which approach should you recommend?

A.Use a carefully scoped DML trigger and validate recursion settings.
B.Use a random delay inside the application.
C.Drop the primary key before auditing.
D.Avoid all auditing because it affects performance.