DP-800 AI-Enabled Database Solutions Guide (2026)

Exam DP-800: Developing AI-Enabled Database Solutions targets a role that is broader than a traditional database developer and more database-centered than a general AI engineer. Microsoft describes candidates who design and develop solutions across SQL Server, Azure SQL, and SQL databases in Microsoft Fabric. They work with structured and semi-structured data, write T-SQL, integrate AI into enterprise applications, secure and optimize the data tier, automate database delivery, and implement models, embeddings, intelligent search, and retrieval-augmented generation.

The exam blueprint makes one architectural point clear: AI features do not replace database engineering. A vector column still needs a source key, model version, dimension, trusted metadata, permissions, maintenance process, backup strategy, query plan, release artifact, and cost boundary. A generated answer is useful only if retrieval respected tenant isolation, source approval, freshness, and citations. DP-800 preparation should therefore connect database correctness and DevSecOps evidence to AI quality.

The PrepKloud five-phase DP-800 roadmap organizes that connection. This guide explains the reasoning behind the phases and shows how the two hands-on projects cover the official scope without using exam dumps, recalled questions, or marketplace content.

Official DP-800 domains and study allocation

The first two domains each carry the largest range. Do not spend three quarters of study time on model calls and vectors. A realistic question may ask you to choose a table design, repair a transaction, protect a DAB endpoint, interpret a plan regression, select a change mechanism, or deploy a dacpac. The AI domain then assumes that this foundation is trustworthy enough to host embeddings and retrieval logic.

Domain 1: design and develop modern Microsoft SQL solutions

Start with table intent and workload. Size data types from real ranges, identify required and optional values, and enforce business rules with primary keys, foreign keys, unique constraints, checks, and defaults. An index is not merely a remembered syntax pattern. It has a key order, included columns, selectivity, maintenance cost, storage cost, and relationship to the query plan. A rowstore path usually suits transactional point access; columnstore can accelerate analytical scans and compression. A nonclustered columnstore index can support reporting over an OLTP table, but its write overhead must be measured.

The blueprint explicitly includes specialized tables. System-versioned temporal tables answer “what did this row look like at a time?” Ledger addresses tamper-evident verification. Graph tables and MATCH model relationship traversal. In-memory objects target specific latency and concurrency patterns. External tables provide access to external data without pretending it has the same transaction semantics as local storage. Choosing one requires a stated requirement; “newer feature” is not a design criterion.

Semi-structured data deserves similar discipline. A product catalog may have stable relational keys, prices, tenant IDs, approval state, and timestamps plus supplier-specific attributes in JSON. Keep frequently joined, filtered, constrained, and protected values relational. Validate JSON, extract values through JSON functions, and make common predicates indexable with a supported JSON indexing approach or computed expression. Do not move the entire schema into one opaque string merely because JSON is flexible.

Programmability objects each have a role. Views present a stable relational contract. Inline table-valued functions provide parameterized, composable table expressions. Stored procedures package governed operations, transactions, and permissions. Triggers can enforce or react to data changes but should remain short and set-based; they are not a safe place for slow model calls. Scalar functions are useful for scalar logic, but repeatedly invoking procedural work per row can become a performance problem.

Advanced T-SQL includes common table expressions, window functions, JSON construction and shredding, regular expressions, fuzzy string matching, graph queries, correlated queries, and error handling. Use an OVER clause when calculations should preserve detail rows. Add a deterministic tie-breaker to ordered windows. Use TRY...CATCH with an explicit transaction when a multi-object business operation must be atomic, and rethrow errors so callers can make an informed retry or compensation decision.

AI-assisted SQL development is also in scope. GitHub Copilot, Copilot in Fabric, instruction files, models, and MCP endpoints can accelerate exploration, but their output is untrusted. Restrict tool identities, permitted statements, databases, and schemas. Use sanitized fixtures instead of customer rows. Keep passwords and keys out of prompts and instruction files. Inspect generated SQL, deployment plans, and data access before execution. Prompt injection can come from repository text, database content, or tool output—not only from a chat user.

Domain 2A: security and compliance as layered controls

DP-800 expects you to distinguish controls by threat. Transparent Data Encryption protects database and backup files at rest. Always Encrypted creates a client-held key boundary for selected columns so the Database Engine and administrators without key access do not receive plaintext. Column-level encryption can protect selected values but has different application and key-management implications. Dynamic Data Masking changes values shown in query results for users without unmask permission; it does not modify the stored value and is not a substitute for encryption or least privilege.

Row-Level Security centralizes row filtering and optional write blocking in the database. A multitenant middle tier can validate a Microsoft Entra token, set tenant identity in SESSION_CONTEXT, and rely on an RLS predicate function and security policy. Connection pooling makes context hygiene critical: clear and reset trusted context before reuse. Test direct SQL paths and every API role, because an application WHERE TenantId = ... clause is easy to omit.

Passwordless does not mean permissionless. A managed identity can authenticate DAB, a Function, or another Azure-hosted workload to Azure SQL without a stored SQL password. The database still needs a contained user and the minimum grants required for that workload. Separate runtime, deployment, enrichment, model administration, audit review, and emergency duties. Configure auditing to collect useful security events with an owner and retention policy, while avoiding sensitive payload duplication.

Model endpoints and REST, GraphQL, and MCP endpoints create additional boundaries. The SQL managed identity might need permission to invoke one model deployment, not administer the resource. A DAB reader needs selected fields and rows, not wildcard actions. An MCP tool should expose a narrow stored procedure, not arbitrary SQL execution. Authentication proves who or what called; authorization decides what that identity can do.

Domain 2B: performance, isolation, and troubleshooting

Performance work begins with evidence. Capture an actual execution plan, runtime statistics, logical reads, CPU, duration, waits, memory grants, spills, and parameter behavior. Dynamic management views show current and cumulative system state. Query Store retains query texts, plans, and runtime history so you can compare behavior before and after a deployment. Query Performance Insight surfaces useful Azure SQL views over query resource consumption.

Transaction isolation is a correctness choice with performance consequences. Azure SQL Database and SQL database in Fabric commonly use read-committed snapshot behavior by default, reducing reader-writer blocking through row versions. That does not eliminate write locks, update conflicts, tempdb or version-store considerations, or the need for appropriate transactions. READ UNCOMMITTED can produce dirty and inconsistent reads; it is not a general “make it fast” setting.

For blocking, find the head blocker, transaction age, statement, wait type, and resources. Long user interactions inside transactions, missing indexes, broad updates, and inconsistent object access order are common causes. A deadlock is a cycle, not simply a slow lock. Collect the deadlock graph, make transactions access objects in a consistent order, keep them short, index predicates to reduce locked rows, and let the application retry a chosen victim within a strict bound.

Data API builder: secure REST and GraphQL from configuration

Data API builder is a stateless, open-source, configuration-based engine for REST, GraphQL, and supported MCP endpoints over databases. The configuration maps tables, views, and stored procedures into named entities. It selects fields, relationships, actions, roles, policies, routes, GraphQL behavior, caching, and data sources. DAB also provides pagination, filtering, sorting, selection, aggregation for SQL-family databases, health checks, OpenAPI, GraphQL tooling, and OpenTelemetry integration.

Configuration convenience does not justify exposing the whole schema. A view needs key fields and should generally receive read permission unless it is deliberately updatable. A stored procedure receives execute permission and should define the intended REST method or GraphQL operation. Procedures return only supported result shapes and have limitations around relationships, pagination, and parameter authorization, so design a small contract.

DAB authorization is layered. Entity permissions select operations, field rules limit columns, policy expressions can filter by claims, SQL RLS restricts rows, and request validation limits the API surface. Production authentication can use Microsoft Entra ID or another supported JWT provider. The DAB runtime can separately use managed identity for Azure SQL. Add request limits, bounded page sizes and caching, sensitive telemetry redaction, and contract tests for malformed filters, overposting, GraphQL depth, role selection, and cross-tenant access.

SQL Database Projects and CI/CD

An SDK-style SQL Database Project treats the desired schema as source. Building validates T-SQL and object relationships against a target platform and produces a .dacpac, a compiled database model. A healthy pipeline separates build from deployment: compile and test once, publish the dacpac as an immutable artifact, then promote the same bytes through environments. Rebuilding for production can produce a different artifact from the one tested.

Pull requests should run model builds, code analysis, unit tests, ephemeral-database integration tests, security tests, and DAB configuration validation. Store reference or static data as reviewed source with idempotent deployment behavior. Detect secrets, uncontrolled dynamic SQL, unresolved references, and changes likely to cause data loss. Use code owners and branch policies so database and security reviewers participate where needed.

Before a sensitive deployment, generate a report or script to understand how the dacpac differs from the target. Check for unauthorized schema drift. Use a workload identity or another approved secret-management pattern rather than a committed connection string. Protected environments can require approval. After deployment, run smoke, authorization, performance, and migration checks, observe Query Store, and maintain a rollback or forward-fix plan that accounts for data compatibility.

Choosing a change mechanism for embedding maintenance

Embeddings must change when source text, metadata, chunk policy, or model changes. DP-800 lists table triggers, Change Tracking, Azure Functions with SQL trigger binding, Logic Apps, CDC, change event streaming, and Microsoft Foundry among maintenance options. The correct mechanism depends on required detail and orchestration.

Change Tracking is useful when a worker needs changed keys since a durable synchronization version and can read current rows. CDC is appropriate when detailed captured changes or intermediate operations matter. Event streaming fits event-driven consumers. Functions or Logic Apps can orchestrate model calls and retries. A trigger can mark a row or enqueue lightweight work, but making a network model call inside the source transaction increases lock duration and makes OLTP availability depend on an external endpoint.

Regardless of the mechanism, store durable progress and make processing idempotent. A practical embedding identity includes source key, source content hash, chunk-policy version, embedding-model version, and dimension. Record pending, processing, current, stale, failed, and quarantined states. Bound retries and concurrency against endpoint quotas. Backfill a new model version, measure coverage and relevance, then switch retrieval without mixing incompatible vectors.

Models, chunks, embeddings, and native SQL vector search

An embedding model converts input into a fixed-length vector. Evaluate models by supported modalities and languages, output dimensions, context limits, semantic quality, latency, throughput, region, price, privacy controls, and lifecycle. The SQL VECTOR(n) dimension must match model output. Preserve model and data versions so a future migration is explicit.

Chunking affects retrieval as much as the index. Very large chunks dilute topics and consume context; very small chunks can lose meaning. Prefer document structure and semantic boundaries, add measured overlap, inherit trusted metadata, and preserve source and ordinal for citations. Exclude secrets and irrelevant boilerplate. Test chunk policies against a labeled question set instead of choosing a universal token count.

Microsoft SQL platforms can register an external embedding model and generate embeddings through AI_GENERATE_EMBEDDINGS where supported. A database-scoped credential can use managed identity, and database permissions should limit who can use or alter the model. Verify applies-to, preview, endpoint, and region requirements. Model calls are external dependencies, so include timeouts, quota handling, safe retries, cost telemetry, and an emergency kill switch.

Exact nearest-neighbor search orders eligible rows by VECTOR_DISTANCE. It exhaustively calculates distances and provides a ground-truth result set. Microsoft documentation offers a general recommendation that exact search can be suitable when the searched set is below roughly 50,000 vectors, including when relational predicates reduce a larger table to that size. Treat that as guidance to benchmark, not an immutable threshold.

Approximate nearest-neighbor search trades some recall for lower resource use and latency. Native SQL vector indexes use DiskANN, a graph-based approach that balances SSD, memory, CPU, I/O, and search quality. Build an index with current syntax and query with the current VECTOR_SEARCH form where supported. Preview features and index versions can change. Measure ANN recall against the ENN top-k set, then add p95 latency, throughput, CPU, I/O, index size, build time, DML behavior, and filtered-query tests.

Hybrid search and reciprocal rank fusion

Full-text search excels at literal identifiers, error codes, phrases, and names. Vector search excels at paraphrases and semantic intent. A support question such as “why does pump XJ-442 show E19?” benefits from both. Full text should reward XJ-442 and E19; vector search can connect “why does” to troubleshooting and failure semantics.

Raw full-text scores and vector distances are not naturally comparable. Reciprocal rank fusion combines rank positions instead. One common form is:

For each lexical and vector list, assign a rank, add reciprocal contributions, deduplicate by a stable chunk or document key, then order by fused score. Tune the constant k, candidate depths, final result count, tie-breakers, and optional diversity against labeled judgments. Run identical tenant, approval, language, date, and source-version filters for both retrieval branches. Otherwise the fused list can reintroduce data that one branch correctly excluded.

Evaluate lexical-only, vector-only, and hybrid search by query class. Use recall at k, mean reciprocal rank, nDCG, exact-code success, unauthorized count, stale-result count, latency, and resource use. Hybrid is not automatically better: it adds work and complexity, so retain it only when measured relevance gain justifies the cost.

Database-centered retrieval-augmented generation

RAG is useful when a model needs current, governed source evidence without model retraining. The retrieval procedure should accept trusted identity context and a parameterized question, obtain a bounded candidate set, and serialize only the required snippets and metadata to JSON. Include stable citation identifiers and source versions. Treat retrieved text as untrusted instructions; it is evidence, not authority to modify system policy or call tools.

The blueprint names sp_invoke_external_rest_endpoint for creating a prompt and calling a model endpoint. Use a scoped database credential, an approved URL, bounded payloads, timeout and retry behavior appropriate to the operation, and permission separation. An alternative architecture can keep retrieval in SQL and generation in an application service when network, scaling, safety, or operational requirements favor that boundary.

Request structured output when supported and validate it. Check HTTP status, response size, JSON schema, finish reason, citation IDs, and content policy fields before persisting or displaying an answer. Every citation must map to a retrieved, authorized source. If evidence is weak, stale, unauthorized, or absent, return an explicit insufficient-evidence result. A fluent unsupported answer is a defect, not a fallback.

Do not log endpoint credentials, full prompts, sensitive chunks, raw vectors, or unrestricted model responses. Preserve safe correlation fields such as operation ID, model version, retrieval mode, candidate count, latency, token count, answer status, and citation-validation result. Audit and telemetry need retention, access controls, redaction, sampling, and cost limits.

Two portfolio projects that cover the blueprint

The first DP-800 portfolio project is a governed AI-enabled product catalog SQL API. Model relational and JSON product data, add temporal history and ledger evidence, write advanced T-SQL, secure tenants with RLS and managed identity, configure DAB REST and GraphQL, build an SDK-style SQL project, promote one dacpac through approvals, and operate the API with Query Store, auditing, Application Insights, and cost controls.

The second project is a secure hybrid-search RAG database. Model document versions, chunks, embedding jobs, vectors, search runs, judgments, answers, and citations. Register an external model, maintain embeddings asynchronously, benchmark full text, ENN, and DiskANN ANN, combine results with RRF, invoke a governed generation endpoint, validate structured output and citations, expose procedures through DAB, and gate deployment on security, freshness, relevance, performance, and cost.

Both projects use synthetic or public source data. This protects employers and customers while still demonstrating architecture, schema, T-SQL, APIs, tests, release controls, monitoring, and teardown. A portfolio should explain trade-offs and evidence, not expose real connection details or claim that one design is universally best.

A ten-to-fourteen-week DP-800 study plan

1. Weeks 1–2: design relational tables, constraints, rowstore and columnstore indexes, JSON fields, sequences, and partitioning. Add deliberate invalid data and inspect why constraints fail.
2. Weeks 3–4: implement temporal, ledger, graph, views, TVFs, procedures, triggers, CTEs, windows, JSON queries, regex, fuzzy matching, correlated queries, and transaction error handling.
3. Weeks 5–6: practice Always Encrypted, masking, RLS, Microsoft Entra authentication, managed identity, object permissions, auditing, isolation, Query Store, plans, blocking, and deadlocks.
4. Weeks 7–8: configure and secure DAB entities, views, procedures, relationships, pagination, filters, caching, REST, GraphQL, deployment, and OpenTelemetry.
5. Weeks 9–10: build an SDK-style SQL project, tests, reference data, dacpac artifact, drift checks, deployment report, branch protection, workload identity, approval, rollback, and monitoring.
6. Weeks 11–12: select a model, design chunks and VECTOR dimensions, create an external model, generate embeddings, and implement durable change-driven maintenance.
7. Weeks 13–14: benchmark full text, ENN, ANN, DiskANN, filters, RRF, and RAG. Add citation validation, abstention, injection tests, relevance gates, p95 monitoring, cost analysis, and cleanup.

Use 25 original DP-800 practice questions to find weak decisions rather than memorize answer positions. The arrays in the course data are zero-based because the PrepKloud quiz engine expects indexes. Use 25 DP-800 flashcards for active recall, then open the official reference for every missed item and explain why each distractor violates the scenario.

Preparation and implementation mistakes to avoid

• Studying only vector syntax. Seventy to eighty percent of the blueprint is database design plus secure, optimized delivery.
• Putting every field in JSON. Stable, constrained, joined, protected, and commonly filtered values usually belong in typed columns.
• Treating masking as encryption. DDM changes query presentation; it does not create an encryption key boundary.
• Trusting only an application tenant filter. Layer validated claims, DAB policies, SQL RLS, and direct-path tests.
• Using READ UNCOMMITTED as a tuning default. It can return dirty or inconsistent results and does not eliminate write locks.
• Running model calls inside OLTP triggers. External latency and quota failures can extend locks and reduce availability.
• Mixing embedding versions. Store model, dimension, metric, source hash, and state; validate a backfill before switching.
• Adding raw lexical and vector scores. Use a tested normalization or rank-based fusion such as RRF.
• Evaluating only final prose. Score retrieval, authorization, freshness, citations, abstention, and generation separately.
• Rebuilding the dacpac for production. Promote the same reviewed artifact through protected environments.
• Ignoring preview and applies-to notes. Vector, AI, regex, fuzzy, and platform features can differ across SQL Server, Azure SQL, and Fabric.
• Leaving labs running. SQL compute, model calls, private endpoints, DAB hosting, and telemetry can accrue cost after practice ends.

Official references

• Microsoft Learn: Study guide for Exam DP-800
• The Microsoft SQL Database Engine
• What's new in SQL Server 2025
• JSON data in SQL Server
• Secure SQL Server
• Azure SQL security capabilities
• Monitor performance with Query Store
• Data API builder overview
• Data API builder authorization
• SQL projects automation
• Track data changes with CDC and Change Tracking
• CREATE EXTERNAL MODEL
• AI_GENERATE_EMBEDDINGS
• Vector search and vector indexes in the SQL Database Engine
• Reciprocal rank fusion concepts and hybrid ranking
• sp_invoke_external_rest_endpoint

Continue across all PrepKloud surfaces

• DP-800 five-phase roadmap
• DP-800 original practice questions
• DP-800 flashcards
• DP-800 portfolio projects
• Database, cloud, and AI jobs board
• Technology career paths
• All certification roadmaps
• All PrepKloud guides
• PrepKloud editorial policy

Frequently asked questions