Sign In
Study Guides/DP-800/Design and Develop Database Solutions
35-40% of exam

Design and Develop Database Solutions

Build tables, indexes, and specialized object types; write advanced T-SQL; and safely bring GitHub Copilot and MCP into the database development workflow.

Design and Develop Database SolutionsSecure, Optimize, and Deploy Database SolutionsImplement AI Capabilities in Database Solutions

Table design: data types, indexes, and specialized table types

Table design is the foundation DP-800 builds everything else on top of — the exam expects you to know not just `CREATE TABLE` basics, but which of SQL Server's several specialized table types fits a given scenario, since picking the wrong one is a common trap in scenario questions.

Constraints and identity patterns

  • `PRIMARY KEY`, `FOREIGN KEY`, `UNIQUE`, `CHECK`, and `DEFAULT` constraints are enforced by the engine at write time, which is why they're preferred over application-level validation for anything that must never be violated regardless of which client writes the row.
  • `SEQUENCE` objects generate numeric values independently of any single table — unlike an `IDENTITY` column, a sequence can be shared across multiple tables, pre-fetched in application code with `NEXT VALUE FOR`, and its increment/cycle behavior configured after creation, which `IDENTITY` doesn't allow.

Columnstore indexes and JSON columns

  • A columnstore index stores data column-by-column rather than row-by-row, giving large analytical scans dramatically better compression and I/O than a traditional rowstore index — the trade-off is that it's optimized for bulk scan/aggregate patterns, not high-frequency single-row lookups or updates.
  • The native `JSON` data type (SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance) stores documents in an optimized binary format instead of plain `nvarchar`, giving faster reads/writes and smaller storage than string-based JSON, with no application code changes needed since the same `JSON_VALUE`/`JSON_QUERY`/`OPENJSON` functions work against it.
  • A `CREATE JSON INDEX` (SQL Server 2025 preview) accelerates `JSON_VALUE` equality/range predicates and the `JSON_CONTAINS` function against a json column, the same way a regular index accelerates a `WHERE` clause on a relational column.

Specialized table types

  • Temporal tables (`PERIOD FOR SYSTEM_TIME`, `SYSTEM_VERSIONING = ON`) automatically keep a full history of every row change in a linked history table, letting you query `FOR SYSTEM_TIME AS OF` a past point without any application-level auditing code.
  • In-memory tables (`MEMORY_OPTIMIZED = ON`) live primarily in memory with optionally durable logging, built for workloads with extreme concurrent insert/update rates where traditional locking becomes the bottleneck.
  • External tables point at data living outside the database (another SQL instance, or a data lake via PolyBase-style connectivity) so it can be queried with normal T-SQL without first importing it.
  • Ledger tables (`LEDGER = ON`) add cryptographically verifiable, tamper-evident history — `APPEND_ONLY = ON` blocks `UPDATE`/`DELETE` entirely at the API level for insert-only audit patterns, while an updatable ledger table (the default once `LEDGER = ON` is set) still allows updates and deletes but chains every change into a verifiable digest.
  • Graph tables (`AS NODE` / `AS EDGE`) model many-to-many relationships natively — covered in depth alongside the `MATCH` operator later in this topic.

Partitioning

  • Table and index partitioning splits data across multiple physical units (a partition function plus a partition scheme) based on a partitioning column, most commonly a date — this speeds up maintenance operations that only touch recent data (like purging old rows via `SWITCH`) and can improve query performance when queries filter on the partitioning column, at the cost of added schema complexity.

Common confusion

  • Ledger tables and temporal tables both keep history, but for different reasons: temporal tables exist so *any* authorized user or application can query past states for business logic (an as-of report), while ledger tables exist specifically to make tampering by a privileged user (a DBA editing rows directly) cryptographically detectable — a ledger table can also be temporal under the hood, but the two features solve different trust problems and aren't interchangeable.

Programmability objects: views, functions, procedures, and triggers

Programmability objects are how you package T-SQL logic into a reusable, permission-able unit — the exam tests whether you know which object type fits a given need, since all four can sometimes achieve a similar-looking result.

Views

  • A view is a saved `SELECT` statement queried like a table — useful for hiding join complexity from consumers, presenting a restricted column set without granting table-level access, or maintaining a stable interface while the underlying schema evolves.
  • `WITH SCHEMABINDING` locks the view's definition to its base tables' current schema, preventing a column the view depends on from being dropped or changed underneath it — required if the view will itself be indexed.

Scalar and table-valued functions

  • A scalar function returns a single value and can be called inline in a `SELECT` list or `WHERE` clause, but a scalar function that isn't inlineable by the optimizer can silently force row-by-row execution across a large result set — a well-known performance trap.
  • An inline table-valued function (iTVF) returns a table from a single `RETURN (SELECT ...)` statement and *is* inlined into the calling query's plan, making it the generally preferred pattern over a scalar function when a per-row computation can be reshaped as a set-based one — this is exactly the technique used to write a row-level security predicate function.
  • A multi-statement table-valued function builds its result set with multiple statements into a declared `@table` variable — more flexible, but not inlined by the optimizer the way an iTVF is.

Stored procedures

  • Stored procedures accept input/output parameters, can contain multiple statements and control-of-flow logic, and are the natural home for a unit of work an application calls as a single round trip (an order-placement transaction, a batch update) rather than a value the optimizer needs to reason about inline.
  • Because a stored procedure's plan is cached and reused, parameterized procedures avoid the ad hoc query plan-cache bloat that comes from sending the same shape of query as a literal string over and over.

Triggers

  • `AFTER` (or `FOR`) triggers fire once the triggering `INSERT`/`UPDATE`/`DELETE` has completed, and are the classic mechanism for cascading side effects (an audit log write, a denormalized summary update) that must happen atomically with the original change.
  • `INSTEAD OF` triggers replace the triggering statement entirely — the standard technique for making an otherwise non-updatable view (one spanning multiple base tables) accept `INSERT`/`UPDATE`/`DELETE` statements by translating them into operations against the correct underlying tables.

Common confusion

  • A view and an inline table-valued function look similar (both wrap a `SELECT` and both get inlined into the caller's plan), but only a function can take parameters — a view's filtering logic has to live in the caller's `WHERE` clause, while a function can encapsulate parameterized logic (like a security predicate that takes a user ID) that a view structurally can't express on its own.

Modern T-SQL: CTEs, window functions, correlated queries, and error handling

Beyond basic joins and aggregates, DP-800 expects fluency with the T-SQL constructs that solve problems a plain `GROUP BY` can't — hierarchical data, running calculations, and row-by-row comparisons against related rows.

Common table expressions

  • A CTE (`WITH cte_name AS (...)`) is a named, scoped result set that can be referenced multiple times in the statement that follows it — mainly a readability tool for breaking a complex query into named steps, functionally similar to a derived subquery.
  • A *recursive* CTE (a base case `UNION ALL`'d with a recursive member referencing the CTE itself) is the standard way to walk a hierarchy — an org chart, a bill-of-materials tree, or a category tree with arbitrary depth — without knowing the depth in advance.

Window functions

  • Window functions (`OVER (PARTITION BY ... ORDER BY ...)`) compute a value across a set of rows related to the current row *without* collapsing them into one row the way `GROUP BY` does — a running total, a rank within a group, or a row's value compared to the previous row all stay at the original row grain.
  • `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` differ specifically in how they handle ties: `ROW_NUMBER()` always assigns unique sequential numbers even to tied rows, `RANK()` gives tied rows the same rank and then skips the next rank(s), and `DENSE_RANK()` gives tied rows the same rank with no gap afterward.
  • `LAG()`/`LEAD()` read a value from a preceding/following row in the same result set (commonly used for period-over-period comparisons), while framing clauses like `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` control exactly which rows a running aggregate accumulates over.

Correlated subqueries

  • A correlated subquery references a column from the outer query, so it's logically re-evaluated once per outer row (find each customer's most recent order, or each product's price versus its category average) — semantically distinct from an uncorrelated subquery, which is evaluated once regardless of the outer row.
  • Many correlated subqueries can be rewritten as a join or a window function, and the optimizer often produces a better plan from the rewritten form — worth checking when a correlated subquery shows up as a performance bottleneck in a query plan.

Error handling

  • `TRY...CATCH` wraps a block of T-SQL so that a runtime error transfers control to the `CATCH` block instead of aborting the batch outright — `ERROR_NUMBER()`, `ERROR_MESSAGE()`, `ERROR_LINE()`, and `ERROR_PROCEDURE()` inside the `CATCH` block describe exactly what failed.
  • Inside a transaction, a `CATCH` block typically checks `XACT_STATE()` before deciding to `COMMIT` or `ROLLBACK` — a value of `-1` means the transaction is in an uncommittable state and must be rolled back, while `1` means it can still be committed or rolled back at the caller's discretion.

Common confusion

  • A window function and a `GROUP BY` aggregate can compute the same underlying math (a sum, a count) but return fundamentally different shapes: `GROUP BY` collapses the result to one row per group, while a window function keeps every original row and attaches the computed value alongside it — needing both a detail row and an aggregate value side by side is exactly when a window function is the right tool and `GROUP BY` alone isn't.

Pattern matching at scale: JSON, regular expressions, fuzzy matching, and graph queries

SQL Server 2025 (and the equivalent Azure SQL / Fabric SQL database surfaces) added a cluster of pattern-matching functions the exam calls out by name — these let you do text and structural matching natively in T-SQL instead of round-tripping data to application code.

JSON construction and search functions

  • `JSON_OBJECT('name':value, ...)` and `JSON_ARRAY(value, ...)` build JSON text directly from SQL expressions, and `JSON_OBJECTAGG`/`JSON_ARRAYAGG` do the aggregate equivalent — turning a whole result set's rows into one JSON object or array, which is exactly the shape an LLM prompt or a REST response usually needs.
  • `OPENJSON(json_expression [, path]) WITH (...)` is still the standard way to shred a JSON document back into relational rows and columns, and `JSON_VALUE`/`JSON_QUERY` still extract a scalar or an object/array from JSON text respectively.
  • `JSON_CONTAINS(target, search_value [, path])` (SQL Server 2025 preview) tests whether a value, object, or array is present anywhere inside a JSON document or at a specific path, returning `1`/`0`/`NULL` — a purpose-built alternative to writing an `OPENJSON` cross-apply just to check for containment, and one that a JSON index can accelerate.

Regular expression functions

  • `REGEXP_LIKE(string_expression, pattern [, flags])` returns a boolean and is the direct regex replacement for a `LIKE` predicate that needs more power than `%` and `_` wildcards provide — it requires database compatibility level 170 or higher.
  • `REGEXP_REPLACE`, `REGEXP_SUBSTR`, `REGEXP_INSTR`, and `REGEXP_COUNT` mirror familiar regex operations (replace, extract, locate, count matches); `REGEXP_MATCHES` and `REGEXP_SPLIT_TO_TABLE` are table-valued, returning one row per captured match or per split segment respectively.
  • Flags (`i` case-insensitive, `m` multi-line, `s` dot-matches-newline, `c` case-sensitive/default) are passed as a short string argument, and `REGEXP_REPLACE` also lets you target the *n*th occurrence rather than replacing every match.
  • Regex-based dynamic data masking (`MASKED WITH (FUNCTION = 'REGEXP_REPLACE("<pattern>", "<replacement>")')`, Azure SQL Database preview) applies the same pattern-matching engine to obscure part of a column's value — for example masking all but a phone number's country code — rather than only the coarser `default()`/`partial()`/`email()`/`random()` masks.

Fuzzy string matching

  • `EDIT_DISTANCE(str1, str2 [, maximum_distance])` implements Damerau-Levenshtein distance — the number of insertions, deletions, and substitutions needed to turn one string into another — and `EDIT_DISTANCE_SIMILARITY` expresses the same comparison as a normalized 0-100 score instead of a raw count.
  • `JARO_WINKLER_DISTANCE`/`JARO_WINKLER_SIMILARITY` implement a different algorithm that gives extra weight to strings that match from the beginning — generally a better fit than edit distance for things like matching names or product codes where a shared prefix should count for more.
  • These functions are preview features that require enabling `PREVIEW_FEATURES` via `ALTER DATABASE SCOPED CONFIGURATION`, and only support Windows or binary (`BIN`/`BIN2`) collations — a database using a non-binary `SQL_*` collation needs an explicit `COLLATE` clause on the arguments before the functions will accept them.

Graph queries with MATCH

  • `CREATE TABLE ... AS NODE` and `AS EDGE` create graph tables; an edge constraint (`CONSTRAINT ... CONNECTION (NodeA TO NodeB)`) restricts which node types an edge can legally connect, the graph equivalent of a foreign key.
  • The `MATCH` clause uses ASCII-art syntax to express a traversal — `MATCH(Person1-(friendOf)->Person2)` finds direct connections, and chaining the pattern (`Person1-(friendOf)->Person2-(friendOf)->Person3`) finds friends-of-friends without a self-join for every hop.
  • `SHORTEST_PATH` (with a quantifier like `{1,3}`) finds the shortest route between nodes across a variable number of hops — the tool for "how are these two entities connected" questions that a fixed-depth `MATCH` pattern can't answer.

Common confusion

  • `REGEXP_LIKE` and the classic `LIKE` operator both test a string against a pattern, but `LIKE`'s wildcard vocabulary (`%`, `_`, `[...]`) is far more limited than full regular expression syntax (alternation, quantifiers, character classes, capture groups) — reaching for `REGEXP_LIKE` only when `LIKE` genuinely can't express the pattern keeps queries both correct and easier to optimize, since a plain `LIKE` with a leading literal can still use an index seek in ways a regex generally can't.

AI-assisted SQL development: GitHub Copilot, Copilot in Fabric, and MCP

DP-800 is the first Microsoft database exam to test AI-assisted *development* workflow directly — not just AI features inside the database, but how you safely bring an AI coding assistant into the SQL authoring process itself.

Enabling and configuring Copilot for SQL work

  • GitHub Copilot integrates into SQL tooling (SSMS's AI Assistance workload, the MSSQL extension for VS Code, and Copilot in Microsoft Fabric) to generate T-SQL, explain existing queries, and suggest schema changes from natural-language prompts — Agent mode specifically is what's required to use MCP tools, since plain Ask mode doesn't support them.
  • Model and tool options are configured per Copilot chat session — which underlying model answers a prompt, and which MCP tools (if any) are enabled for that session — separate from whether Copilot itself is licensed and turned on for the organization.
  • An organization's GitHub Copilot administrator can turn off Agent mode/MCP access tenant-wide, and can maintain an MCP server allow list restricting exactly which servers are permitted to connect — a governance layer worth knowing exists independently of any individual developer's local configuration.

GitHub Copilot instruction files

  • An instruction file (a `.github/copilot-instructions.md`-style file checked into the repository) gives Copilot durable, project-specific context — coding conventions, naming standards, which schema patterns to prefer — so every suggestion in that repository follows the same guidance without the developer re-explaining it in every prompt.
  • Because instruction files are just files under source control, they go through the same pull-request review as any other code change, which is what makes "the AI assistant's standing instructions for this project" itself an auditable, versioned artifact rather than personal, undocumented tribal knowledge.

Connecting to MCP server endpoints

  • The Model Context Protocol (MCP) is the open standard that lets an AI coding assistant call external tools through a client-server model: the assistant (an MCP *client*) requests actions, and an MCP *server* exposes a well-defined set of tools it's allowed to perform.
  • A SQL Server or Azure SQL MCP server exposes tools like listing schemas/tables/views/functions and running an approved query, letting Copilot answer "what tables are in this database" or generate and execute T-SQL directly against live schema context instead of guessing at column names.
  • A Fabric lakehouse or Fabric Data Warehouse MCP server does the analogous thing for Fabric items — the SQL analytics endpoint's server, for example, exposes a single `executeSQL` tool that runs an approved T-SQL statement and returns the result, deliberately not exposing a separate metadata-browsing tool (schema questions are instead answered by having the agent query `INFORMATION_SCHEMA` through that same tool).
  • MCP tools are disabled by default the moment a server is added — each tool has to be explicitly enabled in the Tools list before Copilot can invoke it, and a destructive or sensitive tool call still prompts for explicit approval before it runs.

Security implications of AI-assisted tooling

  • Because an MCP-connected Copilot session can execute real T-SQL against a real database, the account or connection profile it uses should carry the least privilege that still lets it do its job — an assistant wired up with elevated (say, `db_owner`) credentials turns a mistaken or misinterpreted natural-language prompt into a real schema or data change.
  • Reviewing AI-suggested T-SQL before running it matters more, not less, once MCP is involved, since a suggestion is no longer just inert text in a chat window — approving a tool call is the point where a hallucinated or overly broad statement (an unfiltered `DELETE`, an unintended schema change) actually executes.

Common confusion

  • Enabling GitHub Copilot for a SQL project and connecting it to an MCP server are two separate steps that are easy to conflate: Copilot alone can generate T-SQL text based on what's visible in your editor and instruction files, but it can't see live database schema or execute anything until an MCP server is added, its tools are individually enabled, and (in Agent mode) each tool call is approved.
Back to DP-800 OverviewPractice Questions