Master the DP-800

An interactive study guide built on 7 memory techniques to help you pass the Microsoft SQL AI Developer Associate exam.

Design & Develop 40-45%Secure, Optimise & Deploy 25-30%AI Capabilities 30-35%

What it covers

T-SQL development, database design across SQL Server/Azure SQL/Fabric SQL, CI/CD with GitHub, AI integration (embeddings, vectors, Azure OpenAI), performance tuning, security, and deployment.

Ideal for

SQL developers, database developers, and data engineers who want to integrate AI capabilities into SQL-based solutions.

Aspire to this if

You're a SQL developer ready to add AI to your skillset, building modern data applications that combine relational databases with AI features like vector search, embeddings, and generative AI.

Section 1 / Spatial Memory

The Map

Tap any component to see what you need to know for the exam.

📝 T-SQL Development

Stored Procs · Functions · Views

🖥️ SQL Server

On-premises · SQL 2022+

☁️ Azure SQL Database

PaaS · Serverless · Hyperscale

🏢 Managed Instance

Near 100% SQL Server compat

🧵 Fabric SQL Database

SQL in Microsoft Fabric

🔒 Security

Encryption · RLS · Masking

⚡ Performance

Indexing · Query Store · Tuning

🚀 CI/CD & Deployment

GitHub Actions · DACPAC · Migrations

🧭 Vector Search

Embeddings · DiskANN · Similarity

🤖 Azure OpenAI

sp_invoke_external_rest · RAG

💡 AI Integration Patterns

RAG · Enrichment · Classification

Section 2 / Narrative Memory

The Story

Follow the narrative to build a mental model of how everything connects.

📝

The SQL Foundation

Everything starts with T-SQL — the language that has powered relational databases for decades. Stored procedures encapsulate business logic, CTEs tame complex queries, and window functions unlock analytical power. Temporal tables add a time dimension, letting you query data as it existed at any point in the past.

Exam IntelT-SQL fundamentals: stored procedures, scalar/table-valued functions, views, CTEs, window functions (ROW_NUMBER, RANK, LAG/LEAD). Temporal tables: system-versioned for audit/time-travel. JSON support: OPENJSON, JSON_VALUE, FOR JSON. Error handling: TRY/CATCH + THROW.

🖥️

The Platform Landscape

SQL runs everywhere. SQL Server 2022 on-premises brings native vector support and Azure Arc. Azure SQL Database offers serverless auto-pause for cost efficiency and Hyperscale for massive scale. Managed Instance provides near 100% compatibility for lift-and-shift. Fabric SQL databases integrate directly into the Fabric ecosystem.

Exam IntelKnow when to use each: SQL Server = full control, on-prem/IaaS. Azure SQL DB = PaaS, serverless, Hyperscale. Managed Instance = SQL Server compat, cross-DB queries, SQL Agent. Fabric SQL = autonomous, auto-mirrored to OneLake, Fabric capacity.

🏗️

The Design Workshop

Good database design is the bedrock. Normalisation reduces redundancy, but star schemas serve analytics. Partitioning splits large tables for manageability. Columnstore indexes compress analytical data by orders of magnitude. The design must balance transactional integrity with analytical performance.

Exam IntelNormalisation (3NF) for OLTP. Star schema (facts + dimensions) for analytics. Table partitioning by date ranges. Columnstore for read-heavy analytics. Rowstore clustered + nonclustered for OLTP. Computed columns, indexed views for derived data.

🔒

The Security Vault

Security wraps every layer. Row-Level Security filters rows invisibly per user. Dynamic Data Masking hides sensitive fields from unauthorised eyes. Always Encrypted keeps data encrypted even in memory. TDE protects at rest. Microsoft Entra authentication eliminates passwords entirely with managed identities.

Exam IntelRLS: CREATE SECURITY POLICY with filter/block predicates. Dynamic Masking: default, email, partial, random. Always Encrypted: deterministic vs randomised, column master/encryption keys. TDE: on by default in Azure SQL. Entra auth: managed identity, no connection strings with passwords.

⚡

The Performance Engine

Query Store is the flight recorder — capturing every plan and its performance over time. Intelligent Query Processing auto-tunes without code changes. Wait stats reveal where time is spent. The art is knowing when to add an index, when to rewrite a query, and when to scale the platform.

Exam IntelQuery Store: enable, review top resource consumers, force plans, detect regressions. IQP: adaptive joins, batch mode on rowstore, memory grant feedback, DOP feedback. Indexes: clustered, nonclustered, columnstore, filtered, included columns. Wait stats: PAGEIOLATCH, LCK, CXPACKET, SOS_SCHEDULER_YIELD.

🚀

The Deployment Pipeline

Modern SQL development treats schema as code. SQL Database Projects define every table, view, and procedure in source control. DACPAC deployments compare and synchronise schemas declaratively. GitHub Actions automate the build-test-deploy cycle. Schema drift is caught before it causes incidents.

Exam IntelSQL Database Projects (.sqlproj): schema-as-code, build to DACPAC. SqlPackage: publish, extract, export, import. GitHub Actions: build DACPAC, run tests, deploy to Azure SQL. Schema comparison for drift detection. Fabric SQL: built-in GitHub integration. Deployment slots for zero-downtime.

🧭

The Vector Dimension

AI meets SQL through vectors. Text, images, and code become high-dimensional embeddings stored alongside relational data. DiskANN indexes make similarity search blazing fast. VECTOR_DISTANCE calculates how close two embeddings are. Hybrid queries combine the precision of SQL filters with the intelligence of vector similarity.

Exam IntelVECTOR data type (up to 1998 dimensions). DiskANN index for approximate nearest neighbour. VECTOR_DISTANCE(metric, v1, v2): cosine, dot_product, euclidean. Hybrid search: WHERE filters + ORDER BY VECTOR_DISTANCE. Store embeddings in varbinary(max) or VECTOR columns.

🤖

The AI Gateway

sp_invoke_external_rest_endpoint is the bridge between SQL and Azure OpenAI. From within a stored procedure, you can generate embeddings, call chat completions, and build RAG pipelines — all without leaving T-SQL. Managed identities handle authentication, keeping secrets out of code.

Exam Intelsp_invoke_external_rest_endpoint: POST to Azure OpenAI from T-SQL. Generate embeddings: text-embedding-ada-002 / text-embedding-3-small. Chat completions: GPT-4o for in-database AI. Managed identity auth: no API keys in code. Rate limiting: handle 429 responses with retry logic.

💡

The RAG Pipeline

Retrieval-Augmented Generation brings it all together. User queries are converted to embeddings, matched against stored vectors, and the top results augment an LLM prompt. The database becomes both the knowledge base and the AI orchestrator — no external vector database needed.

Exam IntelRAG pattern: query → embedding → vector search (top-K) → augment prompt → LLM completion. Chunking strategies: fixed-size, sentence-based, semantic. Embedding refresh: batch or trigger-based on data changes. Token budgets: fit retrieved context within model limits.

🧵

The Fabric Connection

Fabric SQL databases close the loop. Data is automatically mirrored to OneLake, making it available for Spark, Power BI, and the entire Fabric ecosystem. Auto-tuning handles indexes and statistics. GitHub integration enables CI/CD. It is SQL Server, reimagined for the AI era.

Exam IntelFabric SQL DB: autonomous database in Fabric. Auto-mirror to OneLake (no ETL). Auto-tuning: create/drop indexes, update stats. Built-in CI/CD with GitHub. Shares Fabric capacity units. T-SQL compatible with Azure SQL Database. Direct Lake semantic models on mirrored data.

Section 3 / Acronym Memory

Mnemonic Wall

Memorable acronyms and phrases to anchor key exam concepts in your memory.

🖥️

SAMF

The four SQL platforms. SQL Server = on-prem. Azure SQL DB = PaaS. MI = SQL compat. Fabric = autonomous + OneLake.

🔒

RATE

Security stack top to bottom. RLS filters rows. AE encrypts columns. TDE encrypts at rest. Entra removes passwords.

⚡

QIWC

Performance tuning toolkit. Query Store records plans. IQP auto-tunes. Wait stats diagnose. Columnstore compresses.

🚀

SDGD

CI/CD pipeline. SQL project defines schema. DACPAC packages it. GitHub Actions automates. Deploy to any environment.

🧭

VDH

Vector search stack. VECTOR stores embeddings. DiskANN indexes them. Hybrid combines SQL + vector similarity.

🤖

SIRE

AI integration from T-SQL. sp_invoke calls REST APIs. Identity = keyless auth. RAG = retrieve + generate. Embeddings = vector representation.

📝

TCWJ

Modern T-SQL features. Temporal = time-travel queries. CTEs = readable recursion. Window = analytical ranking. JSON = semi-structured.

🏢

LACL

Managed Instance differentiators. Link = real-time replication. Agent = scheduled jobs. Cross-DB = multi-database. Server-level logins.

🧵

AMAG

Fabric SQL database superpowers. Auto-tuning built in. OneLake mirroring automatic. Indexes managed. GitHub native.

🔍

DDM

Obfuscate data in query results without changing stored data. Mask types: default, email, partial, random, custom.

📊

CRN

Three distance metrics for VECTOR_DISTANCE. Cosine for text similarity. Dot product for normalised vectors. Euclidean for spatial distance.

📦

CHUNK

RAG chunking pipeline. Split text into chunks. Overlap for context. Generate embeddings. Search nearest neighbours. Return top-K.

🛡️

SHD

Azure SQL DB tiers. Serverless = auto-pause. Hyperscale = 100 TB+. DTU = bundled. vCore = flexible compute/storage.

🔧

IQP

Automatic query tuning. Adaptive joins, batch mode on rowstore, memory grant feedback, DOP feedback, parameter sensitivity plan.

💾

DACPAC

Declarative schema deployment. Contains table/view/proc definitions. SqlPackage publishes, extracts, exports, imports.

Section 4 / Contrast Memory

Versus Arena

Side-by-side comparisons to sharpen your understanding of similar concepts.

Azure SQL DBvsManaged Instance

Azure SQL DB vs Managed Instance

Aspect	Azure SQL DB	Managed Instance
Compatibility	Database-scoped features	Near 100% SQL Server
SQL Agent	No (use Elastic Jobs)	Yes (built-in)
Cross-DB queries	No (use Elastic Query)	Yes (native)
CLR/.NET	No	Yes
Deployment model	Single database / elastic pool	Instance with multiple DBs
Best for	New cloud-native apps	Lift-and-shift from SQL Server
Scaling	Serverless / Hyperscale	vCore tiers

Fabric SQL DBvsAzure SQL DB

Fabric SQL DB vs Azure SQL DB

Aspect	Fabric SQL DB	Azure SQL DB
Management	Autonomous (auto-tune)	Manual tuning options
OneLake mirror	Automatic	Not built-in
Billing	Fabric capacity (CUs)	DTU or vCore
CI/CD	Built-in GitHub integration	Manual DACPAC / GitHub Actions
Analytics	Direct Lake via OneLake	Separate Fabric/PBI connection
Best for	Fabric-native apps, AI + analytics	Standalone PaaS workloads

RLSvsDynamic Data Masking

RLS vs Dynamic Data Masking

Aspect	RLS	Dynamic Data Masking
What it hides	Entire rows	Column values
Mechanism	Security policy + predicate function	Mask definition on column
Stored data	Unchanged (filter only)	Unchanged (display only)
Bypass	Only with ALTER SECURITY POLICY	UNMASK permission
Use case	Multi-tenant row isolation	Hide SSN/email from helpdesk
Granularity	Row level	Column level

ColumnstorevsRowstore Indexes

Columnstore vs Rowstore Indexes

Aspect	Columnstore	Rowstore Indexes
Storage	Column-oriented, compressed	Row-oriented, B-tree
Best for	Analytics, aggregations, scans	OLTP, point lookups, seeks
Compression	10x+ compression ratio	Page/row compression (modest)
Batch mode	Native batch processing	Row-by-row (batch on rowstore w/IQP)
Updates	Delta store + merge	In-place B-tree updates
When to use	Data warehouse, reporting tables	Transactional tables, CRUD

DACPACvsMigration-Based Deploy

DACPAC vs Migration-Based Deploy

Aspect	DACPAC	Migration-Based Deploy
Approach	Declarative (desired state)	Imperative (ordered scripts)
Drift handling	Auto-detects, generates diff	Must track manually
Rollback	No built-in rollback	Down migrations possible
Tooling	SqlPackage, SQL Database Projects	EF Core, Flyway, DbUp
Best for	Full schema management, CI/CD	Incremental, data-aware changes
Data loss risk	Blocks by default (set AllowDataLoss)	Explicit control per migration

Vector SearchvsFull-Text Search

Vector Search vs Full-Text Search

Aspect	Vector Search	Full-Text Search
Matching	Semantic similarity	Keyword / lexical matching
Index type	DiskANN vector index	Full-text index (inverted)
Query	VECTOR_DISTANCE()	CONTAINS / FREETEXT
Data type	VECTOR (embeddings)	Text (varchar/nvarchar)
AI required	Yes (embedding model)	No
Best for	Semantic search, RAG, recommendations	Keyword search, document retrieval

Always EncryptedvsTDE

Always Encrypted vs TDE

Aspect	Always Encrypted	TDE
Encryption scope	Selected columns	Entire database at rest
Server sees plaintext	Never	Yes (data decrypted in memory)
Key management	Column master key (client-side)	Database encryption key (server)
Query support	Equality only (deterministic)	Full T-SQL (transparent)
Use case	PII, secrets (untrusted DBA)	Compliance, at-rest protection
Performance	Higher overhead per column	Minimal overhead

Query StorevsExtended Events

Query Store vs Extended Events

Aspect	Query Store	Extended Events
Purpose	Plan history + regression detection	Detailed event tracing
Persistence	Stored in database (survives restart)	Session-based (configure target)
Overhead	Low (always-on recommended)	Variable (depends on events)
Analysis	Built-in reports, top queries	Custom event sessions
Use case	Plan forcing, regression fixing	Deadlock analysis, wait tracing
AI integration	Identifies candidates for auto-tune	Diagnostic deep dives

ServerlessvsProvisioned Compute

Serverless vs Provisioned Compute

Aspect	Serverless	Provisioned Compute
Compute	Auto-scales, auto-pauses	Fixed, always running
Cost	Pay per second of use	Pay for provisioned capacity
Cold start	Yes (resume delay)	No (always warm)
Best for	Dev/test, intermittent workloads	Production, steady workloads
Min/max vCores	Configurable range	Fixed tier
Auto-pause	After configurable idle period	Not available

Section 5 / Grouping Memory

Cheat Sheet

Organised reference grouped by exam domain — everything you need on one page.

Design & Develop

40-45%

T-SQL Development

Stored procedures: encapsulate logic, parameterised, recompile hints
Functions: scalar (single value), table-valued (inline vs multi-statement)
CTEs: WITH clause for readable recursion and complex query decomposition
Window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE
Temporal tables: system-versioned, FOR SYSTEM_TIME AS OF / BETWEEN
JSON: OPENJSON, JSON_VALUE, JSON_QUERY, FOR JSON AUTO/PATH

Database Design

Normalisation (3NF) for OLTP — reduce redundancy, enforce integrity
Star schema (facts + dimensions) for analytical/reporting workloads
Table partitioning: partition function + scheme, aligned indexes
Computed columns: persisted for indexing, non-persisted for derived values
Indexed views: materialised aggregations for repeated queries
Filegroups: separate data, indexes, and LOB across storage

SQL Platforms

SQL Server 2022: vector support, ledger tables, IQP, Azure Arc
Azure SQL DB: PaaS, serverless/Hyperscale, elastic pools, DiskANN
Managed Instance: near 100% compat, SQL Agent, cross-DB, Link feature
Fabric SQL DB: autonomous, auto-mirror OneLake, GitHub CI/CD, auto-index

Schema Management

SQL Database Projects (.sqlproj): schema-as-code in source control
Schema comparison: detect drift between dev/staging/prod
Synonyms: abstract database/server references for portability
Sequences: NEXT VALUE FOR — cross-table identity alternative

Secure, Optimise & Deploy

25-30%

Security Features

RLS: CREATE SECURITY POLICY + inline TVF predicate, filter/block
Dynamic Masking: default(), email(), partial(), random() mask functions
Always Encrypted: deterministic (equality) vs randomised, secure enclaves
TDE: transparent at-rest encryption, on by default in Azure SQL
Entra auth: managed identity, no passwords, token-based authentication
Ledger tables: append-only, tamper-evident with cryptographic hashes

Performance Tuning

Query Store: top resource consumers, plan forcing, regression detection
IQP: adaptive joins, batch mode on rowstore, memory grant feedback
Columnstore: 10x compression, batch processing for analytics
Wait stats: PAGEIOLATCH (I/O), LCK (locks), CXPACKET (parallelism)
Automatic tuning: auto-create index, force plan, drop unused index
Statistics: auto-update, manual UPDATE STATISTICS for large tables

CI/CD & Deployment

DACPAC: declarative schema, SqlPackage publish/extract/export/import
GitHub Actions: build .sqlproj, run sqlcmd tests, deploy to Azure SQL
Schema comparison: prevent unintended drift between environments
Fabric SQL: built-in GitHub integration, automatic deployments
Blue-green deployment: deployment slots, connection string swap
Database copy / point-in-time restore for testing and rollback

Monitoring & Diagnostics

Query Store reports: regressed queries, overall resource consumption
DMVs: sys.dm_exec_query_stats, sys.dm_os_wait_stats, sys.dm_db_index_usage_stats
Extended Events: lightweight tracing for deadlocks, waits, errors
Azure Monitor: alerts, metrics, diagnostic logging for Azure SQL
Intelligent Insights: automated performance diagnostics in Azure SQL

AI Capabilities

30-35%

Vector Search

VECTOR data type: store embeddings (up to 1998 dimensions)
DiskANN index: approximate nearest neighbour for high-performance search
VECTOR_DISTANCE: cosine, dot_product, euclidean distance functions
Hybrid search: WHERE clause filters + ORDER BY VECTOR_DISTANCE
Embedding storage: VECTOR column or varbinary(max) with conversion

Azure OpenAI Integration

sp_invoke_external_rest_endpoint: call REST APIs from T-SQL
Generate embeddings: text-embedding-3-small / ada-002 models
Chat completions: GPT-4o for in-database generative AI
Managed identity: system-assigned, no API keys in code or config
Rate limiting: handle HTTP 429, implement retry with backoff

RAG Pattern

Pipeline: query → embedding → vector search (top-K) → augment prompt → LLM
Chunking: fixed-size, sentence-based, semantic — with overlap
Embedding refresh: batch job or trigger-based on data changes
Token budget: fit retrieved context within model token limits
Grounding: include source references for verifiable AI responses

AI Patterns & Use Cases

Semantic search: conceptual similarity beyond keyword matching
Data enrichment: auto-classify, summarise, translate on INSERT/UPDATE
Anomaly detection: LLM-assisted data quality checks
Recommendations: vector similarity for content/product matching
Batch processing: generate embeddings for large datasets with chunking

Section 6 / Method of Loci

The Memory Palace

Walk through themed rooms — each object anchors a concept in spatial memory.

The SQL Workshop

Development — Where queries take shape

📝

Stored Procedures

Encapsulate business logic. Parameters, error handling, TRY/CATCH

🔢

Window Functions

ROW_NUMBER, RANK, LAG/LEAD, NTILE. Analytics without GROUP BY

⏰

Temporal Tables

System-versioned. FOR SYSTEM_TIME AS OF. Point-in-time queries

📋

CTEs & JSON

WITH clause for readability. OPENJSON, JSON_VALUE, FOR JSON

🏗️

Table Design

3NF for OLTP. Star schema for analytics. Partitioning for scale

📊

Indexed Views

Materialised aggregations. Persisted computed columns. Schema binding

The Platform Gallery

Infrastructure — Where databases live

🖥️

SQL Server 2022

On-prem/IaaS. Vector support. Ledger tables. Azure Arc integration

☁️

Azure SQL Database

PaaS. Serverless auto-pause. Hyperscale 100 TB. DiskANN vectors

🏢

Managed Instance

Near 100% SQL Server compat. SQL Agent. Cross-DB queries. Link feature

🧵

Fabric SQL Database

Autonomous. Auto-mirror OneLake. Auto-index. GitHub CI/CD built in

💰

Elastic Pools

Share vCore/DTU across multiple databases. Cost optimisation

💤

Serverless Tier

Auto-pause after idle. Auto-scale vCores. Pay per second of usage

The Security Vault

Protection — Where trust is enforced

🔒

Row-Level Security

Filter predicates per user. CREATE SECURITY POLICY. Multi-tenant isolation

🎭

Dynamic Data Masking

Obfuscate display. default(), email(), partial(), random() masks

🔐

Always Encrypted

Client-side encryption. Server never sees plaintext. Secure enclaves

🛡️

TDE

Transparent at-rest encryption. On by default in Azure SQL

👤

Entra Authentication

Managed identities. No passwords. Token-based. Passwordless connections

📖

Ledger Tables

Tamper-evident. Cryptographic hashes. Append-only audit trail

The Engine Room

Performance — Where speed is forged

📊

Query Store

Plan history. Top consumers. Force plans. Regression detection

🧠

Intelligent QP

Adaptive joins. Batch mode on rowstore. Memory grant feedback. DOP feedback

📑

Columnstore

10x compression. Batch processing. Best for analytics and aggregations

⏱️

Wait Stats

PAGEIOLATCH, LCK, CXPACKET. Diagnose I/O, locking, parallelism issues

🔄

Auto-Tuning

Create indexes. Force plans. Drop unused indexes. Azure SQL built-in

📈

Statistics

Auto-update stats. Cardinality estimation. UPDATE STATISTICS for accuracy

The Deployment Hangar

CI/CD — Where code ships to production

📦

SQL Database Projects

.sqlproj schema-as-code. Build to DACPAC. Source control everything

🚀

SqlPackage

Publish, extract, export, import. Declarative schema deployment

🛠️

GitHub Actions

Build DACPAC. Run tests. Deploy to Azure SQL. Automated pipelines

🔍

Schema Comparison

Detect drift. Compare dev vs prod. Generate diff scripts

🧵

Fabric CI/CD

Built-in GitHub integration. Auto-deploy on push. Native to Fabric SQL

🔄

Point-in-Time Restore

Recover to any second within retention. Test deployments safely

The Vector Observatory

AI — Where intelligence meets data

🧭

VECTOR Data Type

Store embeddings. Up to 1998 dimensions. Native SQL column type

⚡

DiskANN Index

Approximate nearest neighbour. Billion-scale vector search. Low latency

📏

VECTOR_DISTANCE

Cosine, dot product, Euclidean. Compare embedding similarity

🤖

sp_invoke_external_rest

Call Azure OpenAI from T-SQL. Embeddings + completions. Managed identity

💡

RAG Pipeline

Query → embed → vector search → augment → LLM. All in T-SQL

🔎

Hybrid Search

Combine SQL WHERE filters with vector similarity. Best of both worlds

Section 7 / Pattern Recognition

Pattern Spotter

Decision trees and trigger-answer pairs — see the pattern, know the answer.

Which SQL Platform?

Which SQL Platform?
  ├── Full SQL Server compat, SQL Agent, cross-DB queries → Azure SQL Managed Instance
  ├── Cloud-native PaaS, serverless auto-pause → Azure SQL Database
  ├── Fabric ecosystem, auto-mirror to OneLake → Fabric SQL Database
  ├── On-premises, full control, IaaS → SQL Server 2022
  ├── Massive scale, 100 TB+, fast read replicas → Azure SQL Hyperscale
  └── Dev/test, intermittent, cost-sensitive → Azure SQL Serverless

Which Security Feature?

Which Security Feature?
  ├── Hide entire rows from specific users → Row-Level Security (RLS)
  ├── Obfuscate column values in query results → Dynamic Data Masking
  ├── Encrypt columns, server never sees plaintext → Always Encrypted
  ├── Encrypt entire database at rest → Transparent Data Encryption (TDE)
  ├── Eliminate passwords from connections → Entra Auth + Managed Identity
  └── Tamper-proof audit trail → Ledger Tables

Which Index Type?

Which Index Type?
  ├── OLTP point lookups and range scans → Clustered + Nonclustered (B-tree)
  ├── Analytics, aggregations, large scans → Columnstore Index
  ├── Vector similarity / nearest neighbour → DiskANN Vector Index
  ├── Keyword / document search → Full-Text Index
  ├── Subset of rows only → Filtered Index
  └── Include non-key columns for coverage → Nonclustered with INCLUDE

Which AI Pattern?

Which AI Pattern?
  ├── Find conceptually similar records → Vector Search (VECTOR_DISTANCE)
  ├── Answer questions using your data → RAG (vector search + LLM)
  ├── Auto-classify or summarise on INSERT → AI Data Enrichment (trigger + sp_invoke)
  ├── Find similar products or content → Embedding-Based Recommendations
  ├── Detect data quality issues at scale → LLM-Assisted Anomaly Detection
  └── Combine keyword + meaning search → Hybrid Search (SQL WHERE + vectors)

Decision Cards

"sp_invoke_external_rest_endpoint"→Call Azure OpenAI from T-SQL

"VECTOR_DISTANCE" or "cosine similarity"→Vector similarity search in SQL

"DiskANN" or "vector index"→Approximate nearest neighbour index

"RAG" or "retrieval-augmented generation"→Vector search + LLM completion

"VECTOR data type"→Store embeddings natively in SQL

"RLS" or "Row-Level Security"→Filter rows per user via security policy

"Always Encrypted" or "column encryption"→Client-side encryption, server blind

"Dynamic Data Masking"→Obfuscate sensitive column values

"Query Store" or "plan regression"→Track plans, force good ones

"IQP" or "Intelligent Query Processing"→Auto-tune: adaptive joins, memory grant feedback

"DACPAC" or "SqlPackage"→Declarative schema deployment

"SQL Database Projects" or ".sqlproj"→Schema-as-code in source control

"Fabric SQL" or "auto-mirror OneLake"→Autonomous SQL in Fabric ecosystem

"Managed Instance" or "lift-and-shift"→Near 100% SQL Server compat PaaS

"temporal tables" or "system-versioned"→Point-in-time queries and audit

"columnstore" or "batch mode"→10x compression for analytics

"serverless" or "auto-pause"→Azure SQL cost optimisation tier

"managed identity" or "keyless auth"→Entra token-based, no passwords

Ready to certify?

Train with practitioners, not presenters

Lucid Labs delivers Microsoft certification training led by Microsoft Certified Trainers (MCTs) and grounded in real-world project experience. We adapt every session to your team's environment, data stack, and business objectives — because the best exam prep comes from engineers who build these solutions every day.

🎯

Tailored Content

Training built around your actual databases, your SQL workloads, and your AI use cases — not generic slides.

🛠️

Hands-On Labs

Work through real scenarios with vector search, RAG pipelines, and Azure OpenAI integration in your own environment.

📈

Exam + Capability

Pass the exam and build lasting SQL AI skills your team can apply from day one.

Talk to us about Microsoft SQL AI Developer (DP-800) training

Custom training for teams & individuals — remote or on-site across Australia

Keith Oak

Chief Technology Officer — Lucid Labs

Microsoft Solutions Partner architect specialising in Fabric, Azure Data & AI, and GitHub Enterprise. 18+ years delivering data platforms for Australian businesses — building the systems these exams test every day.

LinkedIn ↗lucidlabs.com.au ↗Published 29-03-2026