Domain 1: Design and develop database solutions (35-40%)
Why This Domain Matters
This is one of the two highest-weighted sections. The exam expects you to be strong at traditional SQL engineering first, then layer in AI-assisted development without compromising security or maintainability.
1.1 Design and implement database objects
Table design signals
| Topic | What to know for the exam |
|---|---|
| Data types and sizing | Pick the smallest correct type, especially for keys, money, and text-heavy columns. |
| Indexes | Understand clustered vs nonclustered, filtered indexes, included columns, and when columnstore fits analytics workloads. |
| JSON columns and indexes | Know when semi-structured payloads belong in JSON columns, how JSON path access affects query design, and when indexing JSON access paths improves retrieval. |
| Specialized tables | Know when to use in-memory, temporal, external, ledger, and graph tables. |
| Constraints | Expect PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT questions tied to integrity. |
| SEQUENCES | Use when you need reusable number generation across objects or more control than IDENTITY. |
| Partitioning | Use for large tables with predictable access patterns and maintenance needs. |
Specialized table quick map
| Feature | Best for | Common exam signal |
|---|---|---|
| Temporal tables | Row history and point-in-time queries | "Need audit history of changes" |
| Ledger tables | Tamper-evident records | "Need cryptographic proof of integrity" |
| Graph tables | Relationship-heavy patterns | "Many-to-many traversal" or "social/network traversal" |
| External tables | Querying outside core storage | "Data lives in external source or lakehouse" |
| In-memory OLTP | Extreme low-latency transactional workloads | "Hot path writes" or "reduce latch contention" |
Exam Trap
If the requirement is simply "keep historical versions", think temporal first.
If the requirement is "prove nobody tampered with records", think ledger.
JSON columns and indexes
Microsoft explicitly lists JSON columns and indexes in this domain.
| Need | Direction |
|---|---|
| Store semi-structured app payloads without fully normalizing first | Use a JSON column |
| Query specific properties repeatedly | Extract predictable paths and support them with indexing strategy |
| Return model-ready payloads or API-shaped output | Combine relational data with JSON shaping functions |
TIP
If the scenario mixes relational rows with evolving application metadata, JSON columns are often the bridge. The exam is less about "JSON instead of SQL" and more about SQL plus JSON where it improves flexibility.
SEQUENCE vs IDENTITY
| Need | Prefer |
|---|---|
| Auto-number tied to one table insert path | IDENTITY |
| Reusable number generation across multiple tables or processes | SEQUENCE |
| Need to request the next value before insert logic completes | SEQUENCE |
SEQUENCE is an explicit exam skill. Expect questions that test whether you need a table-bound incrementing key or a broader reusable numbering mechanism.
1.2 Implement programmability objects
- Views are for abstraction, reuse, and controlled data exposure.
- Scalar functions are simple but can hurt performance if overused row-by-row.
- Table-valued functions are often better for composability inside queries.
- Stored procedures are still the default for operational logic, parameterization, and controlled execution.
- Triggers are powerful but easy to abuse. Prefer them for precise event-driven database behavior, not broad business workflows.
Stored procedure vs trigger
| Need | Prefer |
|---|---|
| Explicitly called data operation | Stored procedure |
| Automatically react to insert/update/delete | Trigger |
| Expose consistent app-facing write API | Stored procedure |
| Capture every row change event at source | Trigger |
Programmability object distinctions
Microsoft’s wording here is implementation-oriented, so expect questions that ask which object to create and manage, not just what each object means.
| Object | Best fit |
|---|---|
| View | Abstraction, controlled projection, simplified read surface |
| Function | Reusable calculation or table-returning logic inside queries |
| Stored procedure | Controlled operational entry point, parameterized write/read workflow |
| Trigger | Automatic database-side reaction to DML events |
1.3 Write advanced T-SQL code
Core patterns to know
- CTEs for readable staged logic, recursive queries, and query decomposition.
- Window functions such as
ROW_NUMBER,RANK,LAG,LEAD, and running aggregates. - JSON functions for shaping and parsing semi-structured data.
- Regular expressions and fuzzy matching for text cleanup, search, and approximate matching.
- Graph queries with
MATCHfor node-edge traversal scenarios. - Correlated queries when outer-row context changes inner evaluation.
- Error handling with
TRY...CATCH, transaction boundaries, and defensive logic.
JSON mental model
| Task | Typical SQL feature |
|---|---|
| Extract one value from JSON | JSON_VALUE |
| Extract object/array fragments | JSON_QUERY |
| Turn JSON into rows | OPENJSON |
| Build JSON output | JSON_OBJECT, JSON_ARRAY, aggregates |
Other T-SQL patterns Microsoft is clearly signaling
- Correlated subqueries when each outer row affects inner execution
- Window framing and ranking for ordered analytics patterns
- Regular expressions for matching and validation scenarios
- Fuzzy functions when exact string matching is too strict
- Graph traversal for node/edge relationships
- Error handling to keep database code predictable under failure
TIP
DP-800 repeatedly blends structured and semi-structured data. If a scenario mentions application payloads, model prompts, or API responses, expect JSON shaping to matter.
1.4 Design and implement SQL solutions by using AI-assisted tools
This is one of the most distinctive parts of DP-800.
What Microsoft expects here
- Enable and use GitHub Copilot or Copilot in Fabric
- Understand the security implications of AI-assisted tooling
- Configure model options and MCP tools
- Create instruction files to shape Copilot behavior
- Connect to MCP endpoints such as SQL Server or Fabric lakehouse
Coverage note
This objective is not only about generating SQL faster. Microsoft also expects you to understand:
- how Copilot behavior is shaped by instruction files
- how MCP servers and tools improve context quality
- how model choice and tool access affect security and accuracy
- why generated changes still need review, testing, and least privilege
Practical interpretation
| Capability | Why it matters |
|---|---|
| Instruction files | Keep generated SQL aligned with naming, migration, testing, and safety rules. |
| MCP connectivity | Lets the assistant use live schema/tool context instead of guessing. |
| Model/tool options | Affect latency, capability, and risk surface. |
| Security review | AI-generated SQL can still leak data, over-permission access, or suggest unsafe changes. |
AI-Assisted Tooling Trap
The correct answer is rarely "let Copilot decide and run unrestricted changes."
Microsoft explicitly expects you to reason about security impact, guardrails, and reviewability.
Good operating model
- Use Copilot for acceleration, not blind execution.
- Keep schema definitions and reference data in source control.
- Use instruction files to constrain code generation.
- Prefer least-privilege access for tools and MCP endpoints.
- Review generated SQL for correctness, locking behavior, and security.
Fast Recall
- Temporal = history
- Ledger = tamper evidence
- Columnstore = analytics/compression
- Window functions = ranking, offsets, running totals
- JSON = bridge between app payloads and SQL
- Instruction files + MCP = safer, context-aware AI-assisted SQL work