DP-800 - Microsoft Certified SQL AI Developer Associate Cheat Sheet

Updated 2026-08-07

Next Topic: Google Cloud Professional Data Engineer Cheat Sheet

🎯Take a practice test on this topic8 practice tests · 333 questions→

🧠Study flashcards on this topic254 cards · spaced repetition→

DP-800 is Microsoft's associate-level exam for the SQL AI Developer Associate certification, testing your ability to design, secure, and AI-enable database solutions across Microsoft SQL Server, Azure SQL, and SQL databases in Microsoft Fabric. It is a 120 minute proctored exam split across three domains: designing and developing database solutions with advanced T-SQL and AI-assisted tooling (35-40%), securing, optimizing, and deploying those solutions through CI/CD and Azure integration (35-40%), and implementing genuinely new AI capabilities such as embeddings, vector search, and retrieval-augmented generation directly inside the database engine (25-30%). The exam leans hard on syntax you actually have to write, from window functions and JSON path expressions to the exact system stored procedure that calls an external model from T-SQL, so this sheet favors concrete commands and configuration over abstract description. Skills measured as of March 12, 2026.

What This Cheat Sheet Covers

This topic spans 26 focused tables and 263 indexed concepts, 254 flashcards, 8 practice tests with 333 questions. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Table Design Fundamentals: Data Types, Indexes, Constraints & SequencesTable 2: Specialized Table Types: In-Memory, Temporal, External, Ledger & GraphTable 3: JSON Columns, JSON Indexes & Table/Index PartitioningTable 4: Views, Functions, Stored Procedures & TriggersTable 5: CTEs, Window Functions & Correlated QueriesTable 6: JSON Query Functions in T-SQLTable 7: Regular Expression & Fuzzy String Matching FunctionsTable 8: Graph Queries with MATCH & T-SQL Error HandlingTable 9: AI-Assisted SQL Development: GitHub Copilot & Copilot in FabricTable 10: Data Encryption, Dynamic Data Masking & Row-Level SecurityTable 11: Object-Level Permissions, Secure Access & AuditingTable 12: Securing AI Model, GraphQL, REST & MCP EndpointsTable 13: Database Performance: Isolation Levels, Execution Plans & DeadlocksTable 14: SQL Database Projects: Models, SDK-Style Projects & Source ControlTable 15: Testing Strategy & Schema Drift DetectionTable 16: Branching, Pull Requests & Secrets ManagementTable 17: Deploying SQL Database Projects & Pipeline ControlsTable 18: Data API Builder (DAB): Configuration & Entity DesignTable 19: Data API Builder (DAB): Exposing Database Objects & DeploymentTable 20: Azure Monitor & Change Event HandlingTable 21: External AI Models in SQL: Evaluation & ManagementTable 22: Embeddings: Column Selection, Chunking, Maintenance & GenerationTable 23: Choosing a Search Strategy & Implementing Full-Text SearchTable 24: Vector Data Types, Functions & Index DesignTable 25: Implementing Vector Search, Hybrid Search & Reciprocal Rank FusionTable 26: Retrieval-Augmented Generation (RAG) in SQL

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Table Design Fundamentals: Data Types, Indexes, Constraints & Sequences

Covers the DP-800 "Design and implement database objects" task area: sizing columns and choosing data types, building rowstore and columnstore indexes, enforcing data integrity with PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT constraints, and generating values with SEQUENCE objects.

Concept	Example	Description
NVARCHAR/NCHAR Sizing (Byte-Pairs, Not Characters)	`nchar(10)` stores 10 characters from the common Unicode range (0 to 65,535), but fewer once Supplementary Characters (two byte-pairs each) appear	• In `nchar(n)`/`nvarchar(n)`, `n` sets the string length in byte-pairs, not a guaranteed character count. • `nvarchar(max)` skips the 4,000 byte-pair cap for widely varying, very long text.
Row-Size Limit and Row-Overflow Storage	Two columns, `varchar(7000)` and `varchar(2000)`, each stay under 8,000 bytes alone but together can push a row past the limit	• A row's data plus overhead is capped at 8,060 bytes; the engine dynamically moves the widest overflowing `varchar`/`nvarchar`/`varbinary` column to a ROW_OVERFLOW_DATA page. • LOB types like `varchar(max)` are exempt from the limit.
Clustered Index	`CREATE UNIQUE CLUSTERED INDEX IX_Employee_ID ON dbo.Employee(EmployeeID)` physically sorts the table by `EmployeeID`	• Sorts and stores the table's data rows by the index key; only one clustered index per table, since rows can only be physically ordered one way. • Without one, the table is stored as an unordered heap.
Nonclustered Index with Included Columns (Covering Index)	`CREATE INDEX IX_Address_PostalCode ON Person.Address(PostalCode) INCLUDE (City)` lets a query on `PostalCode` and `City` read the index alone	• A nonclustered index that adds nonkey (included) columns to its leaf level so a query is fully satisfied from the index, avoiding a lookup on the base table. • LOB types can't be key columns but can be included columns.
Clustered Columnstore Index	A 20 GB fact table stored as a clustered columnstore index typically shrinks about 10 times, cutting storage and scan I/O	• Becomes the primary storage for the whole table in a column-wise format; the standard choice for large data warehouse fact and dimension tables scanned by analytic queries. • New rows land in a rowstore deltastore until they're compressed in.
Nonclustered Columnstore Index for Real-Time Analytics	Adding a nonclustered columnstore index to an OLTP orders table lets analysts scan sales trends while order entry keeps using the rowstore clustered index	• A secondary columnstore layered on a rowstore (or memory-optimized) table so analytics run in real time on transactional data, without a separate warehouse. • Best when updates and deletes touch under about 10% of rows; heavier write churn favors a rowstore index instead.
PRIMARY KEY Constraint	`EmployeeID INT PRIMARY KEY CLUSTERED` uniquely identifies each row and, by default, becomes the table's clustered index	• Enforces entity integrity by guaranteeing a unique, non-null value (or combination, for a composite key) per row; one per table, capped at 32 columns and 900 bytes of total key length. • Defaults to CLUSTERED unless a clustered index already exists or NONCLUSTERED is specified.
FOREIGN KEY Constraint and Cascading Referential Actions	`FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID) ON DELETE CASCADE` removes matching order rows whenever the referenced salesperson row is deleted	• Requires every value in the referencing column to already exist in the referenced table's PRIMARY KEY or UNIQUE column, protecting referential integrity. • Default action is NO ACTION (blocks the parent change); CASCADE, SET NULL, and SET DEFAULT are opt-in, and no index is created automatically.
UNIQUE Constraint	`ALTER TABLE Employee ADD CONSTRAINT UQ_Email UNIQUE (Email)` blocks a second row from reusing the same email address	• Enforces uniqueness on a column (or columns) that isn't the primary key, via an automatically created nonclustered unique index. • Unlike PRIMARY KEY, it allows NULL, but only one NULL per column.
CHECK Constraint	`CHECK (salary >= 15000 AND salary <= 100000)` rejects any row where `salary` falls outside that range	• Enforces domain integrity with any Boolean expression evaluated row by row. • A CHECK on a table with zero rows always evaluates TRUE, and CHECK constraints are never validated by DELETE statements.
DEFAULT Constraint	`Status VARCHAR(20) DEFAULT 'Pending'` fills in `'Pending'` automatically whenever an INSERT omits the `Status` value	• Supplies a constant, `NULL`, or scalar-function value only when a column is left out of an explicit INSERT. • Can't be combined with an IDENTITY column, and is dropped automatically if the table is dropped.
WITH CHECK / WITH NOCHECK Constraint Validation	`ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT FK_Cust FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)` adds the FK without checking existing rows	• Controls whether a newly added or re-enabled FOREIGN KEY or CHECK constraint is validated against existing data. • `WITH CHECK` is assumed for a brand-new constraint; the query optimizer ignores a constraint left `WITH NOCHECK` until it's re-verified.
SEQUENCE Object	`CREATE SEQUENCE Test.OrderSeq AS INT START WITH 1 INCREMENT BY 1 CACHE 20` hands out cached integers to callers of `NEXT VALUE FOR`	• A schema-bound object that generates numbers per its own definition (start, increment, min/max, cycle, cache), independent of any table. • Defaults to `NO CYCLE` and `CACHE`; a hard shutdown can lose cached-but-unused values, leaving gaps.
SEQUENCE vs IDENTITY	One `CREATE SEQUENCE Audit.EventCounter` object can feed unique numbers into three separate tables (`ProcessEvents`, `ErrorEvents`, `StartStopEvents`)	• Unlike an IDENTITY column, a SEQUENCE isn't tied to a single table; an application calls `NEXT VALUE FOR` to get a number before the INSERT even runs, and one sequence can be shared across many tables. • Only one IDENTITY column is allowed per table, with no such sharing.

Table 2: Specialized Table Types: In-Memory, Temporal, External, Ledger & Graph

DP-800's Design and implement database objects task covers five specialized table types beyond a standard relational table: memory-optimized tables for high-throughput transactions, system-versioned temporal tables for point-in-time history, external tables for querying data in place, ledger tables for tamper-evident auditing, and graph tables for many-to-many relationships.

Concept	Example	Description
Memory-Optimized Tables	`CREATE TABLE dbo.Orders` `(OrderId INT NOT NULL PRIMARY KEY NONCLUSTERED,` `Total DECIMAL(10,2))` `WITH (MEMORY_OPTIMIZED = ON);`	• Rows live in memory with a durable copy on disk kept only for recovery; fully durable and ACID by default. • Every `CREATE TABLE` for a memory-optimized table needs at least one index, supplied explicitly or through a `PRIMARY KEY`/`UNIQUE` constraint. • Not to be confused with a disk-based table that just has a large buffer pool cache.

The rest of this section is part of the full cheat sheet

Table 3: JSON Columns, JSON Indexes & Table/Index Partitioning

DP-800's Design and implement database objects task also covers storing and indexing JSON documents inside a table, and horizontally partitioning large tables and indexes across boundary values for manageability and performance.

Concept	Example	Description
JSON Data Type	`CREATE TABLE Orders` `(order_id INT,` `order_details JSON NOT NULL` `CHECK (JSON_PATH_EXISTS(order_details, '$.basket') = 1));`	• Stores a document in a native binary format (UTF-8) instead of plain text, giving more efficient reads, writes, and storage than `nvarchar(max)`. • Input must be a JSON object or array; a bare scalar like `'true'` or `'1234.56'` is rejected. • Can't be used as a `CREATE INDEX` key column, only as an included column or inside a filtered index's `WHERE` clause.

The rest of this section is part of the full cheat sheet

Table 4: Views, Functions, Stored Procedures & Triggers

DP-800 Design and develop database solutions, Implement programmability objects: views (including indexed views), scalar and table-valued functions, stored procedures, and triggers, the reusable building blocks of a T-SQL database solution.

Concept	Example	Description
View	`CREATE VIEW Sales.vTotals` `WITH SCHEMABINDING` `AS SELECT ProductID, SUM(Qty) AS Total` `FROM Sales.OrderDetail GROUP BY ProductID`	A stored SELECT that acts as a virtual table. • WITH SCHEMABINDING locks it to its base tables' schema • WITH CHECK OPTION forces writes through the view to still satisfy its WHERE clause

The rest of this section is part of the full cheat sheet

Table 1: Table Design Fundamentals: Data Types, Indexes, Constraints & Sequences

Concept	Example	Description
NVARCHAR/NCHAR Sizing (Byte-Pairs, Not Characters)	`nchar(10)` stores 10 characters from the common Unicode range (0 to 65,535), but fewer once Supplementary Characters (two byte-pairs each) appear	• In `nchar(n)`/`nvarchar(n)`, `n` sets the string length in byte-pairs, not a guaranteed character count. • `nvarchar(max)` skips the 4,000 byte-pair cap for widely varying, very long text.
Row-Size Limit and Row-Overflow Storage	Two columns, `varchar(7000)` and `varchar(2000)`, each stay under 8,000 bytes alone but together can push a row past the limit	• A row's data plus overhead is capped at 8,060 bytes; the engine dynamically moves the widest overflowing `varchar`/`nvarchar`/`varbinary` column to a ROW_OVERFLOW_DATA page. • LOB types like `varchar(max)` are exempt from the limit.
Clustered Index	`CREATE UNIQUE CLUSTERED INDEX IX_Employee_ID ON dbo.Employee(EmployeeID)` physically sorts the table by `EmployeeID`	• Sorts and stores the table's data rows by the index key; only one clustered index per table, since rows can only be physically ordered one way. • Without one, the table is stored as an unordered heap.
Nonclustered Index with Included Columns (Covering Index)	`CREATE INDEX IX_Address_PostalCode ON Person.Address(PostalCode) INCLUDE (City)` lets a query on `PostalCode` and `City` read the index alone	• A nonclustered index that adds nonkey (included) columns to its leaf level so a query is fully satisfied from the index, avoiding a lookup on the base table. • LOB types can't be key columns but can be included columns.
Clustered Columnstore Index	A 20 GB fact table stored as a clustered columnstore index typically shrinks about 10 times, cutting storage and scan I/O	• Becomes the primary storage for the whole table in a column-wise format; the standard choice for large data warehouse fact and dimension tables scanned by analytic queries. • New rows land in a rowstore deltastore until they're compressed in.
Nonclustered Columnstore Index for Real-Time Analytics	Adding a nonclustered columnstore index to an OLTP orders table lets analysts scan sales trends while order entry keeps using the rowstore clustered index	• A secondary columnstore layered on a rowstore (or memory-optimized) table so analytics run in real time on transactional data, without a separate warehouse. • Best when updates and deletes touch under about 10% of rows; heavier write churn favors a rowstore index instead.
PRIMARY KEY Constraint	`EmployeeID INT PRIMARY KEY CLUSTERED` uniquely identifies each row and, by default, becomes the table's clustered index	• Enforces entity integrity by guaranteeing a unique, non-null value (or combination, for a composite key) per row; one per table, capped at 32 columns and 900 bytes of total key length. • Defaults to CLUSTERED unless a clustered index already exists or NONCLUSTERED is specified.
FOREIGN KEY Constraint and Cascading Referential Actions	`FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID) ON DELETE CASCADE` removes matching order rows whenever the referenced salesperson row is deleted	• Requires every value in the referencing column to already exist in the referenced table's PRIMARY KEY or UNIQUE column, protecting referential integrity. • Default action is NO ACTION (blocks the parent change); CASCADE, SET NULL, and SET DEFAULT are opt-in, and no index is created automatically.
UNIQUE Constraint	`ALTER TABLE Employee ADD CONSTRAINT UQ_Email UNIQUE (Email)` blocks a second row from reusing the same email address	• Enforces uniqueness on a column (or columns) that isn't the primary key, via an automatically created nonclustered unique index. • Unlike PRIMARY KEY, it allows NULL, but only one NULL per column.
CHECK Constraint	`CHECK (salary >= 15000 AND salary <= 100000)` rejects any row where `salary` falls outside that range	• Enforces domain integrity with any Boolean expression evaluated row by row. • A CHECK on a table with zero rows always evaluates TRUE, and CHECK constraints are never validated by DELETE statements.
DEFAULT Constraint	`Status VARCHAR(20) DEFAULT 'Pending'` fills in `'Pending'` automatically whenever an INSERT omits the `Status` value	• Supplies a constant, `NULL`, or scalar-function value only when a column is left out of an explicit INSERT. • Can't be combined with an IDENTITY column, and is dropped automatically if the table is dropped.
WITH CHECK / WITH NOCHECK Constraint Validation	`ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT FK_Cust FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)` adds the FK without checking existing rows	• Controls whether a newly added or re-enabled FOREIGN KEY or CHECK constraint is validated against existing data. • `WITH CHECK` is assumed for a brand-new constraint; the query optimizer ignores a constraint left `WITH NOCHECK` until it's re-verified.
SEQUENCE Object	`CREATE SEQUENCE Test.OrderSeq AS INT START WITH 1 INCREMENT BY 1 CACHE 20` hands out cached integers to callers of `NEXT VALUE FOR`	• A schema-bound object that generates numbers per its own definition (start, increment, min/max, cycle, cache), independent of any table. • Defaults to `NO CYCLE` and `CACHE`; a hard shutdown can lose cached-but-unused values, leaving gaps.
SEQUENCE vs IDENTITY	One `CREATE SEQUENCE Audit.EventCounter` object can feed unique numbers into three separate tables (`ProcessEvents`, `ErrorEvents`, `StartStopEvents`)	• Unlike an IDENTITY column, a SEQUENCE isn't tied to a single table; an application calls `NEXT VALUE FOR` to get a number before the INSERT even runs, and one sequence can be shared across many tables. • Only one IDENTITY column is allowed per table, with no such sharing.

Table 2: Specialized Table Types: In-Memory, Temporal, External, Ledger & Graph

Concept	Example	Description
Memory-Optimized Tables	`CREATE TABLE dbo.Orders` `(OrderId INT NOT NULL PRIMARY KEY NONCLUSTERED,` `Total DECIMAL(10,2))` `WITH (MEMORY_OPTIMIZED = ON);`	• Rows live in memory with a durable copy on disk kept only for recovery; fully durable and ACID by default. • Every `CREATE TABLE` for a memory-optimized table needs at least one index, supplied explicitly or through a `PRIMARY KEY`/`UNIQUE` constraint. • Not to be confused with a disk-based table that just has a large buffer pool cache.

Table 3: JSON Columns, JSON Indexes & Table/Index Partitioning

Concept	Example	Description
JSON Data Type	`CREATE TABLE Orders` `(order_id INT,` `order_details JSON NOT NULL` `CHECK (JSON_PATH_EXISTS(order_details, '$.basket') = 1));`	• Stores a document in a native binary format (UTF-8) instead of plain text, giving more efficient reads, writes, and storage than `nvarchar(max)`. • Input must be a JSON object or array; a bare scalar like `'true'` or `'1234.56'` is rejected. • Can't be used as a `CREATE INDEX` key column, only as an included column or inside a filtered index's `WHERE` clause.

Table 4: Views, Functions, Stored Procedures & Triggers

Concept	Example	Description
View	`CREATE VIEW Sales.vTotals` `WITH SCHEMABINDING` `AS SELECT ProductID, SUM(Qty) AS Total` `FROM Sales.OrderDetail GROUP BY ProductID`	A stored SELECT that acts as a virtual table. • WITH SCHEMABINDING locks it to its base tables' schema • WITH CHECK OPTION forces writes through the view to still satisfy its WHERE clause