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:
CREATE TABLE Customer( CustomerID INT PRIMARY KEY, 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, TotalAmountFROM Sales.OrdersWHERE 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 DatabaseTable:Sales.OrdersRows:150 millionGoal:Reduce CPU utilizationCurrent 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.
Go to the DP-800 Exam Prep Hub main page
The Data Community 