DP-800 Cheat Sheet: Quick Reference for the SQL AI Developer Associate Exam
All three domains condensed to reference tables, plus decision tables for the security, vector-search, and change-handling features candidates mix up most.
Quick Facts
On duration, question count, and price: Microsoft's general policy for Associate-level role-based exams without labs is roughly 100 minutes, and most Microsoft Certification exams run 40-60 questions at a typical Associate price of $165 USD — but none of that is stated on DP-800's own pages, so treat it as a baseline, not a confirmed figure.
1. Design and Develop Database Solutions (35-40%)
| Concept | Area | Quick note |
|---|---|---|
| Column store indexes | Table design | Optimizes large analytical tables for aggregation-heavy queries |
| In-memory, temporal, external, ledger, graph tables | Specialized tables | Each solves a different need: speed, history, external data, tamper-evidence, relationships |
| JSON columns & indexes | Semi-structured data | Native JSON storage without a separate document database |
| PRIMARY KEY / FOREIGN KEY / UNIQUE / CHECK / DEFAULT | Constraints | Enforce data integrity at the table level |
| SEQUENCES | Object type | Number generation independent of any one table, unlike IDENTITY |
| Partitioning | Table & index design | Splits large tables/indexes for manageability and performance |
| Views, scalar functions, table-valued functions | Programmability | Encapsulate and reuse query logic |
| Stored procedures, triggers | Programmability | Procedural logic and automatic reactions to data changes |
| CTEs, window functions | Advanced T-SQL | Readable recursive queries and running/ranking aggregates |
| JSON_OBJECT / JSON_ARRAY / OPENJSON / JSON_VALUE | JSON functions | Build and shred JSON directly in T-SQL |
| REGEXP_LIKE / REGEXP_REPLACE / REGEXP_SPLIT_TO_TABLE | Regex functions | Pattern matching without CLR or app-side logic |
| EDIT_DISTANCE / JARO_WINKLER_DISTANCE | Fuzzy matching | Approximate string similarity scoring in T-SQL |
| MATCH operator | Graph queries | Traverses node and edge graph tables |
| GitHub Copilot & Copilot in Fabric | AI-assisted tools | Configure model/MCP options, instruction files, MCP server endpoints |
2. Secure, Optimize, and Deploy Database Solutions (35-40%)
| Concept | Area | Quick note |
|---|---|---|
| Always Encrypted / column-level encryption | Data security | Protects data from anyone reading the column in plaintext, including DBAs |
| Dynamic Data Masking | Data security | Obscures a value in query results without touching stored data |
| Row-Level Security (RLS) | Data security | Restricts which rows a query can return, not which columns |
| Object-level permissions | Data security | Grants or denies access to specific tables, views, procedures |
| Passwordless access / Managed Identity | Secure access | Removes stored credentials from connection strings |
| Auditing | Compliance | Tracks who did what, for compliance review |
| Execution plans / DMVs / Query Store / Query Performance Insight | Performance tuning | Different lenses on the same slow-query diagnosis |
| Blocking & deadlocks | Performance issues | Concurrency problems resolved via isolation levels |
| SQL Database Projects (SDK-style) | CI/CD | Source-controlled, buildable database schema models |
| Schema drift detection | CI/CD | Catches manual out-of-band changes before deployment |
| Branching, pull requests, code owners | CI/CD | Review discipline applied to schema like application code |
| Data API builder (DAB) | Azure integration | Auto-generates REST and GraphQL endpoints from database objects |
| Change Event Streaming (CES) / CDC / Change Tracking | Change handling | Three overlapping ways to detect and react to data changes |
| Azure Monitor / Application Insights / Log Analytics | Observability | Recommended monitoring targets for deployed solutions |
3. Implement AI Capabilities in Database Solutions (25-30%)
| Concept | Area | Quick note |
|---|---|---|
| External models (multimodal, multilanguage, structured output) | Model evaluation | Choosing a model by size, language, and output-format needs |
| Embedding maintenance method | Embeddings | Table triggers, CDC, CES, Change Tracking, Azure Functions, Logic Apps, or Microsoft Foundry |
| Chunking | Embeddings | How source text is split before embeddings are generated |
| Full-text vs. semantic vector vs. hybrid search | Intelligent search | Keyword match vs. meaning match vs. both combined |
| VECTOR data type & vector indexes | Vector search | Native storage and indexing for embedding vectors |
| VECTOR_DISTANCE / VECTOR_NORMALIZE / VECTORPROPERTY / VECTOR_SEARCH | Vector functions | The core T-SQL vector search surface |
| ANN vs. ENN | Vector search | Approximate (fast, scalable) vs. exact (precise, smaller-scale) nearest neighbor |
| Reciprocal Rank Fusion (RRF) | Hybrid search | Combines full-text and vector result rankings into one score |
| sp_invoke_external_rest_endpoint | RAG | Calls an external language model directly from T-SQL |
| Structured data to JSON, send, extract response | RAG pipeline | The three steps of a RAG flow built in T-SQL |
Security Feature Quick Decision
Four security features that solve different problems but get tested against each other constantly:
| If the scenario says… | Use this |
|---|---|
| Hide a value in an app's query results without touching storage | Dynamic Data Masking |
| Prevent anyone, including a DBA, from reading a column in plaintext | Always Encrypted / column-level encryption |
| Let users query a shared table but only see their own tenant's rows | Row-Level Security |
| Block a role from a specific stored procedure or view entirely | Object-level permissions |
| Remove passwords from a connection string | Passwordless access with Managed Identity |
Vector & Hybrid Search Quick Decision
The exam describes a data volume and accuracy requirement, not the acronym directly:
| If the scenario says… | Use this |
|---|---|
| Small vector set, guaranteed exact nearest neighbors required | ENN (Exact Nearest Neighbor) |
| Large-scale vector set, approximate results acceptable for speed | ANN (Approximate Nearest Neighbor) |
| Need both keyword and meaning-based relevance in one ranked result | Hybrid search with Reciprocal Rank Fusion |
| Query is a literal keyword or phrase match | Full-text search |
| Query is conceptual ("find similar meaning") | Vector search |
Change-Handling Quick Decision
CDC, Change Tracking, CES, and SQL trigger bindings all detect that something changed — the exam tests which one fits the stated latency and detail need:
| If the scenario says… | Use this |
|---|---|
| Need row-level before/after values for auditing | Change Data Capture (CDC) |
| Just need to know which rows changed since last check | Change Tracking |
| Need a continuous, near-real-time change stream to a target system | Change Event Streaming (CES) |
| Need to trigger a function the moment a specific row changes | Azure Functions with a SQL trigger binding |
DP-800 Cheat Sheet FAQ
What's the difference between Always Encrypted and Dynamic Data Masking?
Always Encrypted keeps a column unreadable in plaintext to anyone, including DBAs. Masking only changes how a value looks in query results — the stored value is untouched.
What's the difference between ANN and ENN vector search?
ENN guarantees exact closest matches but scales poorly at high volume. ANN trades a small amount of accuracy for speed and scale.
Does DP-800 cover Microsoft Fabric as well as SQL Server?
Yes — Microsoft's audience profile explicitly spans SQL Server, Azure SQL, and SQL databases in Microsoft Fabric as one T-SQL surface.
Can I print this DP-800 cheat sheet?
Yes — every table here is plain HTML, so a browser print or "print to PDF" renders cleanly.
MSCertQuiz sells practice-exam access for DP-800 and other Microsoft certifications; this cheat sheet is written by the same team that builds those questions.
More DP-800 Resources
Domain weights, a 4-week plan, and where candidates lose points.
Scenario questions with full rationale, grouped by domain.
Full exam details and the complete 500-question practice bank.
A data-fundamentals starting point if DP-800 feels like a big jump.
Turn This Cheat Sheet Into Practice Reps
Start with 40 free DP-800 questions covering every domain above.
Start Free DP-800 Practice