DP-800: Hands-On Skills to Practice

DP-800 is a developer exam, so hands-on work should prove that you can build, diagnose, secure, and evolve a Microsoft SQL solution with AI capabilities. A useful lab does not need to be huge. It needs enough moving parts to make you choose between alternatives and observe what happens when a design is wrong.

Use the DP-800 exam objectives as a lab checklist. Microsoft has published a minor skills refresh effective October 19, 2026, so confirm the live guide before final review and update any exercise whose exact feature wording changed.

Design tables for two different workloads

Create one transactional table optimized for selective reads and updates and one analytical pattern that benefits from column-oriented processing. Choose data types, keys, constraints, and indexes deliberately. Load enough rows to make the access pattern visible.

Then change the requirement: add history, semi-structured attributes, or time-based retention. Decide whether temporal behavior, JSON, or partitioning improves the design.

Build an advanced T-SQL practice set

Write queries using CTEs, window functions, JSON operations, pattern or fuzzy functions where available, correlated logic, graph constructs if supported, and explicit error handling.

Add edge cases such as nulls, duplicates, malformed data, and empty result sets. Do not stop when the query runs; explain why it is correct.

Use an AI assistant on SQL you understand

Ask GitHub Copilot or Copilot in Fabric to draft, explain, or refactor a query. Review the result for semantics, permissions, performance, and assumptions. Ask for test cases and counterexamples.

The point is to learn where AI assistance accelerates work and where database expertise remains the final review layer.

Configure a narrow tool-enabled database scenario

Where supported, connect an AI-assisted development environment to a SQL or lakehouse endpoint through an MCP-capable path. Begin with read-only metadata or a tightly scoped query operation.

The tool-use fundamentals apply: the model can request an operation, but server-side policy should control what the identity is actually permitted to do.

Implement several security controls

Practice row-level security, object permissions, masking, encryption choices, auditing, and passwordless or managed-identity access where available. Use two identities with intentionally different permissions.

Write down the expected denial and confirm it. Security becomes easier to reason about when a failed access is expected evidence rather than a surprise.

Create and diagnose a performance problem

Build or use a deliberately inefficient query. Inspect its plan and relevant performance evidence, then change one material factor and measure again. Introduce blocking or a concurrency issue in a safe lab and practice diagnosing it.

Do not default to adding indexes. Look for poor estimates, non-sargable predicates, excessive reads, contention, or transaction behavior before choosing a fix.

Move a schema through a SQL Database Project

Put objects under source control, build and validate the project, add tests, create a branch, open a pull request, handle one conflict, and deploy to another environment. Include reference data and a controlled schema change.

The delivery pattern in CI/CD fundamentals becomes more concrete when you see how stateful database changes differ from replacing an application binary.

Expose one capability through an API layer

Configure a REST or GraphQL-style access path with pagination, search, or filtering. Map a database object or stored procedure and verify that authorization works as expected.

Then change one underlying database object and test whether the API remains compatible. This connects database evolution to application contract design.

Build an embedding-maintenance workflow

Choose fields worth embedding, create chunks if necessary, generate vectors through the supported model path, and store them. Then update the source record and refresh its embedding using one of the change-detection or workflow patterns in the Microsoft ecosystem.

The important lesson is synchronization. An old embedding attached to a new row is a retrieval defect even if the vector search itself works perfectly.

Compare lexical, vector, and hybrid search

Build a small corpus with exact codes, synonyms, and conceptually similar descriptions. Run the same questions through full-text search, semantic vector search, and a hybrid method.

Use embeddings, vector databases, and RAG as conceptual background, then measure retrieval quality inside the SQL platform rather than assuming one method is superior.

Test nearest-neighbor trade-offs

Compare the exact and approximate nearest-neighbor options supported by the current SQL feature set. Measure latency and retrieval quality on representative queries, and note how vector index choices affect updates as well as reads.

Microsoft’s terminology can evolve as the feature set changes, so recheck the live guide before exam day rather than memorizing one preview-era label.

Build a hybrid-ranking exercise

Combine lexical and semantic result sets using the ranking method supported by the current platform. Create questions where exact terms should dominate and others where semantic similarity matters more.

Inspect several ranked results, not only the first result. Search quality is about the evidence available to the next stage, not one lucky top hit.

Finish with a SQL-centered RAG lab

Retrieve the relevant database content, package it for model processing, create a prompt, send the evidence, and extract the response. Include a request with no supporting data and define the expected behavior.

Use AI evaluation fundamentals to measure retrieval and generation separately so a wrong answer leads to the right fix.

Practice one security failure and one performance failure back to back

Configure a request that is denied correctly by policy, then a separate request that is allowed but slow. Compare the evidence. The first should point toward identity or authorization; the second toward execution or concurrency. This trains you not to treat every database problem as a generic connectivity issue.

Write down the diagnostic sequence you used for each one. On an exam scenario, that sequence helps you choose the tool or action that actually addresses the stated symptom.

Rebuild one lab after deleting your notes

Once you can complete a guided exercise, remove the step-by-step notes and rebuild it from a short requirement. Use official documentation only when you need to verify a command or current feature detail.

The second build reveals whether you understand the architecture or were following instructions mechanically. Record the decisions that required the most thought and add them to your revision list.

Measure before and after every tuning exercise

Performance labs should have a baseline. Record duration, reads, plan shape, wait or blocking evidence, and any relevant resource metrics before making a change. Then measure again.

This prevents “tuning” by superstition. A change is useful because the evidence improved without causing unacceptable trade-offs, not because it resembles a common recommendation.

Use a final capstone with a deliberate defect

Build your integrated SQL-plus-AI lab with one intentionally bad choice: stale embeddings, excessive permissions, a missing index, weak retrieval filters, or an unsafe deployment step. Then diagnose and repair it.

The capstone becomes more realistic when something is wrong. It also gives you a final opportunity to connect data design, operations, security, delivery, and AI behavior before exam day.

Practice endpoint security with an intentional misconfiguration

Expose a lab API or model endpoint with a deliberately excessive permission or missing restriction, then repair it. Confirm the difference using two identities or scopes. This makes endpoint security tangible and trains you to notice when an architecture diagram grants more access than the use case requires.

Document which control actually fixed the problem. Avoid relying on the application prompt to compensate for an authorization mistake.

Use a branch-and-merge exercise for database changes

Create two branches that modify related database objects, then merge them and resolve the conflict. Build the project after the merge and verify that the resulting model is internally consistent. Database source control becomes easier to understand when you have seen a conflict rather than only read about one.

Add a pull-request review note explaining the schema risk, expected deployment effect, and rollback consideration. This ties Git practice to the operating concerns DP-800 actually tests.

Capture one observability signal for every major component

For the database, use query and performance evidence. For APIs, capture request status and latency. For model calls, record safe usage and error metadata. For retrieval, keep result identifiers and ranking evidence. A lab becomes far more useful when you can explain how you would diagnose it after deployment.

This also reinforces that AI-enabled database solutions are normal production systems with logs, metrics, dependencies, and failure modes.

Repeat the hardest lab with a different dataset

Once an exercise works, change the data shape or workload instead of repeating the same steps. Use different cardinality, text content, permission scope, or query pattern and observe which design choices still hold.

This makes the practice transferable. The exam will not reproduce your lab exactly, so the goal is to learn the engineering rule behind the successful configuration.

Keep a lab notebook of decisions and evidence

Record the requirement, chosen feature, one rejected alternative, identity, test evidence, and one failure for each exercise. This becomes a compact revision tool based on your own engineering decisions.

If the notebook is mostly screenshots of menus, redo the explanation. DP-800 expects you to recognize the design even when no interface is shown.