DP-800 SQL AI Developer Associate: The Complete Study Guide for SQL Server Professionals

---

Microsoft launched the DP-800 SQL AI Developer Associate certification in March 2026 and it is already one of the most sought-after credentials in the SQL Server community. It is the first Microsoft certification that explicitly validates expertise in AI-enabled database development across SQL Server 2025, Azure SQL, and SQL databases in Microsoft Fabric. If you are a SQL Server professional in 2026 this certification is the most direct path to demonstrating that your skills extend into the AI era.

What makes DP-800 different from every other certification on the market is that it is not an AI certification that touches SQL. It is a SQL certification that covers AI. The exam assumes deep T-SQL knowledge and builds AI capabilities on top of it. Every SQL Server DBA who understands query performance, security, indexes, and advanced table types already has the foundation. The new material is the vector search, embeddings, and RAG content that sits on top of that foundation.

This guide covers every priority area with the T-SQL you need to know, the scenarios the exam tests, and links to the SQLYARD deep-dive articles that go further on each topic.

1 What DP-800 Tests and Who It Is For

DP-800 validates expertise in designing and developing AI-enabled database solutions across Microsoft SQL platforms. The target candidate is a SQL Server professional or developer who works with structured data and needs to demonstrate that they can extend that expertise into AI-enabled applications: vector search, semantic retrieval, RAG pipelines, and AI-assisted development tools.

The exam is not primarily an AI certification. It is primarily a SQL certification that includes AI. The largest portion of the exam tests SQL design, security, and performance. AI capabilities make up 25 to 30 percent of the exam weight. SQL professionals who know their T-SQL deeply have a significant advantage over AI developers who are learning SQL for the first time.

The exam covers three SQL platforms: SQL Server 2025 (on-premises and Azure VM), Azure SQL Database and Managed Instance, and SQL databases in Microsoft Fabric. Questions span all three and expect you to know where capabilities differ between platforms.

2 The Three Exam Domains

Domain	Weight	What It Covers
Design and Develop Database Solutions	35 to 40%	Tables including temporal, ledger, graph, in-memory. Indexes. Advanced T-SQL. JSON. Graph queries. GitHub Copilot integration. MCP integration.
Secure, Optimize, and Deploy	35 to 40%	Encryption, RLS, DDM, auditing, managed identity. Query tuning, Query Store, indexes, statistics. CI/CD with SQL Database Projects. Azure integration.
Implement AI Capabilities	25 to 30%	Embeddings, vector search, DiskANN indexes, VECTOR_DISTANCE, hybrid search, RAG workflows, External Models, embedding maintenance with CDC and Change Tracking.

3 Temporal Tables Priority 1

Temporal tables automatically maintain a history of all row changes by storing every version of a row with the time period it was valid. The exam tests when to use them (audit trail, point-in-time queries, slowly changing dimensions) and the T-SQL syntax to query them.

-- Create a temporal table
CREATE TABLE dbo.CustomerProfile (
    CustomerID      INT             NOT NULL PRIMARY KEY CLUSTERED,
    CustomerName    NVARCHAR(200)   NOT NULL,
    CreditLimit     DECIMAL(18,2)   NOT NULL,
    Region          NVARCHAR(100)   NOT NULL,
    -- System-time period columns (required)
    ValidFrom       DATETIME2       GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo         DATETIME2       GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.CustomerProfile_History
));

-- Query the table as it existed at a point in time
SELECT CustomerID, CustomerName, CreditLimit
FROM dbo.CustomerProfile
FOR SYSTEM_TIME AS OF '2026-01-01 00:00:00';

-- Query all versions between two dates
SELECT CustomerID, CustomerName, CreditLimit, ValidFrom, ValidTo
FROM dbo.CustomerProfile
FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-06-01'
WHERE CustomerID = 1042
ORDER BY ValidFrom;

-- Turn off system versioning (required before dropping)
ALTER TABLE dbo.CustomerProfile
SET (SYSTEM_VERSIONING = OFF);

-- KEY EXAM POINTS:
-- Temporal tables track WHEN rows changed automatically
-- No trigger or application code needed
-- FOR SYSTEM_TIME AS OF is the point-in-time query syntax
-- Use for: audit history, slowly changing dimensions, undo capability
-- Do NOT use for: immutable tamper-evident records (use Ledger for that)

4 Ledger Tables Priority 1

Ledger tables provide cryptographically verified, tamper-evident history. Every change is chained with a hash making it mathematically provable that no record was altered after the fact. This is the key distinction from temporal tables: temporal tables track history but that history can be modified by a sufficiently privileged user. Ledger history cannot be altered even by a database administrator.

-- Updatable ledger table: tracks history + cryptographic verification
CREATE TABLE dbo.FinancialTransactions (
    TransactionID   INT             IDENTITY(1,1),
    AccountID       INT             NOT NULL,
    Amount          DECIMAL(18,2)   NOT NULL,
    TransactionDate DATETIME2       NOT NULL DEFAULT SYSDATETIME()
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

-- Append-only ledger table: INSERT only, no UPDATE or DELETE
CREATE TABLE dbo.AuditLog (
    LogID           INT             IDENTITY(1,1),
    EventType       NVARCHAR(100)   NOT NULL,
    EventTime       DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    UserName        NVARCHAR(256)   NOT NULL DEFAULT SUSER_SNAME(),
    Details         NVARCHAR(MAX)
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Verify ledger integrity (confirms no tampering has occurred)
EXEC sys.sp_verify_database_ledger;

-- KEY EXAM POINTS:
-- Ledger = tamper-evident, cryptographically verified
-- Temporal = history tracking, can be modified by SA
-- Append-only ledger: INSERT only, no UPDATE/DELETE allowed
-- Updatable ledger: full DML but all changes are chained and verified
-- Use for: financial records, compliance audit, chain of custody

5 Graph Tables Priority 1

Graph tables store entities (nodes) and relationships (edges) natively in SQL Server. They are designed for queries that traverse relationships: who follows whom, what products are related, which employees report to which managers. The MATCH clause is the graph-specific query syntax the exam tests.

-- Create node and edge tables
CREATE TABLE dbo.Person (
    PersonID    INT         NOT NULL PRIMARY KEY,
    Name        NVARCHAR(200)
) AS NODE;

CREATE TABLE dbo.Follows (
    FollowedDate DATE
) AS EDGE;

-- Insert nodes
INSERT INTO dbo.Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');

-- Insert edges (who follows whom)
INSERT INTO dbo.Follows ($from_id, $to_id, FollowedDate)
VALUES (
    (SELECT $node_id FROM dbo.Person WHERE PersonID = 1),  -- Alice
    (SELECT $node_id FROM dbo.Person WHERE PersonID = 2),  -- follows Bob
    '2026-01-15'
);

-- MATCH clause: find who Alice follows
SELECT
    p1.Name    AS Follower,
    p2.Name    AS Following
FROM dbo.Person p1,
     dbo.Follows f,
     dbo.Person p2
WHERE MATCH(p1-(f)->p2)
AND   p1.Name = 'Alice';

-- KEY EXAM POINTS:
-- AS NODE: stores entities
-- AS EDGE: stores relationships between nodes
-- MATCH: the graph traversal syntax unique to graph tables
-- Use for: social graphs, product recommendations, org hierarchies
-- Not for: simple relational data that doesn't need traversal queries

6 In-Memory OLTP Priority 1

In-Memory OLTP (formerly Hekaton) stores tables entirely in memory with lock-free optimistic concurrency. For extremely high-throughput OLTP workloads it delivers significantly faster INSERT, UPDATE, and DELETE performance than disk-based tables. The exam tests when to use it and its limitations.

-- In-Memory OLTP requires a MEMORY_OPTIMIZED filegroup
ALTER DATABASE YourDatabase
ADD FILEGROUP InMemoryFG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDatabase
ADD FILE (NAME = 'InMemoryFile',
          FILENAME = 'C:\SQL\InMemory\') TO FILEGROUP InMemoryFG;

-- Create a memory-optimized table
CREATE TABLE dbo.HighVolumeOrders (
    OrderID         INT             NOT NULL PRIMARY KEY NONCLUSTERED
                                    HASH WITH (BUCKET_COUNT = 1000000),
    CustomerID      INT             NOT NULL,
    OrderTotal      DECIMAL(18,2)   NOT NULL,
    OrderTime       DATETIME2       NOT NULL DEFAULT SYSDATETIME()
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- DURABILITY options:
-- SCHEMA_AND_DATA: survives restart (fully durable)
-- SCHEMA_ONLY: lost on restart (for temp/staging tables)

-- KEY EXAM POINTS:
-- Memory-optimized tables are entirely in RAM
-- Lock-free: uses optimistic concurrency, no row or page locks
-- Natively compiled stored procedures give maximum throughput
-- Limitations: no foreign keys referencing disk tables, limited data types
-- Use for: high-volume OLTP, session state, shopping carts, real-time scoring
-- Not for: tables with LOB columns, tables needing ALTER TABLE frequently

7 External Tables and JSON Columns Priority 1

External Tables

External tables let SQL Server query data stored outside the database (Azure Blob Storage, Azure Data Lake, other SQL instances) using T-SQL as if the data were a local table. No data movement required.

-- External table pointing to Azure Blob Storage (PolyBase)
CREATE EXTERNAL DATA SOURCE AzureBlobSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://yourstorage.blob.core.windows.net/data'
);

CREATE EXTERNAL FILE FORMAT CSVFormat
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS (FIELD_TERMINATOR = ',', FIRST_ROW = 2));

CREATE EXTERNAL TABLE dbo.ExternalSales (
    SaleID      INT,
    SaleDate    DATE,
    Amount      DECIMAL(18,2)
)
WITH (LOCATION = 'sales/2026/',
      DATA_SOURCE = AzureBlobSource,
      FILE_FORMAT = CSVFormat);

JSON Columns

-- Store and query JSON in SQL Server
CREATE TABLE dbo.ProductCatalog (
    ProductID   INT             PRIMARY KEY,
    ProductName NVARCHAR(200),
    Attributes  NVARCHAR(MAX)   -- JSON stored as NVARCHAR
);

INSERT INTO dbo.ProductCatalog VALUES (
    1, 'Laptop',
    '{"color":"silver","weight_kg":1.4,"features":["USB-C","WiFi6"]}'
);

-- Query JSON values
SELECT
    ProductID,
    ProductName,
    JSON_VALUE(Attributes, '$.color')           AS Color,
    JSON_VALUE(Attributes, '$.weight_kg')       AS WeightKG,
    JSON_QUERY(Attributes, '$.features')        AS Features
FROM dbo.ProductCatalog;

-- Filter on JSON property
SELECT * FROM dbo.ProductCatalog
WHERE JSON_VALUE(Attributes, '$.color') = 'silver';

-- KEY JSON FUNCTIONS:
-- JSON_VALUE: extracts a scalar value
-- JSON_QUERY: extracts an object or array
-- JSON_MODIFY: updates a value within JSON
-- ISJSON: validates that a string is valid JSON
-- OPENJSON: shreds JSON into relational rows

8 Always Encrypted and Dynamic Data Masking Priority 2

Always Encrypted

Always Encrypted encrypts sensitive data at the client application before it reaches SQL Server. The database engine never sees plaintext values. Even a SQL Server administrator with full access cannot read the encrypted column values. This is the key distinction the exam tests: Always Encrypted protects data from privileged database users including DBAs and cloud operators.

-- Always Encrypted: data is encrypted client-side
-- SQL Server stores and returns only ciphertext
-- The engine never decrypts the data

-- Two encryption types:
-- DETERMINISTIC: same plaintext always produces same ciphertext
--   Allows equality comparisons and joins but lower randomness
--   Use for: SSN, national ID, columns you need to search on
-- RANDOMIZED: different ciphertext each time for same plaintext
--   Prevents pattern analysis, higher security
--   Use for: salary, medical data, columns you only retrieve not search

-- Check which columns are Always Encrypted
SELECT
    c.name                          AS ColumnName,
    c.encryption_type_desc,
    c.encryption_algorithm_name,
    k.name                          AS ColumnEncryptionKey
FROM sys.columns c
JOIN sys.column_encryption_keys k
    ON k.column_encryption_key_id = c.column_encryption_key_id
WHERE c.encryption_type IS NOT NULL;

Dynamic Data Masking

Dynamic Data Masking (DDM) hides sensitive data in query results without encrypting the stored data. Unlike Always Encrypted, DDM is a presentation layer control only. Users with UNMASK permission or elevated roles see the real data. It is not a security boundary, it is a convenience for reducing accidental exposure.

-- Add masking to existing columns
ALTER TABLE dbo.Customers
ALTER COLUMN EmailAddress NVARCHAR(200) MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customers
ALTER COLUMN PhoneNumber NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');

ALTER TABLE dbo.Customers
ALTER COLUMN CreditCardNumber NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

-- Grant UNMASK to privileged users who need real data
GRANT UNMASK ON dbo.Customers TO SupportManager;

-- KEY EXAM DISTINCTION:
-- Always Encrypted: real encryption, DBA cannot see plaintext, client-side keys
-- Dynamic Data Masking: masking only, DBA sees real data, no encryption
-- DDM is suitable for: casual exposure prevention, development environments
-- DDM is NOT suitable for: compliance requirements needing true encryption

9 Row-Level Security and Auditing Priority 2

Row-Level Security

-- RLS: filter rows based on the executing user's identity
-- Create a security predicate function
CREATE FUNCTION Security.fn_SalesRegionFilter(@Region NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    SELECT 1 AS fn_result
    WHERE @Region = (
        SELECT Region FROM dbo.SalesRepRegion
        WHERE RepID = USER_NAME()
    )
    OR IS_MEMBER('db_owner') = 1
);

-- Create the security policy
CREATE SECURITY POLICY SalesRegionPolicy
ADD FILTER PREDICATE Security.fn_SalesRegionFilter(Region)
ON dbo.SalesOrders
WITH (STATE = ON);

-- KEY EXAM POINT:
-- FILTER predicate: hides rows from SELECT (reader never sees them)
-- BLOCK predicate: prevents INSERT/UPDATE/DELETE of rows
-- Works transparently: application needs no changes

SQL Server Auditing

-- Create a server audit
CREATE SERVER AUDIT DataAccessAudit
TO FILE (FILEPATH = 'C:\Audits\', MAXSIZE = 100MB)
WITH (ON_FAILURE = CONTINUE);

-- Create a database audit specification
CREATE DATABASE AUDIT SPECIFICATION DataAuditSpec
FOR SERVER AUDIT DataAccessAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.FinancialData BY public)
WITH (STATE = ON);

ALTER SERVER AUDIT DataAccessAudit WITH (STATE = ON);

-- Read audit log
SELECT event_time, action_id, object_name, statement, server_principal_name
FROM sys.fn_get_audit_file('C:\Audits\DataAccessAudit*', NULL, NULL)
ORDER BY event_time DESC;

10 Managed Identity and Passwordless Authentication Priority 2

Managed Identity is the Azure mechanism for authenticating services to each other without storing credentials. Instead of a connection string with a username and password, the Azure resource (App Service, Azure Function, ADF pipeline) uses its Azure AD identity to authenticate to Azure SQL. No password, no secret, no rotation required.

-- Enable a System-Assigned Managed Identity on Azure SQL
-- (done in Azure Portal or CLI, not T-SQL)
-- az sql server update --assign-identity

-- Grant the managed identity access to the database
-- Connect to the database and run:
CREATE USER [your-app-service-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-app-service-name];
ALTER ROLE db_datawriter ADD MEMBER [your-app-service-name];

-- Connection string using Managed Identity (no password):
-- Server=yourserver.database.windows.net;
-- Database=YourDatabase;
-- Authentication=Active Directory Managed Identity;

-- KEY EXAM POINTS:
-- System-assigned: identity tied to the resource lifecycle
-- User-assigned: standalone identity that can be shared across resources
-- Managed Identity eliminates stored credentials in connection strings
-- Required for: production Azure deployments, compliance requirements
-- Passwordless authentication = Managed Identity + Azure AD integration

11 Query Store, Parameter Sniffing, and Execution Plans Priority 3

The performance domain expects scenario-based questions. You will be given a situation and asked to identify the cause and the correct tool. The most common scenarios involve Query Store for plan regression detection, parameter sniffing for inconsistent query performance, and missing indexes for slow full table scans.

-- Scenario: query was fast, now slow, need to identify plan regression
SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    qsp.plan_id,
    qsrs.avg_duration / 1000.0          AS AvgDurationMs,
    qsrs.count_executions,
    qsrs.last_execution_time
FROM sys.query_store_runtime_stats      qsrs
JOIN sys.query_store_plan               qsp  ON qsp.plan_id        = qsrs.plan_id
JOIN sys.query_store_query              qsq  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text         qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE qsrs.last_execution_time >= DATEADD(DAY, -1, GETDATE())
ORDER BY qsrs.avg_duration DESC;

-- Force a previously good plan
EXEC sys.sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 7;

-- Parameter sniffing fix options (know all three for the exam):
-- Option 1: OPTIMIZE FOR UNKNOWN hint
SELECT * FROM dbo.Orders
WHERE OrderDate >= @StartDate
OPTION (OPTIMIZE FOR (@StartDate UNKNOWN));

-- Option 2: RECOMPILE hint (recompiles every execution)
SELECT * FROM dbo.Orders
WHERE OrderDate >= @StartDate
OPTION (RECOMPILE);

-- Option 3: SQL Server 2022+ Parameter Sensitive Plan optimization
-- (automatic, no hint needed, enabled by default on compatibility 160+)

12 Embeddings and Vector Data Types Priority 4

This is the new material that separates DP-800 from every previous SQL Server certification. SQL Server 2025 introduces native VECTOR data type support, meaning vector embeddings can be stored and searched directly in SQL Server without a separate vector database.

An embedding is a numerical representation of text, an image, or any other content as a list of decimal numbers called a vector. Semantically similar content produces vectors that are mathematically close to each other. This property enables semantic search: finding content that means the same thing rather than just content that contains the same words.

-- VECTOR data type in SQL Server 2025
-- Stores a fixed-dimension array of float values

-- Create a table with a VECTOR column for semantic search
CREATE TABLE dbo.KnowledgeBase (
    ArticleID       INT             PRIMARY KEY,
    ArticleTitle    NVARCHAR(500),
    ArticleText     NVARCHAR(MAX),
    -- 1536 dimensions matches text-embedding-3-small from Azure OpenAI
    Embedding       VECTOR(1536)
);

-- Insert a pre-computed embedding (generated by Azure OpenAI)
-- In practice an application or pipeline generates the embedding
-- and inserts it here

-- Create a DiskANN vector index for fast approximate nearest neighbor search
-- DiskANN is in preview on SQL Server 2025 with PREVIEW_FEATURES = ON
CREATE INDEX IX_KnowledgeBase_Embedding
ON dbo.KnowledgeBase (Embedding)
WITH (ONLINE = ON);

-- KEY EXAM POINTS:
-- VECTOR(N): stores N-dimensional float vector
-- Dimensions must match the embedding model used
--   text-embedding-3-small: 1536 dimensions
--   text-embedding-ada-002: 1536 dimensions
--   text-embedding-3-large: 3072 dimensions
-- Embeddings enable SEMANTIC search, not just keyword matching
-- Without embeddings: "car" does not match "automobile"
-- With embeddings: "car" and "automobile" are semantically close

13 VECTOR_DISTANCE and Semantic Search Priority 4

VECTOR_DISTANCE is the SQL Server 2025 function that computes the mathematical distance between two vectors. The distance value indicates semantic similarity: a lower cosine distance means the two pieces of content are more semantically similar.

-- VECTOR_DISTANCE function: compute similarity between vectors
-- distance_metric options:
--   'cosine'     : cosine distance (most common for text, 0=identical, 2=opposite)
--   'euclidean'  : Euclidean (L2) distance
--   'dot'        : dot product (use for normalized vectors)

-- Semantic similarity search: find the 5 most relevant articles
DECLARE @QueryEmbedding VECTOR(1536);

-- In practice: generate this embedding from the user's question
-- via Azure OpenAI external model call (see Section 14)
-- For exam purposes: know that this vector represents the question

SELECT TOP 5
    ArticleID,
    ArticleTitle,
    VECTOR_DISTANCE('cosine', Embedding, @QueryEmbedding)   AS Distance
FROM dbo.KnowledgeBase
WHERE Embedding IS NOT NULL
ORDER BY Distance ASC;    -- LOWER distance = MORE similar

-- Hybrid search: combine vector similarity with keyword search
-- BM25 (keyword) + cosine similarity (semantic) = better results
SELECT TOP 10
    k.ArticleID,
    k.ArticleTitle,
    VECTOR_DISTANCE('cosine', k.Embedding, @QueryEmbedding) AS SemanticDistance,
    ft.RANK                                                  AS KeywordRank
FROM dbo.KnowledgeBase k
JOIN CONTAINSTABLE(dbo.KnowledgeBase, ArticleText,
     'database performance') ft
    ON ft.[KEY] = k.ArticleID
ORDER BY SemanticDistance ASC, KeywordRank DESC;

-- KEY EXAM POINTS:
-- Cosine distance: 0 = identical, higher = less similar
-- ORDER BY Distance ASC to get most similar results first
-- DiskANN index makes nearest-neighbor search fast at scale
-- Without DiskANN: brute-force scan of all vectors (slow for large tables)
-- With DiskANN: approximate nearest neighbor (ANN) search in milliseconds

14 External Models: Calling AI from T-SQL Priority 4

SQL Server 2025 introduces External Models, a mechanism to register Azure OpenAI or other AI model endpoints and call them directly from T-SQL. This enables generating embeddings, making completions, and running other AI operations without leaving the SQL environment.

-- Register an External Model (Azure OpenAI endpoint)
-- Requires PREVIEW_FEATURES = ON and a database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword2026!';

CREATE DATABASE SCOPED CREDENTIAL AzureOpenAICredential
WITH IDENTITY = 'HTTPEndpointHeaders',
SECRET = '{"api-key":"your-azure-openai-api-key"}';

-- Create the external model reference
CREATE EXTERNAL MODEL EmbeddingModel
WITH (
    LOCATION = 'https://your-openai.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings',
    API_FORMAT = 'Azure_OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    CREDENTIAL = AzureOpenAICredential
);

-- Generate an embedding from T-SQL
SELECT AI_GENERATE_EMBEDDINGS('What is the best index for a range query?'
    USING EmbeddingModel)
AS Embedding;

-- Practical use: generate and store embedding for new article
DECLARE @NewText    NVARCHAR(MAX) = 'Index tuning for range queries...';
DECLARE @Embedding  VECTOR(1536);

SELECT @Embedding = AI_GENERATE_EMBEDDINGS(@NewText USING EmbeddingModel);

INSERT INTO dbo.KnowledgeBase (ArticleTitle, ArticleText, Embedding)
VALUES ('Index Tuning', @NewText, @Embedding);

-- KEY EXAM POINTS:
-- External Models connect SQL Server to AI model endpoints
-- Enables embedding generation without leaving T-SQL
-- Also supports text completion and chat models
-- Microsoft Foundry is a valid embedding maintenance method per exam blueprint
-- Alternative embedding maintenance: CDC, Change Tracking, CES (Fabric)

15 RAG: The Complete Workflow Priority 4

Retrieval-Augmented Generation is the AI architecture pattern that will appear on the exam. Know the five steps in order and be able to identify which step a given problem or failure belongs to.

-- RAG WORKFLOW: 5 Steps (memorize this for the exam)

-- Step 1: USER ASKS A QUESTION
-- "What is the maximum compression savings for a write-heavy table?"

-- Step 2: EMBEDDING GENERATED FROM THE QUESTION
DECLARE @QuestionEmbedding VECTOR(1536);
SELECT @QuestionEmbedding =
    AI_GENERATE_EMBEDDINGS(
        'What is the maximum compression savings for a write-heavy table?'
        USING EmbeddingModel);

-- Step 3: SIMILAR DOCUMENTS RETRIEVED FROM SQL SERVER VECTOR STORE
SELECT TOP 5
    ArticleTitle,
    ArticleText,
    VECTOR_DISTANCE('cosine', Embedding, @QuestionEmbedding) AS Distance
FROM dbo.KnowledgeBase
ORDER BY Distance ASC;
-- Returns: compression articles, performance tuning content,
-- index optimization guides relevant to the question

-- Step 4: LLM RECEIVES CONTEXT + ORIGINAL QUESTION
-- The application assembles a prompt:
-- "Using only the following context, answer the question.
--  Context: [top 5 retrieved articles]
--  Question: What is the maximum compression savings..."
-- This prompt is sent to the LLM (GPT-4, etc.)

-- Step 5: ANSWER RETURNED
-- LLM generates answer grounded in the retrieved SQL Server documentation
-- rather than hallucinating from general training data

-- KEY EXAM POINTS FOR RAG:
-- RAG grounds LLM responses in YOUR data
-- Without RAG: LLM answers from training data only (may be wrong or outdated)
-- With RAG: LLM answers from your retrieved context (grounded, current)
-- The quality of retrieved context = quality of the answer
-- Bad embeddings or bad vector search = wrong context = wrong answer
-- This is why schema quality and documentation matter (see SQLYARD article)

16 Data API Builder and MCP Endpoints Priority 5

Data API Builder (DAB) is a Microsoft open-source tool that automatically generates REST and GraphQL API endpoints from SQL Server, Azure SQL, and Fabric SQL objects. No custom API code required. You configure a JSON file mapping tables, views, and stored procedures to API endpoints and DAB handles the rest.

-- Data API Builder configuration example (dab-config.json)
-- This exposes the Orders table as both REST and GraphQL endpoints
{
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('DATABASE_CONNECTION_STRING')"
  },
  "entities": {
    "Order": {
      "source": "dbo.Orders",
      "permissions": [
        {
          "role": "authenticated",
          "actions": ["read"]
        }
      ],
      "rest": {
        "enabled": true,
        "path": "/orders"
      },
      "graphql": {
        "enabled": true,
        "type": { "singular": "Order", "plural": "Orders" }
      }
    }
  }
}

-- After configuration:
-- REST endpoint:    GET /api/orders
-- REST filter:      GET /api/orders?$filter=CustomerID eq 1042
-- GraphQL endpoint: POST /graphql
-- Query:            { orders(filter: {CustomerID: {eq: 1042}}) { items { OrderID Amount } } }

-- MCP endpoint: DAB can also expose SQL objects as MCP tools
-- This is the connection layer that Foundry AI agents use
-- to query SQL Server through a governed, documented API

-- KEY EXAM POINTS:
-- DAB eliminates hand-written REST/GraphQL API code for SQL objects
-- Supports: tables, views, stored procedures as API endpoints
-- Security: integrates with Azure AD / Entra ID and role-based access
-- MCP server endpoints are explicitly tested in DP-800 blueprint
-- DAB can serve as the MCP server layer between AI agents and SQL Server

Exam Strategy and Final Tips

The exam uses scenario-based questions heavily. You will be given a business requirement and asked which feature to use, which T-SQL to write, or which problem a given implementation has. Here are the most important decision rules to have ready:

• Temporal vs Ledger: If the question involves audit history that DBAs might need to modify, use temporal tables. If the question requires tamper-evident proof that records were not changed, use ledger tables. This distinction appears frequently.
• Always Encrypted vs DDM: If the question says DBAs must not see the data, Always Encrypted is the answer. If the question says hide data from some users but admins can see it, Dynamic Data Masking is the answer.
• When to use DiskANN index: Any question about vector similarity search at scale requires a DiskANN index. Without it SQL Server does a brute-force vector scan that is slow for large tables.
• Cosine distance ordering: ORDER BY Distance ASC for most similar. Lower distance equals more similar. Never ORDER BY DESC for a nearest-neighbor search.
• RAG step identification: If a question describes incorrect answers from an AI assistant, the failure is almost always in Step 3 (retrieval returning wrong context) or in embedding quality at Step 2. Know all five steps.
• Query Store vs Execution Plan: Query Store is for historical plan comparison and regression detection. Execution plans are for current query analysis. If the question involves “was this faster before,” Query Store is the answer.

References and Further Reading

• Microsoft Learn: Official DP-800 Study Guide (updated March 12, 2026)
• Microsoft Docs: VECTOR Data Type in SQL Server 2025
• Microsoft Docs: VECTOR_DISTANCE Function
• Microsoft Docs: Data API Builder Overview
• Microsoft Docs: Temporal Tables
• Microsoft Docs: Ledger Tables
• Microsoft Docs: Graph Tables in SQL Server
• Microsoft Docs: In-Memory OLTP Overview
• SQLYARD: Query Store Complete Screen-by-Screen Guide
• SQLYARD: SQL Server Index Tuning Guide
• SQLYARD: LLM, RAG, Agents, and MCP in Plain English
• SQLYARD: MCP and SQL Server: What Every DBA Needs to Know
• SQLYARD: Microsoft Foundry for SQL Server Professionals
• SQLYARD: From Rows to Reasoning: Designing SQL Server for AI