| tags | DP-800, Reference |
|---|---|
| GA | G-DXYJBX6BH8 |
A 3-day intermediate course covering how to design, develop, secure, optimize, and deploy AI-enabled database solutions using Microsoft SQL platforms. Prepares for the Microsoft Certified: SQL AI Developer Associate (exam DP-800). Topics span T-SQL programmability, CI/CD for database projects, vector search, embeddings, and Retrieval-Augmented Generation (RAG) in T-SQL.
:::success
Date: 20260804
Course ID: 104188
:::
:::info Course Survey: https://aka.ms/dp800survey :::
:::success
Training key: E9C6FD76A9443435
:::
In-Memory OLTP overview and usage scenarios
Graph Processing - SQL Server and Azure SQL Database
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE JSON INDEX (Transact-SQL)
CREATE SEQUENCE (Transact-SQL)
Introduction to memory-optimized tables
Create an updatable ledger table
CREATE EXTERNAL TABLE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
WITH common_table_expression (Transact-SQL)
Format query results as JSON with FOR JSON
GitHub Copilot in SQL Server Management Studio
SQL MCP Server overview (Preview)
Adding repository custom instructions for GitHub Copilot
Microsoft Entra authentication for Azure SQL
Monitor performance by using the Query Store
vCore purchasing model - Azure SQL Database
Query Performance Insight - Azure SQL Database
SET TRANSACTION ISOLATION LEVEL
Quickstart: Use Data API builder with SQL
Change Data Capture with Azure SQL Database
CREATE EXTERNAL MODEL (Transact-SQL)
AI_GENERATE_EMBEDDINGS (Transact-SQL)
Vector search and vector indexes in SQL Server
Intelligent applications and AI in SQL Server
AI_GENERATE_CHUNKS (Transact-SQL)
VECTOR_DISTANCE (Transact-SQL)
CREATE VECTOR INDEX (Transact-SQL)
sys.sp_invoke_external_rest_endpoint (Transact-SQL)
CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
Intelligent applications and AI in SQL Server
Remove square brackets from JSON with WITHOUT_ARRAY_WRAPPER
# DP-800T00-A: Develop AI-enabled database solutions
## M01 - Design and implement database objects with SQL
### Table design
- Select appropriate data types, sizes, and columns
- Implement [in-memory](https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios?view=sql-server-ver17), [temporal](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver17), external, ledger, and graph tables
- Use temporal tables for built-in system-time history
### Data integrity and scale
- Define primary key, foreign key, unique, check, and default constraints
- Generate ordered values with sequences
- Design JSON columns and indexes
- Apply index design principles to support query access patterns
- [Partition tables and indexes](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver17) by data ranges
## M02 - Implement programmability objects with SQL
### Reusable database logic
- Use views to simplify access and establish security boundaries
- Encapsulate business logic in stored procedures
- Return calculated values with scalar functions
### Table-valued functions and triggers
- Choose inline or multi-statement table-valued functions
- Use parameterized functions for dynamic filtering
- Respond to changes with [DML, DDL, and logon triggers](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers?view=sql-server-ver17)
- Use `INSTEAD OF` triggers to control updates through views
## M03 - Write advanced T-SQL code
### Analytical and hierarchical queries
- Build [common table expressions and recursive CTEs](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver17)
- Apply ranking, aggregation, and running totals with [window functions](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-window-transact-sql?view=sql-server-ver17)
- Use correlated subqueries when results depend on an outer row
### Semi-structured and connected data
- Parse and construct JSON with `OPENJSON` and `FOR JSON`
- Query graph relationships with `MATCH`
- Apply regular expressions and fuzzy string matching
### Reliable execution
- Handle errors with [`TRY...CATCH`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver17)
- Inspect error details and control transaction outcomes
## M04 - Implement SQL solutions by using AI-assisted tools
### AI-assisted SQL development
- Use [GitHub Copilot in SSMS](https://learn.microsoft.com/en-us/ssms/github-copilot/overview) and VS Code
- Use Fabric Copilot for supported SQL development experiences
- Review and validate generated SQL before execution
- Evaluate the security impact of AI tools and keep credentials out of prompts
### Context and customization
- Configure model and tool options in chat sessions
- Define [repository guidance](https://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/add-custom-instructions/add-repository-instructions) in `.github/copilot-instructions.md`
- Connect SQL and Fabric [MCP server endpoints](https://learn.microsoft.com/en-us/azure/data-api-builder/mcp/overview)
- Use MCP tools to provide structured database context
## M05 - Implement data security and compliance with SQL
### Protect data
- Encrypt sensitive columns with [Always Encrypted](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver17)
- Choose deterministic encryption when equality comparisons are required
- Protect data files with Transparent Data Encryption
- Obscure query results with [dynamic data masking](https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver17)
### Control access and compliance
- Filter rows with row-level security policies
- Grant least-privilege object permissions
- Authenticate Azure SQL users with Microsoft Entra identities
- Audit database activity to supported destinations
- Secure model and API endpoints with scoped credentials and permissions
## M06 - Optimize database performance
### Platform and concurrency choices
- Compare General Purpose, Business Critical, and Hyperscale tiers
- Select isolation levels for consistency and concurrency
- Use row versioning to reduce reader-writer blocking
### Diagnose query performance
- Compare [estimated and actual execution plans](https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver17)
- Identify resource-intensive queries with dynamic management views
- Investigate blocking and deadlocks
### Stabilize workloads
- Capture query history with [Query Store](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17)
- Detect regressed plans and force a known plan
- Use Query Performance Insight for Azure SQL workloads
## M07 - Implement CI/CD by using SQL database projects
### Database projects and source control
- Model database objects in [SDK-style SQL projects](https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver17)
- Build and validate projects with `dotnet build`
- Organize schema objects as source-controlled files
- Manage changes through branches and pull requests
### Automated delivery
- Detect schema drift with schema comparison and SqlPackage
- [Deploy with GitHub Actions or Azure DevOps pipelines](https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-projects-automation?view=sql-server-ver17)
- Use `Azure/sql-action` to publish database changes
- Manage reference data with pre- and post-deployment scripts
- Add database unit and integration tests to the delivery pipeline
## M08 - Integrate SQL solutions with Azure services
### Data API builder
- Create a [`dab-config.json` configuration](https://learn.microsoft.com/en-us/azure/data-api-builder/overview)
- Define entities, field mappings, relationships, and permissions
- Reference connection strings with `@env()`
- Expose database objects through REST and GraphQL
### Azure integration
- Deploy APIs to supported Azure application services
- Monitor applications with Application Insights and Log Analytics
- Capture database changes with Change Data Capture
- Process change events with Azure Functions
## M09 - Design and implement models and embeddings with SQL
### Models and endpoints
- Evaluate AI models for SQL workloads
- Define model endpoint metadata with [`CREATE EXTERNAL MODEL`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-model-transact-sql?view=sql-server-ver17)
- Manage access to external model services
### Embedding design
- Divide content with fixed-size or semantic chunking
- Generate embeddings with [`AI_GENERATE_EMBEDDINGS`](https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=sql-server-ver17)
- Store embeddings in the `vector` data type
- Plan how embeddings are generated, refreshed, and indexed
## M10 - Design and implement intelligent search with SQL
### Text and semantic retrieval
- Search linguistic forms with [full-text predicates](https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver17)
- Use `CONTAINS` for precise conditions and `FREETEXT` for meaning
- Measure exact similarity with [`VECTOR_DISTANCE`](https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-distance-transact-sql?view=sql-server-ver17)
### Approximate and hybrid search
- Use [`VECTOR_SEARCH`](https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-search-transact-sql?view=sql-server-ver17) for approximate nearest-neighbor retrieval
- Create [vector indexes](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-vector-index-transact-sql?view=sql-server-ver17) for scalable search
- Choose distance metrics that match the embedding model
- Combine full-text relevance and vector similarity with reciprocal rank fusion
- Evaluate search quality, relevance, and performance
## M11 - Design and implement RAG with SQL
### Retrieval-augmented generation
- Retrieve top-ranked context with vector search
- Augment prompts with current database content
- Use RAG to ground responses without retraining a model
### T-SQL orchestration
- Format context with `FOR JSON PATH`
- Remove array wrappers for a single JSON object when required
- Call model endpoints with [`sp_invoke_external_rest_endpoint`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=sql-server-ver17)
- Store endpoint secrets in [database-scoped credentials](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver17)
- Grant `EXECUTE ANY EXTERNAL ENDPOINT` to authorized callers
- Parse model responses and handle endpoint or payload errors
Microsoft Certified: SQL AI Developer Associate
Get Certified SQL+AI (DP-800): Design and Develop SQL Solutions Like a Pro (APAC)
Get Certified DP-800: Secure, Optimize, & Ship SQL+AI Solutions (APAC)
Exam duration and question types
Exam scoring and score reports
Accessing Microsoft Learn during your certification exam
Microsoft Certification Exam Sandbox
Renew your Microsoft Certifications for free
Money Yu
- Mail: Money.Yu@microsoft.com
- LinkedIn: @abc12207