Domain 1: Design and Develop Database Solutions
- Design tables from the data rather than from habit: choose the narrowest data type that holds the domain, since type width multiplies across every row and every index, and prefer a clustered index on a narrow, ever-increasing key so inserts do not fragment the table.
- Columnstore indexes store data by column with heavy compression and are for analytical scans over large tables; rowstore indexes are for seeking individual rows. A table serving both patterns may need a clustered columnstore with a nonclustered rowstore index on top.
- Know the specialised table types and what each solves: memory-optimized tables for extreme throughput and latch contention, temporal tables for automatic row history, external tables for querying data that lives elsewhere, ledger tables for tamper evidence, and graph tables for relationship traversal.
- A system-versioned temporal table keeps a history table updated automatically, so you can query the data as it stood at any past point with FOR SYSTEM_TIME. It is the supported answer to an audit or point-in-time requirement, in preference to hand-rolled history triggers.
- Ledger tables provide cryptographic proof that data has not been tampered with, which is a different requirement from knowing what changed. Use temporal for history and ledger for tamper evidence.
- Store semi-structured data in JSON columns when the shape genuinely varies, and index it so it stays queryable - either a computed column with an index over a frequently filtered path, or a JSON index where the platform supports one. An unindexed JSON path filter scans.
- Constraints are the cheapest correctness mechanism you have: PRIMARY KEY for identity, FOREIGN KEY for referential integrity, UNIQUE for alternate keys, CHECK for domain rules, and DEFAULT for sensible absent values. Enforcing these in application code instead is how data drifts.
- A SEQUENCE is a schema-level object that generates numbers independently of any table, so several tables can share one series and a value can be obtained before the row is inserted - which IDENTITY cannot do.
- Partition large tables on a column that matches how data is queried and aged, usually a date. The real benefits are partition elimination during queries and near-instant archiving by switching a partition out, rather than raw speed.
- Views encapsulate a query and can simplify or secure access; a schema-bound view can be indexed to materialise it. Remember that a plain view is not a performance feature - it is expanded into the calling query.
- Prefer inline table-valued functions to scalar and multi-statement functions. An inline TVF is expanded into the plan and can be optimised; scalar functions historically execute per row, which is the classic cause of a query that is slow for no visible reason.
- Stored procedures give a stable interface, plan reuse and a place to grant permission without granting access to the underlying tables. Triggers should be used sparingly and kept short, because they run inside the calling transaction.
- Common table expressions make complex logic readable and are the mechanism for recursion. A recursive CTE needs an anchor member, a recursive member and a termination condition, and MAXRECURSION guards against a runaway.
- Window functions compute across a set of rows related to the current row without collapsing them, which is what separates them from GROUP BY. Know ROW_NUMBER, RANK and DENSE_RANK, the aggregate window forms, and LAG and LEAD with OVER, PARTITION BY and ORDER BY.
- Work with JSON in T-SQL using the current function set: JSON_OBJECT and JSON_ARRAY to construct, JSON_VALUE to extract a scalar, JSON_CONTAINS to test, JSON_ARRAYAGG to aggregate, and OPENJSON to shred a document into rows and columns.
- Regular expression functions are new and explicitly examinable: REGEXP_LIKE to test, REGEXP_REPLACE to substitute, REGEXP_SUBSTR to extract, REGEXP_INSTR for position, REGEXP_COUNT for occurrences, REGEXP_MATCHES for matches, and REGEXP_SPLIT_TO_TABLE to split into rows.
- Fuzzy string matching functions handle approximate comparison: EDIT_DISTANCE counts the edits between two strings, EDIT_DISTANCE_SIMILARITY normalises that to a score, and JARO_WINKLER_DISTANCE favours strings that agree at the start, which suits names.
- Graph tables use NODE and EDGE tables queried with the MATCH operator, which expresses traversal far more readably than repeated self-joins. Use them when relationships and paths are the point rather than an incidental foreign key.
- A correlated subquery references the outer query and is evaluated per outer row conceptually, which makes EXISTS the efficient way to test for existence - it stops at the first match rather than counting everything.
- Handle errors with TRY...CATCH and re-raise with THROW rather than RAISERROR in new code. Use XACT_ABORT and check XACT_STATE so a doomed transaction is rolled back rather than left open.
- AI-assisted development is a published objective, not an aside. Enable GitHub Copilot and Copilot in Fabric, and know how to choose the model and configure Model Context Protocol tool options within a chat session.
- GitHub Copilot instruction files put project conventions - naming, formatting, preferred patterns - in the repository so generated code follows them consistently rather than depending on how each developer phrases a prompt.
- Connect Copilot to MCP server endpoints such as SQL Server or a Fabric lakehouse so it can work against real schema and data rather than guessing at table names. That grounding is what makes generated T-SQL useful.
- Interpret the security impact of AI-assisted tools honestly: what schema or data leaves the environment, whether an MCP connection grants more access than intended, and that generated code is a draft to review - it can produce a query that is correct but leaks data through an over-broad projection.
Domain 2: Secure, Optimize, and Deploy Database Solutions
- Always Encrypted encrypts data in the client driver so the engine never sees plaintext, which is what protects a column from database administrators. The trade-off is limited server-side operations - deterministic encryption allows equality, randomised allows none.
- Column-level encryption inside the engine protects data at rest but is visible to anyone who can decrypt it in-session, so it answers a different requirement from Always Encrypted. Match the mechanism to who exactly must be prevented from reading the value.
- Dynamic Data Masking obscures values in query results for unprivileged users without changing stored data. It is a presentation control, not a security boundary - a determined user can infer masked values through filtering, so it does not replace permissions.
- Row-Level Security applies a predicate function to a table so each user sees only their rows, enforced by the engine for every query path. Filter predicates silently remove rows; block predicates stop writes that would violate the policy.
- Grant object-level permissions to roles rather than users, and grant EXECUTE on stored procedures in preference to SELECT on tables where ownership chaining lets the procedure act without the caller having direct table access.
- Implement passwordless access with Microsoft Entra authentication and managed identities so no connection string holds a secret. It is the recommended pattern for applications and Azure services reaching a database.
- Auditing records who did what and when, and must be written somewhere the audited principals cannot alter. Audit configuration changes and permission grants, not only data access, since those are the events that precede misuse.
- Secure model endpoints with managed identity rather than API keys held in the database, and secure GraphQL, REST and MCP endpoints with authentication and authorisation as deliberately as the database itself - an unauthenticated Data API builder endpoint exposes the tables behind it.
- Transaction isolation levels trade consistency against concurrency. READ COMMITTED is the default and blocks on writers; READ COMMITTED SNAPSHOT and SNAPSHOT use row versioning so readers do not block writers, at the cost of tempdb usage.
- Know the concurrency anomalies each level prevents: dirty reads, non-repeatable reads and phantom reads. Choosing SERIALIZABLE to avoid an anomaly that snapshot isolation already prevents is a common over-correction that costs throughput.
- Blocking and deadlocks are different problems. Blocking is one session waiting on another and is resolved by shortening transactions and indexing the predicate; a deadlock is a cycle the engine breaks by killing a victim, resolved by accessing objects in a consistent order.
- Read execution plans for the operators that signal trouble: scans where a seek was expected, key lookups in volume, sorts and spills, and a large gap between estimated and actual row counts, which usually means stale statistics or a non-sargable predicate.
- Query Store captures query text, plans and runtime statistics over time, which is what lets you prove a query regressed after a change and force the previous plan. Query Performance Insight is the Azure SQL portal view over the same idea.
- Dynamic management views expose live state - waits, active requests, index usage, missing index suggestions. Treat missing index recommendations as evidence to evaluate, not instructions to apply, since they ignore write cost and existing indexes.
- SQL Database Projects hold the database schema as code, so the desired state is in source control and a deployment is a comparison between the project and the target. SDK-style projects are the current format and build with the standard tooling.
- Keep reference and static data in source control alongside the schema, so a fresh environment comes up complete. Data that a deployment must guarantee is part of the definition, not something to insert by hand afterwards.
- Detect schema drift by comparing the project against the deployed database before releasing. Drift means somebody changed production directly, and deploying over it without knowing is how changes are silently reverted.
- Test databases at more than one level: unit tests for functions, procedures and constraints against known inputs, and integration tests that exercise the schema and data together. Both belong in the pipeline rather than in someone's local session.
- Control deployment pipelines with branching policies, required reviewers and code owners, approval gates on the production stage, and secrets held in a secret store rather than in pipeline variables in plain text.
- Data API builder generates REST and GraphQL endpoints over database objects from a configuration file, without writing an API layer. Entities map to tables, views and stored procedures, and relationships become GraphQL fields.
- Configure DAB entities with the behaviour the client needs - caching, pagination, filtering and searching - and with permissions per role and per operation, because exposing an entity without restricting operations exposes writes as well as reads.
- Recommend Azure Monitor configuration alongside the solution: Application Insights for application-side dependency and query telemetry, Log Analytics for the database logs and metrics, and alerts on the signals that indicate a problem rather than on everything.
- Choose a change-handling mechanism by what the consumer needs: Change Tracking for a lightweight "what changed" list, Change Data Capture for full change history including intermediate values, change event streaming for pushing changes outward, and Azure Functions with a SQL trigger binding or Logic Apps for reacting to them.
Domain 3: Implement AI Capabilities in Database Solutions
- Evaluate external models before choosing one: whether it needs to handle images or audio as well as text, what languages it covers, its context window, whether it can return structured output, and the latency and cost per call - which matters because a database-driven workload can call a model per row.
- Create and manage external models in the database so T-SQL can call them through a defined object rather than each procedure hard-coding an endpoint and credential. That is what makes the model a managed dependency you can change in one place.
- An embedding is a numeric vector representing the meaning of text, so that similar meanings sit close together in vector space. Semantic search works by embedding the query the same way and finding the nearest stored vectors.
- Choose which columns to embed by what a user would search on in their own words - a description, a body of notes, a title - not identifiers, dates or numeric codes, which are better served by ordinary predicates.
- Chunk long text before embedding it, because one vector over a long document averages away the specifics and retrieves poorly. Chunk on natural boundaries with a little overlap so a sentence spanning a boundary is not lost.
- Embeddings go stale when the source text changes, so choose a maintenance method deliberately: a table trigger for immediate consistency, Change Tracking or CDC for a batch catch-up, or Azure Functions with a SQL trigger binding, Logic Apps or Microsoft Foundry for an external pipeline.
- The vector data type stores embeddings natively with a fixed dimension count that must match the model that produced them. Mixing vectors from two different models in one column produces distances that mean nothing.
- VECTOR_DISTANCE computes the distance between two vectors under a chosen metric, VECTOR_NORMALIZE scales a vector to unit length, VECTORPROPERTY reports properties such as dimension count, and VECTOR_SEARCH performs the search against a vector index.
- Choose the distance metric to match the model: cosine for most text embedding models, dot product where vectors are already normalised, and Euclidean where absolute magnitude carries meaning. The model documentation dictates this rather than preference.
- Exact nearest neighbour compares against every vector and is exactly right but scales linearly; approximate nearest neighbour uses an index to search a fraction of the space, trading a little recall for a large speed gain. Use ENN on small sets and ANN at scale.
- Vector index type and parameters trade build time, memory and recall. Evaluate them with a measured recall figure against a known answer set rather than by feel, because an ANN index that misses the right answer looks fast and is wrong.
- Full-text search matches words and phrases with linguistic processing, so it excels at exact terms, names and identifiers. Vector search matches meaning, so it finds a document that never uses the searcher's words. They fail in opposite directions.
- Hybrid search runs both and combines the results, which is the usual production answer because it keeps keyword precision for exact terms and semantic recall for paraphrases.
- Reciprocal rank fusion merges ranked lists by scoring each result from its rank in each list rather than from raw scores, which is what makes it possible to combine full-text and vector results whose scores are not comparable.
- Evaluate search quality with a labelled set of queries and expected results, measuring recall and the position of the right answer. Without that, tuning chunk size, metric or index parameters is guesswork.
- Retrieval-augmented generation grounds a model in your own data: retrieve the relevant rows or chunks with search, put them in the prompt, and have the model answer from them. It is the answer to a model that is fluent but wrong about your business.
- RAG suits questions over a corpus that changes, where answers must cite current internal data and where fine-tuning would be disproportionate. It does not suit aggregate questions that a SQL query answers exactly - retrieving rows to have a model count them is the wrong tool.
- sp_invoke_external_rest_endpoint is how T-SQL calls an external REST endpoint, including a model endpoint, sending a JSON payload and receiving a JSON response. It is the mechanism behind in-database prompting.
- Convert structured rows to JSON with FOR JSON or the JSON constructor functions before sending them to a model, because a model consumes text - and a well-labelled JSON structure produces noticeably better answers than a raw concatenation of column values.
- Extract the model response with JSON_VALUE or OPENJSON against the documented response shape, and handle the failure cases: a non-200 response, a truncated answer, or a model that did not return the structure you asked for.
DP-800: AI-Enabled Database Solutions exam tips
- The first two domains are 35-40% each, so roughly three quarters of the exam is classic database development plus security, performance and deployment. The AI domain is 25-30% - substantial, but not the majority the exam title suggests.
- The newer T-SQL function families are explicitly published and worth memorising: the JSON functions, the REGEXP_ family, and the fuzzy matching functions EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY and JARO_WINKLER_DISTANCE.
- Know the vector functions by name and purpose - VECTOR_DISTANCE, VECTOR_NORMALIZE, VECTORPROPERTY and VECTOR_SEARCH - along with ANN against ENN and why the distance metric must match the embedding model.
- Distinguish the security features by who they protect against. Always Encrypted hides data from the database administrator; column-level encryption protects at rest; Dynamic Data Masking only obscures results and is not a security boundary; Row-Level Security filters rows for every query path.
- For any "which specialised table" question, match the requirement precisely: temporal for history, ledger for tamper evidence, in-memory for extreme concurrency, external for data held elsewhere, graph for traversal.
- AI-assisted development is examinable in its own right. Know GitHub Copilot instruction files, configuring models and MCP tools in a chat session, connecting to SQL Server and Fabric lakehouse MCP endpoints, and the security implications of doing so.
- Hybrid search plus reciprocal rank fusion is the expected production answer for search quality, because full-text and vector search fail in opposite directions and their raw scores are not comparable.
- Data API builder appears throughout the deployment domain. Know that it generates REST and GraphQL from a configuration file, that entities map to tables, views and stored procedures, and that permissions are set per role and per operation.
Study guide FAQ
What score do I need to pass DP-800?
A score of 700 or greater is required to pass, on Microsoft's scaled 1-1000 range, and it is not a simple percentage of questions answered correctly. Microsoft does not publish a fixed question count or duration for DP-800 on the study guide, and both can vary between forms. There is no penalty for a wrong answer.
Is DP-800 mostly an AI exam?
No, despite the title. Implementing AI capabilities is 25-30%, while designing and developing database solutions and securing, optimizing and deploying them are 35-40% each. The exam assumes a strong T-SQL developer who is adding AI capability, not an AI specialist learning SQL, so weak T-SQL will cost more marks than weak AI knowledge.
How much T-SQL do I need to write?
A lot, and current T-SQL specifically. The published objectives name common table expressions, window functions, correlated queries, error handling, the JSON functions, the REGEXP_ family, the fuzzy string matching functions, and graph queries using MATCH. Expect to read and correct code rather than write it from scratch, but you need to recognise correct syntax on sight.
What is the difference between full-text, vector and hybrid search?
Full-text search matches words and phrases with linguistic processing, so it is precise for exact terms, names and codes but misses paraphrases. Vector search matches meaning through embeddings, so it finds relevant content that never uses the searcher's words but can miss an exact identifier. Hybrid search runs both and merges the results, usually with reciprocal rank fusion, which combines ranked lists by rank rather than by raw score because the two scoring systems are not comparable.
How do I keep embeddings up to date when the source data changes?
Choose a maintenance method to match the freshness requirement. A table trigger regenerates the embedding immediately but adds cost to every write; Change Tracking or Change Data Capture lets a batch process catch up on what changed; and Azure Functions with a SQL trigger binding, Azure Logic Apps, change event streaming or Microsoft Foundry move the work to an external pipeline. Microsoft lists all of these as valid, so the exam is testing whether you can justify the choice.