---
title: "DP-800 Study Guide — Master the DP-800"
description: "An interactive study guide built on 7 memory techniques to help you pass the Microsoft SQL AI Developer Associate exam."
url: "https://lucidlabs.com.au/insights/dp-800"
---

# 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 Intel**T-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 Intel**Know 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 Intel**Normalisation (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 Intel**RLS: 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 Intel**Query 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 Intel**SQL 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 Intel**VECTOR 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 Intel**sp\_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 Intel**RAG 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 Intel**Fabric 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

**S**QL Server, **A**zure SQL DB, **M**anaged Instance, **F**abric SQL

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

🔒

RATE

**R**LS, **A**lways Encrypted, **T**DE, **E**ntra Auth

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

⚡

QIWC

**Q**uery Store, **I**QP, **W**ait Stats, **C**olumnstore

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

🚀

SDGD

**S**qlproj, **D**ACPAC, **G**itHub Actions, **D**eploy

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

🧭

VDH

**V**ECTOR type, **D**iskANN index, **H**ybrid search

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

🤖

SIRE

**S**p\_invoke, **I**dentity auth, **R**AG, **E**mbeddings

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

📝

TCWJ

**T**emporal tables, **C**TEs, **W**indow functions, **J**SON

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

🏢

LACL

**L**ink feature, **A**gent, **C**ross-DB queries, **L**ogins

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

🧵

AMAG

**A**uto-tune, **M**irror to OneLake, **A**uto-index, **G**itHub CI/CD

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

🔍

DDM

**D**ynamic **D**ata **M**asking

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

📊

CRN

**C**osine, do**R** product, euclidea**N**

Three distance metrics for VECTOR\_DISTANCE. Cosine for text similarity. Dot product for normalised vectors. Euclidean for spatial distance.

📦

CHUNK

**C**ut text, **H**andle overlap, **U**se embeddings, **N**earest neighbour, **K**\-top results

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

🛡️

SHD

**S**erverless, **H**yperscale, **D**TU/vCore

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

🔧

IQP

**I**ntelligent **Q**uery **P**rocessing

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

💾

DACPAC

**D**ata-tier **A**pplication **PAC**kage

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.

vs

Azure SQL DBvsManaged Instance

Click to compare

#### 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 |

Click to flip back

vs

Fabric SQL DBvsAzure SQL DB

Click to compare

#### 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 |

Click to flip back

vs

RLSvsDynamic Data Masking

Click to compare

#### 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 |

Click to flip back

vs

ColumnstorevsRowstore Indexes

Click to compare

#### 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 |

Click to flip back

vs

DACPACvsMigration-Based Deploy

Click to compare

#### 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 |

Click to flip back

vs

Vector SearchvsFull-Text Search

Click to compare

#### 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 |

Click to flip back

vs

Always EncryptedvsTDE

Click to compare

#### 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 |

Click to flip back

vs

Query StorevsExtended Events

Click to compare

#### 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 |

Click to flip back

vs

ServerlessvsProvisioned Compute

Click to compare

#### 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 |

Click to flip back

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](https://lucidlabs.com.au/#contact?service=training-consulting&message=I'm%20interested%20in%20SQL%20AI%20Developer%20\(DP-800\)%20training%20for%20my%20team%20-%20covering%20T-SQL%2C%20Azure%20SQL%2C%20Fabric%20SQL%2C%20and%20AI%20integration.)

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

![Keith Oak](https://lucidlabs.com.au/team/koak-400.jpg)

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 ↗](https://www.linkedin.com/in/keithoak/)[lucidlabs.com.au ↗](https://lucidlabs.com.au/)Published 29-03-2026