DP-800 Developing AI-Enabled Database Solutions Study Guide

Independent AI-assisted resource — HUMAN REVIEW PENDING. The October 28, 2026 team refresh reconciles the complete guide and detailed objectives with current public exam, product and learning evidence. See the current group report and sources-and-objectives record. Source conflicts and unexecuted exercises remain explicit. This is not human review or live product validation. The official blueprint is authoritative.

Study guide at a glance

Start here

Report an issue with DP-800

Blueprint checked2026-10-28

Review stateReview required

Guide-only active reading2 hr–3 hr

Prerequisites: T-SQL development, relational and JSON design, Microsoft SQL platforms, security, performance tuning, Git and CI/CD, APIs and change processing, plus model, embedding, vector search, and RAG fundamentals.

Exam essentialsRead the objective map, key distinctions, and readiness checklist.

Deep understandingWork through every domain, including decisions, failure modes, and related items.

Hands-on labsBegin with Lab 1: relational, JSON, temporal and graph design, then use the remaining labs to produce evidence you can inspect and explain.

Official objective domains

• Design and develop database solutions35–40%
• Secure, optimize, and deploy database solutions35–40%
• Implement AI capabilities in database solutions25–30%

The active-reading range uses 90–130 words per minute for technical material. Labs, troubleshooting, note-taking, spaced review, and prerequisite gaps add time; it is not a total preparation promise.

Current baseline: Skills measured as of October 19, 2026.
Blueprint reconciliation (October 28, 2026): The October 19, 2026 English outline is the accepted current scope. The revised outline uses columnstore and Copilot naming and ANN versus KNN terminology. CES remains in the general data-change objective but is omitted from the embedding-maintenance-method bullet; these are separate scope observations. Domain weights are unchanged. Historical objective bytes and dated reviews are preserved. Recheck localized appointment timing separately.
Lifecycle status: Active; no retirement or replacement was announced on the official pages checked.
Exam page: Microsoft Certified: SQL AI Developer Associate · 120-minute assessment · ten listed languages: English, Japanese, Simplified and Traditional Chinese, Korean, German, French, Italian, Brazilian Portuguese and Spanish. Check language-specific revision timing when booking.
Official course: DP-800T00 Develop AI-enabled database solutions · three instructor-led days.
Practice: Microsoft's free Practice Assessment is on AI Skills Navigator; sign-in is required to launch it.

How to use this guide

DP-800 tests database development as a complete system. Trace each design through these chains:

requirement -> platform capability -> schema/object -> query/API -> test evidence
principal -> authentication -> permission/policy -> protected operation -> audit evidence
schema commit -> database project -> build/test -> artifact -> controlled deployment -> drift check
source text -> chunk -> embedding model/version -> vector -> index/search -> retrieval -> grounded answer
symptom -> query/request ID -> plan/wait/telemetry -> root cause -> correction -> measured result

Practice on at least two supported SQL platforms where possible. Keep the database engine and compatibility level, service tier, preview/GA status, schema and sample data, execution plans, Query Store evidence, API requests, identity/grant tests, deployment artifact, model/deployment name, embedding dimensions, search metrics and cleanup notes. Product support varies among SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric. Recheck the linked documentation before implementing or booking.

About related items: A Related item: callout adds prerequisite, architectural, migration, security, operational, or adjacent context that makes the objective easier to understand. It is useful supporting knowledge, not a claim that the item appears verbatim in Microsoft's published exam objectives.

October terminology and practice adjustment

For the October 19 blueprint, explain KNN as selecting the k nearest neighbors and distinguish an exact distance-based baseline from an approximate indexed search. The earlier ENN explanation remains useful for measuring recall; a renamed objective does not imply that every KNN implementation uses the same algorithm. Recheck the platform-specific VECTOR_SEARCH documentation before choosing syntax or an index. Extend the vector lab by reporting k, metric, filters, recall against exact results, and latency together. Continue studying CES alongside CDC, Change Tracking, Functions and Logic Apps: the currently published October objective explicitly includes it.

Objective map

Published domain	Weight	System question
Design and develop database solutions	35–40%	Can you choose the right relational, JSON, temporal, graph and programmable objects, then write correct modern T-SQL?
Secure, optimize, and deploy database solutions	35–40%	Can you prove least privilege, diagnose performance, deliver schema safely, expose governed APIs and react to change?
Implement AI capabilities in database solutions	25–30%	Can you generate and maintain embeddings, choose exact/approximate/hybrid retrieval and build a secure grounded application?

---

1. Build the operating model

Distinguish the platforms before selecting a feature

Platform	Strong fit	Capability questions to verify
Azure SQL Database	Managed cloud database with database-scoped scaling, identity, monitoring and continuously delivered engine features	service tier/hardware, networking, regional availability, compatibility level, preview state, vector/index/AI support
Azure SQL Managed Instance	Managed instance compatibility for migrations and instance-scoped capabilities	update policy, SQL Server 2025 feature availability, networking, instance/database permissions, external connectivity
SQL Server 2025	Customer-managed engine for on-premises, VM, edge or hybrid requirements	edition, cumulative update, compatibility level 170, OS/network, feature enablement, patching and outbound endpoint policy
SQL database in Microsoft Fabric	SaaS operational database integrated with Fabric, OneLake and source control	current limitations, capacity, mirroring/analytics endpoint behavior, source-control scope, networking and feature parity

Azure SQL shares a common engine, but deployment model capabilities differ. Use the current Azure SQL features comparison, SQL database in Fabric overview, Fabric SQL limitations, and SQL Server 2025 release notes. Do not transfer syntax or a preview label from one platform to another without checking the Applies to section.

VERIFY CURRENT: SQL AI features evolve faster than conventional relational features. Record the platform, region, engine/build, database compatibility level and documentation date with every experiment. Treat preview features as changeable and unsuitable for a production dependency unless the organization accepts their support terms.

Related item: Fabric source control stores database object definitions, not the table data and not every database-level configuration. Its SQL analytics endpoint is a separate read-only analytical surface. A repository sync is therefore neither a backup nor a complete disaster-recovery plan. Current Fabric limitations also exclude CDC and Always Encrypted; do not select the Fabric database for labs that require those features. Mirroring limits state that source RLS, masking and sensitivity labels do not propagate into OneLake. Secure the replicated read path separately. Clustered columnstore creation has mirroring restrictions, and such tables are not mirrored even where creating the index is supported.

Translate a requirement into evidence

For each feature, be able to state:

1. Requirement: data shape, transaction semantics, query pattern, security boundary, latency, recovery and cost.
2. Decision: platform, object type, data type, key/index, isolation, interface and AI/search mechanism.
3. Implementation: versioned DDL/code/configuration and a non-personal identity.
4. Evidence: positive and negative tests, execution plan/telemetry, reconciliation, audit and deployment record.
5. Failure behavior: timeout, duplicate, stale vector, policy denial, external-model failure, drift or rollback path.

This keeps an attractive demo from becoming an unsupported production design.

---

2. Design and develop database solutions (35–40%)

Design tables from workload and integrity requirements

Start with grain: one sentence defining what a row represents. Then choose business key, stable primary key, data types, nullability, constraints, temporal/history behavior, write pattern, expected row count/growth and dominant predicates. Microsoft's SQL Server index design guide is a useful decision reference.

Choose data types and row shape

• Use the narrowest type that represents the entire valid domain without lossy conversion. Prefer exact numeric types for money-like values when rounding rules require them.
• Define character length from the contract; nvarchar(max) is not a harmless default. Large-object values can move off-row and restrict indexing/operators.
• Use datetime2 for precision and datetimeoffset when the offset must be preserved; store a clear canonical time convention.
• Use a native relational column for values with strong type, constraint, join, update or indexing needs. Use JSON for genuinely variable aggregates or externally shaped payloads, not to avoid modeling.
• Define nullable because absence is meaningful, not because the source sometimes omits bad data. Distinguish absent, unknown, empty and zero.
• Consider compression, row width, update frequency and hot-page behavior before choosing a monotonically increasing clustered key.

Choose rowstore, columnstore and memory-optimized structures

Structure	Prefer when	Watch for
Clustered/nonclustered rowstore	selective lookups, ordered ranges, transactional point writes	excess indexes amplify writes/storage; key order must match useful predicates
Clustered columnstore	large scans, aggregation, compression and analytical fact-like workloads	small updates/deletes, rowgroup quality, ordered access and operational latency
Nonclustered columnstore	operational rowstore plus selected analytical acceleration	maintenance/write overhead and supported object/features
Memory-optimized table	measured latch/locking or high-throughput low-latency need and supported platform	durability, indexes, memory sizing, T-SQL/platform limitations and operations

Learn rowgroup, segment elimination, delta store and batch-mode implications from the columnstore index overview. An index is justified by a measured workload and plan, not by a rule that every foreign key or predicate needs its own index.

Related item: An embedding vector is usually updated less often than its business row. Separating source content, chunk, model/version and vector metadata can prevent ordinary transactional updates from rewriting a wide vector-bearing row.

Enforce domain and relationship integrity

• A PRIMARY KEY identifies each row and implies uniqueness plus non-nullability. Decide clustered placement separately where the platform permits.
• A UNIQUE constraint protects an alternate business key. Define how nullable values should behave.
• A FOREIGN KEY prevents orphaned relationships but does not automatically create the supporting child-side performance index.
• A CHECK constraint protects a row-local rule such as a range or allowed relationship among columns.
• A DEFAULT supplies a value only when an insert omits the column; it does not validate explicit input or repair existing rows.
• A sequence generates numbers independently of a specific table and can serve multiple consumers; numbers can have gaps. An identity property is table-column scoped and can also have gaps. See sequence numbers.

Use trusted constraints so the optimizer can reason from them. Treat disable/re-enable and WITH NOCHECK as correctness and performance decisions. Validate the existing population before declaring a constraint trusted.

Partition for management, not automatic speed

Partitioning maps rows through a partition function and scheme. It is useful for large sliding-window management, aligned loading/switching, retention and some query patterns. It does not guarantee faster queries: a predicate must support elimination, indexes often need alignment, and too many partitions increase metadata/maintenance cost. Read partitioned tables and indexes.

Define:

• the partition key and boundary semantics (RANGE LEFT versus RANGE RIGHT);
• future empty partition creation and oldest partition archival/removal;
• aligned indexes and uniqueness-key implications;
• proof of elimination from actual plans and logical reads;
• statistics, compression and maintenance per partition.

Related item: Partitioning is primarily a data-lifecycle and manageability tool. A nonpartitioned table with the right index can outperform a badly partitioned table for selective queries.

Select specialized table models deliberately

Temporal tables

A system-versioned temporal table maintains current rows plus a history table using period columns. FOR SYSTEM_TIME supports as-of and interval queries. It fits audit-like reconstruction and point-in-time business analysis, but is not a substitute for database backups or a complete actor/reason audit. Plan history retention, indexes, schema changes, consistency checks and privacy deletion. See temporal tables.

Ledger tables

Ledger adds cryptographic evidence so tampering with database data can be detected. Updatable ledger tables retain history; append-only ledger tables reject updates/deletes. Digest storage and later verification are essential—ledger does not encrypt data, authorize users, prove input truth or replace backup/HA. Use the ledger overview.

Graph tables

Node tables represent entities and edge tables relationships; pseudo-columns identify graph elements. MATCH expresses topology patterns. Graph fits variable-depth or relationship-centric traversal where a join-table model becomes awkward, while ordinary relational tables remain better for most fixed relationships and constraints. Learn SQL graph and its architecture.

SELECT p2.Name
FROM Person AS p1, Follows, Person AS p2
WHERE MATCH(p1-(Follows)->p2)
  AND p1.PersonId = @person_id;

Fabric note: Graph objects may be supported in the operational SQL database while node and edge tables are not mirrored to OneLake. Verify the current Fabric limitation before using graph as an analytics integration boundary.

External tables

An external table exposes data that remains outside the database. It fits federation or external lake/object access when supported, but remote availability, credentials, predicate pushdown, transaction consistency, statistics and egress remain part of the design. Check the exact platform/data-source rules in CREATE EXTERNAL TABLE.

Design native JSON storage and access

SQL can store, validate, construct, query, modify and index JSON. Use the JSON data documentation as the current support matrix.

CREATE TABLE dbo.OrderEvent
(
    OrderEventId bigint IDENTITY PRIMARY KEY,
    OccurredAt datetime2(3) NOT NULL,
    Payload json NOT NULL
);

SELECT OrderEventId,
       JSON_VALUE(Payload, '$.customer.id') AS CustomerId
FROM dbo.OrderEvent
WHERE JSON_CONTAINS(Payload, '"expedited"', '$.labels') = 1;

Understand these families:

• JSON_VALUE returns one scalar; JSON_QUERY returns an object/array; path strict/lax behavior changes missing/type-error handling.
• OPENJSON turns an object/array into rows; an explicit WITH schema is safer and more efficient than implicit key/value typing for production transformations.
• JSON_OBJECT, JSON_ARRAY and JSON_ARRAYAGG construct JSON while handling escaping and null behavior intentionally.
• JSON_MODIFY updates a property in text-based JSON; native json behavior and modify syntax/support must be checked by platform.
• JSON_CONTAINS tests containment where currently supported.
• CREATE JSON INDEX accelerates selected paths but adds storage/write cost and has platform/preview requirements. Use the current CREATE JSON INDEX page.

VERIFY CURRENT: The native json type, JSON indexes and newer functions had different GA/preview states across Azure SQL Database, Managed Instance, SQL Server 2025 and Fabric on the checked date. Do not say “SQL Server supports it” without naming the deployment target.

Related item: Persisted computed columns over stable JSON scalar paths plus normal indexes can be a useful alternative where a native JSON index is unavailable. Compare correctness, storage and plan evidence rather than assuming the newest feature is required.

Create reusable programmable objects

Views and functions

• A view provides a reusable relational interface and can limit exposed columns/rows. It stores a query, not data, unless indexed-view requirements are deliberately met.
• A scalar function returns one value. It is convenient but can create row-by-row cost depending on inlining/eligibility and the query.
• An inline table-valued function behaves like a parameterized relational expression and is often easier for the optimizer to integrate.
• A multi-statement table-valued function builds and returns a table variable; understand estimation and performance implications.

Use schema binding only when its dependency constraints and benefits fit. Grant access to a stable view/procedure interface rather than broad base-table rights where ownership chaining and dynamic SQL behavior are understood.

Stored procedures and triggers

Stored procedures encapsulate multi-statement operations, parameter handling, transactions and controlled permissions. Use strongly typed parameters, SET NOCOUNT ON, explicit transaction boundaries, safe dynamic SQL with sp_executesql, predictable result/error contracts and observability.

Triggers run synchronously with the data change. They must handle multirow inserted/deleted sets, avoid hidden recursive/cascading behavior and remain short. Prefer constraints for row-local integrity and asynchronous change mechanisms for integrations. A trigger failure rolls back the caller's transaction.

CREATE OR ALTER PROCEDURE dbo.TransferBalance
    @FromAccount bigint,
    @ToAccount bigint,
    @Amount decimal(19,4)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;
        -- Validate and update both accounts with an appropriate concurrency design.
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Review TRY...CATCH. Preserve the original error with THROW unless the interface deliberately maps it. A catch block does not automatically roll back an open or uncommittable transaction.

Write set-based analytical and hierarchical queries

Common table expressions

A CTE names a query expression for one statement. It improves readability and enables recursion; it does not automatically materialize/calculate once. A recursive CTE needs anchor and recursive members, termination, cycle-aware design and a defensible MAXRECURSION. See CTEs.

Window functions

Window functions compute across related rows without collapsing them like GROUP BY. Be able to use partition, order and frame deliberately:

SELECT CustomerId,
       OrderDate,
       Amount,
       ROW_NUMBER() OVER
           (PARTITION BY CustomerId ORDER BY OrderDate, OrderId) AS SequenceNumber,
       SUM(Amount) OVER
           (PARTITION BY CustomerId ORDER BY OrderDate, OrderId
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningAmount
FROM dbo.CustomerOrder;

Tie breakers make ranking deterministic. ROWS and RANGE frames differ. Learn the OVER clause and inspect sorts, memory grants and supporting index order.

Correlated subqueries

A correlated subquery references the outer row. It can express EXISTS/NOT EXISTS clearly, but may execute as an inefficient repeated operation if the optimizer cannot transform it. Compare a semijoin/antijoin, window or pre-aggregation. Prefer NOT EXISTS over NOT IN when nullable input could produce three-valued-logic surprises.

Use regular-expression and fuzzy matching functions

Current SQL platforms add functions for validation, extraction, replacement, splitting and matching. Learn the function named in the objective—LIKE, regex replace/substr/instr/count/matches/split-to-table—and verify the exact name/signature for the target. The regular expressions overview documents RE2-style behavior and platform requirements.

• Use LIKE for simple wildcard patterns and indexes where a useful anchored predicate is possible.
• Use regex for structural patterns, not as a substitute for a proper type or trusted constraint.
• Bound expensive input; regex behavior does not simply inherit SQL collation semantics, and documented large-object limits apply.
• Treat user-supplied patterns as untrusted workload input; enforce length, timeout/resource controls and allowed use cases.

Fuzzy functions such as EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY and JARO_WINKLER_DISTANCE rank lexical similarity; they do not establish identity. Use fuzzy string matching, normalize data, generate a bounded candidate set, set domain-tested thresholds and route uncertain matches for review.

Confirmed — VERIFY CURRENT (October 28, 2026): The fuzzy-matching overview now says supported Windows and BIN/BIN2 collations determine comparison behavior, including case/accent sensitivity and linguistic rules. Nonbinary SQL_* collations are unsupported; apply a supported Windows collation to the input expressions when broader changes are unsuitable. SQL Server 2025 fuzzy matching remains preview. Keep regex behavior separate and recheck the exact platform/update policy.

Proposed practice extension — not executed: Lab 2: record database/argument collation, compare a bounded synthetic case/accent pair under supported Windows collations, and record unsupported SQL_* cases.

Answer checkpoint: Does fuzzy comparison ignore the input collation? No: current documentation defines supported Windows/binary collation behavior and excludes nonbinary SQL_* collations.

Related item: Entity resolution is a decision system, not a distance function. Preserve which fields, normalization, algorithm/version, threshold and reviewer produced a match so false merges can be reversed.

Worked queries — recursion, structure and approximate names

Original example — proposed; service/tenant execution not performed.

VERIFY CURRENT: use a supported engine/build and compatibility level; fuzzy functions can require preview enablement. These independent examples use synthetic literals and are not executed results.

;WITH Levels AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Levels WHERE n < 3
)
SELECT n FROM Levels ORDER BY n OPTION (MAXRECURSION 3);

SELECT REGEXP_SUBSTR('SKU-42', '[0-9]+') AS Digits,
       REGEXP_REPLACE('SKU-42', '[0-9]+', 'XX') AS Redacted;

SELECT EDIT_DISTANCE(
           N'stone' COLLATE Latin1_General_100_CI_AS,
           N'stove' COLLATE Latin1_General_100_CI_AS) AS Changes,
       EDIT_DISTANCE_SIMILARITY(
           N'same' COLLATE Latin1_General_100_CI_AS,
           N'same' COLLATE Latin1_General_100_CI_AS) AS SameScore,
       JARO_WINKLER_DISTANCE(
           N'same' COLLATE Latin1_General_100_CI_AS,
           N'same' COLLATE Latin1_General_100_CI_AS) AS SameDistance;

Predict ordered recursion rows 1, 2, 3; extracted text 42 and SKU-XX; one substitution, identical-string similarity 100 and distance 0. Remove the recursive stop condition only as a paper exercise and explain why a bounded recursion limit matters. A regex match validates a chosen structure; a short edit distance proposes a candidate, without proving two customer records represent one person.

Question: Does edit distance 1 authorize merging two customer records?

Answer: No. It selects candidates; corroborate identifiers, evaluate false merges, retain provenance and require the appropriate review.

CTEs · regular expressions overview · fuzzy string matching

Use AI-assisted development without outsourcing responsibility

GitHub Copilot can explain, generate, refactor and help troubleshoot SQL in supported tools. Copilot in Fabric adds product-integrated assistance. Start with the GitHub Copilot SQL overview and Copilot in Fabric FAQ.

For every generated change:

1. give only the minimum approved schema/context;
2. state platform/version, correctness, security and performance constraints;
3. review identifiers, joins, null behavior, transaction/isolation, injection, destructive DDL/DML and permissions;
4. execute first with synthetic or masked data in an isolated environment;
5. inspect actual plan, results and tests; commit only reviewed code;
6. never paste secrets, production personal data or unapproved proprietary content into a prompt.

Instruction files and MCP endpoints

Repository/database instruction files can supply conventions and architecture context. They are versioned configuration, not a security boundary. Review changes to them because untrusted instructions can redirect an assistant. Current references include database instructions in SSMS, MCP servers in SSMS, Fabric data warehouse MCP server, and Fabric SQL database skills.

An MCP server exposes tools/resources to an AI client. The client still acts through a configured identity and its database permissions. Apply least privilege, separate read and write capabilities, confirm impactful actions, restrict network/tool configuration, log calls and assume database metadata/results passed to the model are sensitive.

Related item: Prompt injection can arrive through an instruction file, issue, documentation row or retrieved database content—not only the user's chat message. Treat model output and tool arguments as untrusted until policy and code validate them.

Worked configuration — client, model and tool boundaries

Original example — proposed; service/tenant execution not performed.

VERIFY CURRENT: record client/version, signed-in account, selected model when the client exposes a picker, task mode, enabled tools, server transport and identity. A model selection does not grant database permission.

For GitHub Copilot with MSSQL in VS Code, install the supported SQL/Copilot components, sign in with the entitled account, connect only the disposable database, and choose Ask for explanation or Agent for reviewed tool use. In SSMS, MCP requires Agent mode. Add the approved HTTP endpoint, or a reviewed stdio command, through the Tools picker; authenticate and explicitly enable only the required tools because newly added MCP tools are disabled by default. Use a SQL/DAB endpoint configured by its administrator; a server address alone does not define its permission model.

For Fabric SQL database, scope the Copilot tenant settings to an approved pilot security group, check delegated capacity settings if used, assign the workspace to an eligible enabled capacity and grant the pilot workspace access; then open Copilot in the SQL query editor. Processing-geography and subprocessor settings require an approved organizational decision, not an automatic enable-all lab step. Treat this as a different integrated experience from a general GitHub Copilot chat session. For lakehouse metadata, a supported choice is the local Fabric MCP extension with OneLake tools: authenticate using the approved Azure identity, select the permitted workspace/lakehouse, and request its table definitions. The remote Fabric Core server at https://api.fabric.microsoft.com/v1/mcp/core provides workspace/item management; it is not an interchangeable SQL query endpoint.

Create evidence rows for SQL tool, Fabric SQL assistant and lakehouse discovery. Record actual subject/resource, allowed read, denied unrelated access, returned schema and proposed tool action before approving anything. Disable unused tools and remove test configuration on completion.

Question: Does connecting Fabric Core MCP prove the client can run SQL against any lakehouse?

Answer: No. Core metadata/management tools, lakehouse OneLake tools and SQL execution have different endpoints, actions and authorization.

GitHub Copilot SQL overview · MCP servers in SSMS · Get started with Fabric Core MCP Server · Use the local Fabric MCP server with OneLake · Copilot in Fabric FAQ · Enable and configure Copilot in Microsoft Fabric

---

3. Secure, optimize, and deploy database solutions (35–40%)

Start security with identity and data flow

Draw the path before granting access:

human/workload -> Microsoft Entra authentication -> server/database principal
principal -> role/direct grant -> schema/object/row/column/endpoint
application -> managed identity/token -> database/API/model endpoint
operation -> audit/diagnostic record -> protected monitoring workspace -> alert/review

Prefer Microsoft Entra identities and passwordless connections for Azure-hosted workloads. A managed identity removes application-managed credential rotation, but still needs a database user, least-privileged grants and controlled Azure resource assignment. Review Microsoft Entra authentication for Azure SQL.

Use database roles for stable job functions, schemas as permission boundaries and explicit grants/denies only with a documented reason. Ownership and ownership chaining can allow access without a direct base-object grant; dynamic SQL may break that chain. Test as the actual principal, including negative operations.

Protect data at the correct layer

Control	Protects	Does not by itself protect
TLS	data in transit between endpoints	authorized endpoint use, data after decryption
TDE/storage encryption	database/data files, backups and storage media at rest	privileged query access or application result sets
Always Encrypted	selected values from database engine/operators when keys remain client-side	unsupported computations, compromised client/key store, access-pattern metadata
Dynamic data masking	casual exposure in query results to non-exempt users	stored data, inference/exfiltration through allowed queries, privileged users
Row-level security	rows returned/modified according to a predicate	columns within permitted rows, object discovery or a badly written predicate
Object permissions	allowed actions on securables	row/column nuance unless paired with policy/view/mask
Auditing	evidence of configured events	preventive authorization or proof that every required event was configured/retained

The SQL encryption overview separates encryption boundaries. Use Always Encrypted when the threat model requires protected columns to remain encrypted from the database system/administrators. Plan column master/encryption key ownership, enclave needs, driver parameterization, supported operations, rotation and recovery.

Column-level encryption with database cryptographic functions/keys encrypts selected values while the database engine can decrypt them for an authorized caller. It can reduce plaintext exposure at rest or through broad table reads, but privileged key/control access remains in the database trust boundary. Protect the certificate/asymmetric key that secures a symmetric key, open keys only for the operation, deny unnecessary key permissions, back up keys/certificates and test rotation/restoration. Choose it instead of Always Encrypted only when server-side decrypt/processing and that threat boundary are acceptable.

Dynamic data masking

Dynamic data masking changes result presentation for users lacking UNMASK; data remains unchanged. Apply default, email, random or partial masks as supported, then test direct selection, joins, filters, aggregates, inference and privileged paths. It is a least-exposure convenience, not an adversarial data-protection boundary. See dynamic data masking.

Row-level security

RLS binds an inline table-valued security predicate to a table through a security policy. A filter predicate hides rows; a block predicate rejects prohibited writes. Keep predicates schema-bound, deterministic and efficient; index the columns used to resolve tenant/user scope.

CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_tenant_access(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS allowed
    WHERE @TenantId = TRY_CONVERT(int, SESSION_CONTEXT(N'tenant_id'));
GO
CREATE SECURITY POLICY Security.TenantPolicy
ADD FILTER PREDICATE Security.fn_tenant_access(TenantId) ON dbo.CustomerOrder,
ADD BLOCK PREDICATE Security.fn_tenant_access(TenantId) ON dbo.CustomerOrder AFTER INSERT,
ADD BLOCK PREDICATE Security.fn_tenant_access(TenantId) ON dbo.CustomerOrder AFTER UPDATE
WITH (STATE = ON);

This illustrative policy assumes an existing table and a trusted application connection. sp_set_session_context can be called by any user for their session: read_only=1 prevents subsequent change on that logical connection, but does not authenticate the first value. Derive tenant context from validated identity, restrict direct database access, and initialize it on every pooled connection. Do not use a caller-supplied tenant ID as proof of tenancy. Test read, insert, update across tenant, administrator/bypass paths, connection reuse and plans. Learn row-level security.

Worked example: a filtered row can move out of sight

Tenant 7 can read its order O1. With only a filter and an AFTER INSERT block, an allowed update could change O1 to tenant 8 and make it disappear from tenant 7’s next query. The added AFTER UPDATE block rejects that tenant-changing write. Test insert, update, delete, missing context and connection reuse as the actual application principal; merely observing that the row became invisible is not proof the write was prevented.

Related item: RLS bugs are often identity-context bugs. If connection pooling carries stale session state or the app can choose its own tenant value, a correct predicate can still enforce the wrong boundary.

Secure model endpoints and data interfaces

When SQL calls a model or external REST endpoint:

• use a managed identity or a database-scoped credential rather than embedding a token;
• grant only the invocation permission and model deployment/action required;
• allow-list supported HTTPS destinations and control DNS/network egress where the platform permits;
• minimize prompt/context data, classify it, and account for provider logging/retention and regional processing;
• validate output shape and size; enforce timeouts, retry/backoff and cost/rate limits outside a long user transaction;
• audit who invoked which endpoint/model/version without logging raw sensitive prompts unnecessarily.

Secure Data API builder endpoints with identity/provider configuration, entity permissions and field/action restrictions. REST/GraphQL exposure is not authorization by itself. Secure MCP endpoints as tool interfaces: tool descriptions, arguments and results can carry sensitive data or instructions.

Use the broader SQL security best practices and, for Fabric-specific inheritance and roles, the Fabric SQL security overview.

Audit for an answerable question

Define the event question first: who changed a role, read a sensitive object, invoked an endpoint, deployed schema or changed an audit policy? Then configure the platform's auditing/diagnostic categories, destination, retention, access and alert. Store logs outside the database's ordinary administrator boundary where the threat model requires it.

Prove:

• an expected successful and denied operation appears;
• identity, database/object, action, timestamp and correlation fields are usable;
• destination ingestion delay and retention meet policy;
• the audit configuration itself is monitored;
• sensitive statement text/parameters are handled appropriately.

Configure connection, transaction and concurrency behavior

Application configuration should make behavior explicit: connection target/database, Microsoft Entra authentication mode, encryption and certificate validation, pooling, connect/command timeout, retry policy and application name. Do not retry every error. Retry transient connection/throttling faults with bounded backoff and idempotency; do not blindly replay an ambiguous write.

Isolation and versioning

Isolation choice	Main behavior	Design concern
Read committed locking	readers can wait behind writers and see committed statements	blocking and lock duration
Read committed snapshot	statement-level committed versions reduce reader/writer blocking	temp/version-store pressure and changes from one statement to the next
Snapshot	transaction reads a consistent version	update conflicts and version storage
Repeatable read	protects rereads of touched keys with locks	phantoms and longer blocking
Serializable	range locks prevent phantoms	greatest blocking/deadlock risk

Keep transactions short, touch resources in consistent order and avoid user/network/model calls inside them. Optimistic concurrency can compare a rowversion or original values and handle zero-row updates as conflict. Pessimistic locks may be correct for scarce resources but require timeout/deadlock recovery.

Related item: Database isolation and application idempotency solve different problems. Isolation coordinates concurrent database operations; an idempotency key prevents a client retry from creating a second business operation.

Diagnose with waits, plans and workload history

Use a consistent investigation sequence:

1. Confirm user impact, scope and time window; capture application/request/query/run identifiers.
2. Check service health, resource saturation, connection failures/throttling and recent deployment/config change.
3. Identify top duration, CPU, reads, writes, waits and regressions through Query Store/Query Performance Insight.
4. Capture actual plan safely; compare estimated versus actual rows, access paths, joins, sorts/hashes, spills, memory grant, parallelism and warnings.
5. Inspect DMVs for current requests, sessions, locks, waits and resource state.
6. Correct the root cause; replay a representative workload and compare latency, logical reads, CPU, waits and plan stability.
7. Retain evidence and monitor for regression.

Use monitoring with DMVs and identify query performance issues. DMV state can reset after failover/restart and permissions/platform columns differ; Query Store supplies durable workload history when correctly configured.

Read execution plans as explanations

Do not optimize by operator icon alone. Look for:

• large actual/estimated row differences suggesting stale statistics, parameter sensitivity or correlation;
• scan versus seek in context—a scan can be optimal for many rows;
• implicit conversion or non-SARGable expression preventing useful access;
• lookup repeated at high row count;
• sort/hash spill and memory-grant feedback;
• join choice/order and residual predicates;
• missing-index suggestion as hypothesis, not an instruction;
• parallel skew, exchange cost and serial zones;
• scalar/UDF behavior and row goals.

Parameter-sensitive workload correction might be query/schema/statistics redesign, parameter-sensitive plan optimization, recompilation, OPTIMIZE FOR, Query Store hint or plan forcing. Each trades compilation cost, stability and generality; prove behavior for multiple parameter shapes.

Diagnose blocking and deadlocks separately

Blocking is waiting behind an incompatible lock. Identify the head blocker, its transaction, query, wait resource, age and application context. The blocked query is not necessarily the cause. Follow understand and resolve blocking.

A deadlock is a cycle; the engine chooses a victim. Capture the deadlock graph and map processes, resources, lock modes and statements. Corrections include consistent access order, shorter transactions, useful indexes, appropriate isolation/versioning and retry of the victim only when the transaction is safe to replay. Use analyze and prevent deadlocks.

Related item: Killing a blocker restores capacity but does not correct the transaction design. Retain evidence first where safe, anticipate rollback time, then fix the source of the long transaction.

Deliver schema through SQL database projects

A SQL database project represents database objects as versioned declarative source, builds a .dacpac, validates references and deploys through SqlPackage or supported tooling. SDK-style projects based on Microsoft.Build.Sql are the current direction. See SQL database projects.

Source and branch workflow

1. Import/model the intended schema in a project; do not treat a production database as the only source of truth.
2. Use feature branches and small commits tied to a change. Keep object source, tests and deployment scripts together.
3. Build on every change; run static/code analysis and tests against an ephemeral/integration database.
4. Review generated DDL, compatibility, permissions, data movement and destructive-change warnings.
5. Resolve merge conflicts from the intended final schema, then rebuild and compare. Do not mechanically accept both definitions of one object.
6. Publish one immutable .dacpac through environments; vary approved configuration/secrets, not compiled schema.

Reference/static rows such as controlled type codes may be represented in reviewed source and deployed idempotently when they are truly part of the database contract. Give each dataset an owner, stable key and deletion/update policy; test both an empty target and an upgrade from prior values. Do not place environment secrets, mutable business master data or large production extracts in the repository under the label “reference data.”

The store database schema in Git tutorial covers the core model. Pull requests are a useful governance pattern, but the technical requirement is controlled review and validation; a solo repository can enforce the same checks directly before merge/deploy.

Test at multiple layers

• Build/static analysis: syntax, unresolved reference, compatibility and configured rule failures.
• Unit/database behavior: procedure/function results, constraints, null/boundary/error and transaction behavior on controlled data.
• Contract: expected columns, types, nullability, keys, permissions, REST/GraphQL schema and model-output schema.
• Integration: identity, external endpoint/model, DAB, change mechanism and real supported engine behavior.
• Migration: deploy from each supported prior version with realistic size; prove data preservation and duration/locking.
• Security: positive and negative principal, row/column policy, secret absence and audit evidence.
• Performance: representative distribution/concurrency and plan/latency regression threshold.
• Recovery: failed deployment, rollback/forward-fix, backup/restore and post-deploy task replay.

Detect drift and deploy safely

Schema comparison can compare project/database/dacpac states. Classify intentional emergency drift, platform-managed differences and unauthorized change. Reconcile intentional changes back to source.

Before publish, generate and review the deployment script/report. Block unapproved data loss; back up or export critical data; control DDL transaction and lock/time budget; use pre/post-deployment scripts sparingly and idempotently; capture artifact hash, target, approver/operator and output. Keep environment secrets in a protected secret store/CI identity, never project source or publish profiles committed with credentials.

Pipeline controls can combine protected branch policy, required build/test/status checks, code owners for sensitive paths, controlled triggers, separation of build and deploy, environment approval, workload-identity authentication, scoped target roles and an immutable artifact. Restrict who can change the pipeline itself. A manual approval without deployment evidence is weaker than a repeatable gate that proves artifact, target, identity, test result and data-loss review.

Related item: A dacpac describes desired schema, not arbitrary data migration logic or every newer object type. Vector-index deployment needs version-specific verification: current documentation supports BACPAC with DacFx 170.5.96 or later, while DACPAC wording is inconsistent. Inspect sys.vector_indexes after deployment/import and plan an idempotent supported creation step when needed; do not infer index existence from a successful pipeline.

Configure and expose Data API builder

Data API builder (DAB) maps configured database entities to REST and GraphQL without writing a conventional controller. Start with the DAB overview and configuration reference.

Runtime and entity configuration

The runtime config selects data source/connection, host mode, REST and GraphQL paths, authentication provider, CORS, telemetry and other runtime behavior. Keep environment-specific connection material outside source.

An entity maps a table, view or stored procedure; it controls source mapping, exposed actions, role permissions, field inclusion/exclusion, REST/GraphQL naming, relationships and caching. Read entities configuration and runtime configuration.

{
  "entities": {
    "Product": {
      "source": { "object": "dbo.Product", "type": "table" },
      "rest": true,
      "graphql": true,
      "permissions": [
        { "role": "authenticated", "actions": [ "read" ] }
      ]
    }
  }
}

Treat this as a conceptual fragment and validate against the current DAB schema/version. Configure authentication explicitly. DAB 2.0 defaults new configurations to Unauthenticated: it does not validate a supplied JWT, and authenticated/custom roles never activate under that provider. A proxy authenticating someone does not automatically make this configuration authenticated. Use an explicit production role model; “anonymous” or “authenticated” alone may be too broad. Limit exposed fields and operations, use database permissions as defense in depth, validate request limits and avoid placing sensitive filter values in logged URLs.

Authorization evaluates one effective role per request. With a JWT provider, a named role requires X-MS-API-ROLE and membership in the token; an invalid token is rejected. DAB 2.0 also inherits missing entity permissions from named role → authenticated → anonymous. Inspect dab configure --show-effective-permissions; do not interpret an omitted named-role block as a denial or assume all token roles are unioned.

Autoentities can expose newly matching tables when DAB restarts. Preview include/exclude patterns with dab auto-config-simulate, constrain the SQL identity and review the resulting API contract. Purpose-built MCP procedure tools still require review of the procedure’s actual authority and side effects.

For SQL RLS, DAB claim-to-session-context setup requires set-session-context and an agreed claim key matching the predicate. The username-based sample is not an automatic mapping to this guide’s tenant_id. Alternatively, DAB 2.0 OBO authenticates to SQL as the user, with separate user pools and documented app-registration prerequisites. Runtime caching must be disabled with OBO; it is not just a performance preference.

REST, GraphQL, relationships and query behavior

• REST supports entity collection/item operations, filtering, ordering, selection, pagination and searching according to current capability. Learn the REST overview.
• GraphQL exposes typed queries/mutations and configured relationships. Prevent unbounded depth/complexity and N+1-style database pressure.
• Relationships declare how source/target fields connect; they do not repair missing relational integrity.
• Stored procedures expose defined operations and results but have different query/composability behavior from tables/views.
• Cache only where staleness, invalidation, tenant/security partitioning and sensitive-data storage are acceptable. Never let one role/tenant receive another's cached representation.
• Pagination must have deterministic ordering; define page-size ceilings and continuation behavior.

Deploy DAB as a versioned application configuration with a managed identity, network restrictions, health probes, safe scaling and telemetry. Run contract, authorization, injection, concurrency, pagination and failure tests against the deployed service.

Instrument the application and database path

Application Insights captures application requests, dependencies, exceptions, traces and distributed correlation; Log Analytics stores/query telemetry in a workspace. Use Application Insights overview and Log Analytics overview.

Instrument:

• request/operation ID propagated from API through database and model calls;
• endpoint/operation name and result, not raw sensitive payload;
• database dependency duration/failure and safe query identifier;
• external model endpoint/model/deployment/version, latency, status, tokens/cost where available;
• DAB request/auth/throttle/cache behavior;
• business freshness/completeness and embedding coverage/version;
• deployment version/commit and feature flag.

Alert from a user-impact symptom plus a diagnostic signal, with owner and runbook. Example: p95 API latency plus database CPU/waits, or retrieval empty-result rate plus embedding backlog. Avoid high-cardinality dimensions and secret/PII logging.

Select a change mechanism from semantics

Mechanism	Returns/does	Strong fit	Main caution
Change Tracking	keys/version for changed rows; read current row separately	synchronization needing latest state, lower capture detail	retention gap, deletes and race-safe synchronization algorithm
Change Data Capture	historical row-change data with operation/LSN metadata	incremental ETL/audit-like change feed requiring values	cleanup/retention, capture lag and platform support
Change Event Streaming	streams change events to supported event infrastructure	event-driven near-real-time integration	preview/support, ordering/replay/schema and delivery semantics
Azure Functions SQL trigger	invokes function from tracked database changes	serverless processing with managed binding	scaling, lease/state, at-least-once/idempotency and poison/failure handling
Logic Apps SQL connector	workflow trigger/action over SQL	low-code approvals/integration and connector-supported polling	polling interval, connector limits, identity, duplicates and long workflows
DML trigger	synchronous code inside source transaction	small invariant/audit operation requiring same atomic transaction	latency, blocking, recursion and integration availability

Implement Change Tracking with version capture and a transactionally safe enumeration window; if CHANGE_TRACKING_MIN_VALID_VERSION has advanced beyond the consumer's watermark, reinitialize rather than silently missing changes. Microsoft recommends a snapshot transaction covering validation, version capture and enumeration to avoid cleanup/concurrent-change races. Join changed keys to current data while preserving delete records; an inner join can discard deletions. Review work with Change Tracking.

Worked example: a saved watermark can become unusable

A consumer last committed version 12; the table’s minimum valid version is now 15 and the database current version is 20. Starting at 15 does not repair the missing interval after 12. Reinitialize a consistent baseline, capture its version and reconcile destination deletions before resuming. Persist progress only after the destination commit. Change Tracking supplies changed keys/current state, not every intervening value transition.

CDC exposes capture tables/functions and cleanup; consumers persist the last successfully committed LSN and handle update before/after semantics. Read Change Data Capture.

CES publishes changes to an event stream. Design consumer-group/checkpoint, key/order, schema evolution, duplicate/idempotency, retention/replay, backpressure and monitoring. Use the current Change Event Streaming overview.

Current transition — verify before configuring: As of August 15, 2026, new Azure SQL Database and Fabric SQL database CES stream groups must use the Kafka-based AzureEventHubs option. SQL Server 2025 and Managed Instance still accept AzureEventHubsApacheKafka; their move to the unified value is a future update with no confirmed date in the notice. Choose the value supported by the actual target/build, not one universal script. Follow the official AMQP deprecation notice for target-specific migration steps.

For serverless/low-code consumers, inspect Azure Functions SQL trigger and Azure Logic Apps SQL connector. Neither removes the need for idempotent processing, persisted progress and reconciliation. The Functions SQL trigger polls Change Tracking and can coalesce repeated changes to a row. Its documented retry behavior ignores a row for future changes after five consecutive failed function executions for that row; do not assume a managed dead-letter queue. Monitor failures, reconcile source/target, and verify recovery for the extension version in use.

Existing CES AMQP groups have an announced April 2027 support end; no exact day is supplied. Migration must drain pending changes and coordinate writes while moving tables between groups, otherwise unpublished changes can be missed. Check port 9093, target-specific destination names and supported authentication. Changing the publisher protocol does not itself require consumers to change theirs.

Related item: An event is not “exactly once” because a connector hides retries. Give each business/change event a stable identity, make the sink idempotent, commit progress with output where possible and reconcile source-to-target.

Confirmed — VERIFY CURRENT: CES platform supportability still labels CES preview. SQL Server 2025 requires preview configuration; Entra authentication needs CU3 or later plus Azure Arc enablement or Azure VM hosting. Managed Instance requires SQL Server 2025 or Always-up-to-date policy; Fabric SQL database does not currently support CES Entra authentication. The AMQP notice’s generic recreation sentence uses AzureEventHubs for every product, while its platform table still lists the older names for SQL Server/Managed Instance. Use the supported target-specific value and preserve this documentation mismatch until clarified.

Proposed practice extension — not executed: Labs 7/10: add target/build/update-policy/authentication/destination matrix; record unsupported authentication as a gap, never paste one universal migration script.

Answer checkpoint: Does SQL2025 CU3 Entra support prove Fabric CES supports Entra? No; the platform supportability section explicitly separates them.

---

4. Implement AI capabilities in database solutions (25–30%)

Design an AI data contract before choosing syntax

A useful AI-ready relational model keeps provenance and lifecycle visible:

Document(DocumentId, source URI, source version/hash, ACL, updated time)
Chunk(ChunkId, DocumentId, ordinal, text, token count, metadata JSON)
Embedding(ChunkId, model/deployment, model version, dimensions, vector, generated time, source hash)
RetrievalRun(query hash, filters, method/index version, candidates/scores, latency)
AnswerRun(model/deployment, prompt version, citations, safety result, token/cost/latency)

This permits selective re-embedding, permission-aware retrieval, stale-vector detection, evaluation and rollback. The vector column alone does not show which source or model produced it.

Define:

• business use case and unacceptable answer/failure;
• approved source boundary, classification and row/document ACL;
• chunking method and overlap;
• embedding model/deployment, output type and fixed dimensions;
• freshness/backfill and delete propagation;
• retrieval filters, exact/approximate choice, top-k and hybrid fusion;
• answer model, prompt/output schema, citations and safety controls;
• offline relevance set and online quality/latency/cost signals.

Configure external model access

An external model object encapsulates endpoint/model metadata and credentials for SQL embedding generation. The current syntax accepts MODEL_TYPE = EMBEDDINGS; it does not define a general completion-model object. Evaluate completion models separately for the application or supported REST invocation path. Follow CREATE EXTERNAL MODEL for exact target syntax and supported providers.

Evaluate the model against the actual operation:

Dimension	Questions
Modality	Does it accept/return the required text, image, audio or other form, and how is nontext content represented before SQL processing?
Language	Does measured quality meet the need for every supported language and mixed-language query/document pair?
Size/capability	Does a larger model's quality justify its latency, quota and cost; can a smaller approved model meet the structured task?
Context/output	Are input/context and output limits sufficient after chunking, prompt and response schema overhead?
Structured output	Can it reliably produce a constrained JSON schema, and does application/SQL validation reject malformed or unsafe output?
Governance	Are region, retention, content filters, version lifecycle, identity/network and data classification acceptable?

For multimodal or multilingual choices, build representative evaluation cases rather than extrapolating from an English text demo. Model size is not the same as embedding dimensionality or context window.

Design for:

• a named model deployment and expected output type/dimensions;
• managed identity or protected database-scoped credential;
• least privilege to create/use/alter model objects;
• endpoint network reachability, quota, rate limit, timeout and region/data handling;
• structured output/schema when the completion must be machine-consumed;
• model and prompt version recorded with generated data;
• safe rotation and a test that detects dimension/model mismatch before corrupting the index population.

Never assume the display name identifies immutable model behavior. A deployment can be repointed or upgraded. Pin what the provider supports, run evaluation before change and preserve the provenance required to rebuild.

Related item: Model permissions and database permissions form one chain. A principal allowed to run a procedure that invokes a model may indirectly export selected database data to that endpoint even without direct endpoint credentials.

Chunk and generate embeddings

Embeddings convert meaning into fixed-length numeric vectors. Similar meanings should be near one another under the selected metric, but scores are model- and corpus-dependent; they are not universal percentages.

Use AI_GENERATE_CHUNKS where supported to split input with configured strategy/size/overlap, or chunk in the application/ETL layer when format-aware parsing, advanced tokenization or provider portability is needed. Preserve headings, source anchors and ACL metadata. Current SQL chunking uses FIXED character counts, with integer overlap percentages from 0 through 50 and compatibility level at least 170. A 1,000-character chunk with 20% overlap repeats 200 characters; this is not a 1,000-token budget. Too-large chunks dilute relevance and consume context; too-small chunks lose meaning and increase vector/count/cost. Measure rather than copy a universal size.

Use AI_GENERATE_EMBEDDINGS or a controlled external application to create vectors. The SQL operation still depends on endpoint availability and can be costly; avoid a synchronous model call in a hot user transaction.

For a background worker, capture immutable text, source hash/version, chunker and model/version first. Generate outside the user transaction, validate vector dimensions, then perform a conditional write. This illustrative write assumes those captured parameters and columns exist:

UPDATE dbo.Chunk
SET Embedding = @validated_vector,
    EmbeddedAt = SYSUTCDATETIME(),
    SourceHashAtEmbedding = @captured_source_hash,
    EmbeddingModelVersion = @captured_model_version
WHERE ChunkId = @chunk_id
  AND SourceHash = @captured_source_hash
  AND RequiredModelVersion = @captured_model_version;
-- Zero affected rows: source/model changed or row was removed; reconcile/requeue.

Handle a null stored hash explicitly in the work selector, and include model/chunker changes even when text has not changed. Recheck ACL/deletion at retrieval time, independently of whether an embedding worker has caught up.

Batch with a durable status such as pending/in-progress/succeeded/failed, bounded concurrency and retry classification. A timeout can be ambiguous; make the update idempotent. Quarantine permanent failures and reconcile counts: eligible chunks, current embeddings, wrong dimensions/model, stale source hash and errors.

Worked example: an old embedding must not become current

A worker captures source hash A and model v1. While it calls the endpoint, the source becomes hash B or the required model becomes v2. An unconditional write followed by copying the row’s current hash would falsely label the old vector current. The conditional write above affects zero rows; a later worker must use B/v2 as required. A deleted row also stays absent. Stable work IDs and reconciliation handle retries; SQL cannot atomically commit an external model call with its local write.

Maintain embeddings when source data changes

Choose a mechanism from scale and latency:

• Trigger: record a cheap outbox/work item in the source transaction; do not normally call the external model synchronously from the trigger.
• Change Tracking: detect changed keys and read the current content; handle deletes and expired retention window.
• CDC: consume detailed value changes/LSN where historical sequencing matters.
• Azure Functions or Logic Apps: react/poll through managed integration with idempotency and poison handling.
• Related item: CES streams change events where current preview/platform support and semantics meet the need. This integration option is outside the October 19 embedding-maintenance bullet.
• Microsoft Foundry pipeline/application job: orchestrate parsing, safety, model call, evaluation and writeback for a richer AI workflow.

Use delete/tombstone processing so deleted or newly restricted text cannot remain retrievable. Re-embed when source hash, chunker, embedding model/version or dimensions change. Blue/green embedding columns/tables let an evaluation approve a new model before switching retrieval and then retiring the old index.

Current naming: Current documentation calls the platform Microsoft Foundry. Older material may say Azure AI Foundry or Azure AI Studio. Treat those as historical names and verify that UI, SDK, endpoint and identity instructions still apply. The current RAG concepts documentation provides the platform context.

Related item: A database transaction cannot make the external model call and local write atomically commit together. Use an outbox/state machine, stable work key and reconciliation rather than pretending there is a distributed transaction.

Store vectors with an explicit contract

Confirmed — VERIFY CURRENT (October 28, 2026). The native vector reference now separates 1,998 float32 from 3,996 float16 dimensions, resolving the earlier limit disagreement. Half precision is GA in Azure SQL Database, Managed Instance and Fabric SQL database, but remains preview in SQL Server 2025. Check the Managed Instance update policy, engine/build and driver. A model’s dimensions, precision and distance contract must fit the target; do not truncate vectors or infer index support from storage support.

Proposed practice extension — not executed: Extend Lab 8 step 2: record platform/update policy/base type/dimensions/driver and test an invalid-length vector in an authorized disposable database.

Answer checkpoint: Can a 3072-dimensional model use the default float32 vector column? No: select a supported half-precision design with quality testing, choose a fitting model, or another supported storage design; do not silently truncate.

Store alongside the vector:

• model/deployment and version;
• dimensions/base type and distance metric intended;
• source/chunk hash and generated timestamp;
• status/error and tenant/ACL/filter metadata;
• normalized content language/type where relevant.

Prevent mixed dimensions or models in one search population. Vector serialization in drivers/APIs must preserve order, numeric range and dimensionality. Encrypt and authorize vectors according to the source sensitivity; embeddings can leak attributes and are not automatically anonymous.

The vector functions reference is the current inventory. Know distance/similarity calculation and normalization implications rather than memorizing only syntax.

• VECTOR_NORMALIZE produces a unit vector under the supported norm and can make intended cosine/dot-product behavior explicit; do not normalize blindly if the model/metric requires magnitude.
• VECTORPROPERTY inspects properties such as dimensions/base type as currently supported; use it to validate data and detect an incompatible model output before indexing/search.
• VECTOR_DISTANCE performs exact comparison for the chosen metric.
• VECTOR_SEARCH allows approximate retrieval but can use a kNN path chosen by the optimizer, or fall back when no compatible ANN index exists. Inspect the actual plan and warning; the function name alone does not prove DiskANN ran.

Choose exact or approximate vector search

Exact nearest-neighbor (ENN) calculates distance over all eligible vectors and returns the true nearest values for that metric. It is simple and provides ground truth but becomes expensive as the candidate set grows.

Approximate nearest-neighbor (ANN) searches an index to reduce latency/compute with a recall tradeoff. It fits larger, repeated low-latency workloads after relevance testing. SQL's current vector indexes use platform-specific DiskANN support; follow CREATE VECTOR INDEX and VECTOR_SEARCH.

-- Exact conceptual pattern; check the current VECTOR_DISTANCE signature.
SELECT TOP (@k)
       ChunkId,
       ChunkText,
       VECTOR_DISTANCE('cosine', Embedding, @query_vector) AS distance
FROM dbo.Chunk
WHERE TenantId = @tenant_id
ORDER BY distance, ChunkId;

Choose distance metric to match model guidance:

• cosine compares direction and is common for text embeddings;
• dot product depends on magnitude unless vectors/model normalize appropriately;
• Euclidean measures straight-line distance.

Lower distance versus higher similarity semantics vary by function. Do not sort the wrong direction. Evaluate top-k recall, precision/nDCG/MRR as appropriate, p50/p95 latency, CPU/IO, index size/build time, write cost and filter selectivity.

Current vector-index constraints

Confirmed — VERIFY CURRENT (October 28, 2026). Vector indexes and VECTOR_SEARCH are GA in Azure SQL Database, Fabric SQL database and Managed Instance with Always-up-to-date policy. They remain preview in SQL Server 2025 and Managed Instance with the SQL Server 2025 update policy. Latest-format support follows the first group. Verify platform, update policy and index version before applying a version-3 example. Check before using:

• current CREATE VECTOR INDEX syntax and supported metric/type/dimensions;
• minimum populated non-null vector count and required primary/clustered index;
• DML and iterative-filter support for the current index version;
• partitioning and platform restrictions;
• current SELECT TOP (...) WITH APPROXIMATE/VECTOR_SEARCH syntax;
• rebuild/recreate requirements after format change;
• DacFx version, BACPAC support and observed index creation after schema deployment;

Current version-3 indexes support DML and iterative filtering, require at least 100 non-null vectors at creation, and cannot be partitioned. The reference places the integer clustered-primary-key restriction under earlier index versions; check the actual target’s prerequisites. BACPAC support starts with DacFx 170.5.96. The same reference has inconsistent DACPAC wording: it both excludes deployment and describes schema-only deployments skipping an index below 100 qualifying rows. More research needed — next pass: verify this deployment path with the exact DacFx/build. After import or publish, inspect sys.vector_indexes; adding rows later does not automatically create a skipped index. Earlier index formats require drop/recreation. Latest queries use SELECT TOP (N) WITH APPROXIMATE, omit TOP_N and order by ascending distance only.

Proposed practice extension — not executed: Lab 8/10: record DacFx/index version; tabletop 80 qualifying rows at schema publish then 120 later; verify index existence explicitly before measuring ANN. Do not execute production drop/recreate.

Answer checkpoint: Does a successful schema publication prove ANN exists after adding more rows? No; inspect index metadata and create/re-publish using the supported path. BACPAC support depends on DacFx version.

Build an ENN ground-truth set before tuning ANN. If a selective tenant/category filter is applied after ANN candidate selection, recall can collapse. Prefer supported pre/iterative filtering or search within a properly bounded authorized candidate set, and test worst-case tenants.

Worked example: evaluate recall over the same authorized population

For tenant T, the exact top three eligible IDs are A, B and C. An approximate run returns A, C and D, all authorized: recall@3 is 2/3. If a global top-three search instead finds three other tenants’ rows and SQL then removes them, the result is empty despite eligible T rows elsewhere. That is a candidate/recall problem; correctly enforced SQL filtering has not itself disclosed those removed rows. Passing the unfiltered candidates into a prompt or cache before authorization would create a separate exposure. Record index version, filters, k, ties and plan with every comparison.

Related item: Security filtering must happen before an unauthorized row can become prompt context. Test authorization and retrieval quality independently; a fast query or high recall does not prove correct access control.

Combine full-text, vector and relational filtering

Full-text search builds language-aware indexes for terms, inflection and proximity; learn full-text search and its setup workflow.

Retrieval	Strong at	Weakness
Relational filters	exact tenant, date, product, status and authorization	semantic meaning
Full-text/lexical	exact terminology, identifiers, rare names and phrase/proximity	paraphrases and conceptual similarity
Vector	semantic similarity and paraphrase	exact identifiers, model bias, opaque score thresholds
Hybrid	combines lexical and semantic candidates	fusion/tuning complexity and duplicate handling

A practical hybrid query obtains lexical and vector candidate ranks, applies authorized relational filters and combines rankings using reciprocal rank fusion (RRF):

rrf_score(document) = sum(1 / (k + rank_in_each_result_list))

RRF works with ranks rather than incomparable raw BM25/distance scores. Use a different name for the fusion constant than retrieval top-k.

Worked example: two good ranks can beat one first place

With fusion constant 60, document A ranked 1 and 4 scores 1/61 + 1/64 ≈ 0.032018; document B ranked 2 and 2 scores 2/62 ≈ 0.032258, so B ranks higher. Deduplicate by document/chunk identity and contribute only from lists where the item is present. Raw vector distances and lexical scores should not be added as though their units matched. Choose fusion constant and per-source candidate count through evaluation. SQL may require application/T-SQL composition rather than a single built-in hybrid operator; the vector search architecture guide and intelligent SQL search module provide decision context.

Build retrieval-augmented generation as a controlled pipeline

RAG retrieves approved external knowledge and includes selected context in a model request. It can improve grounding and freshness but does not guarantee truth.

1. Authenticate the user/workload and resolve permitted tenant/document filters.
2. Normalize the question and generate a query embedding with the same approved embedding model/version.
3. Retrieve lexical/vector candidates with ACL and business filters applied before context assembly.
4. Rerank/deduplicate if required; enforce token/record/source-diversity budgets.
5. Assemble prompt with system policy, question, untrusted labeled context and source identifiers.
6. Invoke the approved completion model with structured output where possible.
7. Validate JSON/schema, citations and safety/business rules; refuse or fall back when evidence is insufficient.
8. Record privacy-safe trace, versions, candidate/citation IDs, latency and cost; collect evaluation feedback.

Test prompt injection inside retrieved documents, conflicting sources, stale/deleted ACLs, no relevant result, adversarial query, partial endpoint failure and invalid model JSON. Keep model-generated SQL read-only/parameterized and policy-checked unless a separately authorized workflow confirms a write.

Invoke external REST endpoints from SQL

sp_invoke_external_rest_endpoint makes an HTTPS request to allowed endpoints and returns response details. SQL Server 2025/Managed Instance and Azure SQL/Fabric can differ in enablement and credential handling. Use a managed identity/database-scoped credential, grant EXECUTE ANY EXTERNAL ENDPOINT narrowly, parameterize/escape JSON with SQL JSON functions, validate response status/body and avoid holding locks while waiting. Database-scoped credentials also require REFERENCES permission for the caller. HTTP redirects are not automatically followed; a successful SQL call is not proof of HTTP or model success. Handle non-JSON/204 responses and endpoint-specific response shape.

DECLARE @response nvarchar(max);
DECLARE @payload nvarchar(max) = JSON_OBJECT(
    'messages': JSON_ARRAY(JSON_OBJECT('role':'user', 'content':@approved_prompt))
);

-- Conceptual call: endpoint, credential name, headers and response shape vary.
EXEC sys.sp_invoke_external_rest_endpoint
     @url = @approved_url,
     @method = 'POST',
     @payload = @payload,
     @response = @response OUTPUT;

SELECT JSON_VALUE(@response, '$.response.status.http.code') AS HttpStatus;

This creates an outbound-data/exfiltration path. Restrict endpoint/permission and data, and consider an application/service boundary when retries, asynchronous work, secrets, policy, streaming, complex observability or provider portability are important.

Related item: “Inside the database” does not make a model call transactional, private, fast or free. External latency/quota/failure remains external; design the user transaction and recovery accordingly.

---

5. Integrated design scenarios

Scenario A: tenant-safe support RAG

Requirements: customer-support agents search only their tenant's current product manuals and tickets; exact error codes matter; responses need citations; source changes appear within 15 minutes.

1. Model document, chunk, embedding and ACL separately; store source/version/hash, tenant and model metadata.
2. Use Change Tracking or an outbox to enqueue changed/deleted rows; a Functions worker chunks and embeds with managed identity.
3. Build full-text plus vector retrieval. Apply tenant/ACL and active-version filters before top-k; fuse lexical and semantic ranks.
4. Invoke the approved completion model with retrieved text labeled untrusted and demand structured answer plus cited chunk IDs.
5. Validate citations belong to retrieved authorized chunks; refuse when evidence score/coverage is inadequate.
6. Monitor change watermark/backlog, embedding coverage/staleness, recall test set, p95 latency, empty results, citation validity and cost.

Failure trap: generating embeddings synchronously in a source-table trigger makes the user's transaction depend on the model endpoint. Record durable work in the transaction and process it asynchronously.

Scenario B: governed database API and deployment

Requirements: expose products/orders through REST and GraphQL, hide cost fields, isolate tenants, deploy schema and API configuration through CI/CD with no stored password.

1. Put schema, RLS predicate/policy, views/procedures, DAB config and tests in source control; build a dacpac.
2. Use a managed identity for pipeline and DAB. Grant DAB only approved views/procedures/actions; keep base-table grants minimal.
3. Set tenant context from validated identity claims on every connection and test pooled-connection reuse.
4. Configure DAB roles, excluded fields, relationships, deterministic pagination and request limits; avoid shared tenant-unsafe cache.
5. Generate/review deployment report/script; test migration from current production-sized schema and block data loss.
6. Correlate DAB request, SQL query and deployment version in Application Insights/Log Analytics; audit denied and sensitive operations.

Failure trap: removing a field from the GraphQL schema does not protect it if the same principal can query the base table through another connection.

Scenario C: high-volume operational database with event integration

Requirements: point-order workload plus daily analytics, low blocking, event-driven fulfillment and five-year tamper-evidence for approved records.

1. Use narrow rowstore transaction tables and measured covering indexes; add a nonclustered columnstore only after proving analytical benefit/write cost.
2. Select row-versioning isolation where consistency requirements fit; keep transaction boundaries short and use idempotency keys.
3. Use an updatable or append-only ledger design only for rows requiring tamper evidence; externalize and verify digests.
4. Publish order change through current CES or an outbox/CDC architecture based on platform/support. Key events by order and make fulfillment idempotent.
5. Track Query Store regressions, blocking/deadlock graphs, CES lag/failure and source-to-consumer reconciliation.
6. Partition/archive only when lifecycle scale justifies it; test partition elimination and aligned maintenance.

Failure trap: ledger detects later tampering but does not prove the original order was valid or prevent an authorized application from inserting a false value.

---

6. Hands-on labs

Use disposable databases, synthetic data and a budget. Record platform/build/compatibility level, DDL/code/config, identities, plans/query IDs, API/model requests, metrics, failures, cleanup and links to the exact documentation used.

Lab 1: relational, JSON, temporal and graph design

1. Model orders, events and relationships with explicit grain, keys, constraints and appropriate types.
2. Compare rowstore and columnstore plans for transactional lookups versus a large aggregation.
3. Store a variable event payload as JSON; extract/project with OPENJSON, construct JSON and add a supported JSON access index/computed-column alternative.
4. Create a disposable system-versioned temporal table and answer an as-of query; prove history growth/retention behavior.
5. Create graph node/edge tables and a MATCH query; implement the same fixed relationship relationally and compare clarity/constraints.
6. Document platform-specific unsupported/preview features instead of skipping them silently.

Lab 2: advanced T-SQL and programmable objects

1. Write deterministic ranking/running-total window queries with ties and nulls.
2. Build a recursive hierarchy CTE with cycle/maximum-depth protection.
3. Compare correlated EXISTS/NOT EXISTS to an equivalent join/pre-aggregation and inspect plans.
4. Implement JSON, regex and fuzzy matching on controlled data; measure false positives and candidate bounding.
5. Create a view, inline TVF and stored procedure with typed parameters and safe error/transaction handling.
6. Write a multirow-safe trigger that only records an outbox row, then explain when it should be replaced.

Lab 3: identity, encryption and fine-grained access

1. Connect an application/workload with Microsoft Entra managed identity and create the least-privileged database user/role.
2. Implement RLS by tenant and masking for one display field; test two tenants, pooled connection reuse, owner/admin and denied writes.
3. Compare TDE boundary with an Always Encrypted column in a supported client; record which queries/operators work.
4. Grant one procedure/interface without base-table access and test ownership-chain/dynamic-SQL behavior.
5. Produce audit evidence for successful and denied sensitive operations.
6. Threat-model model endpoint, DAB and MCP access paths as possible data export paths.

Lab 4: plans, Query Store, blocking and deadlocks

1. Generate data with skew and parameter-sensitive selectivity; capture Query Store history and actual plans.
2. Create one non-SARGable query, implicit conversion, bad estimate and spill; correct each one separately and compare logical reads/CPU/latency.
3. Reproduce blocking with a long transaction; identify head blocker and wait resource without guessing.
4. Reproduce a safe deadlock; interpret the graph, make access order/index/transaction correction and verify.
5. Compare a row-versioned isolation option with locking behavior and monitor version-store implications.
6. Save before/after evidence and a rollback condition for any forced plan/hint.

Lab 5: database project and controlled deployment

1. Create an SDK-style SQL project from a small database; build a dacpac and enable useful analysis rules.
2. Add constraints, RLS, view/procedure and test fixtures on a branch; intentionally create and resolve an object conflict.
3. Deploy to disposable dev, introduce drift, detect it with schema compare and reconcile the intended state.
4. Test a destructive column change against realistic data; generate/review script and enforce a data-loss gate.
5. Publish the same artifact with a non-personal identity to a second environment; record artifact hash and output.
6. Choose an artifact whose support is verified for the exact DacFx/platform version. For vector indexes, distinguish BACPAC with DacFx 170.5.96+ from the unresolved DACPAC wording, test row-count prerequisites, inspect sys.vector_indexes, and prove any required post-deployment creation step is idempotent.

Lab 6: Data API builder and observability

1. Expose approved table/view/procedure entities through REST and GraphQL with explicit roles/actions/fields.
2. Add a relationship, filter/search, deterministic pagination and bounded page size.
3. Test anonymous/authenticated/tenant roles, injection-like input, missing/invalid token and direct database denial.
4. Evaluate caching for a public entity and reject it for tenant-sensitive data unless partitioning/invalidation are proven.
5. Deploy with managed identity and instrument requests, dependencies, exceptions and version in Application Insights.
6. Create a Log Analytics query and alert for p95 latency plus database dependency failures; verify a synthetic incident.

Lab 7: change processing and embedding maintenance

1. Generate inserts, updates and deletes and consume them separately through Change Tracking and CDC.
2. Persist watermarks safely, simulate retention expiry and prove reinitialization/reconciliation.
3. Implement an outbox/Functions worker that chunks and embeds changed content with idempotent work IDs.
4. Simulate timeout, rate limit, bad dimensions, poison content and deletion; inspect retry/quarantine/tombstone behavior.
5. If eligible, configure current CES to Event Hubs using the nondeprecated option and prove consumer checkpoint/replay/duplicate handling.
6. Compare latency, captured detail, operating state and failure semantics; select one mechanism from requirements.

Lab 8: vector, hybrid and grounded-answer evaluation

1. Create a synthetic/document corpus with source version, ACL, chunk and model metadata; build a labeled relevance question set.
2. Generate embeddings with a supported model and verify dimensions/completeness/source hashes.
3. Implement ENN cosine retrieval as ground truth; measure relevance and latency.
4. Create a current supported vector index and ANN query where eligible; compare recall/latency at several candidate counts and filters.
5. Add full-text retrieval and RRF; test exact identifiers, paraphrases, conflicting documents and unauthorized high-score rows.
6. Invoke a completion model through an approved application or external REST path; require structured answer and citations.
7. Test prompt injection in retrieved text, no evidence, invalid JSON, endpoint failure and stale/deleted ACL.
8. Record retrieval/answer model and prompt versions, citation validity, relevance, p95 latency and cost; remove endpoints/indexes/resources.

Lab 9: identity, RLS and DAB upgrade contracts

1. Use two synthetic tenants and test the RLS policy’s read, insert and tenant-changing update paths; compare the update-blocking correction.
2. Demonstrate why a freely supplied session-context value is not authentication, using isolated identities and approved test data.
3. Pin DAB 2.0 and set the intended authentication provider; inspect effective permissions for anonymous, authenticated and selected custom roles.
4. Simulate autoentity patterns before adding a matching table; prove the API exposes exactly the approved objects/fields/actions.
5. Test claim-key mapping and pooled connections, or the supported OBO path with caching disabled. Verify direct database and MCP access separately.
6. Inspect an approved procedure exposed as a custom tool; test its actual side effects and denied operations.

Lab 10: change recovery and retrieval version evidence

1. Simulate an expired Change Tracking watermark and reconcile a consistent full baseline including deletions.
2. Make an embedding worker finish after a source/model change; prove its conditional write cannot mark the old vector current.
3. Compare exact and approximate retrieval on the same authorized corpus and record k, ties, index version, query plan, recall and latency.
4. Reproduce the RRF arithmetic and compare retrieval budgets; distinguish SQL character chunking from model token limits.
5. Test Functions row failures and a documented recovery procedure in a disposable setup; do not assume failed work reaches a dead-letter queue.
6. If eligible, document a CES publisher migration with drained changes and coordinated writes, or record the unsupported target/build boundary without running it.

---

7. Original knowledge checks

These are original prompts, not recalled exam questions. Answer each with requirement, decision, implementation, evidence, failure mode and correction.

1. Choose Azure SQL Database, Managed Instance, SQL Server 2025 or Fabric SQL database for four different constraints. Which feature assumptions must be rechecked?
2. Define row grain, primary key and alternate business key for an order line. Which constraints express each rule?
3. Compare rowstore, clustered columnstore, nonclustered columnstore and memory-optimized tables for write-heavy and analytical workloads.
4. Why can a foreign key improve integrity without improving the child lookup plan?
5. Compare identity and sequence behavior, including gaps and multi-table use.
6. When does partitioning improve lifecycle operations but fail to improve a query?
7. Compare temporal and ledger guarantees. Which one is neither a backup nor proof that the input was true?
8. When does a graph model improve traversal, and what Fabric integration limitation must be checked?
9. Choose a relational column, JSON text/native type or normalized child table for three variable-data cases.
10. Compare JSON_VALUE, JSON_QUERY, OPENJSON, JSON constructors and JSON_CONTAINS.
11. Why can an inline TVF optimize differently from a multi-statement TVF or scalar function?
12. What makes a trigger multirow-safe, and why should an external model call not normally run inside it?
13. Explain anchor, recursive member, termination and cycle protection in a hierarchy CTE.
14. Why do a deterministic window rank and running total require deliberate ORDER BY ties and frame?
15. When does NOT IN return a surprising result that NOT EXISTS avoids?
16. Design a safe fuzzy entity-match pipeline; why is one edit-distance threshold not identity proof?
17. Which review/tests are mandatory before accepting AI-generated SQL?
18. How can a malicious database instruction or retrieved row influence an MCP-enabled assistant?
19. Compare TDE, Always Encrypted, masking, RLS and auditing against one concrete threat model.
20. How can connection pooling break tenant RLS even when the predicate is correct?
21. Why does a managed identity still need database permissions and endpoint restrictions?
22. Compare read committed locking, RCSI, snapshot and serializable for blocking and consistency.
23. Why are automatic retries unsafe for an ambiguous non-idempotent write?
24. Which plan evidence distinguishes bad cardinality, spill, lookup amplification and non-SARGable access?
25. Compare blocking with a deadlock; what evidence and remediation differ?
26. What belongs in SQL project source, the dacpac, protected pipeline configuration and post-deployment tasks?
27. How do schema compare and deployment report help detect drift and destructive change?
28. Design DAB entity roles/fields/actions for public products and tenant orders. Where is defense in depth enforced?
29. Why can caching or GraphQL nesting create security/performance issues even when the API works functionally?
30. Compare Change Tracking, CDC, CES, Functions SQL trigger, Logic Apps and DML trigger for one change-integration need.
31. How does the August 2026 CES Event Hubs transition change a new configuration?
32. Which metadata proves an embedding is current and compatible with a vector column/index?
33. Compare ENN and ANN. How do you measure ANN recall rather than assume it?
34. How do cosine, dot-product and Euclidean semantics affect model/search compatibility and sort direction?
35. How does post-filtering affect recall, and when does handling unfiltered candidates create a separate authorization failure?
36. Design a hybrid RAG evaluation covering exact terms, paraphrase, ACL, prompt injection, citation, latency and cost.
37. Why is an AFTER INSERT block insufficient to prevent an order moving to another tenant?
38. Can a read-only session-context key authenticate the tenant value supplied by its caller?
39. What do DAB 2.0’s Unauthenticated default, effective role and inherited permissions imply?
40. How do automatic entity discovery and OBO change API deployment checks?
41. What should happen when a worker’s source hash or required model changes before writeback?
42. Does WITH APPROXIMATE guarantee that DiskANN executes, and which index version supports iterative filtering?
43. What do SQL chunk size/overlap mean, and why do vector dimensions need a base-type check?
44. What can five failed Functions executions and a CES group move do to change delivery?

Answer checkpoints

1. Match management, instance compatibility, operational control and Fabric integration to the requirement; verify each feature’s platform, tier, build, region and preview state.
2. One order line needs a stable row key plus a protected business identity such as order/line number; foreign keys and checks enforce additional rules.
3. Rowstore favors selective transactional access; columnstore favors scans/aggregation; nonclustered columnstore adds analytical cost/benefit to rowstore; memory optimization needs measured pressure and support.
4. A foreign key validates relationships but does not automatically create the child-side access index.
5. Both can have gaps; a sequence is independently consumed and can serve multiple tables.
6. Switching/retention can improve without query elimination; prove the predicate and index layout support the intended partition access.
7. Temporal reconstructs row versions; ledger provides verifiable tamper evidence. Neither replaces backups or proves original input truth.
8. Graph helps relationship traversal; Fabric mirroring excludes graph node/edge tables, so verify the analytical path separately.
9. Use relational columns for strong typed operations, JSON for variable aggregates, and child tables for repeated relational entities needing independent constraints/joins.
10. VALUE extracts a scalar, QUERY an object/array, OPENJSON rows, constructors build JSON and CONTAINS tests containment where supported.
11. Inline relational expressions can integrate into optimization; multi-statement/scalar behavior depends on estimation and inlining eligibility.
12. Use sets from inserted/deleted and keep work short; remote-model latency and failure should not govern the user transaction.
13. Define an anchor, recursive step, termination, cycle protection and tested maximum depth.
14. Unique tie breakers make ranking repeatable; an explicit ROWS frame avoids unintended peer grouping in cumulative results.
15. NULL in a NOT IN input can make the predicate UNKNOWN; a correctly correlated NOT EXISTS avoids that trap.
16. Normalize, bound candidates, evaluate precision/recall, retain provenance and route uncertain matches to review.
17. Validate platform syntax, authorization, injection, joins/nulls, transaction behavior, destructive changes and actual results/plans in isolation.
18. Instructions or data can redirect tool use; treat retrieved text as untrusted and enforce permissions and argument validation outside the model.
19. TDE protects storage, Always Encrypted protects selected values from the engine under its key model, masking limits presentation, RLS constrains rows and audit records events.
20. Missing or stale context can select another tenant’s scope; set validated context for every logical connection and test reuse.
21. Passwordless authentication does not grant database actions or constrain outbound destinations by itself.
22. Locking can block; RCSI versions each statement; snapshot versions a transaction and can conflict on writes; serializable prevents phantoms with stronger locking.
23. The first write may have committed despite a lost response; a retry needs a stable idempotency key and reconciliation.
24. Compare actual/estimated rows, memory grants/spills, lookup count, predicates/conversions, reads and representative parameters.
25. Blocking has a head blocker; a deadlock is a cycle with a victim. Capture transaction/wait evidence versus a deadlock graph, then correct the cause.
26. Store schema, approved reference data and tests in source; build schema into a dacpac; protect environment credentials; separately validate unsupported artifact creation.
27. Schema compare finds differences; the deployment report/script shows proposed operations and data-loss/locking implications.
28. Explicitly authorize entity operations/fields and tenant rules; constrain the SQL identity and test other database/API/MCP entry points.
29. Cache scope can violate identity/freshness contracts, while unbounded nesting creates workload pressure; test both, and disable runtime cache for OBO.
30. Change Tracking reports changed keys/current state; CDC retains changes for a window; CES publishes events; Functions polls tracked changes; Logic Apps orchestrates; DML triggers run in the transaction.
31. New Azure SQL/Fabric groups use AzureEventHubs. SQL Server/MI currently use their supported Kafka option until the announced future update; verify build, auth and network.
32. Source/chunk hash, model/deployment/version, required dimensions/base type, generation state/time and ACL prove compatibility and freshness together.
33. Compare ANN IDs to an exact baseline over identical eligible data, metric, k and tie rules; report recall and latency separately.
34. Cosine compares direction, dot product depends on magnitude/sign convention, and Euclidean measures distance; align model guidance and sort semantics.
35. A late filter can exhaust candidates without exposing rows if enforced correctly. Sending unfiltered rows to a model/cache first is the separate security failure.
36. Include exact terms, paraphrases, conflicting/no evidence, stale/deleted ACLs, injected text, citation validity, recall/answer quality, latency and cost.
37. A filter allows updating a currently visible row into an invisible state. AFTER UPDATE blocks a resulting tenant value that violates the policy.
38. No. Any user can set their session context; read-only freezes a value on a logical connection but does not verify its origin.
39. The default provider ignores JWTs; a configured JWT provider selects one role, and missing 2.0 permission entries inherit. Inspect effective permissions and negative tests.
40. New matching tables can appear after restart. OBO requires supported Entra/SQL configuration, per-user identity and runtime caching disabled.
41. The conditional write must affect zero rows; reconcile or queue current source/model work instead of labeling the stale vector current.
42. No; the optimizer can choose kNN. The latest index format supports iterative filtering and DML on Azure SQL Database, Fabric SQL database and Managed Instance with Always-up-to-date policy; verify platform/update policy and actual index version.
43. FIXED uses characters and overlap percentages. The current type page distinguishes float32 up to 1,998 dimensions from float16 up to 3,996. Half precision is GA in the documented Azure SQL/MI/Fabric targets and preview in SQL Server 2025; verify target/update policy and driver.
44. The documented Functions behavior can ignore that row after repeated failures. Removing a CES table with unpublished changes can lose delivery; monitor, drain and reconcile.

---

8. Final readiness checklist

• I can map every October 19, 2026 objective to a section, lab and evidence artifact.
• I can distinguish Azure SQL Database, Managed Instance, SQL Server 2025 and Fabric SQL database support without assuming parity.
• I can design tables, data types, constraints, sequences, partitions and rowstore/columnstore/memory-optimized indexes from workload evidence.
• I can select and operate temporal, ledger, graph, external and JSON designs with their boundaries.
• I can build views, functions, procedures and multirow-safe triggers with explicit transaction/error behavior.
• I can write CTE/window/correlated, JSON, regex, fuzzy and graph queries and inspect their plans/correctness.
• I can use GitHub Copilot/Copilot in Fabric, instruction files and MCP endpoints with reviewed output and least privilege.
• I can apply Entra/passwordless identity, encryption, masks, RLS, permissions and auditing at the correct boundary.
• I can configure safe connections, concurrency/isolation, retry and idempotency.
• I can use plans, DMVs, Query Store and Query Performance Insight to resolve bottlenecks, blocking and deadlocks.
• I can build/test/version a SQL project, resolve conflicts, detect drift and safely deploy one dacpac through environments.
• I can configure, secure, deploy and observe DAB REST/GraphQL entities, relationships, cache, pagination, filter and search.
• I can select and operate Change Tracking, CDC, current CES, Functions SQL trigger and Logic Apps by semantics.
• I can configure external models and maintain chunks/embeddings through update/delete/model changes.
• I can store vectors, choose metric, implement ENN/ANN/vector indexes and verify current preview/platform constraints.
• I can combine relational, full-text and vector retrieval with RRF and authorization before context assembly.
• I can invoke external REST/model endpoints securely and validate structured JSON results.
• I can build/evaluate RAG for grounding, citations, ACL, prompt injection, latency and cost.
• I have rechecked blueprint, lifecycle, practice link, platform Applies to sections, compatibility levels, preview states and CES transition immediately before the exam.

---

Places to learn

This is not a complete list, and it is not a recommendation to consume everything. Pick one current primary path, build the labs, and use targeted references or practice for gaps. Times are page-published when available; otherwise they are clearly labeled estimates. Catalogs, access, duration, price and alignment change. DP-800 is new, so several established vendors did not yet have a dedicated full certification path on the pages found; map broader SQL Server 2025 material to the current October 19 outline; March-only coverage is historical. Avoid dumps or products claiming recalled/live exam questions.

October 28 learning route and evidence task

Use the three paths linked from the current DP-800 course as one sequence: database design, secure delivery, then AI retrieval. Their public outlines still contain 11 modules (4/4/3), and the official exercise index lists eleven matching labs. The three instructor-led days are publisher metadata; the earlier 18h22 self-study total remains historical because fresh path runtimes were not exposed. The existing resource set is sufficient. Rudi Bruchez’s vector course remains 1h19, updated March 30, 2026; its description mentions HNSW while its chapter list names DiskANN, so use current SQL documentation to settle the algorithm and platform assumptions.

Original worksheet (about 35–50 minutes, editorial estimate): Choose one object-design exercise, one API/deployment exercise and one retrieval exercise. For each, record platform/build, intended identity, prerequisite, allowed case, denied or stale-data case, expected evidence and cleanup. Add a second-platform column that can explicitly say unsupported or untested.

Answer checkpoint: A working vector query on one SQL platform does not prove the API, index or authorization behavior on another. Keep unsupported and unexecuted cases visible instead of marking the whole workflow complete.

Start with Microsoft

Resource	Access	Estimated time	Best use
Official DP-800 study guide	Public	30–60 min	Authoritative objectives, weights and lifecycle
DP-800T00 Microsoft Learn course	Public self-study; paid instructor option	3 instructor-led days; 18h22 previously recorded path total, not independently reverified	Primary structured coverage of all three domains
Design and develop database solutions	Public	7h11 previously recorded / 4 modules; duration not reverified	Schema, objects, T-SQL and AI-assisted development
Secure, optimize, and deploy database solutions	Public	7h31 previously recorded / 4 modules; duration not reverified	Security, performance, projects, APIs, monitoring and change
Implement AI capabilities in database solutions	Public	3h40 previously recorded / 3 modules; duration not reverified	Models, embeddings, vectors, search and RAG
Microsoft Learning DP-800 course labs	Public; Azure/SQL resources may cost	12–24 hours for all 11 labs (estimate)	Official hands-on companion; retain evidence and clean up resources
AI Skills Navigator DP-800 Practice Assessment	Free account; sign-in required	45–90 min per attempt plus review (estimate)	Official readiness diagnostic and gap finding
Microsoft Reactor DP-800 series	Public/on demand	About 7 hours for 7 sessions; 3–5 hours selectively (estimate)	Domain-by-domain instruction and demonstrations
Microsoft exam sandbox	Public	20–30 min	Exam interface familiarity, not technical preparation

Courses, books and video

Resource	Access	Estimated time	Best use and freshness note
O'Reilly SQL Server 2025 Unveiled	Paid subscription/book	10–15 hours selectively (estimate)	Earlier listing covers SQL AI, vector, JSON, regex, CES, REST, security and Fabric; current retrieval blocked, so map chapters to DP-800 rather than reading every page
Pluralsight SQL Server 2025 Fundamentals path	Paid/trial depending plan	8 hours published / 6 courses	Listing now includes the September 21 AI course alongside engine, T-SQL, security, performance and hybrid topics; not a complete DP-800 path
Pluralsight Optimize Vector Search with Indexes in SQL Server 2025	Paid/trial depending plan	1h19 published; updated March 30, 2026	Focused vector data, distance and performance supplement; verify preview syntax
Udemy DP-800 Exam Prep: Microsoft SQL Server AI Developer	Paid; price varies	19h6 / 170 lectures previously recorded; current access blocked	Earlier listing dated June 2026; paid/current content not reviewed. Compare every preview statement to current Microsoft docs
Microsoft Reactor YouTube channel	Public	3–8 hours selectively (estimate)	Search DP-800, SQL Server 2025, vector and AI-enabled SQL; prefer recent sessions

Practice, samples and implementation references

Resource	Access	Estimated time	Best use and caution
Microsoft DP-800 Practice Assessment	Free account	45–90 min per attempt plus remediation (estimate)	Use first as the official diagnostic; do not memorize answers
Whizlabs DP-800 SQL AI Developer Associate	Paid catalog; earlier quiz listing not currently verifiable	2–4 hours including explanation review (estimate)	Current fetch exposes only a site shell; verify the actual catalog and explanation quality before considering it, and reject recalled/live-question claims
Azure SQL AI samples	Public	6–15 hours selectively (estimate)	Official examples for embeddings, vector search and AI-enabled SQL; inspect platform/version prerequisites
Azure SQL database chat with Semantic Kernel	Public	3–6 hours (estimate)	End-to-end database chat/RAG architecture; update dependencies and threat-model before reuse
Azure SQL external REST endpoint samples	Public	2–4 hours (estimate)	Endpoint, credential and JSON call patterns; use only approved endpoints and synthetic data
Microsoft SQL Server samples	Public	4–12 hours selectively (estimate)	Relational, performance and engine experimentation; select current 2025-relevant samples
This guide's ten labs	Azure/SQL/model resources; costs vary	28–52 hours (estimate)	Cross-domain implementation, failure, security, deployment and evaluation evidence

No dedicated MeasureUp DP-800 product was found in the public catalog checked on August 31, 2026. Recheck the vendor later rather than substituting an unrelated exam product. No one practice vendor is authoritative; use explanations to identify a source/documentation gap.

Useful blog reading with an exercise

• Azure SQL optimizer intelligence for vector search — Pooja Kamath, August 17, 2026. Use its filtered-query discussion to compare exact and approximate plans on the same authorized corpus. Verify current index/version/regional requirements; a query accepting approximation does not force ANN or guarantee a particular speedup.
• Introducing SQL MCP Server — Jerry Nixon, April 8, 2026; public page reviewed September 27. Design a small entity/tool/role matrix, then test a denied read/write and a procedure’s real authority. Its permissive anonymous setup is demonstration scaffolding; choose deliberate authentication and least privilege for your exercise. Current DAB 2.0 documentation controls defaults, inheritance, OBO and cache limits.

These are supplemental explanations, not exam-authoritative material. Public text was read; linked demos, downloads, videos and performance claims were not executed or independently benchmarked.

A practical study sequence

1. Read the official blueprint and map every objective to a platform-supported lab in 30–60 minutes.
2. Complete the three Microsoft Learn paths or one current structured course; do not stack passive courses.
3. Build Labs 1–4 and remediate SQL design, security and performance with exact reference pages.
4. Build Labs 5–10 and retain artifact/deployment, API/telemetry, change/embedding and retrieval-evaluation evidence.
5. Take the official Practice Assessment once; remediate by objective and implementation, not answer memory.
6. Use one ethical third-party practice resource only if its explanations are current and source-based.
7. Recheck the official guide, credential page, Applies to sections, preview notices, model/vector limits and CES transition immediately before the exam.

---

This independent guide is based only on public sources and original synthesis. It is not affiliated with or endorsed by Microsoft, GitHub, HashiCorp, or any learning vendor.