Navigate Domains
Design and Implement Database Objects
MS DocsCREATE TABLE in T-SQLData types, constraints, indexes, computed columns, identity — full reference›MS DocsTemporal tables in SQL Server / Azure SQLSystem-versioned temporal tables: period columns, history, time-travel queries›MS DocsIn-memory OLTP overviewMemory-optimized tables, natively compiled procedures, durability options›MS DocsLedger tables in Azure SQL / SQL ServerTamper-evident ledger: append-only, updatable ledger, database ledger›MS DocsExternal tables with PolyBase and Elastic QueryQuery remote SQL, Azure Blob, Hadoop, Cosmos DB as external tables›MS DocsJSON data in SQL Server / Azure SQLStore, query, index JSON — FOR JSON, OPENJSON, JSON_VALUE, JSON_ARRAY›MS DocsGraph tables and MATCH operator in SQLNode/edge tables, pattern matching, shortest path for relationship data›MS DocsColumnstore indexes overviewBatch mode processing, delta stores, compressed row groups, HTAP workloads›
MS DocsPrimary key, foreign key, unique, check, default constraintsConstraint types, enforcement, disabled constraints, trust status›MS DocsSEQUENCE objects in SQL ServerCREATE SEQUENCE, NEXT VALUE FOR, sequence cycling and caching›MS DocsTable and index partitioningPartition functions, schemes, switching, elimination, performance impact›
Study GuideTables, Data Types, and IndexesDesigning and implementing tables is foundational to the DP-800 exam. This covers choosing appropriate data...›Study GuideSpecialized TablesSQL Server and Azure SQL support several specialized table types for specific scenarios: in-memory OLTP tab...›Study GuideJSON Columns and IndexesSQL Server and Azure SQL store JSON data as nvarchar columns, but provide native JSON functions, path expre...›Study GuideConstraints and SequencesConstraints enforce data integrity at the database level. SQL Server supports PRIMARY KEY, FOREIGN KEY, UNI...›Study GuidePartitioning for Tables and IndexesTable and index partitioning divides large tables into smaller, manageable chunks based on a partition key ...›Study GuideDesign and Implement Database Objects (Domain 1 — 35–40%)Core database design skills covering tables, indexes, constraints, and partitioning across SQL Server, Azur...›
Implement Programmability Objects
MS DocsCREATE VIEW in T-SQLStandard and indexed views, SCHEMABINDING, updateable views, view security›MS DocsCREATE FUNCTION — scalar and table-valuedScalar, inline TVF, multi-statement TVF; determinism, SCHEMABINDING, parallelism›MS DocsCREATE PROCEDURE in T-SQLInput/output parameters, dynamic SQL, error handling, execution context›MS DocsCREATE TRIGGER in T-SQLDML triggers (AFTER/INSTEAD OF), DDL triggers, inserted/deleted tables›
Study GuideViewsViews are stored SELECT statements that act as virtual tables. SQL Server supports standard views, schema-b...›Study GuideScalar and Table-Valued FunctionsT-SQL functions encapsulate reusable logic. SQL Server supports scalar functions (return a single value) an...›Study GuideStored ProceduresStored procedures are precompiled T-SQL batches stored in the database. They support parameters (input, out...›Study GuideTriggersTriggers are stored procedures that execute automatically in response to DML (INSERT, UPDATE, DELETE) or DD...›Study GuideImplement Programmability Objects (Domain 1 — 35–40%)Creating and managing T-SQL programmability objects: views, scalar and table-valued functions, stored proce...›
Write Advanced T-SQL Code
MS DocsWITH — Common Table Expressions (CTEs)Recursive CTEs, multiple CTEs, CTE vs temp table performance tradeoffs›MS DocsWindow functions — OVER clausePARTITION BY, ORDER BY, ROWS/RANGE frames; ranking, offset, aggregate›MS DocsJSON functions in T-SQLJSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, JSON_CONTAINS, OPENJSON, JSON_VALUE›
MS DocsRegular expression functions in SQLREGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT›MS DocsFuzzy string matching functionsEDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE for approximate matches›MS DocsMATCH operator for graph queriesMATCH pattern: node/edge traversal, shortest path, graph aggregations›MS DocsTRY...CATCH and error handling in T-SQLStructured exception handling, ERROR_* functions, XACT_ABORT, transactions›
Study GuideCTEs and Window FunctionsCommon Table Expressions (CTEs) provide named temporary result sets for readable, reusable queries. Window ...›Study GuideJSON FunctionsSQL Server provides a comprehensive set of JSON functions for reading, constructing, modifying, and filteri...›Study GuideRegex and Fuzzy String MatchingSQL Server (and especially SQL databases in Microsoft Fabric) provide regex functions for pattern-based str...›Study GuideGraph QueriesSQL Server graph tables store node and edge data using special columns ($nodeid, $fromid, $toid) and suppor...›Study GuideCorrelated Queries and Error HandlingCorrelated subqueries reference the outer query and execute once per outer row. Error handling with TRY/CAT...›Study GuideWrite Advanced T-SQL Code (Domain 1 — 35–40%)Advanced T-SQL querying including CTEs, window functions, JSON functions, regex/fuzzy matching, graph queri...›
AI-Assisted SQL Development
MS DocsGitHub Copilot in SQL Server Management StudioEnable Copilot in SSMS, chat, completions, SQL generation, explain queries›MS DocsMicrosoft Copilot in Fabric for SQL databaseGenerate T-SQL, fix errors, explain query plans, chat-based development›MS DocsMCP tool for Microsoft SQL ServerConnect Copilot to SQL Server via Model Context Protocol for schema-aware queries›MS DocsGitHub Copilot instruction filesRepo-level .github/copilot-instructions.md for SQL coding standards and conventions›MS DocsSecurity considerations for AI-assisted SQL developmentData exposure risks, prompt injection via schema, sensitive query generation›
Study GuideSecurity Impact of AI-Assisted ToolsUsing AI-assisted development tools (GitHub Copilot, Copilot in Fabric) introduces new security considerati...›Study GuideGitHub Copilot and Copilot in Fabric SetupThe DP-800 exam covers enabling GitHub Copilot and Copilot in Fabric, configuring model and Model Context P...›Study GuideMCP Server EndpointsThe Model Context Protocol (MCP) is an open standard that allows AI tools (like GitHub Copilot) to connect ...›Study GuideDesign and Implement SQL Solutions Using AI-Assisted Tools (Domain 1 — 35–40%)Using GitHub Copilot and Microsoft Copilot in Fabric to accelerate SQL development — including configuratio...›
Implement Data Security and Compliance
MS DocsAlways Encrypted in SQL Server / Azure SQLClient-side encryption: deterministic vs randomized, key management, EKM›MS DocsDynamic Data MaskingMasking functions: default, email, partial, random — UNMASK permission›MS DocsRow-Level Security (RLS)Security predicates: filter, block; inline TVF design, performance impact›MS DocsColumn-level and object-level permissionsGRANT/DENY on columns, schemas, views for fine-grained access control›
MS DocsPasswordless Azure SQL authenticationManaged identity, Azure AD, DefaultAzureCredential patterns for Azure SQL›MS DocsAzure SQL Database auditingAudit log events, storage/log analytics/event hub destinations, retention›MS DocsSecure managed identity for SQL model endpointsManaged identity for external model and REST endpoint authentication›MS DocsSecure GraphQL, REST, and MCP endpointsAuthorization, API keys, managed identity for Data API Builder and DAB endpoints›
Study GuideData EncryptionSQL Server provides multiple encryption layers: Transparent Data Encryption (TDE) for data at rest, Always ...›Study GuideDynamic Data Masking and Row-Level SecurityDynamic Data Masking (DDM) obfuscates sensitive column values for unauthorized users without changing store...›Study GuideObject-Level Permissions and Secure AccessSQL Server uses a layered permission system: server-level logins, database-level users, roles, and object-l...›Study GuideDatabase AuditingSQL Server Audit captures database activity events and writes them to a file, Windows Event Log, or Azure M...›Study GuideSecuring Model, GraphQL, REST, and MCP EndpointsAI-enabled database solutions expose multiple endpoint types: AI model endpoints, REST APIs (via Data API B...›Study GuideImplement Data Security and Compliance (Domain 2 — 35–40%)Securing SQL database solutions through encryption, data masking, row-level security, permission management...›
Optimize Database Performance
MS DocsDisplay actual execution plans in SQLGraphical and XML plans: operators, cost estimates, actual vs estimated rows›MS DocsQuery Store for performance monitoringForced plans, regressed queries, wait statistics, parameter sensitivity›MS DocsDynamic Management Views (DMVs) for performancesys.dm_exec_*, sys.dm_os_wait_stats, sys.dm_tran_locks for diagnostics›MS DocsQuery Performance Insight in Azure SQLTop resource-consuming queries, recommendations, auto-tuning options›
MS DocsTransaction isolation levels in T-SQLREAD UNCOMMITTED, READ COMMITTED SNAPSHOT, REPEATABLE READ, SERIALIZABLE›MS DocsSQL Server deadlock detection and resolutionDeadlock graph, victim selection, DEADLOCK_PRIORITY, detection techniques›MS DocsBlocking in SQL Server and Azure SQLsys.dm_exec_requests, lock hierarchy, blocking chains, wait types›
Study GuideDatabase Configuration RecommendationsDatabase configuration choices — service tier, compatibility level, memory grants, and database-scoped opti...›Study GuideTransaction Isolation Levels and Concurrency ControlsTransaction isolation levels control how concurrent transactions see each other's changes. Choosing the rig...›Study GuideQuery Performance TroubleshootingSQL Server provides several tools for diagnosing query performance: execution plans (estimated and actual),...›Study GuideOptimize Database Performance (Domain 2 — 35–40%)Database configuration recommendations, concurrency controls, and query performance investigation using exe...›
Implement CI/CD Using SQL Database Projects
MS DocsSQL Database Projects overviewVS Code extension, Azure Data Studio, SDK-style .sqlproj, deployment artifacts›MS DocsSDK-style SQL Database ProjectsBuild with dotnet, pack as NuGet, pipeline tasks for modern CI/CD workflows›MS DocsDeploy SQL Database ProjectsSqlPackage.exe, DACPAC publish, deployment reports, schema comparison›MS DocsSchema drift detection with SQL Database ProjectsDetect and report differences between project model and live database›
MS DocsConfigure source control for SQL Database ProjectsGit integration: branching, PRs, conflict resolution, SSDT Git workflows›MS DocsGitHub Actions for SQL Database ProjectsBuild, test, and deploy SQL projects using GitHub Actions CI/CD pipelines›MS DocsUnit testing and integration testing for SQLtSQLt framework, unit test setup, mocking, assertions for SQL objects›MS DocsSecrets management for SQL deployment pipelinesAzure Key Vault, GitHub secrets, variable groups for connection strings›
Study GuideTesting Strategy for SQL Database ProjectsA robust testing strategy for database projects combines unit tests (isolated, fast, using mock data) with ...›Study GuideSQL Database Projects (SDK-Style)SQL Database Projects allow you to version-control your entire database schema as T-SQL source files and bu...›Study GuideSource Control and Branching for Database ProjectsStoring SQL Database Projects in Git enables collaboration, history tracking, and structured review of sche...›Study GuideDeployment Pipelines for Database ProjectsCI/CD pipelines automate the build, validation, and deployment of SQL Database Projects. Key concerns inclu...›Study GuideImplement CI/CD by Using SQL Database Projects (Domain 2 — 35–40%)Building robust deployment pipelines for SQL database solutions using SQL Database Projects (SDK-style), so...›
Integrate SQL Solutions with Azure Services
MS DocsData API Builder (DAB) overviewREST and GraphQL over SQL tables, views, stored procedures — zero code›MS DocsDAB configuration file referencedab-config.json: data sources, entities, permissions, REST/GraphQL settings›MS DocsConfigure REST and GraphQL entities in DABPagination, searching, filtering, caching, relationships, stored proc calls›MS DocsDeploy Data API BuilderContainer deployment, Azure App Service, Azure Container Apps, CI/CD›
MS DocsAzure Functions SQL trigger bindingTrigger on INSERT/UPDATE/DELETE via SQL change tracking, parallel scale›MS DocsChange Data Capture (CDC) in SQL ServerTrack row-level changes: enable CDC, query change tables, cleanup jobs›MS DocsChange Tracking in SQL ServerLightweight net change tracking: CT tables, sync version, cleanup›MS DocsAzure Monitor and Application Insights for SQLMetrics, diagnostics, log analytics workspace, alert rules for Azure SQL›
Study GuideData API Builder (DAB)Data API Builder (DAB) is an open-source tool from Microsoft that automatically generates REST and GraphQL ...›Study GuideREST and GraphQL Endpoints in DABData API Builder exposes both REST and GraphQL endpoints from the same configuration. REST follows OData co...›Study GuideMonitoring Azure SQL and Fabric SQLAzure Monitor is the unified monitoring platform for Azure services. For SQL databases, monitoring covers q...›Study GuideChange and Event HandlingReacting to data changes is fundamental to building event-driven systems. SQL Server and Azure SQL offer se...›Study GuideIntegrate SQL Solutions with Azure Services (Domain 2 — 35–40%)Integrating SQL databases with Azure services including Data API Builder (DAB), REST/GraphQL endpoints, mon...›
Design and Implement Models and Embeddings
MS DocsExternal models in Fabric SQL databaseCREATE EXTERNAL MODEL: OpenAI, Azure OpenAI — multimodal, multilanguage, sizes›MS DocsCREATE EXTERNAL MODEL (T-SQL)Syntax, LOCATION, CREDENTIALS, MODEL_TYPE, structured output configuration›MS DocsGenerate embeddings from SQL Server / Azure SQLCall embedding models, store results as VECTOR type, batch processing patterns›
MS DocsAzure Functions SQL trigger for embedding maintenanceAuto-update embeddings on row change using SQL trigger binding›MS DocsChange Tracking for incremental embedding updatesDetect changed rows efficiently using CT version for embedding refresh›MS DocsChunking strategies for vector searchFixed-size, semantic, recursive chunking; overlap, chunk size optimization›
Study GuideExternal Models in SQL DatabaseSQL Database in Microsoft Fabric and Azure SQL support calling AI models directly from T-SQL using external...›Study GuideEmbedding MaintenanceEmbeddings stored in a vector column go stale when the source text changes. Maintaining embeddings means de...›Study GuideChunking and Embedding GenerationBefore generating embeddings, you must decide which columns to embed and how to prepare the text. For long ...›Study GuideDesign and Implement Models and Embeddings (Domain 3 — 25–30%)Evaluating and managing external AI models, designing embedding strategies, and generating embeddings for u...›
Design and Implement Intelligent Search
MS DocsVECTOR data type in SQL Server 2025 / Azure SQLVECTOR column type, dimensions, storage format, size limits›MS DocsVector search in SQL Server and Azure SQLVECTOR_SEARCH, ANN vs ENN, DiskANN index, cosine/dot/Euclidean metrics›MS DocsVECTOR_DISTANCE function in T-SQLCompute cosine, dot product, Euclidean distance between vector columns›MS DocsVECTOR_NORMALIZE and VECTORPROPERTY functionsNormalize vectors to unit length, inspect vector metadata with VECTORPROPERTY›
MS DocsFull-text search in SQL ServerFull-text indexes, CONTAINS, FREETEXT, CONTAINSTABLE, language analyzers›MS DocsHybrid search with Reciprocal Rank Fusion (RRF)Combine BM25 full-text + vector ANN results using RRF score fusion›MS DocsVector index types and performance metricsDiskANN index, IVF, HNSW: build time, query latency, recall tradeoffs›
Study GuideFull-Text SearchFull-text search (FTS) enables linguistic searching of character-based data — matching words, phrases, prox...›Study GuideVector SearchVector search finds rows whose vector embeddings are mathematically similar to a query vector. This enables...›Study GuideHybrid Search and Reciprocal Rank FusionHybrid search combines full-text search (keyword matching) with vector search (semantic similarity) to prod...›Study GuideDesign and Implement Intelligent Search (Domain 3 — 25–30%)Choosing and implementing the right search strategy — full-text, semantic vector, or hybrid — and evaluatin...›
Design and Implement RAG with SQL
MS Docssp_invoke_external_rest_endpoint stored procedureCall any REST API from T-SQL: OpenAI, Azure AI, custom APIs with auth headers›MS DocsBuild RAG applications with Azure SQL and OpenAIRetrieve vector search results → format as JSON prompt → send to LLM → parse response›MS LearnBuild a RAG solution with Azure OpenAI and SQLEnd-to-end: embed documents, store vectors, query, grounding, chat loop›MS DocsIntelligent applications in Microsoft Fabric SQL databaseFull AI stack in Fabric: external models, embeddings, vector search, RAG patterns›
Study GuideRAG Use Cases and ArchitectureRetrieval-Augmented Generation (RAG) grounds large language model (LLM) responses in data from a database, ...›Study GuidePrompts and Responses in T-SQL RAGspinvokeexternalrestendpoint is the T-SQL stored procedure for calling HTTP endpoints from within SQL Serve...›Study GuideDesign and Implement Retrieval-Augmented Generation (RAG) (Domain 3 — 25–30%)Building RAG pipelines that convert structured data to JSON, retrieve relevant context via vector search, a...›
🎓 Official Instructor-Led Course
📚 Microsoft Learn Learning Paths (Free)
Get started with Azure AI for database developersIntro LP for SQL + AI: embeddings, vector search, OpenAI integrationImplement Azure SQL databaseCore Azure SQL LP: provisioning, security, performance, HA/DR, monitoringBuild AI apps with SQL and Azure OpenAIPractical LP: vector search, RAG, sp_invoke_external_rest_endpoint patternsImplement CI/CD for database solutionsSQL Database Projects, GitHub Actions, schema drift, testing strategies
🔬 Microsoft Applied Skills (Free)
✅ Free Practice Assessment
💳 Third-Party Courses (Paid)
🌐 Community & Official Channels
Data Exposed (Microsoft Show)Official Microsoft video series on SQL Server, Azure SQL, and AI integrationsExam Readiness Zone — DP-800Official Microsoft per-domain readiness videos for DP-800SQL Server Tech Community BlogSQL Server announcements, AI integration demos, performance tips from the teamMicrosoft Fabric BlogFabric SQL database AI features, releases, tutorials from the Fabric team
📋 Cheat Sheets
DP-800 — Cheat SheetsQuick-reference guides for high-frequency exam topics. Each sheet is designed for fast lookup during study sessions — not a sub...T-SQL Core Commands — Quick ReferenceCompact reference for the most frequently tested T-SQL commands on the DP-800 exam.Security — Quick ReferenceEncryption, masking, row-level security, permissions, and auditing for Azure SQL Database.Performance & DMVs — Quick ReferenceKey Dynamic Management Views (DMVs), diagnostic queries, and performance tuning patterns for Azure SQL Database.JSON Functions — Quick ReferenceAll JSON functions available in Azure SQL Database and SQL Server 2022+, with syntax and examples.Vector & AI — Quick ReferenceVECTOR type, distance functions, DiskANN indexing, full-text search, hybrid search, and RAG patterns for Azure SQL Database.Azure SQL Config — Quick ReferenceDatabase configurations, Query Store, Data API Builder (DAB), and sqlpackage for Azure SQL Database.
📖 Appendix
AppendixReference materials for DP-800 exam preparation and troubleshooting.DP-800 GlossaryA reference of key terms and concepts for the DP-800: Developing AI-Enabled Database Solutions exam.Comparison TablesQuick-reference comparison tables for the DP-800 exam, organized by topic category. Use these to solidify distinctions between ...Common T-SQL Error Messages and SolutionsQuick reference for error messages you are likely to encounter in Azure SQL Database and Fabric SQL when developing AI-enabled ...Performance Troubleshooting GuideA systematic reference for diagnosing and resolving SQL Server and Azure SQL performance issues.
💻 T-SQL Code Examples
T-SQL Code ExamplesThis folder contains standalone T-SQL code examples referenced throughout the DP-800 study guide. Each file focuses on a specif...Window Function PatternsReusable T-SQL window function patterns for Azure SQL Database and Microsoft Fabric SQL analytics workloads.JSON Patterns in T-SQLCommon JSON patterns for Azure SQL and Fabric SQL — covering construction, parsing, scalar functions, indexing, and SQL Server ...Security Implementation PatternsT-SQL patterns for implementing Row-Level Security, Dynamic Data Masking, permissions,Vector Search PatternsT-SQL patterns for vector storage, similarity search, DiskANN indexing, and hybrid search with Reciprocal Rank Fusion (RRF). Re...RAG Patterns with sp_invoke_external_rest_endpointRetrieval-Augmented Generation (RAG) in Azure SQL Database and Microsoft Fabric SQL usesBuilding a Policy Q&A Bot in ~80 lines of T-SQLA complete RAG (Retrieval-Augmented Generation) pipeline built entirely inside Azure SQL Database. Read this once and every Dom...
🧪 Hands-On Labs
DP-800 Hands-on LabsA small, runnable lab pack for the AI-heavy Domain 3 of the DP-800 exam, plus theLab 01 — Vector Search with DiskANNA runnable walkthrough of the SQL Server 2025 / Azure SQL / Fabric SQL vectorLab 02 — End-to-End RAG with `sp_invoke_external_rest_endpoint`A runnable, self-contained RAG pipeline built entirely in T-SQL. You chunk aLab 03 — Full-Text Search and Hybrid Retrieval with RRFStand up a full-text catalogue and index on the shared product catalogue fromLab 04 — MCP Server Endpoints with CopilotStand up a Model Context Protocol (MCP) server endpoint over the
🗂️ Anki Flashcard Deck
🎯 Exam Prep & Final Review
DP-800 Final Review — Exam Morning1. JSONVALUE on an object/array path returns NULL — not an error. Use JSONQUERY to extract objects or arrays.DP-800 Exam TipsDP-800 includes interactive case studies — a multi-paragraph scenario with several linked questions. Get this format right and ...DP-800 — Exam ResourcesPreparation materials for the Microsoft DP-800: Developing AI-Enabled Database Solutions exam.DP-800 Official LinksPart of the DP-800 community study guide — see DP-800 Official Links.DP-800 Companion ExamsAudience. Data engineers building Fabric analytics pipelines — Lakehouse, OneLake, Eventstreams, Spark, KQL. Sits between Power...DP-800 Renewal GuideSource: Microsoft Certification Renewal and renewal FAQ.
🧾 Mock Exam Guides & Debriefs
Practice & Mock Exams
160 community-contributed questions from kengio/dp-800-study-guide — topic practice sets plus two full 50-question mock exams.
Set
Domain
0Correct
0Answered
1/ 0
