This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Implement AI capabilities in database solutions (25–30%) --> Design and implement models and embeddings --> Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
One of the most important aspects of building AI-enabled database applications is maintaining the accuracy of vector embeddings. Embeddings represent the semantic meaning of data at a specific point in time. Whenever the underlying source data changes, the associated embeddings may become outdated. If stale embeddings remain in a vector index, semantic search, Retrieval-Augmented Generation (RAG), recommendation engines, and AI assistants can produce inaccurate or misleading results.
For the DP-800 exam, candidates should understand the various methods available to detect changes to relational data and automatically regenerate embeddings. Microsoft SQL Server 2025 and Azure SQL provide several mechanisms to detect data changes, each with different tradeoffs in performance, scalability, complexity, and latency.
The exam focuses on selecting the most appropriate embedding maintenance strategy based on business requirements.
What Is Embedding Maintenance?
Embedding maintenance is the process of keeping vector embeddings synchronized with the underlying relational data.
Whenever data changes, one or more of the following actions may be required:
Generate a new embedding.
Replace the old embedding.
Update the vector index.
Remove deleted vectors.
Refresh search indexes.
Without proper maintenance, semantic search quality gradually degrades.
Why Embedding Maintenance Is Important
Suppose a product catalog contains this description:
“Wireless Bluetooth Noise-Cancelling Headphones”
An embedding is generated from that description.
Later, the product description changes to:
“Wireless Bluetooth Noise-Cancelling Headphones with Spatial Audio and USB-C Fast Charging”
If the embedding is not regenerated:
AI searches may not return the product.
Vector similarity decreases.
RAG answers become outdated.
Recommendation quality drops.
Keeping embeddings synchronized ensures AI applications remain accurate.
Common Embedding Maintenance Workflow
Most embedding maintenance solutions follow this lifecycle:
User Updates SQL Data
│
▼
Change Detection
│
▼
Generate New Embedding
│
▼
Store Updated Vector
│
▼
Refresh Vector Search Index
The primary difference between maintenance methods is how they detect changes.
Choosing the Right Maintenance Strategy
Microsoft provides several approaches:
Method
Typical Latency
Complexity
Best For
Table Triggers
Immediate
Low
Small databases
Change Tracking
Low
Medium
Incremental synchronization
Change Data Capture (CDC)
Medium
Medium
ETL and analytics
Azure Functions SQL Trigger
Near real-time
Medium
Event-driven cloud apps
Azure Logic Apps
Near real-time
Low
Low-code automation
Change Event Streaming (CES)
Real-time
High
Streaming architectures
Microsoft Foundry Pipelines
Scheduled or event-driven
Medium
AI data pipelines
Table Triggers
What Are They?
Table triggers automatically execute SQL code whenever data changes.
Example events include:
INSERT
UPDATE
DELETE
Triggers provide immediate notification that data has changed.
Embedding Workflow Using Triggers
UPDATE Product
│
▼
Trigger Executes
│
▼
Identify Changed Row
│
▼
Queue Embedding Job
The trigger usually should not generate the embedding itself because AI model inference may take several seconds.
Instead, the trigger inserts a work item into a processing queue.
Advantages
Immediate detection
Simple implementation
Works entirely within SQL
No polling required
Disadvantages
Can increase transaction duration
Poor choice for expensive AI operations
May reduce OLTP performance
Difficult to scale for very high transaction volumes
Best Practice
Use triggers only to record changes—not to call AI models directly.
Change Tracking
What Is Change Tracking?
Change Tracking is a lightweight SQL Server feature that records which rows have changed without recording every individual data modification.
Applications periodically retrieve changed rows and regenerate only affected embeddings.
Workflow
Application
│
▼
Read Change Tracking
│
▼
Changed Rows
│
▼
Generate Embeddings
│
▼
Update Vector Table
Advantages
Lightweight
Low storage overhead
Incremental processing
Excellent for synchronization
Limitations
Does not capture previous values
Does not store complete history
Requires periodic polling
Best Use Cases
RAG applications
Semantic search
Incremental embedding refresh
Azure SQL synchronization
Change Data Capture (CDC)
What Is CDC?
Change Data Capture records detailed information about every change made to a table.
It captures:
Inserts
Updates
Deletes
Previous values
New values
Log sequence numbers (LSNs)
CDC reads the SQL transaction log rather than relying on triggers.
Workflow
Transaction Log
│
▼
CDC Tables
│
▼
Embedding Pipeline
│
▼
Vector Updates
Advantages
Complete history
High reliability
Efficient large-scale processing
Ideal for ETL
Disadvantages
More storage than Change Tracking
Higher administrative overhead
Not truly instantaneous
Best Use Cases
Enterprise ETL
Large databases
Historical auditing
Batch embedding refresh
Comparing Change Tracking and CDC
Feature
Change Tracking
CDC
Tracks changed rows
Yes
Yes
Stores previous values
No
Yes
Transaction log based
No
Yes
Full history
No
Yes
Storage overhead
Low
Medium
Synchronization
Excellent
Excellent
Auditing
Limited
Excellent
Azure Functions with SQL Trigger Binding
Azure Functions provide serverless compute that automatically executes code when SQL data changes.
Instead of polling SQL continuously, the SQL trigger binding reacts to data modifications.
Typical workflow:
SQL Change
│
▼
Azure Function
│
▼
Generate Embedding
│
▼
Store Vector
Advantages
Serverless
Automatic scaling
Pay-per-execution
Near real-time processing
Excellent Azure integration
Best Use Cases
Cloud-native AI applications
Azure SQL Database
RAG systems
Intelligent search solutions
Azure Logic Apps
Azure Logic Apps provide a low-code workflow engine.
Instead of writing custom code, developers configure workflows visually.
Typical workflow:
SQL Change
│
▼
Logic App Trigger
│
▼
Call Azure OpenAI
│
▼
Update Embedding Table
Advantages
Low-code development
Hundreds of built-in connectors
Easy integration with Azure services
Fast implementation
Limitations
Less flexible than custom code
Higher latency than Azure Functions
Complex workflows can become difficult to maintain
Best Use Cases
Business automation
Small AI workflows
Rapid prototyping
Citizen developers
Choosing Between Triggers, Change Tracking, CDC, Azure Functions, and Logic Apps
Scenario
Recommended Method
Small OLTP database
Table Trigger + Queue
Incremental synchronization
Change Tracking
Historical auditing
CDC
Serverless AI processing
Azure Functions
Low-code workflow
Azure Logic Apps
DP-800 Exam Tips (Part 1)
Remember these key points for the exam:
Triggers provide immediate notification but should not directly perform expensive AI inference.
Change Tracking records which rows changed and is optimized for lightweight synchronization.
CDC captures detailed change history and is ideal for enterprise ETL and auditing.
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Implement AI capabilities in database solutions (25–30%) --> Design and implement models and embeddings --> Choose an embedding maintenance method, including table triggers, Change Tracking, Azure Functions with SQL trigger binding, Azure Logic Apps, CDC, CES, and Microsoft Foundry
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Change Event Streaming (CES)
What Is Change Event Streaming?
Change Event Streaming (CES) is an event-driven architecture that publishes database changes as a continuous stream of events. Instead of periodically polling the database for updates, applications subscribe to events as they occur.
In AI-enabled database solutions, CES enables embeddings to be regenerated almost immediately after data changes, making it well suited for near real-time AI applications.
Typical event streaming technologies include:
Azure Event Hubs
Azure Service Bus
Apache Kafka-compatible services
Microsoft Fabric Eventstreams
Azure Event Grid (for certain event-driven scenarios)
Although the underlying messaging technology can vary, the goal remains the same: publish changes once and allow multiple downstream consumers to react independently.
CES Workflow
Application Updates Product
│
▼
Database Change Event
│
▼
Event Stream
│
▼
Embedding Service
│
▼
Generate New Embedding
│
▼
Update Vector Table
│
▼
Refresh Vector Index
Unlike triggers, the database transaction completes first before downstream processing begins.
Advantages of CES
Near Real-Time Processing
Embeddings are regenerated within seconds instead of waiting for scheduled synchronization jobs.
Loose Coupling
The database does not directly invoke AI services.
Instead:
Database → Event Stream → AI Service
Each component evolves independently.
Scalability
Multiple consumers can process the same event stream simultaneously.
Examples include:
Embedding generation
Analytics
Notifications
Data warehouse loading
Audit logging
Reliability
Most event streaming platforms support:
Message durability
Retry policies
Dead-letter queues
Checkpointing
Replay capability
Limitations of CES
CES introduces additional infrastructure.
Organizations must manage:
Event brokers
Message retention
Consumer groups
Retry policies
Monitoring
Event ordering
Duplicate message handling
Consequently, CES is best suited to enterprise-scale systems rather than small departmental applications.
Best Use Cases for CES
CES is particularly appropriate for:
Large AI-powered search platforms
High-volume ecommerce catalogs
Recommendation engines
Enterprise RAG applications
Distributed microservices
Real-time personalization
AI copilots
Event-driven architectures
Microsoft Foundry for Embedding Maintenance
What Is Microsoft Foundry?
Microsoft Foundry (Azure AI Foundry) provides an end-to-end platform for building, evaluating, orchestrating, and managing AI applications.
Within embedding maintenance scenarios, Foundry can orchestrate the entire embedding lifecycle, including:
Detecting changes
Invoking embedding models
Validating outputs
Updating vector stores
Monitoring AI workloads
Managing model versions
Instead of writing custom orchestration code, developers can leverage Foundry pipelines and workflows.
Foundry Workflow
SQL Database
│
▼
Change Detection
│
▼
Foundry Pipeline
│
▼
Embedding Model
│
▼
Vector Generation
│
▼
Azure SQL Vector Column
│
▼
Vector Search
Advantages of Microsoft Foundry
Centralized AI Management
Developers manage:
Models
Prompts
Pipelines
Evaluations
Monitoring
within a unified environment.
Model Flexibility
Foundry supports many foundation models, including:
OpenAI GPT models
Phi models
Llama models
Mistral
Cohere
Other supported models
This flexibility allows organizations to switch models without redesigning their database architecture.
Integrated Evaluation
Foundry provides tools to evaluate:
Response quality
Latency
Cost
Safety
Groundedness
Hallucination rates
These capabilities help organizations choose the most appropriate embedding model over time.
Choosing the Appropriate Embedding Maintenance Method
The DP-800 exam expects candidates to recommend the most suitable approach for a given scenario.
Scenario 1
A small inventory system updates only a few records each day.
Recommended solution:
Table Trigger + Background Queue
Reason:
Simple implementation with minimal infrastructure.
Scenario 2
An ecommerce application updates thousands of products every hour.
Recommended solution:
Change Tracking
Reason:
Incremental synchronization with low overhead.
Scenario 3
A financial organization requires complete auditing of every database modification.
Recommended solution:
Change Data Capture (CDC)
Reason:
Captures historical values and detailed change information.
Scenario 4
A cloud-native AI chatbot must update embeddings immediately after documents change.
Recommended solution:
Azure Functions with SQL Trigger Binding
Reason:
Serverless, scalable, near real-time processing.
Scenario 5
A business analyst wants to automate embedding generation without writing code.
Recommended solution:
Azure Logic Apps
Reason:
Visual workflow designer with numerous connectors.
Scenario 6
A global ecommerce platform updates millions of products continuously.
Recommended solution:
Change Event Streaming (CES)
Reason:
Highly scalable event-driven architecture.
Scenario 7
An enterprise AI team manages multiple models and complex AI workflows.
Recommended solution:
Microsoft Foundry
Reason:
Centralized orchestration, evaluation, and lifecycle management.
Hybrid Architectures
Many enterprise solutions combine multiple technologies.
Example:
Azure SQL Database
│
▼
Change Tracking
│
▼
Azure Function
│
▼
Azure OpenAI Embedding Model
│
▼
Vector Table
│
▼
Azure AI Search
Or
CDC
│
▼
Event Hub
│
▼
Microsoft Foundry Pipeline
│
▼
Embedding Generation
│
▼
Azure SQL Vector Store
Hybrid solutions often provide the best balance between scalability, reliability, and operational simplicity.
Performance Considerations
When designing an embedding maintenance strategy, consider:
Latency
How quickly must embeddings be updated?
Seconds
Minutes
Hours
Overnight
Volume
How many records change?
Hundreds
Thousands
Millions
Cost
Real-time updates generally cost more than scheduled batch updates because they invoke AI services more frequently.
Reliability
Determine how failures are handled.
Best practices include:
Retry policies
Dead-letter queues
Logging
Checkpointing
Idempotent processing (safe repeated execution)
Scalability
Solutions should scale horizontally without affecting OLTP performance.
Avoid placing expensive AI inference directly inside database transactions.
Security Considerations
Embedding maintenance processes should follow Microsoft security recommendations.
Authentication
Prefer:
Managed Identity
Microsoft Entra ID
Avoid hardcoded API keys whenever possible.
Secret Storage
Store credentials in:
Azure Key Vault
Do not embed secrets in:
SQL scripts
Stored procedures
Source code
Configuration files checked into source control
Least Privilege
Embedding services should receive only the permissions required to:
✔ Choose the solution that balances latency, complexity, scalability, and cost.
❌ Azure Logic Apps are intended only for business workflows.
Instead:
✔ Logic Apps can orchestrate AI-powered embedding updates using Azure connectors.
DP-800 Exam Tips
For the exam, remember the following associations:
Requirement
Recommended Solution
Immediate notification
Table Trigger
Lightweight synchronization
Change Tracking
Full audit history
CDC
Serverless event processing
Azure Functions
Low-code automation
Azure Logic Apps
Massive real-time streaming
Change Event Streaming (CES)
AI orchestration and lifecycle management
Microsoft Foundry
Also remember:
Triggers are appropriate for detecting changes, but expensive AI operations should execute outside the transaction.
Change Tracking is optimized for incremental synchronization with minimal overhead.
CDC is best when historical change information is required.
Azure Functions provide scalable, event-driven embedding generation.
Azure Logic Apps are ideal for low-code integration workflows.
CES supports highly scalable, distributed, event-driven architectures.
Microsoft Foundry centralizes AI model management, orchestration, evaluation, and monitoring.
Key Takeaways
Choosing the right embedding maintenance strategy is essential for ensuring that vector representations remain synchronized with relational data. The optimal solution depends on business requirements for latency, scalability, complexity, cost, and governance. Smaller systems may benefit from triggers or Change Tracking, while enterprise AI applications often use Azure Functions, CES, or Microsoft Foundry to automate embedding generation at scale. Understanding the strengths and tradeoffs of each option is a key objective of the DP-800 certification exam.
Practice Exam Questions
Question 1
A company stores product descriptions in Azure SQL Database and generates vector embeddings for semantic search. Product descriptions change only a few times per week, and the company wants a lightweight mechanism to identify modified rows before regenerating embeddings.
Which feature should be recommended?
A. Change Tracking
B. AFTER UPDATE triggers
C. SQL Agent Jobs
D. Transaction Replication
Correct Answer:A
Explanation
Change Tracking records which rows have changed with minimal overhead, making it ideal for periodically identifying records whose embeddings need regeneration.
Why the other answers are incorrect:
B: Triggers execute synchronously and increase transaction time.
C: SQL Agent is not available in Azure SQL Database.
D: Replication is intended for data synchronization, not change detection for AI workflows.
Question 2
A financial services company must regenerate embeddings immediately after a customer profile changes because AI-powered recommendations must always reflect the latest data.
Which maintenance approach best satisfies this requirement?
A. Nightly batch processing
B. Azure Logic Apps scheduled every hour
C. AFTER INSERT and UPDATE table triggers
D. Weekly CDC processing
Correct Answer:C
Explanation
Table triggers execute immediately after data modifications, making them suitable when embeddings must remain synchronized with transactional data.
Why the other answers are incorrect:
A: Introduces unacceptable latency.
B: Scheduled workflows are not immediate.
D: CDC is asynchronous.
Question 3
A retailer updates millions of inventory records daily. Embedding generation is computationally expensive, and the organization wants processing to occur asynchronously without affecting transaction performance.
Which architecture is the best choice?
A. Table triggers that call Azure OpenAI directly
B. Change Data Capture combined with Azure Functions
C. Manual nightly exports
D. Recursive stored procedures
Correct Answer:B
Explanation
CDC captures database changes asynchronously, while Azure Functions can process those changes independently to generate embeddings.
Why the other answers are incorrect:
A: External service calls should not occur inside triggers.
C: Manual exports are inefficient.
D: Stored procedures are not designed for event-driven processing.
Question 4
A company wants a low-code solution that automatically updates embeddings whenever new documents are added while integrating with Azure AI services.
Which service should be recommended?
A. SQL CLR
B. Azure Kubernetes Service
C. Azure Logic Apps
D. SQL Replication
Correct Answer:C
Explanation
Azure Logic Apps provide low-code workflow automation and easily integrate SQL Database with Azure AI services.
Why the other answers are incorrect:
A: CLR is unsupported in Azure SQL Database.
B: AKS is unnecessary for simple workflows.
D: Replication does not generate embeddings.
Question 5
A global retailer wants multiple downstream applications—including AI pipelines, analytics systems, and notification services—to receive database change events independently.
Which technology is best suited?
A. SQL Agent
B. Change Event Streaming (CES)
C. Table triggers
D. Dynamic Data Masking
Correct Answer:B
Explanation
CES publishes change events that multiple consumers can process independently, making it ideal for scalable event-driven architectures.
Why the other answers are incorrect:
A: SQL Agent is scheduler-based.
C: Triggers execute only within the database transaction.
D: Dynamic Data Masking is unrelated.
Question 6
An organization wants a centralized AI platform that manages embedding generation, model lifecycle, monitoring, governance, and orchestration across multiple databases.
Which solution best meets these requirements?
A. Microsoft Foundry
B. SQL Server Agent
C. Azure Backup
D. Elastic Query
Correct Answer:A
Explanation
Microsoft Foundry provides enterprise AI orchestration, governance, monitoring, and centralized management of embedding workflows.
Why the other answers are incorrect:
B: SQL Agent schedules jobs only.
C: Azure Backup is unrelated.
D: Elastic Query supports distributed querying, not AI orchestration.
Question 7
A company stores thousands of product descriptions in an Azure SQL Database. New rows are added every few hours, while updates to existing descriptions are relatively rare. The organization wants an efficient solution that minimizes database overhead while identifying only rows that require regenerated embeddings.
Which approach should be recommended?
A. Enable Change Tracking and periodically process changed rows.
B. Create AFTER INSERT and AFTER UPDATE triggers that immediately regenerate embeddings.
C. Rebuild embeddings for every record every night.
D. Disable change detection and regenerate embeddings manually.
Correct Answer:A
Explanation
Change Tracking records which rows have changed without capturing full before-and-after values, making it lightweight and well suited for identifying documents requiring updated embeddings.
Why the other answers are incorrect:
B: Triggers increase transaction duration.
C: Full regeneration wastes resources.
D: Manual processes are unsuitable for production.
Question 8
A development team uses Azure SQL Database and wants embedding generation to occur automatically whenever qualifying data changes. The solution should require minimal infrastructure management while supporting serverless execution.
Which option best meets these requirements?
A. SQL Agent jobs
B. Azure Logic Apps with a daily recurrence trigger
C. Azure Functions using SQL trigger binding
D. Manual PowerShell execution
Correct Answer:C
Explanation
Azure Functions with SQL trigger binding provide event-driven, serverless processing that reacts automatically to SQL changes.
Why the other answers are incorrect:
A: SQL Agent is unavailable in Azure SQL Database.
B: Polling introduces unnecessary latency.
D: Manual execution is not scalable.
Question 9
A company has implemented Microsoft Foundry to orchestrate its AI workloads. Multiple databases contribute documents that require embeddings, and administrators want centralized orchestration, monitoring, and model lifecycle management.
Which embedding maintenance approach is most appropriate?
A. Table triggers on every database
B. Change Tracking only
C. Microsoft Foundry orchestration
D. Manual nightly SQL scripts
Correct Answer:C
Explanation
Microsoft Foundry provides centralized orchestration for AI pipelines, including embedding generation, monitoring, governance, and model management.
Why the other answers are incorrect:
A: Triggers do not provide orchestration.
B: Change Tracking only detects changes.
D: Manual scripts do not scale well.
Question 10
An organization maintains embeddings for customer support articles. The business requires that embedding updates remain resilient even if the external AI model becomes temporarily unavailable. Failed requests should be retried without affecting database transactions.
Which architecture best satisfies these requirements?
A. Generate embeddings inside SQL table triggers.
B. Use an asynchronous event-driven process such as CDC or CES combined with Azure Functions or Microsoft Foundry.
C. Regenerate every embedding immediately within the user transaction.
D. Require users to manually regenerate embeddings after every update.
Correct Answer:B
Explanation
An asynchronous architecture decouples database transactions from AI processing. Failed embedding generation requests can be retried without impacting database writes, improving resiliency and scalability.
Why the other answers are incorrect:
A: External service failures may block transactions.
C: Tightly coupling AI services to transactions reduces reliability.
D: Manual updates are inefficient and error-prone.
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Implement AI capabilities in database solutions (25–30%) --> Design and implement models and embeddings --> Identify which columns to include in embeddings
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
Embeddings are one of the most important technologies behind modern AI-powered applications such as semantic search, Retrieval-Augmented Generation (RAG), recommendation systems, document similarity, and intelligent chatbots. Rather than storing simple keywords, embeddings represent text as high-dimensional numerical vectors that capture semantic meaning.
One of the most important design decisions when implementing embeddings is determining which database columns should be embedded. Selecting the appropriate columns directly affects:
Search relevance
AI response quality
Storage requirements
Embedding generation cost
Update frequency
Query performance
The DP-800 exam expects candidates to understand how to evaluate database schemas and determine which columns should contribute meaningful semantic information to an embedding.
Why Column Selection Matters
Every embedding represents the semantic meaning of the text supplied to the embedding model.
For example, consider a Products table.
ProductID
Name
Description
Category
Price
SKU
101
Surface Laptop
Lightweight business laptop with AI features.
Laptop
1299
SL-100
If only Name is embedded:
Surface Laptop
the embedding contains very little context.
If Name + Description + Category are embedded:
Surface Laptop
Lightweight business laptop with AI features.
Laptop
the vector contains much richer semantic information.
This significantly improves semantic search accuracy.
General Rule
Good embedding columns contain:
Meaning
Context
Natural language
Descriptive text
Poor embedding columns contain:
IDs
Numbers
Codes
Random values
Technical metadata
Common Column Types
Excellent Candidates
These almost always improve embeddings.
Examples include:
Product descriptions
Document bodies
Knowledge base articles
Support tickets
FAQs
Customer reviews
Employee biographies
Blog articles
Research papers
Medical notes
Legal clauses
These contain rich natural language.
Example
"This laptop includes an NPU for AI acceleration and 18-hour battery life."
An embedding model can understand concepts such as:
AI laptop
battery life
portable computer
Windows device
Very Good Candidates
These provide additional context.
Examples
Product Name
Job Title
Category
Department
Brand
Tags
Keywords
Topics
Example
Category:
Gaming Laptop
Brand:
Contoso
Description:
High-performance RTX graphics...
Combining these improves semantic similarity.
Sometimes Useful
Examples
City
Country
Industry
Region
Language
These may improve retrieval depending on the application.
For example
Restaurant
Italian
Orlando
provides valuable context.
Usually Poor Candidates
These rarely improve embeddings.
Examples
Identity columns
GUIDs
Invoice numbers
SKUs
Phone numbers
ZIP codes
Timestamps
Row versions
Example
CustomerID = 28471
This carries no semantic meaning.
Never Useful
Examples
Binary files
Password hashes
Encryption keys
Checksums
Internal IDs
These should never be embedded.
Combining Multiple Columns
Rather than embedding each column individually, applications often concatenate multiple descriptive columns into one text document before generating the embedding.
Example
Instead of embedding
Title
only
combine
Title
Category
Description
Features
Example generated text
Surface Laptop
Category:
Business Laptop
Features:
Touchscreen
NPU
AI Copilot+
18-hour battery
Description:
Premium lightweight laptop designed for professionals...
This produces much richer embeddings.
Typical SQL Example
SELECT
Name +
CHAR(13)+
Description +
CHAR(13)+
Category
AS EmbeddingText
FROM Products;
This creates a single block of text for embedding generation.
Avoid Including Irrelevant Data
Do not include unrelated information simply because it exists.
Bad example
Description
LastModifiedDate
ModifiedBy
RecordID
Checksum
InternalVersion
Only Description contributes semantic meaning.
Include Business Context
Business metadata often improves search.
Instead of
Description
consider
Product Name
Category
Brand
Description
Features
The additional context helps distinguish similar products.
Example: Knowledge Base
Table
Title
Problem
Solution
Author
Good embedding text
Title
Problem Description
Solution
Avoid
Author
unless searching by author.
Example: HR Resume Database
Good columns
Name
Skills
Certifications
Experience
Summary
Poor columns
EmployeeID
HireDate
PayrollNumber
Example: Healthcare
Useful
Diagnosis
Symptoms
Treatment
Clinical Notes
Not useful
Patient ID
Visit Number
Insurance Number
Example: Retail
Useful
Product Name
Description
Category
Features
Brand
Not useful
Inventory Count
Warehouse Bin
SKU
Example: Support Tickets
Useful
Ticket Title
Problem Description
Resolution
Avoid
Ticket Number
Assigned Agent ID
Status Code
Structured Data vs Natural Language
Embedding models perform best with natural language.
Instead of
RAM=32
CPU=i9
GPU=RTX4090
consider
This gaming laptop includes 32 GB RAM, an Intel Core i9 processor, and an NVIDIA RTX 4090 GPU.
Natural language improves semantic understanding.
Sensitive Information
Avoid embedding:
Passwords
SSNs
Credit card numbers
API keys
Authentication tokens
Encryption secrets
Even if embeddings are stored securely, unnecessary sensitive information should never be included.
Column Size Considerations
Very large text columns increase:
Token count
API costs
Storage
Processing time
Strategies include:
Chunk long documents
Remove boilerplate text
Eliminate duplicate content
Exclude irrelevant sections
Embedding Maintenance
If embedded columns change frequently:
Regenerate embeddings
Track changes using Change Tracking or CDC
Automate updates using Azure Functions, Logic Apps, or Microsoft Foundry
Only regenerate embeddings when relevant columns change.
SQL Server 2025 and Azure SQL
Modern SQL AI solutions support storing vectors directly in SQL databases, allowing developers to:
Store embeddings in vector columns
Perform vector similarity searches
Integrate external embedding models
Build semantic search and RAG applications entirely within SQL-centric architectures
Choosing the correct columns is one of the most important design decisions before generating these vectors.
Best Practices
Embed descriptive text instead of identifiers.
Combine multiple related text columns into a single embedding input.
Include contextual metadata such as category or brand when it improves retrieval.
Exclude numeric identifiers, timestamps, and internal metadata.
Avoid embedding sensitive or confidential information.
Chunk long documents before generating embeddings.
Keep embeddings synchronized with changes to the source columns.
Evaluate search quality periodically and refine the selected columns if retrieval results are poor.
DP-800 Exam Tips
For the exam, you should be able to:
Identify which columns provide meaningful semantic content.
Recognize when multiple columns should be combined into one embedding.
Distinguish between descriptive text and operational metadata.
Explain why identifiers and numeric values generally should not be embedded.
Understand the tradeoffs between embedding more context versus increasing token usage and storage costs.
Recommend strategies for maintaining embeddings when source data changes.
Select appropriate columns for common business scenarios such as product catalogs, knowledge bases, customer support systems, healthcare records, HR profiles, and document repositories.
Practice Exam Questions
Question 1
A company is building a semantic search application for an online product catalog. The Products table contains the following columns:
ProductID
ProductName
Category
Description
Price
SKU
Which combination of columns should be included when generating embeddings?
A. ProductID, SKU, Price
B. ProductName, Category, Description
C. ProductID, ProductName, Price
D. SKU, Category, Price
Correct Answer:B
Explanation
Embeddings should be generated from columns containing meaningful natural language and business context. Product names, categories, and descriptions provide semantic information that improves search relevance.
Why the other answers are incorrect:
A: IDs, SKUs, and prices contain little semantic meaning.
C: ProductID and Price contribute little to semantic understanding.
D: SKU and Price are operational values, not descriptive content.
Question 2
A knowledge base stores the following columns:
ArticleTitle
ProblemDescription
Resolution
CreatedDate
ArticleID
The organization wants to optimize AI-powered question answering.
Which columns should be embedded?
A. CreatedDate and ArticleID
B. Resolution only
C. ArticleTitle, ProblemDescription, and Resolution
D. ArticleID, Resolution, and CreatedDate
Correct Answer:C
Explanation
The title, problem description, and resolution contain the contextual information required for semantic retrieval.
Why the other answers are incorrect:
A: Dates and IDs provide no semantic value.
B: Using only the resolution omits important context.
D: IDs and dates should generally not be embedded.
Question 3
An HR department is building an AI assistant to help recruiters locate qualified candidates.
Which candidate profile columns are most appropriate for embeddings?
A. EmployeeID, HireDate, PayrollNumber
B. ResumeSummary, Skills, Certifications, Experience
C. Salary, EmployeeID, OfficeNumber
D. ManagerID, DepartmentID, HireDate
Correct Answer:B
Explanation
Resume summaries, skills, certifications, and experience contain rich natural language describing candidate qualifications.
Why the other answers are incorrect:
A: Administrative identifiers have no semantic value.
C: Salary and IDs are not useful for semantic similarity.
D: Organizational metadata does not describe expertise.
Question 4
A retail company stores product specifications as structured fields:
RAM = 32
CPU = Intel Core i9
GPU = RTX 4090
What is the best practice before generating embeddings?
A. Embed each numeric value separately.
B. Ignore the specification fields completely.
C. Store each field in separate vector columns.
D. Convert the structured values into descriptive natural language.
Correct Answer:D
Explanation
Embedding models understand natural language far better than isolated structured values. Converting structured data into descriptive text produces higher-quality embeddings.
Why the other answers are incorrect:
A: Individual values lose important context.
B: Specifications provide valuable search information.
C: Multiple separate embeddings reduce semantic completeness.
Question 5
A legal document repository contains these columns:
DocumentTitle
ContractText
Author
VersionNumber
LastModifiedDate
Which columns are most appropriate for embeddings?
A. DocumentTitle and ContractText
B. VersionNumber and LastModifiedDate
C. Author and VersionNumber
D. LastModifiedDate and Author
Correct Answer:A
Explanation
Titles and contract text provide meaningful semantic information for legal document retrieval.
Why the other answers are incorrect:
B: Version numbers and dates are operational metadata.
C: Author names generally contribute little to semantic similarity.
D: Dates and authors are rarely useful for AI retrieval.
Question 6
A company stores customer support tickets with these columns:
TicketID
Title
Description
Resolution
AssignedEngineer
Which column should generally not be included when generating embeddings?
A. Resolution
B. Description
C. AssignedEngineer
D. Title
Correct Answer:C
Explanation
The assigned engineer is administrative information and usually has no relationship to the semantic content of the issue.
Why the other answers are incorrect:
A: Resolutions provide valuable context.
B: Descriptions are one of the best embedding sources.
D: Titles summarize the issue.
Question 7
A company wants to reduce embedding generation costs while maintaining high-quality semantic search.
Which strategy is most appropriate?
A. Embed every column in every table.
B. Embed only columns containing meaningful business context and descriptive text.
C. Embed only numeric columns.
D. Embed every database row regardless of relevance.
Correct Answer:B
Explanation
Embedding only semantically meaningful columns minimizes storage, token usage, and generation costs while maintaining search quality.
Why the other answers are incorrect:
A: Creates unnecessary costs.
C: Numeric values rarely provide semantic meaning.
D: Irrelevant data wastes resources.
Question 8
A medical database contains the following fields:
PatientID
Diagnosis
Symptoms
TreatmentPlan
InsurancePolicyNumber
Which field should not normally be included in embeddings?
A. Symptoms
B. Diagnosis
C. InsurancePolicyNumber
D. TreatmentPlan
Correct Answer:C
Explanation
Insurance policy numbers are identifiers and contribute no semantic value to AI retrieval.
Why the other answers are incorrect:
A: Symptoms are highly descriptive.
B: Diagnoses contain important clinical meaning.
D: Treatment plans improve retrieval relevance.
Question 9
An organization notices that AI search results have become less accurate after adding several metadata columns to the embedding input.
What is the most likely cause?
A. Semantic quality decreased because irrelevant metadata diluted the embedding.
B. Embedding vectors became encrypted.
C. The embedding model requires additional indexes.
D. SQL Server cannot process metadata columns.
Correct Answer:A
Explanation
Adding irrelevant metadata introduces noise into the embedding, reducing its ability to represent the true semantic meaning of the content.
Why the other answers are incorrect:
B: Embeddings are not automatically encrypted by adding metadata.
C: Indexes improve retrieval speed, not embedding quality.
D: SQL Server can process metadata columns; they simply should not be embedded unnecessarily.
Question 10
A company stores lengthy product manuals exceeding the token limit of its embedding model.
What is the recommended approach?
A. Truncate every document to the first paragraph.
B. Increase the vector dimension.
C. Store the manuals without embeddings.
D. Divide the manuals into logical chunks before generating embeddings.
Correct Answer:D
Explanation
Chunking large documents allows each section to remain within model limits while preserving semantic meaning. It also improves retrieval because searches return only the most relevant portions of a document.
Why the other answers are incorrect:
A: Truncation discards valuable information.
B: Vector dimensions do not increase a model’s token limit.
C: Without embeddings, semantic search cannot be performed effectively.
DP-800 Exam Tips
For the exam, remember these key principles:
Embed descriptive, natural-language columns such as names, descriptions, summaries, articles, reviews, and documentation.
Avoid identifiers and operational metadata, including IDs, SKUs, timestamps, version numbers, and status codes.
Combine related descriptive columns (for example, title + category + description) into a single text input to provide richer context.
Convert structured specifications into natural language when appropriate to improve semantic understanding.
Exclude sensitive information unless absolutely required and permitted by your security policies.
Chunk large documents before generating embeddings to stay within model token limits and improve retrieval quality.
Review embedding quality periodically and refine the selected columns based on search relevance and user feedback.
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Implement AI capabilities in database solutions (25–30%) --> Design and implement models and embeddings --> Design and implement chunks for embeddings
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
One of the most important design decisions when building AI-enabled database solutions is determining how data should be divided before generating embeddings. This process, known as chunking, directly affects the quality of semantic search, Retrieval-Augmented Generation (RAG), recommendation systems, and AI-powered question answering.
Embedding models convert text into numerical vector representations that capture semantic meaning. However, they have input token limits and perform best when the input represents a coherent concept rather than an entire document. Consequently, documents are usually divided into manageable sections called chunks, and an embedding is generated for each chunk individually.
For the DP-800 exam, Microsoft expects candidates to understand how to design chunking strategies, determine appropriate chunk sizes, preserve context through overlap, store chunk metadata, and balance retrieval quality with storage and processing costs.
What Is Chunking?
Chunking is the process of dividing a document or other textual content into smaller units before generating embeddings.
Instead of embedding an entire 100-page document, the document is divided into logical sections such as:
Chapters
Sections
Paragraph groups
Pages
Individual articles
Code blocks
FAQ entries
Each chunk is embedded independently and stored in a vector database or SQL table.
For example:
Original document
Employee Handbook
Chapter 1
Chapter 2
Chapter 3
Chapter 4
After chunking:
Chunk 1 → Introduction
Chunk 2 → Employee Benefits
Chunk 3 → Leave Policies
Chunk 4 → Workplace Conduct
Chunk 5 → Remote Work
Chunk 6 → Security Policies
Each chunk receives its own embedding vector.
Why Chunk Documents?
Large Language Models and embedding models have practical limitations.
Reasons for chunking include:
Token limits
Improved semantic accuracy
Faster vector searches
Reduced hallucinations
Better retrieval precision
Lower processing costs
Easier document updates
Embedding an entire document often produces a vector that represents many unrelated topics simultaneously, reducing search accuracy.
Chunking in a Retrieval-Augmented Generation (RAG) System
A typical RAG workflow follows these steps:
Documents
│
▼
Chunk Documents
│
▼
Generate Embeddings
│
▼
Store Vectors
│
▼
User Query
│
▼
Generate Query Embedding
│
▼
Similarity Search
│
▼
Retrieve Best Chunks
│
▼
Provide Context to LLM
│
▼
Generate Response
Without proper chunking, the retrieved context may be incomplete, overly broad, or irrelevant.
Characteristics of Good Chunks
An effective chunk should be:
Semantically coherent
Self-contained
Small enough for efficient retrieval
Large enough to preserve context
Easy to trace back to the original source
Consistent in formatting
Good chunks represent one primary idea or closely related concepts.
Example:
Good chunk:
Employees may work remotely up to three days each week with manager approval.
Poor chunk:
Employees may work remotely…
(ends halfway through explanation)
Incomplete chunks reduce retrieval quality.
Choosing an Appropriate Chunk Size
There is no universal chunk size.
Instead, chunk size depends on:
Document type
User questions
Embedding model limits
Retrieval strategy
Desired context
General guidelines:
Document Type
Typical Chunk Strategy
FAQ
One question and answer per chunk
Product documentation
One section per chunk
Legal contracts
One clause per chunk
Research papers
One subsection per chunk
Books
Several paragraphs per chunk
Source code
One function, class, or module per chunk
Knowledge articles
One topic per chunk
Microsoft generally emphasizes semantic chunking over arbitrary character counts.
Fixed-Length Chunking
The simplest strategy divides text into equal-sized pieces.
Example:
Chunk 1
1–500 characters
Chunk 2
501–1000 characters
Chunk 3
1001–1500 characters
Advantages:
Simple
Fast
Easy to automate
Disadvantages:
Breaks sentences
Splits ideas
Reduces semantic quality
Because it ignores meaning, fixed-length chunking is generally less effective than semantic approaches.
Semantic Chunking
Semantic chunking divides documents based on meaning rather than length.
Examples include:
Headings
Sections
Chapters
Topics
Complete paragraphs
Individual procedures
Entire FAQ entries
Example:
Instead of:
Characters 1–1000
Characters 1001–2000
Use:
Vacation Policy
Medical Leave
Parental Leave
Travel Reimbursement
Semantic chunking produces embeddings that more accurately represent individual concepts.
Sliding Window (Overlapping) Chunking
One common challenge occurs when important information spans the boundary between two chunks.
For example:
Chunk 1
Employees may work remotely
after manager approval.
Chunk 2
After manager approval,
employees must...
The phrase “after manager approval” belongs to both chunks.
To preserve context, systems use overlapping chunks.
Example:
Chunk 1
Sentence 1
Sentence 2
Sentence 3
Sentence 4
Chunk 2
Sentence 3
Sentence 4
Sentence 5
Sentence 6
The overlapping sentences help ensure continuity during retrieval.
Advantages of Overlapping Chunks
Overlap helps:
Preserve context
Improve semantic similarity
Prevent broken explanations
Improve RAG responses
Increase retrieval accuracy
Most enterprise RAG systems use overlap rather than completely independent chunks.
Drawbacks of Excessive Overlap
Too much overlap creates problems:
Duplicate embeddings
Larger storage requirements
Longer indexing time
Increased search costs
More duplicate search results
The overlap should be sufficient to maintain context without introducing excessive redundancy.
Chunking Structured Data
Not all embeddings originate from unstructured documents.
Structured SQL data can also be chunked.
Examples include:
Product descriptions
Customer support articles
Knowledge base records
Employee profiles
Medical summaries
Rather than combining multiple rows, each logical business record is typically embedded separately.
Example:
Products
Laptop A
Laptop B
Laptop C
Each product becomes its own chunk.
Chunking Source Code
Source code should not be chunked arbitrarily.
Preferred chunk boundaries include:
Functions
Methods
Classes
Modules
Stored procedures
SQL scripts
Example:
Instead of:
Characters 1–1000
Use:
CreateCustomer()
DeleteCustomer()
CalculateTax()
GenerateInvoice()
This preserves the logical structure of the code and improves semantic retrieval for developer-focused AI assistants.
Chunk Metadata
Every chunk should include metadata that enables accurate retrieval and traceability.
Common metadata includes:
Document ID
Chunk ID
Source file name
Page number
Section heading
Chunk sequence number
Creation date
Last modified date
Author
Security classification
Example:
Field
Value
Document
HR Handbook
Page
14
Section
Leave Policy
Chunk
5
Version
3.2
Metadata supports source attribution, document reconstruction, filtering, and citation generation.
Reconstructing Context
Many RAG applications retrieve more than one chunk for a user query.
For example:
Chunk 21
Chunk 22
Chunk 23
Because each chunk contains metadata indicating its position in the document, the application can present adjacent chunks together to provide richer context to the language model.
Testing Chunk Quality
Designing chunking strategies is an iterative process. After implementing a strategy, evaluate it using representative queries and measure retrieval quality.
Common evaluation criteria include:
Retrieval precision – Are the returned chunks relevant to the query?
Retrieval recall – Are important chunks consistently found?
Context completeness – Does each chunk contain enough information to answer the question?
Duplicate retrieval – Are overlapping chunks causing redundant results?
Latency – Does the chunking strategy affect search performance?
Storage efficiency – How much additional storage is required for embeddings and metadata?
Testing with real-world questions often reveals whether chunks are too large, too small, or improperly aligned with document structure.
Best Practices
Microsoft recommends following several best practices when designing chunks for embeddings:
Prefer semantic chunking over fixed-length splitting whenever possible.
Keep each chunk focused on a single topic or concept.
Use overlap to preserve context across chunk boundaries.
Preserve document structure by chunking at headings, sections, or clauses.
Store comprehensive metadata with every chunk.
Choose chunk sizes appropriate for the document type and expected user queries.
Avoid creating chunks that are so small they lose context or so large they dilute semantic meaning.
Re-evaluate chunking strategies as documents, embedding models, or application requirements evolve.
Test retrieval quality with representative workloads before deploying to production.
Balance retrieval accuracy against storage costs, indexing time, and search performance.
DP-800 Exam Tips
For the DP-800 exam, you should understand how chunk design influences the effectiveness of AI-enabled database solutions. Be prepared to compare fixed-length, semantic, and overlapping chunking strategies and recognize when each is appropriate. Expect scenario-based questions that ask you to optimize retrieval quality, preserve context, reduce hallucinations, or improve the performance of Retrieval-Augmented Generation (RAG) systems. Also know the importance of metadata, chunk boundaries, and testing strategies in building scalable, production-ready embedding solutions.
Practice Exam Questions
Question 1
You are creating a Retrieval-Augmented Generation (RAG) solution for an organization’s HR policy documents. Each document averages 40 pages.
What is the primary reason for dividing the documents into chunks before generating embeddings?
A. SQL databases cannot store documents larger than 1 MB.
B. Embedding models only support numeric data.
C. Smaller chunks improve semantic retrieval by capturing focused context.
D. Chunking compresses documents into fewer tokens.
Answer: C
Explanation: Embedding models generate vector representations for pieces of text. Smaller, semantically coherent chunks produce embeddings that represent specific concepts, making similarity searches much more accurate than embedding an entire document.
Question 2
A development team is designing chunk sizes for technical manuals.
Which chunking strategy generally produces the best retrieval quality?
A. Split documents into semantically meaningful sections of moderate length
B. One chunk for every document
C. Split every five characters
D. Create one chunk for every paragraph regardless of context
Answer: A
Explanation: Chunks should balance context and specificity. Semantically meaningful sections preserve enough information for accurate retrieval without becoming overly broad.
Question 3
Why are overlapping chunks commonly used when generating embeddings?
A. To reduce storage requirements
B. To eliminate duplicate vectors
C. To preserve context across chunk boundaries
D. To increase SQL query speed
Answer: C
Explanation: Without overlap, important information that spans chunk boundaries can be lost. Overlapping text ensures that concepts appearing near boundaries remain available during retrieval.
Question 4
Which factor has the greatest influence on selecting an appropriate chunk size?
A. The SQL Server version
B. The font used in the original document
C. The amount of database memory
D. The type of content and expected user queries
Answer: D
Explanation: Different document types require different chunk sizes. FAQs, manuals, legal documents, and source code each benefit from different chunking strategies based on how users search for information.
Question 5
A company stores product manuals that include headings, tables, and explanatory paragraphs.
Which chunking strategy is most appropriate?
A. Ignore document structure and split every 1,000 characters.
B. Chunk according to logical document sections while preserving headings.
C. Generate one embedding for every sentence.
D. Combine all manuals into one embedding.
Answer: B
Explanation: Preserving document structure helps maintain semantic meaning and improves retrieval quality because headings provide valuable contextual information.
Question 6
What is a disadvantage of creating extremely small chunks?
A. Higher SQL licensing costs
B. More accurate semantic understanding
C. Loss of surrounding context during retrieval
D. Lower storage requirements
Answer: C
Explanation: Very small chunks may not contain enough surrounding information for an LLM to interpret their meaning correctly, resulting in lower-quality retrieval.
Question 7
When implementing chunking for a RAG application, which metadata should typically be stored alongside each embedding?
A. SQL login credentials
B. Original document identifier and chunk position
C. Azure subscription ID
D. CPU utilization statistics
Answer: B
Explanation: Metadata allows applications to identify the source document, reconstruct surrounding context, provide citations, and retrieve neighboring chunks when generating responses.
Question 8
A legal department requires every retrieved answer to reference complete contractual clauses.
Which chunking strategy is most appropriate?
A. Randomly divide text every 500 characters.
B. Generate one embedding for the entire contract.
C. Chunk by individual words.
D. Chunk according to clause boundaries with slight overlap.
Answer: D
Explanation: Legal documents rely on clearly defined clauses. Chunking by clause preserves legal meaning while overlap prevents loss of context between adjacent sections.
Question 9
Which statement best describes the relationship between chunk size and retrieval performance?
A. Larger chunks always produce better search results.
B. Smaller chunks always eliminate hallucinations.
C. There is an optimal balance between context size and retrieval precision.
D. Chunk size has no impact on vector search.
Answer: C
Explanation: Chunks that are too large reduce retrieval precision, while chunks that are too small lose context. Effective systems balance these competing factors.
Question 10
During testing, users report that retrieved passages frequently stop in the middle of explanations.
Which modification would most likely improve retrieval quality?
A. Increase overlap between adjacent chunks.
B. Reduce the embedding vector dimensions.
C. Remove document metadata.
D. Convert embeddings to integers.
Answer: A
Explanation: Increasing overlap preserves continuity across chunk boundaries, allowing retrieval systems to return more complete passages and improving the quality of generated responses.
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Implement AI capabilities in database solutions (25–30%) --> Design and implement models and embeddings --> Generate embeddings
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
Embeddings are one of the foundational technologies behind modern AI-powered applications such as semantic search, Retrieval-Augmented Generation (RAG), intelligent chatbots, recommendation systems, and knowledge assistants. Rather than treating text as simple strings of characters, embeddings transform text into high-dimensional numerical vectors that capture semantic meaning. This enables AI systems to compare concepts based on meaning rather than exact word matches.
For developers working with SQL databases, generating embeddings is often the first step in building AI-enabled database solutions. After embeddings are created, they can be stored in vector columns, indexed using vector indexes, and queried using vector similarity search. This makes it possible to retrieve relevant information efficiently and provide context to Large Language Models (LLMs).
For the DP-800: Developing AI-Enabled Database Solutions exam, candidates should understand when embeddings should be generated, how they are produced, where they are stored, how they are maintained, and how they integrate into SQL-based AI architectures.
What Are Embeddings?
An embedding is a numerical representation of data that captures its semantic meaning. Instead of representing text as characters or words, an embedding model converts the text into an array of floating-point numbers called a vector.
For example:
Text:
"Reset your account password."
Embedding (simplified):
[0.231, -0.118, 0.654, 0.092, ...]
Real embedding vectors typically contain hundreds or thousands of dimensions, depending on the model.
Although humans cannot interpret these numbers directly, embedding models position semantically similar text close together within vector space.
For example:
Text
Relationship
Reset password
Very similar
Change password
Very similar
Forgot my password
Similar
Employee vacation policy
Not similar
Although none of these sentences are identical, the first three express nearly the same concept and therefore produce vectors that are close together.
Why Generate Embeddings?
Embeddings enable SQL databases and AI applications to perform semantic retrieval instead of relying solely on exact keyword matching.
Benefits include:
Semantic search
Retrieval-Augmented Generation (RAG)
Similarity search
Intelligent recommendations
Duplicate detection
Document classification
Clustering similar content
Knowledge discovery
AI-powered assistants
Natural language querying
Without embeddings, searching generally depends on literal text matching.
Example:
Traditional search:
Password reset
Finds:
Password reset
May not find:
Forgot my login
Change my credentials
Reset account access
Semantic search using embeddings retrieves all of these because they express similar meanings.
Embedding Generation Workflow
Generating embeddings typically follows this workflow:
Source Data
│
▼
Prepare Text
│
▼
Chunk Documents
│
▼
Select Embedding Model
│
▼
Generate Embedding Vector
│
▼
Store Vector
│
▼
Vector Index
│
▼
Similarity Search
Each stage contributes to the overall effectiveness of AI retrieval.
Preparing Data Before Generating Embeddings
High-quality embeddings begin with well-prepared data.
Typical preparation steps include:
Removing duplicate documents
Cleaning formatting artifacts
Normalizing whitespace
Removing unnecessary HTML
Converting PDFs into text
Correcting OCR errors
Standardizing encoding
Removing irrelevant content
Identifying document boundaries
Poor-quality input results in poor-quality embeddings.
Choosing Which Data to Embed
Not every database column should be embedded.
Good candidates include:
Product descriptions
Knowledge articles
Documentation
Policies
Customer support content
Email templates
FAQs
User manuals
Research papers
Technical documentation
Less suitable candidates include:
Identity columns
Numeric identifiers
Dates
Foreign keys
Boolean flags
Audit columns
Calculated values
Embedding descriptive, natural-language content provides the greatest value.
Chunking Before Generating Embeddings
Embedding an entire document often produces a vector that represents multiple unrelated topics.
Instead, documents should usually be divided into meaningful chunks.
Example:
Original document:
Employee Handbook
After chunking:
Vacation Policy
Medical Leave
Expense Reimbursement
Remote Work
Each chunk receives its own embedding.
Benefits include:
Improved retrieval precision
Better semantic representation
More accurate RAG responses
Lower processing costs
Easier maintenance
Selecting an Embedding Model
An embedding model converts text into vectors.
Common considerations include:
Vector dimensions
Supported languages
Domain specialization
Cost
Accuracy
Maximum token length
Latency
Azure integration
Microsoft AI-enabled SQL solutions commonly use embedding models hosted through Azure AI Foundry, Azure OpenAI, or compatible external providers.
The embedding model used for indexing should also be used for query embeddings to ensure compatibility.
Embedding Dimensions
Each embedding consists of a fixed number of dimensions.
Examples:
384 dimensions
768 dimensions
1024 dimensions
1536 dimensions
3072 dimensions
Higher dimensions generally capture richer semantic relationships but require:
More storage
Larger vector indexes
Increased memory
More processing during similarity search
Choosing the appropriate dimension is a balance between accuracy and cost.
Batch Generation of Embeddings
Generating embeddings individually is inefficient for large datasets.
Instead, organizations commonly process documents in batches.
Advantages include:
Better throughput
Lower API overhead
Reduced operational costs
Easier scheduling
Improved monitoring
Batch processing is commonly used when:
Loading historical documents
Building initial vector indexes
Reindexing knowledge bases
Incremental Embedding Generation
Production systems rarely regenerate every embedding.
Instead, they generate embeddings only for new or modified content.
Common mechanisms include:
SQL table triggers
Change Tracking
Change Data Capture (CDC)
Change Event Streaming (CES)
Azure Functions with SQL Trigger Binding
Azure Logic Apps
Microsoft Foundry pipelines
Incremental updates reduce cost while keeping vector indexes synchronized with source data.
Storing Embeddings
After generation, embeddings are typically stored alongside their source data or in a dedicated vector table.
Example:
Document ID
Chunk
Embedding
101
Vacation Policy
Vector
102
Medical Leave
Vector
103
Benefits
Vector
In SQL Server 2025 and Azure SQL Database, embeddings can be stored in vector-compatible columns, enabling efficient similarity search.
Metadata Associated with Embeddings
Each embedding should include metadata that supports retrieval and maintenance.
Typical metadata includes:
Document ID
Chunk ID
Source filename
Page number
Section heading
Creation date
Last modified date
Embedding model used
Embedding version
Language
Security classification
Metadata enables filtering, traceability, citation generation, and re-embedding when models are updated.
Keeping Embeddings Current
Embeddings represent the content at the time they were generated. When source data changes, the corresponding embeddings become outdated.
Common maintenance workflow:
Row Updated
│
▼
Detect Change
│
▼
Regenerate Embedding
│
▼
Replace Old Vector
│
▼
Update Vector Index
Automating this process ensures that AI applications always retrieve current information.
Common Challenges When Generating Embeddings
Developers should be aware of several common issues:
Poor Chunking
Large or poorly defined chunks reduce retrieval accuracy.
Incorrect Model Selection
Using different embedding models for indexing and querying can produce incompatible vectors.
Stale Embeddings
Failing to regenerate embeddings after data changes leads to outdated search results.
Excessive Costs
Embedding every column or regenerating vectors unnecessarily increases API usage and storage costs.
Inadequate Metadata
Without metadata, it is difficult to identify sources, filter results, or reconstruct document context.
Best Practices
Microsoft recommends several best practices for generating embeddings in SQL-based AI solutions:
Generate embeddings only for meaningful textual content.
Chunk documents into semantically coherent sections before embedding.
Use the same embedding model for both indexing and query generation.
Store metadata with every embedding.
Automate embedding generation for new and modified content.
Use incremental updates instead of regenerating all embeddings.
Monitor embedding generation jobs for failures and latency.
Evaluate retrieval quality regularly using representative user queries.
Choose embedding dimensions that balance accuracy, storage, and performance.
Version embedding models so vectors can be regenerated consistently when models change.
Real-World Example
A company maintains a knowledge base of 75,000 technical support articles.
Instead of embedding each entire article, they:
Clean and normalize article text.
Divide each article into logical sections.
Generate an embedding for each section using an Azure-hosted embedding model.
Store vectors and metadata in Azure SQL Database.
Create a vector index.
Use vector similarity search to retrieve the most relevant sections.
Supply retrieved sections as context to a Large Language Model for answering user questions.
Automatically regenerate embeddings whenever articles are updated using Change Tracking and Azure Functions.
This architecture provides fast, accurate semantic retrieval while minimizing operational costs.
DP-800 Exam Tips
For the DP-800 exam, understand that generating embeddings is far more than simply calling an AI model. Microsoft expects candidates to understand the complete embedding lifecycle, including data preparation, chunking, model selection, embedding generation, storage, metadata management, incremental updates, and integration with vector search and RAG solutions. Be prepared for scenario-based questions that require choosing appropriate embedding strategies, maintaining embedding freshness, optimizing costs, and designing scalable AI-enabled database solutions that integrate Azure SQL with Azure AI services.
Practice Exam Questions
Question 1
A company is building a Retrieval-Augmented Generation (RAG) solution using Azure SQL Database. Why should documents generally be divided into chunks before generating embeddings?
A. To reduce the number of database tables required
B. To improve semantic retrieval by creating embeddings for focused pieces of content
C. To eliminate the need for vector indexes
D. To ensure embeddings contain fewer than 100 dimensions
Answer:B
Explanation: Chunking documents into semantically meaningful sections improves retrieval accuracy because each embedding represents a single concept or closely related ideas. Embedding an entire document often results in vectors that represent multiple topics, reducing search precision.
Question 2
Which type of database column is generally the best candidate for generating embeddings?
A. Product description
B. Order ID
C. Invoice number
D. Creation timestamp
Answer:A
Explanation: Embeddings are designed to represent semantic meaning. Descriptive text such as product descriptions, documentation, FAQs, and support articles provides meaningful information that can be searched semantically. Numeric identifiers and timestamps contain little semantic value.
Question 3
What is the primary purpose of an embedding model?
A. Compress relational tables
B. Encrypt database records
C. Convert text into numerical vectors representing semantic meaning
D. Generate SQL indexes automatically
Answer:C
Explanation: Embedding models transform text into high-dimensional vectors that preserve semantic relationships. These vectors enable similarity searches, semantic search, clustering, and Retrieval-Augmented Generation (RAG).
Question 4
Why should the same embedding model generally be used for both document indexing and query generation?
A. Different models produce incompatible vector spaces.
B. SQL Server only supports one model.
C. Using multiple models improves similarity scores.
D. Azure SQL automatically converts vectors between models.
Answer:A
Explanation: Embedding vectors generated by different models often exist in different vector spaces and cannot be compared accurately. Using the same model ensures similarity calculations remain meaningful.
Question 5
A development team updates product documentation daily.
Which approach minimizes costs while keeping embeddings current?
A. Regenerate every embedding every hour.
B. Regenerate embeddings only when the corresponding documents change.
C. Never regenerate embeddings.
D. Create duplicate embeddings for every document revision.
Answer:B
Explanation: Incremental embedding generation updates only modified content, reducing API usage, storage requirements, and processing time while maintaining accurate search results.
Question 6
What is a major benefit of storing metadata alongside embeddings?
A. It reduces vector dimensions.
B. It eliminates the need for chunking.
C. It enables filtering, traceability, and source attribution during retrieval.
D. It compresses embeddings automatically.
Answer:C
Explanation: Metadata such as document ID, page number, section heading, language, and security classification allows applications to identify the source of retrieved content, reconstruct document context, and apply filters during searches.
Question 7
Which technology can detect modified SQL data so only affected embeddings are regenerated?
A. SQL Server Agent alerts only
B. Change Tracking or Change Data Capture (CDC)
C. Database snapshots
D. Transaction log backups
Answer:B
Explanation: Both Change Tracking and Change Data Capture (CDC) identify inserted, updated, or deleted rows, making them well suited for triggering incremental embedding regeneration workflows.
Question 8
A company chooses an embedding model with significantly more vector dimensions than its previous model.
What is the most likely tradeoff?
A. Lower storage requirements
B. Reduced semantic accuracy
C. Increased storage and processing requirements
D. Elimination of vector indexes
Answer:C
Explanation: Higher-dimensional vectors typically capture more semantic detail but require additional storage, memory, and computational resources during indexing and similarity searches.
Question 9
Which workflow correctly represents the embedding generation process?
A. Generate vectors → Clean data → Chunk documents → Search
B. Chunk documents → Generate embeddings → Store vectors → Perform similarity search
C. Store vectors → Generate embeddings → Build documents
D. Query database → Generate vectors → Create documents
Answer:B
Explanation: The standard workflow is to prepare and chunk documents, generate embeddings, store them, create vector indexes if appropriate, and then use similarity search to retrieve relevant content.
Question 10
An organization regenerates embeddings every night even though very little data changes. Users report no improvement, but Azure AI costs continue to increase.
What is the best recommendation?
A. Increase the embedding dimensions.
B. Generate duplicate embeddings for verification.
C. Replace semantic search with keyword search.
D. Implement incremental embedding generation triggered by data changes.
Answer:D
Explanation: Incremental embedding generation regenerates vectors only when data changes, reducing unnecessary API calls, lowering costs, and maintaining up-to-date embeddings without repeatedly processing unchanged content.
End of Topic Summary
For the DP-800 exam, understand that generating embeddings is a foundational step in building AI-enabled SQL database solutions. Success depends on more than simply invoking an embedding model—you must also prepare and chunk data appropriately, select a suitable embedding model, generate compatible vectors, store them with useful metadata, and keep them synchronized with changing source data through incremental update mechanisms such as Change Tracking, CDC, Azure Functions, or Logic Apps. Microsoft expects candidates to understand the complete embedding lifecycle and how it supports semantic search, vector indexing, and Retrieval-Augmented Generation (RAG) solutions.
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Design and develop database solutions (35–40%) --> Design and implement SQL solutions by using AI-assisted tools --> Create and configure GitHub Copilot instruction files
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
GitHub Copilot is an AI-powered coding assistant that generates code, explains existing code, creates documentation, and assists with debugging. While Copilot is powerful out of the box, organizations often need the AI to follow company-specific standards instead of producing generic code.
GitHub Copilot instruction files provide persistent guidance to Copilot. Rather than repeatedly telling Copilot the same preferences during every chat session, developers can store instructions in version-controlled files inside the repository. They help ensure that AI-generated code follows an organization’s coding standards, security requirements, architectural patterns, naming conventions, and SQL development best practices. Candidates should understand not only how to create these files, but also how they influence Copilot’s responses.
Instruction files improve:
Consistency
Security
Coding standards
SQL development practices
Documentation quality
Team collaboration
AI response quality
For the DP-800 exam, understand:
What instruction files are
Where they are stored
What types of instructions they contain
How they affect Copilot responses
Best practices for SQL development
Why Use Instruction Files?
Without instruction files:
Developer:
Create a stored procedure.
Copilot:
Creates one using SELECT * and no error handling.
Next time:
Developer:
Remember to avoid SELECT *
Use TRY...CATCH
Use PascalCase
Include comments
Use parameters
The developer must continually repeat instructions.
With instruction files:
Repository contains instructions.
Copilot automatically follows them.
Every developer receives consistent AI assistance.
What Are GitHub Copilot Instruction Files?
Instruction files are Markdown files that contain natural-language guidance for Copilot.
They describe:
Coding style
Naming conventions
Architecture
Security practices
SQL standards
Documentation requirements
Testing expectations
Instead of writing prompts repeatedly, the repository permanently stores the instructions.
Benefits
Instruction files provide:
Consistency
Every developer receives similar AI suggestions.
Faster Development
Less prompt engineering.
Developers spend less time explaining requirements.
Higher Code Quality
Instructions encourage:
Proper formatting
Secure coding
Error handling
Documentation
Better Security
Organizations can require Copilot to:
Parameterize SQL
Avoid dynamic SQL
Validate input
Follow least privilege
Team Standards
New developers immediately receive guidance that matches experienced developers.
Repository-Level Instructions
Instruction files are stored with the project.
Example:
Repository
├── .github
│ copilot-instructions.md
│
├── Database
├── Procedures
├── Functions
└── Tables
The instructions become part of source control.
Everyone cloning the repository receives them.
What Can Instruction Files Contain?
Common guidance includes:
Coding conventions
Example
Use PascalCase for object names.
Avoid abbreviations.
Use descriptive variable names.
SQL Standards
Example
Never use SELECT *
Always qualify object names.
Always use schema prefixes.
Prefer explicit column lists.
Error Handling
Example
Always wrap stored procedures inside TRY...CATCH.
Log errors before rethrowing.
Documentation
Example
Document all procedures.
Include parameter descriptions.
Explain business rules.
Performance
Example
Avoid cursors.
Prefer set-based operations.
Use appropriate indexing.
Avoid unnecessary temp tables.
Security
Example
Always use parameterized queries.
Never concatenate SQL strings.
Validate inputs.
Follow least privilege.
SQL Example
Instruction:
Use schema dbo.
Always include SET NOCOUNT ON.
Use TRY...CATCH.
Document parameters.
Never use SELECT *.
Prompt:
Create a procedure to retrieve customers.
Generated procedure might include:
CREATE PROCEDURE dbo.GetCustomers
(
@Country NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT
CustomerID,
CustomerName,
Country
FROM dbo.Customers
WHERE Country=@Country;
END TRY
BEGIN CATCH
THROW;
END CATCH
END;
The instructions influence the generated output.
SQL Development Standards Commonly Included
Organizations commonly include instructions such as:
Include coding, security, testing, and documentation expectations.
Review instruction files during pull requests.
Avoid contradictory instructions.
Combine repository instructions with task-specific prompts when necessary.
Regularly validate that generated code still meets organizational standards.
Common Mistakes
Avoid:
❌ Extremely long instruction files
❌ Conflicting rules
❌ Outdated architecture guidance
❌ Security rules that contradict current policy
❌ Generic instructions that provide little value
❌ Forgetting to update instructions after framework changes
❌ Assuming Copilot always follows instructions perfectly without human review
DP-800 Exam Tips
Candidates should know:
Instruction files provide persistent repository guidance.
They improve consistency across AI-generated code.
They are stored with the project and version controlled.
They can define coding standards, SQL conventions, security requirements, testing expectations, and documentation guidelines.
They reduce repetitive prompting.
They complement, rather than replace, user prompts.
Developers remain responsible for validating all AI-generated code.
Well-written instruction files improve code quality and team productivity.
Summary
GitHub Copilot instruction files are an important mechanism for guiding AI-generated code within a project. By defining repository-specific coding standards, security practices, documentation requirements, and SQL development conventions, organizations can improve consistency, reduce repetitive prompting, and ensure AI-generated code better aligns with business requirements. However, instruction files do not eliminate the need for developer review. AI-generated code should always be validated for correctness, performance, maintainability, and security before deployment.
Practice Exam Questions
Question 1
A development team wants GitHub Copilot to always generate SQL stored procedures that include SET NOCOUNT ON, TRY...CATCH blocks, and schema-qualified object names. What is the best way to accomplish this?
A. Add these requirements to a GitHub Copilot instruction file stored in the repository.
B. Modify SQL Server configuration settings.
C. Configure database compatibility level.
D. Enable Query Store.
Answer: A
Explanation: Repository instruction files provide persistent guidance that GitHub Copilot automatically considers when generating code.
Question 2
What is the primary purpose of a GitHub Copilot instruction file?
A. Improve SQL Server query performance.
B. Define repository-specific guidance that influences AI-generated code.
C. Store database credentials.
D. Configure Azure SQL firewall rules.
Answer: B
Explanation: Instruction files define coding conventions, security requirements, architectural guidance, and other project-specific expectations for Copilot.
Question 3
Which instruction would most directly reduce the likelihood of SQL injection vulnerabilities in AI-generated code?
A. Use uppercase SQL keywords.
B. Always include comments.
C. Always use parameterized queries and avoid dynamic SQL string concatenation.
D. Use table aliases.
Answer: C
Explanation: Parameterized queries are a primary defense against SQL injection attacks.
Question 4
A team updates its SQL naming conventions. What is the best way to ensure GitHub Copilot follows the new standards for all developers?
A. Send an email describing the new conventions.
B. Create a shared prompt document.
C. Ask every developer to memorize the standards.
D. Update the repository’s Copilot instruction file and commit the changes.
Answer: D
Explanation: Version-controlled instruction files distribute updated guidance to everyone working with the repository.
Question 5
Which guidance is most appropriate for inclusion in a GitHub Copilot instruction file?
A. Temporary debugging notes for one developer.
B. Personal keyboard shortcuts.
C. Repository-wide SQL coding standards and documentation requirements.
D. SQL Server service account passwords.
Answer: C
Explanation: Instruction files should contain reusable project guidance, never personal settings or sensitive information.
Question 6
Why are GitHub Copilot instruction files commonly stored in source control?
A. To improve SQL Server indexing.
B. To enable versioning, collaboration, and consistent AI guidance.
C. To reduce database storage.
D. To encrypt SQL scripts.
Answer: B
Explanation: Source control ensures instruction changes are tracked, reviewed, and shared across the team.
Question 7
Which statement about GitHub Copilot instruction files is correct?
A. They eliminate the need to review AI-generated code.
B. They guarantee every generated query is optimized.
C. They replace database security policies.
D. They supplement prompts by providing persistent project-specific guidance.
Answer: D
Explanation: Instruction files enhance Copilot responses but do not replace human review or additional task-specific prompting.
Question 8
A database team wants Copilot to avoid generating SELECT * statements. Where should this requirement be documented?
A. SQL Server Agent.
B. Azure Key Vault.
C. GitHub Copilot instruction file.
D. SQL Profiler.
Answer: C
Explanation: Coding conventions such as avoiding SELECT * are ideal candidates for repository instruction files.
Question 9
Which practice improves the long-term usefulness of GitHub Copilot instruction files?
A. Adding every possible coding preference.
B. Keeping instructions concise, current, and focused on project standards.
C. Storing passwords for easier AI access.
D. Avoiding updates after the initial creation.
Answer: B
Explanation: Effective instruction files are clear, maintainable, and updated as project standards evolve.
Question 10
A developer receives SQL code from GitHub Copilot that follows all repository instruction files. What should the developer do before committing the code?
A. Commit it immediately because instruction files guarantee correctness.
B. Only verify formatting.
C. Disable Copilot.
D. Review the code for correctness, performance, security, and compliance with business requirements.
Answer: D
Explanation: AI-generated code should always undergo human review, testing, and validation, even when instruction files are used.
Part 3 – End-to-End Development Scenarios and Practice Exam Questions
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Design and develop database solutions (35–40%) --> Design and implement SQL solutions by using AI-assisted tools --> Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
Candidates should understand how AI models and MCP-enabled tools work together throughout the SQL development lifecycle—from planning and coding to testing, deployment, and optimization.
End-to-End SQL Development Workflow
The following illustrates a typical workflow for AI-assisted SQL development.
Requirements
│
▼
Developer Prompt
│
▼
GitHub Copilot /
Copilot in Fabric
│
▼
Selected AI Model
│
▼
(Optional)
Invoke MCP Tools
│
▼
Retrieve Context
• Database schema
• Existing procedures
• Documentation
• APIs
• GitHub repository
│
▼
Generate SQL
│
▼
Developer Review
│
▼
Testing
│
▼
Deployment
The AI assists throughout the workflow, but the developer remains responsible for reviewing, validating, and approving the generated solution.
Scenario 1 – Designing a New Database Table
A developer receives the following requirement:
Create a Customer table with auditing columns, primary key, email uniqueness, and indexes.
Prompt
Design a Customer table for Azure SQL Database. Include an identity primary key, audit columns, email uniqueness, and indexes for common lookup operations.
AI Response
The AI generates:
CREATE TABLE statement
PRIMARY KEY constraint
UNIQUE constraint
DEFAULT values
indexes
documentation
The developer reviews:
naming conventions
data types
indexing strategy
normalization
storage requirements
Scenario 2 – Creating Stored Procedures
The database already contains 150 tables.
Rather than manually examining the schema, GitHub Copilot uses an approved MCP server.
Developer prompt:
Create a stored procedure that returns all active customers with orders placed within the last 90 days.
Possible MCP interactions:
Read Customers table
Read Orders table
Discover foreign keys
Retrieve indexes
The AI produces SQL using the actual schema instead of making assumptions.
Scenario 3 – Query Optimization
A report currently takes 22 seconds.
Developer prompt:
Optimize this query for Azure SQL Database.
The reasoning model determines additional information is needed.
Using MCP:
retrieves execution plan
retrieves index information
retrieves statistics
retrieves row counts
The response includes:
rewritten SQL
missing indexes
parameter sniffing observations
SARGability improvements
estimated performance gains
Scenario 4 – Fabric Warehouse Development
A Fabric Warehouse contains several sales tables.
Developer asks:
Explain the warehouse schema and suggest a star schema optimization.
Copilot may retrieve:
warehouse metadata
table relationships
documentation
semantic model information
The AI can recommend:
dimension tables
fact tables
surrogate keys
partitioning
indexing
warehouse best practices
Scenario 5 – Documentation Generation
Developer prompt:
Document this database.
The AI generates:
table descriptions
column summaries
relationship explanations
stored procedure documentation
index summaries
security notes
This significantly reduces documentation effort.
Scenario 6 – Legacy SQL Refactoring
A SQL Server database contains code written fifteen years ago.
Developer prompt:
Modernize this procedure using current T-SQL best practices.
The AI may recommend:
TRY…CATCH
THROW
CTEs
window functions
JSON functions
simplified joins
improved naming
reduced duplication
Scenario 7 – Code Review
Developer prompt:
Review this stored procedure.
The AI evaluates:
security
SQL injection risks
indexing
readability
performance
maintainability
Rather than replacing human review, AI serves as an intelligent reviewer.
Scenario 8 – Database Migration
An organization is migrating SQL Server databases to Azure SQL Database.
Developer prompt:
Identify compatibility issues.
The AI reviews:
deprecated features
unsupported syntax
compatibility level
indexing recommendations
Azure SQL best practices
Scenario 9 – Troubleshooting Errors
A deployment fails.
Developer prompt:
Explain this SQL error.
The AI:
interprets error messages
explains root causes
recommends fixes
suggests troubleshooting steps
Scenario 10 – Learning Existing Code
A new developer joins the team.
Developer prompt:
Explain this stored procedure.
The AI produces:
high-level summary
business logic
table relationships
parameter explanations
execution flow
This accelerates onboarding.
Choosing the Appropriate Model
Development Task
Preferred Model
Generate CRUD statements
Fast model
Explain SQL syntax
Balanced model
Create stored procedures
Balanced model
Optimize execution plans
Reasoning model
Review security
Reasoning model
Database architecture
Reasoning model
Documentation
Fast/Balanced model
Refactoring
Balanced model
Code review
Reasoning model
Troubleshooting
Reasoning model
Choosing MCP Tools
Not every prompt requires MCP.
Use MCP when the AI needs:
live database metadata
repository contents
API specifications
execution plans
documentation
schema information
Simple questions such as
What is a clustered index?
generally do not require MCP.
Questions like
Show indexes on my Sales table.
typically do.
Common Development Mistakes
Trusting AI Without Validation
Always review generated SQL.
Using Production Data
Avoid exposing confidential production data unnecessarily.
Ignoring Security
Never assume generated permissions are correct.
Using the Wrong Model
Simple code generation does not always require a reasoning model.
Excessive Permissions
Only enable MCP servers with appropriate permissions.
Skipping Testing
Every generated SQL statement should be:
reviewed
tested
validated
Best Practices
Write detailed prompts.
Specify Azure SQL, SQL Server, or Fabric Warehouse when applicable.
Include schema information.
Use reasoning models for optimization tasks.
Use MCP only when external context is beneficial.
Enable only trusted MCP servers.
Follow least privilege.
Review generated SQL before execution.
Validate performance with execution plans.
Keep human oversight throughout the development lifecycle.
DP-800 Exam Tips
Candidates should remember:
AI models generate responses.
MCP connects AI to external systems.
Tools perform actions.
Resources provide information.
Prompts standardize interactions.
Authentication determines identity.
Authorization determines permissions.
AI operates within the user’s security context.
Developers remain responsible for validating all AI-generated SQL.
Practice Exam Questions
Question 1
A developer wants GitHub Copilot to recommend missing indexes based on the actual structure of an Azure SQL Database instead of making assumptions.
What should the developer configure?
A. A larger context window only
B. An MCP server that can expose database metadata and indexing tools
C. A faster AI model
D. A local SQL script containing only CREATE TABLE statements
Answer:B
Explanation:
An MCP server enables GitHub Copilot to access live database metadata, including tables, indexes, and statistics. This allows recommendations based on the actual database rather than inferred information. Increasing the context window or switching to a faster model alone does not provide access to external database metadata.
Question 2
A developer needs AI assistance to analyze an execution plan for a query that runs for several minutes.
Which model type is generally the best choice?
A. Fast code-completion model
B. Lightweight autocomplete model
C. Reasoning-focused model
D. Documentation generation model
Answer:C
Explanation:
Execution plan analysis requires complex reasoning and performance optimization capabilities. Reasoning-focused models are designed to analyze execution strategies, identify bottlenecks, and recommend indexing or query improvements.
Question 3
Which MCP component performs operations such as retrieving index information or executing an approved query?
A. Resource
B. Prompt
C. Client
D. Tool
Answer:D
Explanation:
Tools perform actions. Resources provide information, prompts are reusable instructions, and clients host the AI conversation. Retrieving index information or executing approved operations is performed through tools.
Question 4
A developer asks Copilot:
Explain what this stored procedure does.
No external information is required.
What is the most likely outcome?
A. Copilot automatically invokes every available MCP server.
B. Copilot requires administrator approval.
C. Copilot cannot answer without MCP.
D. Copilot answers using the supplied SQL and its language model.
Answer:D
Explanation:
If the prompt includes all necessary information, the AI can respond using its language model without accessing external tools. MCP is used only when additional external context is needed.
Question 5
Why should organizations implement the principle of least privilege for MCP servers?
A. To increase response speed
B. To reduce the number of AI prompts
C. To limit access to only the resources required
D. To improve SQL syntax generation
Answer:C
Explanation:
Least privilege reduces security risks by ensuring that AI assistants and users have access only to the resources necessary to perform their tasks.
Question 6
Which statement best describes the relationship between an AI model and MCP?
A. MCP replaces the language model.
B. MCP generates SQL while the model manages security.
C. The language model generates responses, while MCP enables access to external tools and resources.
D. MCP is another name for GitHub Copilot Chat.
Answer:C
Explanation:
The language model performs reasoning and response generation. MCP provides standardized access to external systems, tools, and resources that supply additional context.
Question 7
A developer wants Copilot to use repository documentation, API specifications, and database schemas when generating SQL.
What feature provides this capability?
A. Larger prompt length
B. Database compatibility level
C. MCP-enabled resources
D. SQL IntelliSense
Answer:C
Explanation:
MCP resources allow AI assistants to access external information such as documentation, schemas, and specifications, improving the relevance and accuracy of generated responses.
Question 8
After AI generates a stored procedure, what should happen next?
A. Deploy directly to production.
B. Trust the AI because it selected a reasoning model.
C. Execute immediately without testing.
D. Review, validate, test, and approve the code before deployment.
Answer:D
Explanation:
AI-generated code should always undergo code review, testing, validation, and approval before being deployed to production.
Question 9
Which scenario is most likely to benefit from an MCP server?
A. Explaining the syntax of a SELECT statement
B. Defining a PRIMARY KEY
C. Retrieving the latest schema and execution statistics from a production database
D. Explaining SQL keywords
Answer:C
Explanation:
Accessing current schemas and execution statistics requires live information from an external system, making MCP the appropriate solution.
Question 10
Why might a developer choose a balanced AI model instead of a fast model?
A. Balanced models are designed to provide stronger reasoning while maintaining good response speed.
B. Balanced models eliminate the need for testing.
C. Balanced models automatically execute SQL.
D. Balanced models replace MCP servers.
Answer:A
Explanation:
Balanced models provide a compromise between speed and reasoning quality, making them well suited for tasks such as stored procedure development, code explanation, and general SQL assistance. They do not replace testing, execute SQL automatically, or substitute for MCP functionality.
Final DP-800 Summary
For this objective, remember these core concepts:
AI models determine how responses are generated (speed, reasoning, and coding quality).
MCP determines what additional information or actions the AI can access by connecting to external tools and resources.
Tools execute approved operations, while resources provide contextual information.
Authentication identifies the user, and authorization limits what the AI can access on that user’s behalf.
Developers remain responsible for validating, testing, securing, and approving all AI-generated SQL before deployment.
These concepts are foundational to the DP-800 exam and reflect Microsoft’s direction toward secure, AI-assisted database development.
Part 2 – Configuring Model Context Protocol (MCP) Tool Options
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Design and develop database solutions (35–40%) --> Design and implement SQL solutions by using AI-assisted tools --> Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
The DP-800 exam expects candidates to understand how modern AI assistants can securely interact with external tools and enterprise systems through the Model Context Protocol (MCP). Rather than being limited to answering questions from their built-in knowledge, AI assistants can use MCP to retrieve live information, interact with databases, execute approved operations, and integrate with enterprise development workflows.
Understanding MCP is becoming increasingly important because Microsoft is integrating MCP support across GitHub Copilot, Azure services, Microsoft Fabric, and other AI-powered development experiences.
Learning Objectives
After studying this article, you should be able to:
Explain the purpose of Model Context Protocol (MCP)
Understand the components of an MCP architecture
Differentiate between models and tools
Explain MCP servers, tools, resources, and prompts
Configure MCP tool usage within GitHub Copilot
Understand how Copilot in Fabric uses MCP-enabled tools
Recognize security implications of MCP
Apply governance best practices
Identify common DP-800 exam scenarios involving MCP
What Is Model Context Protocol (MCP)?
Model Context Protocol (MCP) is an open protocol that enables AI assistants to securely connect to external tools, applications, services, databases, and other data sources using a standardized interface.
Before MCP, AI assistants were generally limited to:
their training data
information provided in prompts
uploaded files
conversation history
With MCP, an AI assistant can also interact with external systems in real time.
For example, instead of merely explaining how to query a SQL database, an MCP-enabled assistant can:
inspect a database schema
retrieve table metadata
read documentation
query approved data sources
access REST APIs
invoke external business services
This allows AI to generate responses based on current information rather than relying solely on previously learned knowledge.
Why MCP Exists
Organizations typically use dozens or hundreds of systems, such as:
Azure SQL Database
SQL Server
Microsoft Fabric
Azure Storage
Azure AI Search
GitHub repositories
SharePoint
Microsoft Learn documentation
Internal APIs
CRM systems
ERP systems
Ticketing systems
Without MCP, each AI assistant would require custom integrations for every external system.
MCP standardizes these integrations so that AI clients can communicate with many different services using a common protocol.
High-Level MCP Architecture
A simplified architecture looks like this:
Developer
│
▼
GitHub Copilot Chat
or
Copilot in Fabric
│
▼
Large Language Model
│
▼
Model Context Protocol
│
▼
MCP Server
│
▼
External Resources
• SQL Database
• Azure SQL
• REST APIs
• GitHub
• Fabric
• Documentation
• Azure AI Search
The AI model determines what information it needs, while MCP provides the standardized mechanism for retrieving that information or invoking approved tools.
Core MCP Components
Model Context Protocol consists of several key building blocks.
These include:
Clients
Servers
Tools
Resources
Prompts
Each plays a specific role in the overall architecture.
MCP Client
The client is the application through which the user interacts with AI.
Examples include:
GitHub Copilot Chat
Copilot in Microsoft Fabric
Visual Studio Code
Visual Studio
Other MCP-compatible AI clients
The client sends prompts to the language model and coordinates interactions with MCP servers when external information is required.
MCP Server
The MCP server exposes capabilities that AI assistants can use.
Rather than connecting directly to every application, the AI communicates with an MCP server that provides standardized access to approved resources and operations.
Examples include servers that expose:
SQL databases
Azure SQL Database
GitHub repositories
Documentation
File systems
REST APIs
Internal enterprise applications
The MCP server determines which capabilities are available and enforces any configured permissions or policies.
MCP Tools
A tool represents an action that the AI can request.
Unlike resources, which provide information, tools perform operations.
Examples include:
Execute SQL
Search a database schema
Create a pull request
Retrieve execution plans
Query Azure AI Search
Generate documentation
Run a deployment pipeline
Validate a SQL script
Tools typically accept parameters, perform an action, and return structured results to the AI model.
Example
Suppose a developer asks:
Show me the indexes on the Sales.Orders table.
Rather than guessing, the AI could invoke an MCP tool that queries the database metadata and returns the actual index definitions.
MCP Resources
Resources represent information that the AI can read.
Examples include:
SQL schemas
Database documentation
Markdown files
JSON configuration files
API specifications
Technical documentation
Data dictionaries
Knowledge bases
Resources provide context that helps the model generate more accurate responses.
Unlike tools, resources generally do not modify data.
MCP Prompts
Prompts are reusable templates or predefined instructions that help standardize interactions with AI.
An organization might define prompts such as:
Generate a secure stored procedure.
Review SQL for performance issues.
Explain an execution plan.
Generate Azure SQL documentation.
Review database security.
These prompts promote consistency and help developers follow organizational standards.
How MCP Works
Consider this prompt:
Optimize my stored procedure and recommend missing indexes.
Without MCP:
The AI only analyzes the SQL text supplied by the developer.
With MCP:
The AI can:
Inspect the actual schema.
Read index metadata.
Review execution statistics.
Analyze execution plans.
Recommend optimizations based on the current database.
The response becomes significantly more accurate because it is grounded in live data rather than assumptions.
Example Workflow
Developer
│
▼
"Optimize this procedure"
│
▼
LLM decides additional information is needed
│
▼
Invoke MCP Tool
│
▼
Retrieve indexes
Retrieve statistics
Retrieve execution plan
Retrieve schema
│
▼
Return results to LLM
│
▼
Generate optimized SQL
MCP in GitHub Copilot
GitHub Copilot increasingly supports MCP-compatible servers that allow Copilot Chat to interact with external development resources.
Depending on the environment and organizational configuration, developers can enable approved MCP servers to provide additional context during coding sessions.
Common scenarios include:
accessing repository metadata
reading project documentation
querying SQL schema information
retrieving API specifications
integrating with issue tracking systems
interacting with approved development tools
When multiple MCP servers are available, Copilot can select the appropriate server based on the user’s request and the permissions granted.
MCP in Microsoft Copilot in Fabric
Copilot in Fabric benefits from MCP by enabling AI to access enterprise data and services while respecting organizational governance.
Examples include:
examining Fabric Warehouse metadata
understanding Lakehouse schemas
retrieving semantic model information
exploring SQL endpoints
reading documentation
accessing Azure AI Search indexes
connecting to approved enterprise resources
This allows Copilot to produce responses that are informed by the organization’s current data landscape rather than relying solely on general knowledge.
Tool Selection
One MCP server may expose many tools.
For example:
Azure SQL MCP Server
│
├── List Tables
├── Execute Query
├── Show Indexes
├── Retrieve Statistics
├── Analyze Execution Plan
├── List Stored Procedures
└── Search Metadata
The AI chooses the appropriate tool based on the user’s request.
Security Model
One of MCP’s primary goals is secure interaction with enterprise systems.
Security principles include:
authenticated access
authorized operations
least privilege
explicit user consent where appropriate
encrypted communication
auditability
The AI never bypasses organizational security policies.
Instead, it operates within the permissions granted to the authenticated user and the configured MCP server.
Authentication
MCP servers generally rely on existing enterprise authentication mechanisms.
Examples include:
Microsoft Entra ID
OAuth
Personal Access Tokens (where appropriate)
Managed identities
Service principals
Developers should avoid embedding credentials directly in prompts or code.
Authorization
Authentication answers:
Who is the user?
Authorization answers:
What is the user allowed to do?
Even if an MCP server exposes a database, the AI can only perform operations that the authenticated user is permitted to execute.
For example:
Developer A
Read schema ✔
Read tables ✔
Execute SELECT ✔
Drop tables ✖
The AI inherits these permissions rather than receiving elevated privileges.
Least Privilege
Microsoft recommends following the principle of least privilege.
Organizations should establish governance policies for AI-assisted development.
Recommendations include:
approve trusted MCP servers
monitor AI interactions
audit tool usage
classify sensitive resources
restrict production access
review generated SQL
require human approval for deployments
Strong governance reduces the risk of accidental exposure of sensitive information or unintended database changes.
Common Security Risks
Potential risks include:
Excessive Permissions
The AI can only be as secure as the permissions granted to it. Overly broad access increases risk.
Sensitive Data Exposure
Developers should avoid exposing confidential production data unless organizational policies permit it.
Prompt Injection
Malicious or misleading instructions embedded in external content could attempt to manipulate AI behavior. Organizations should validate trusted sources and limit exposure to untrusted content.
Unverified SQL
AI-generated SQL should always be reviewed and tested before execution.
Best Practices for Configuring MCP
Enable only trusted MCP servers.
Grant the minimum required permissions.
Review available tools before enabling them.
Use enterprise authentication mechanisms.
Monitor audit logs where available.
Validate AI-generated recommendations.
Restrict production resources when appropriate.
Keep MCP server configurations up to date.
Follow organizational security and compliance policies.
DP-800 Exam Tips
Remember the following points for the exam:
MCP is a protocol, not an AI model.
MCP standardizes communication between AI assistants and external tools or resources.
Clients (such as GitHub Copilot Chat or Copilot in Fabric) use MCP to interact with servers.
Servers expose tools, resources, and prompts.
Tools perform actions, while resources provide information.
AI assistants operate within the authenticated user’s permissions and do not automatically receive elevated privileges.
Organizations should enable only trusted MCP servers and follow the principles of least privilege, authentication, authorization, and governance.
Understanding the distinction between AI reasoning and externally grounded information retrieved through MCP is an important concept for DP-800.
Part 1 – Configuring AI Models in GitHub Copilot and Microsoft Copilot in Fabric
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Design and develop database solutions (35–40%) --> Design and implement SQL solutions by using AI-assisted tools --> Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
Candidates should understand how to configure and use AI models within GitHub Copilot and Microsoft Copilot in Fabric, select the appropriate model for a task, understand the capabilities and limitations of different models, and use AI effectively when developing SQL solutions.
Unlike traditional SQL development, AI-assisted development requires understanding not only SQL syntax but also how the selected AI model influences the quality, speed, reasoning ability, and accuracy of generated code.
Learning Objectives
After studying this article, you should be able to:
Explain how GitHub Copilot and Copilot in Fabric use Large Language Models (LLMs)
Describe the role of AI models in SQL development
Understand model selection options
Compare reasoning-focused models with speed-focused models
Choose the appropriate model for database development tasks
Understand context windows and token limitations
Apply best practices when interacting with AI assistants
Recognize exam scenarios involving model configuration
AI-Assisted SQL Development
Modern SQL developers spend significant time performing repetitive tasks such as:
Writing CRUD statements
Creating stored procedures
Building database objects
Optimizing queries
Writing documentation
Generating test data
Troubleshooting syntax errors
Refactoring legacy SQL
AI assistants accelerate these activities by generating code from natural language.
Instead of writing:
CREATETABLE Customer
(
CustomerID INTPRIMARYKEY,
FirstName NVARCHAR(100),
LastName NVARCHAR(100),
Email NVARCHAR(200)
)
A developer can simply ask:
Create a customer table with an identity primary key, email validation, audit columns, and an index on Email.
The AI model generates the initial implementation, which the developer reviews and refines.
What Is an AI Model?
An AI model is the language model responsible for interpreting prompts and generating responses.
The model determines:
reasoning quality
SQL accuracy
explanation depth
response speed
context understanding
coding capabilities
Different models are optimized for different workloads.
Some prioritize:
speed
Others prioritize:
complex reasoning
Others balance both.
GitHub Copilot Architecture
A simplified architecture looks like this:
Developer
│
▼
GitHub Copilot Chat
│
▼
Selected AI Model
│
▼
Generated SQL
│
▼
Developer Review
│
▼
Database
The AI never executes SQL automatically.
The developer remains responsible for:
reviewing code
testing
validating security
validating performance
Microsoft Copilot in Fabric
Microsoft Copilot in Fabric provides AI assistance across Fabric workloads including:
SQL Database
Fabric Warehouse
Lakehouse
Data Engineering
Data Science
Power BI
Notebooks
Data Factory
Data Warehouse development
For SQL developers, Copilot can:
generate SQL
explain SQL
optimize SQL
summarize execution plans
generate documentation
create sample data
troubleshoot errors
Why Model Selection Matters
Different AI models excel at different activities.
For example:
A very fast model may generate:
SELECT*
FROM Orders
A reasoning model might instead suggest:
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount
FROM Sales.Orders
WHERE OrderDate >= DATEADD(month,-6,GETDATE());
along with an explanation of:
why SELECT * should be avoided
indexing recommendations
performance implications
The reasoning model produces higher-quality guidance.
Common AI Model Characteristics
Although Microsoft continuously updates available models, most fall into these categories.
Fast Models
Optimized for:
rapid responses
autocomplete
simple SQL
syntax correction
Best for:
INSERT statements
UPDATE statements
CREATE TABLE
formatting SQL
documentation
Advantages
very fast
low latency
excellent for routine work
Disadvantages
less detailed reasoning
weaker optimization suggestions
Balanced Models
Designed for:
coding
explanation
optimization
documentation
Best for:
stored procedures
views
CTEs
joins
JSON
window functions
Advantages
good reasoning
good speed
Disadvantages
may not perform as well as reasoning models on complex architecture questions
Reasoning Models
Reasoning models focus on:
architecture
optimization
debugging
security
query analysis
Ideal for:
execution plans
indexing strategy
normalization
concurrency
deadlocks
performance tuning
Advantages
excellent explanations
identifies tradeoffs
strong analytical reasoning
Disadvantages
slower responses
higher computational cost
Choosing the Appropriate Model
A SQL developer should match the model to the task.
Task
Recommended Model Type
Generate CREATE TABLE statements
Fast
Explain SQL syntax
Balanced
Write stored procedures
Balanced
Optimize slow queries
Reasoning
Analyze execution plans
Reasoning
Explain indexes
Reasoning
Generate documentation
Fast
Review security
Reasoning
Refactor code
Balanced
Produce examples
Balanced
Model Selection in GitHub Copilot
Depending on the supported environment and subscription, GitHub Copilot Chat allows users to select from available models.
The workflow generally involves:
Open GitHub Copilot Chat
Open the model selector
Review available models
Choose the appropriate model
Continue the conversation
Changing models changes how future prompts are processed.
Example
Suppose a developer asks:
Optimize this stored procedure.
A reasoning model may return:
missing indexes
SARGability improvements
parameter sniffing considerations
execution plan observations
rewritten SQL
A fast model may simply reformat the SQL.
Model Selection in Microsoft Copilot in Fabric
Copilot in Fabric similarly enables AI-assisted experiences throughout Microsoft Fabric. Depending on the workload and the capabilities available to your tenant, Copilot uses supported foundation models to generate responses for SQL development, analytics, and data engineering tasks.
When working in Fabric SQL experiences, Copilot can assist with:
generating SQL queries
explaining existing queries
creating tables and views
summarizing schemas
troubleshooting SQL errors
suggesting query improvements
documenting database objects
Administrators control whether Copilot features are enabled for a Fabric capacity. Users with access to Copilot interact through the integrated chat interface rather than manually invoking models.
Understanding Context Windows
Every AI model has a maximum amount of information it can process at one time.
This is called the context window.
The context includes:
prompts
previous conversation
SQL scripts
schemas
documentation
Example:
Prompt
+
Conversation
+
Database Schema
+
SQL Script
=
Context
Larger context windows allow:
larger stored procedures
multiple tables
lengthy conversations
larger execution plans
Token Limits
Large Language Models process text as tokens rather than words.
A very large SQL script consumes more tokens than a small query.
If the context exceeds the model’s limit:
earlier conversation may be truncated
important schema details may be omitted
responses may become less accurate
Best practice:
Break very large SQL tasks into smaller requests.
Effective Prompting
Model quality depends heavily on prompt quality.
Poor prompt:
Fix this.
Better prompt:
Optimize this stored procedure for Azure SQL Database. Reduce logical reads while maintaining identical results.
Even better:
Optimize this stored procedure for Azure SQL Database. The Orders table contains 40 million rows. Focus on indexing recommendations, parameter sniffing, and SARGable predicates while preserving the current output.
Specific prompts produce significantly better responses.
Providing Context
Useful context includes:
database platform
compatibility level
schema
expected row counts
performance goals
business rules
Example:
Platform:
Azure SQL Database
Table:
Sales.Orders
Rows:
150 million
Goal:
Reduce CPU utilization
Current execution time:
18 seconds
The more relevant information supplied, the more useful the AI-generated recommendation.
Responsible Use of AI Models
Although AI significantly improves developer productivity, it does not replace professional judgment.
Developers should always:
review generated SQL
validate security
test performance
verify business logic
confirm permissions
review indexes
test edge cases
Never assume generated SQL is production-ready without validation.
Common DP-800 Exam Scenarios
The certification exam may present scenarios where you must choose the most appropriate AI model for a particular task.
Examples include:
Selecting a reasoning model to analyze an execution plan for a slow query.
Choosing a balanced model to generate and explain a stored procedure.
Using a fast model to quickly scaffold a set of standard CRUD statements.
Understanding that different models may produce different levels of explanation and optimization guidance for the same prompt.
You should also understand that AI-generated SQL should always be reviewed, tested, and validated before deployment.
Best Practices
Choose the model that best matches the complexity of the task.
Provide detailed prompts with sufficient database context.
Include schema information when requesting SQL generation.
Break very large requests into smaller, focused prompts.
Review all generated SQL for correctness, security, and performance.
Validate AI recommendations using execution plans and performance metrics.
Avoid sharing sensitive production data unless organizational policies explicitly allow it.
Remember that AI assists the developer—it does not replace testing, code review, or database design expertise.
DP-800 Exam Tips
Remember the following points for the exam:
AI models differ in reasoning ability, response speed, and context handling.
Reasoning-focused models are generally better suited for performance tuning, query optimization, and architectural guidance.
Simpler or faster models are appropriate for routine SQL generation and code completion.
The quality of AI output depends heavily on the quality of the prompt and the context provided.
GitHub Copilot and Copilot in Fabric accelerate development but do not automatically validate correctness or security.
Developers remain responsible for reviewing and testing all AI-generated SQL before deployment.
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub. This topic falls under these sections: Design and develop database solutions (35–40%) --> Design and implement SQL solutions by using AI-assisted tools --> Enable GitHub Copilot and Microsoft Copilot in Fabric
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Introduction
The DP-800 exam expects candidates to understand how to enable, configure, and effectively use GitHub Copilot and Microsoft Copilot in Microsoft Fabric to improve SQL development productivity while maintaining security, governance, and responsible AI practices.
Unlike traditional SQL development topics, this objective focuses on using AI-assisted development tools rather than writing SQL syntax itself.
After studying this topic, you should be able to:
Understand the purpose of GitHub Copilot and Microsoft Copilot in Fabric.
Identify licensing and prerequisite requirements.
Enable GitHub Copilot in supported development environments.
Enable Copilot features within Microsoft Fabric.
Understand tenant, capacity, and workspace requirements.
Use AI assistants to generate SQL code.
Use AI to explain, optimize, and troubleshoot SQL.
Understand responsible AI and governance considerations.
Identify security best practices when using AI-assisted development.
What is GitHub Copilot?
GitHub Copilot is an AI-powered coding assistant that helps developers write software by generating code suggestions based on natural language prompts and existing code.
It can:
Generate SQL queries
Create stored procedures
Suggest table definitions
Generate JOIN statements
Explain SQL code
Generate comments and documentation
Help debug errors
Recommend code improvements
Convert natural language into SQL
GitHub Copilot is integrated into popular development environments, including:
Visual Studio
Visual Studio Code
GitHub.com
Azure Data Studio (where supported)
SQL development environments that support Copilot extensions
For DP-800, GitHub Copilot is primarily used to accelerate SQL database development.
What is Microsoft Copilot in Fabric?
Microsoft Copilot in Microsoft Fabric is an AI assistant built directly into the Microsoft Fabric platform.
Rather than only generating code, Fabric Copilot helps users:
Create SQL queries
Build Data Warehouses
Generate notebooks
Explain SQL statements
Create Dataflows
Build reports
Analyze datasets
Summarize data
Generate semantic model calculations
Create pipelines
Produce documentation
For SQL developers, Copilot can assist with creating and refining SQL scripts within Fabric Data Warehouse and SQL analytics experiences.
GitHub Copilot vs. Microsoft Copilot in Fabric
Feature
GitHub Copilot
Microsoft Copilot in Fabric
Primary purpose
AI coding assistant
AI assistant across Fabric workloads
SQL generation
Yes
Yes
Code explanations
Yes
Yes
Natural language prompts
Yes
Yes
Notebook assistance
Limited
Yes
Data Warehouse assistance
Yes
Yes
Power BI integration
No
Yes
Fabric workspace integration
No
Yes
Development IDE integration
Yes
Limited to Fabric experiences
GitHub Copilot Prerequisites
Before GitHub Copilot can be used, developers generally need:
A GitHub account
A GitHub Copilot subscription or enterprise license
A supported IDE (Visual Studio, Visual Studio Code, etc.)
Internet connectivity
Authentication with GitHub
Organizations may centrally manage Copilot licensing through GitHub Enterprise.
Enabling GitHub Copilot in Visual Studio Code
The general process includes:
Install Visual Studio Code.
Sign in to GitHub.
Install the GitHub Copilot extension.
Authenticate your GitHub account.
Verify that your organization permits Copilot usage.
Open a SQL file.
Begin typing or enter a natural language prompt.
Example:
-- Create a stored procedure that returns all orders placed during the last 30 days.
Copilot suggests SQL code that can then be reviewed and edited.
Enabling GitHub Copilot in Visual Studio
Visual Studio includes built-in support for GitHub Copilot after the extension is installed.
Developers typically:
Install the GitHub Copilot extension.
Sign in using GitHub credentials.
Enable Copilot in the IDE settings if required.
Open a SQL project.
Accept or reject AI-generated suggestions.
Microsoft Fabric Copilot Requirements
Copilot in Microsoft Fabric requires several prerequisites.
These commonly include:
A Microsoft Fabric tenant
An eligible Fabric capacity that supports Copilot features
Administrator approval for Copilot
Appropriate user licensing
A supported Fabric experience
Access to a Fabric workspace
Not every Fabric environment automatically has Copilot enabled.
Enabling Copilot in Microsoft Fabric
Fabric administrators control whether Copilot features are available within the organization.
Typical steps include:
Open the Fabric Admin Portal.
Navigate to Tenant Settings.
Locate Copilot and AI settings.
Enable Copilot for the organization or selected security groups.
Save configuration changes.
Assign users to workspaces with Copilot-enabled capacities.
Organizations may choose to enable Copilot only for specific departments or security groups.
Workspace Considerations
Users generally require:
Workspace access
Appropriate workspace role
Capacity that supports AI features
Having access to Fabric alone does not guarantee Copilot availability.
Security Permissions
Fabric administrators may control:
Who can use Copilot
Which workspaces allow AI
Which security groups receive access
Which users can create AI-assisted content
This supports governance and compliance requirements.
Using GitHub Copilot for SQL Development
GitHub Copilot can assist with:
Creating Tables
Example prompt:
Create a SQL table for storing customer orders.
Copilot generates a table definition including columns, data types, and constraints.
Generating Stored Procedures
Example prompt:
Create a stored procedure that returns orders by customer.
Copilot generates the T-SQL, which should then be reviewed before deployment.
Creating Functions
Developers can request:
Scalar functions
Table-valued functions
Aggregate calculations
String manipulation
Date calculations
Writing Complex Queries
Copilot can generate:
JOIN statements
CTEs
Window functions
Recursive queries
JSON queries
Graph queries
Regular expression queries
Error handling logic
Using Copilot in Fabric
Fabric Copilot supports natural language interactions.
Example:
Show the top ten customers by total sales during the last fiscal year.
Copilot may generate the corresponding SQL query automatically.
Explaining SQL Code
One valuable feature is code explanation.
Example prompt:
Explain this stored procedure.
Copilot can summarize:
joins
filters
business logic
aggregations
performance considerations
This is especially useful when maintaining legacy SQL code.
Optimizing SQL Queries
Copilot can suggest improvements such as:
adding indexes
eliminating unnecessary scans
simplifying joins
reducing nested queries
replacing cursors
improving readability
However, recommendations should always be validated using execution plans and performance testing.
AI-Assisted Documentation
Developers can use Copilot to generate:
procedure descriptions
function documentation
parameter explanations
inline comments
technical documentation
Good documentation improves maintainability and collaboration.
Responsible AI Considerations
Neither GitHub Copilot nor Fabric Copilot should be considered authoritative.
Developers remain responsible for:
correctness
performance
security
compliance
testing
deployment approval
AI accelerates development but does not replace engineering judgment.
Security Best Practices
When using AI assistants:
Never include passwords in prompts.
Do not paste connection strings.
Remove API keys.
Avoid sharing production customer data.
Use anonymized sample data whenever possible.
Review generated SQL for SQL injection vulnerabilities.
Verify permissions follow the Principle of Least Privilege.
Follow organizational AI governance policies.
Common Limitations
AI assistants may:
Generate inefficient SQL.
Hallucinate nonexistent syntax.
Recommend deprecated features.
Omit indexes.
Produce insecure dynamic SQL.
Misinterpret business requirements.
Always validate generated code before using it in production.
GitHub Copilot vs Manual Development
Task
Manual Development
GitHub Copilot
Create SQL
Fully manual
AI-assisted
Write documentation
Manual
AI-generated drafts
Generate stored procedures
Manual
AI-assisted
Explain existing code
Manual analysis
AI explanations
Query optimization suggestions
DBA experience
AI recommendations (review required)
Security validation
Developer responsibility
Developer responsibility
DP-800 Exam Tips
Be familiar with:
GitHub Copilot licensing prerequisites
Supported development environments
Fabric Copilot enablement requirements
Tenant settings that control Copilot
Workspace and capacity requirements
Appropriate use of AI-generated SQL
Responsible AI principles
Security and governance responsibilities
Human review of AI-generated code
Organizational approval for AI usage
Remember:
GitHub Copilot primarily assists developers inside coding environments, while Microsoft Copilot in Fabric provides AI assistance across multiple Fabric workloads, including SQL development, analytics, notebooks, and reporting.
Key Takeaways
GitHub Copilot is an AI-powered coding assistant that accelerates SQL development.
Microsoft Copilot in Fabric provides AI assistance throughout the Microsoft Fabric ecosystem.
Fabric administrators control Copilot availability through tenant settings and capacity configuration.
Developers need appropriate permissions, licensing, and workspace access.
AI-generated SQL should always be reviewed, tested, and validated.
Sensitive information should never be included in AI prompts.
AI improves productivity but does not replace secure software development practices.
Practice Exam Questions
Question 1
A database developer wants to use GitHub Copilot in Visual Studio Code. Which prerequisite is required before Copilot can provide code suggestions?
A. Install the GitHub Copilot extension and authenticate with a licensed GitHub account
B. Enable Microsoft Fabric capacity
C. Create a SQL Server Agent job
D. Install Azure Data Factory
Correct Answer: A
Explanation: GitHub Copilot requires a GitHub account, an appropriate Copilot license, installation of the GitHub Copilot extension, and authentication before AI-powered code suggestions become available.
Question 2
Who typically enables Microsoft Copilot features for an organization using Microsoft Fabric?
A. Every workspace member individually
B. SQL Server service account
C. Fabric administrator through tenant settings
D. Database owner
Correct Answer: C
Explanation: Microsoft Fabric administrators manage Copilot availability through tenant settings and can enable it for the entire organization or selected security groups.
Question 3
Which task is GitHub Copilot best suited to assist with?
A. Replacing SQL Server security auditing
B. Automatically approving production deployments
C. Generating SQL code and stored procedures from natural language prompts
D. Creating Azure subscriptions
Correct Answer: C
Explanation: GitHub Copilot is designed to help developers generate, explain, and improve code, including SQL statements, stored procedures, and database objects.
Question 4
A developer asks Copilot to optimize a SQL query. What should the developer do before deploying the suggested code?
A. Assume the generated code is correct
B. Skip performance testing
C. Disable indexes
D. Review, test, and validate the generated SQL
Correct Answer: D
Explanation: AI-generated code should always undergo testing, performance evaluation, security review, and validation before being used in production.
Question 5
Which Microsoft Fabric requirement is commonly necessary for users to access Copilot features?
A. Workspace access and a Copilot-supported Fabric capacity
B. SQL Server Express Edition
C. Windows Server Failover Clustering
D. SQL Server Agent enabled
Correct Answer: A
Explanation: Users generally require access to a Fabric workspace that resides on a capacity supporting Copilot features, along with the necessary permissions.
Question 6
What is an appropriate use of Microsoft Copilot in Fabric?
A. Automatically bypassing security reviews
B. Generating SQL queries from natural language requests
C. Granting database administrator privileges
D. Disabling tenant governance
Correct Answer: B
Explanation: Fabric Copilot can translate natural language requests into SQL queries and assist with other Fabric workloads, but it does not replace security or governance processes.
Question 7
Which statement best describes the relationship between GitHub Copilot and Microsoft Copilot in Fabric?
A. They perform exactly the same functions in every environment.
B. GitHub Copilot only works with Power BI.
C. Fabric Copilot replaces all integrated development environments.
D. GitHub Copilot primarily assists with coding, while Fabric Copilot assists across multiple Microsoft Fabric experiences.
Correct Answer: D
Explanation: GitHub Copilot focuses on AI-assisted software development within supported IDEs, whereas Fabric Copilot provides AI capabilities across data engineering, analytics, warehousing, notebooks, reporting, and SQL experiences.
Question 8
Which information should never be included in an AI prompt when requesting SQL assistance?
A. Sample table names
B. General business requirements
C. Production passwords and connection strings
D. Desired query output
Correct Answer: C
Explanation: Sensitive information such as passwords, connection strings, API keys, and confidential customer data should never be shared with AI tools.
Question 9
Which benefit does GitHub Copilot provide during SQL development?
A. It automatically deploys production databases.
B. It generates AI-assisted code suggestions that can improve developer productivity.
C. It permanently replaces code reviews.
D. It guarantees optimal query performance.
Correct Answer: B
Explanation: GitHub Copilot accelerates development by generating code suggestions, but developers remain responsible for testing, reviewing, and validating the generated code.
Question 10
Which statement reflects Microsoft’s recommended approach to AI-assisted database development?
A. AI-generated code should always be deployed without modification.
B. AI eliminates the need for peer reviews.
C. AI-generated code should be treated as a draft that developers validate for correctness, security, and performance.
D. AI guarantees compliance with organizational policies.
Correct Answer: C
Explanation: AI-generated code should be viewed as a productivity aid rather than authoritative output. Developers are responsible for verifying functionality, security, performance, compliance, and adherence to organizational standards before deployment.
The Data Community 