DP-800 design and develop explained

Design and develop database solutions is the joint-largest DP-800 domain at 35–40%. It covers four areas: designing database objects, building programmability objects, writing advanced T-SQL, and using AI-assisted tools such as GitHub Copilot and MCP servers. Most questions show a schema or a query and ask what it does, what is wrong with it, or which feature fits a requirement.

Design and implement database objects

Tables, indexes and columnstore

Choose the smallest data type that fits the data, because width affects index size, memory and I/O. A rowstore clustered index suits transactional lookups; a columnstore index suits large scans and aggregates and compresses well. A nonclustered columnstore index on a transactional table gives you analytics on live data.

Specialized tables

Table type	Use it when
Memory-optimized	You need very high concurrency and low latency on hot data
System-versioned temporal	You need to query data as it was at a past time
Ledger	You need tamper-evidence, so changes are cryptographically verifiable
Graph (node and edge)	The data is many-to-many relationships traversed with `MATCH`
External	You query data that lives outside the database

Temporal tables keep history automatically in a history table; query it with FOR SYSTEM_TIME. Ledger tables detect tampering; they do not prevent a privileged user from trying.

JSON, constraints, sequences and partitioning

JSON can be stored in a column and indexed so that queries on a path are efficient. Constraints enforce integrity in the engine: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and DEFAULT. A sequence is a schema-level number generator shared across tables and fetched with NEXT VALUE FOR; unlike an identity column it is not tied to one table. Partitioning splits a large table or index by a partition function and scheme, which helps with maintenance such as switching out old data; it is not a guaranteed query speed-up.

Implement programmability objects

Object	Key point
View	Stored query; can hide columns and joins; indexed views materialize results
Scalar function	Returns one value; can hurt performance if called per row
Table-valued function	Inline versions behave like parameterised views and optimize well
Stored procedure	Encapsulates logic, permissions and parameters; supports error handling
Trigger	Runs on data changes; keep it short and never wait on external calls

Prefer inline table-valued functions over multi-statement ones. Triggers fire once per statement, not per row, so they must handle multi-row inserted and deleted tables.

Write advanced T-SQL

CTEs structure a query and allow recursion, for example walking an organisation tree.
Window functions (ROW_NUMBER, RANK, LAG, running totals with OVER) compute across related rows without collapsing them.
JSON functions: OPENJSON turns an array into rows; JSON_VALUE extracts a scalar; JSON_OBJECT, JSON_ARRAY and JSON_ARRAYAGG build JSON; JSON_CONTAINS tests for a value.
Regular expressions: REGEXP_LIKE filters, REGEXP_SUBSTR extracts, REGEXP_REPLACE rewrites, and REGEXP_COUNT, REGEXP_INSTR, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE cover counting, positions and set-returning use. Check availability on your platform.
Fuzzy matching: EDIT_DISTANCE counts edits between strings, and the similarity variant and JARO_WINKLER_DISTANCE help match names with typos.
Graph queries: MATCH in the WHERE clause traverses node and edge tables.
Correlated queries reference the outer query; often a join or window function does the same work faster.
Error handling: TRY...CATCH, THROW, and SET XACT_ABORT ON so that a runtime error rolls back the transaction.

AI-assisted tools

The outline asks you to interpret the security impact of AI-assisted tools: prompts may contain schema, code or data, so never paste secrets or production data, review generated code before it runs, and give any MCP connection a least-privilege login. You should also know how to enable GitHub Copilot and Copilot in Fabric, choose model and MCP tool options in a chat session, write Copilot instruction files that tell the assistant your conventions, and connect to MCP endpoints for SQL Server and a Fabric lakehouse.

Sample questions

Question 1. A table of support tickets needs to answer 'what did this ticket look like last Tuesday at 10:00?' without custom audit code. Which feature should you use?

A. A system-versioned temporal table
B. A ledger table
C. A memory-optimized table
D. A graph edge table

Show answer

Answer: A

A system-versioned temporal table keeps old row versions in a history table automatically and can be queried as of a point in time. A ledger table proves tampering did not happen but is not designed for as-of querying. A memory-optimized table improves concurrency, not history. A graph edge table models relationships, not row history.

Want more questions like this? Full DP-800 practice tests →

Question 2. You must find rows in a free-text column where the text contains an order reference such as ORD-2026-00457, and return the reference itself in a separate column. Which T-SQL feature is the best fit?

A. EDIT_DISTANCE against a sample reference
B. OPENJSON over the column
C. A scalar function that uses CHARINDEX
D. REGEXP_SUBSTR with a pattern for the reference

Show answer

Answer: D

REGEXP_SUBSTR returns the part of the string that matches a regular expression, which is what you need to extract a reference with a fixed pattern. EDIT_DISTANCE compares two strings and returns a number of edits, so it does not extract anything. OPENJSON works only on JSON text. A scalar function that uses CHARINDEX would need the reference position to be known, which it is not in free text.

Want more questions like this? Full DP-800 practice tests →

Question 3. An AFTER INSERT trigger on an Orders table updates a Customers row with the latest order date using a variable assigned from the inserted table. After a bulk insert of 500 orders for many customers, only some customers are updated correctly. What is the most likely cause?

A. Triggers do not fire for bulk inserts
B. The trigger assumes a single row but the inserted table holds many rows
C. A CHECK constraint is rejecting some of the rows
D. The Customers table lacks a foreign key

Show answer

Answer: B

A trigger fires once per statement, and the inserted table can contain many rows. Assigning from inserted to a scalar variable picks one arbitrary row, so most rows are missed. The fix is a set-based UPDATE joined to inserted. Triggers do fire for bulk inserts unless disabled, a CHECK constraint failure would fail the statement, and a missing foreign key does not cause partial updates.

Want more questions like this? Full DP-800 practice tests →