AI-Assisted SQL with GitHub Copilot

Your AI pair programmer for SQL

GitHub Copilot for SQL development

GitHub Copilot provides AI assistance directly in your IDE. For SQL developers, it can:

• Generate T-SQL from natural language descriptions
• Complete code as you type (inline suggestions)
• Explain existing queries line by line
• Debug errors by analysing error messages with context
• Optimise queries by suggesting index strategies or rewrites

Enabling GitHub Copilot

1. GitHub account — requires a Copilot subscription (Individual, Business, or Enterprise)
2. IDE extension — install the GitHub Copilot extension in VS Code or Visual Studio
3. Organisation policy — for Copilot Business/Enterprise, an admin must enable it at the org level
4. Sign in — authenticate with your GitHub account in the IDE

Chat vs inline suggestions

Feature	Inline Suggestions	Copilot Chat
How it works	Suggests code as you type	Conversational Q&A in a side panel
Best for	Writing new code, auto-completing patterns	Explaining code, debugging, asking questions
Context	Current file and open tabs	Current file + explicitly attached context
Interaction	Tab to accept, Esc to dismiss	Type questions, get explanations

Copilot in Microsoft Fabric

Fabric integrates Copilot directly into the SQL experience:

• SQL notebooks — write natural language, get T-SQL
• SQL editor — inline suggestions while writing queries
• Data warehouse — AI-assisted query building against Fabric data

Enabling Copilot in Fabric requires:

1. Fabric capacity (F64 or higher, or trial)
2. Tenant-level admin setting enabled
3. User must have appropriate Fabric permissions

Security impact of AI-assisted tools

This is a critical exam topic. AI tools introduce security considerations that developers must understand.

Security risks of AI-assisted tools — the exam expects you to identify and mitigate each

Risk	What Happens	Mitigation
Code sent to external service	Your T-SQL and schema context are sent to the AI model endpoint	Review data handling policies; use Copilot Business/Enterprise for IP protection
Sensitive data in prompts	Connection strings, passwords, or PII in code context may be sent	Never hardcode secrets; use environment variables and Key Vault references
Generated code may be insecure	SQL injection vulnerabilities, missing parameterisation, overly broad permissions	Always review generated code; never deploy AI suggestions without testing
Compliance and data residency	Data may cross geographic boundaries during processing	Check Copilot data handling vs your compliance requirements (GDPR, SOC 2)
Intellectual property	Generated code may resemble public code or copyrighted patterns	Copilot Business/Enterprise includes IP indemnity; configure content filters

💡 Exam tip: The security question pattern

The exam often asks: “A developer is using GitHub Copilot to write queries against a production database. What is the MOST significant security concern?”

The answer is usually about sensitive data in context — schema names, table structures, and especially any hardcoded credentials that appear in the code context sent to the AI service. The mitigation is always: use parameterised queries, never hardcode secrets, and review organisational policies on AI tool data handling.

GitHub Copilot instruction files

Instruction files tell Copilot about your project’s conventions, standards, and constraints. They ensure consistent, compliant code suggestions.

.github/copilot-instructions.md

This file lives in your repository and provides project-wide context:

# Database Coding Standards

## Naming conventions
- Tables: PascalCase singular (Customer, not customers)
- Columns: PascalCase (FirstName, not first_name)
- Stored procedures: usp_ prefix (usp_GetCustomer)
- Views: vw_ prefix (vw_ActiveOrders)
- Indexes: IX_{Table}_{Columns}

## Security requirements
- Always use parameterised queries — never concatenate user input
- Use EXECUTE AS for stored procedures accessing sensitive data
- Apply principle of least privilege in all permission grants

## Performance standards
- Include SET NOCOUNT ON in all stored procedures
- Use SET XACT_ABORT ON for transaction safety
- Prefer inline TVFs over scalar functions
- Always include error handling (TRY...CATCH)

Model and tool options in chat

In Copilot Chat, you can configure:

• Model selection — choose between available models (GPT-4o, Claude, etc.)
• MCP tools — connect to database-aware tools for schema-aware suggestions
• Context attachments — explicitly include files, schemas, or documentation

💡 Scenario: Dev's Copilot setup at PixelForge

Dev Kapoor at PixelForge Studios creates a .github/copilot-instructions.md that includes:

• Naming conventions (all tables use pf_ prefix)
• Security rules (all user-facing queries must use parameterisation)
• Performance guidelines (no scalar functions in SELECT lists)
• Compliance notes (GDPR — never log PII in error messages)

Now when any developer on the team uses Copilot, the suggestions automatically follow PixelForge’s standards. Dev also connects the SQL MCP server so Copilot understands the actual database schema when suggesting queries.

Question

What is the purpose of a .github/copilot-instructions.md file?

Click or press Enter to reveal answer

Answer

It provides project-wide context to GitHub Copilot — coding standards, naming conventions, security requirements, and constraints. Copilot reads this file and tailors its suggestions to follow your team's rules. It lives in the repository root under .github/.

Click to flip back

Question

What is the biggest security risk of using AI-assisted tools for SQL development?

Click or press Enter to reveal answer

Answer

Sensitive data exposure — schema information, connection strings, and potentially PII in code context are sent to the AI model endpoint. Mitigate by: never hardcoding secrets, using Copilot Business/Enterprise for IP protection, and reviewing data handling policies.

Click to flip back

Question

What is required to enable Copilot in Microsoft Fabric?

Click or press Enter to reveal answer

Answer

Three requirements: (1) Fabric capacity F64 or higher (or trial), (2) tenant-level admin setting enabled for Copilot, and (3) user must have appropriate Fabric permissions. Copilot is not available on lower-tier capacities.

Click to flip back

Knowledge Check

Ingrid at Nordic Shield Insurance is evaluating GitHub Copilot for the database team. The company handles sensitive policyholder data subject to GDPR. Which concern should Ingrid raise FIRST?

ACopilot-generated code might have syntax errorsBSchema context and potentially sensitive column names are sent to an external AI service during code generationCCopilot suggestions might not follow the company's naming conventionsDThe team will become dependent on AI and lose their SQL skills

Check Answer

Knowledge Check

Dev at PixelForge wants Copilot to always generate stored procedures with SET NOCOUNT ON, SET XACT_ABORT ON, and TRY...CATCH error handling. What is the best way to enforce this?

ATell each developer to include these requirements in every Copilot chat promptBCreate a .github/copilot-instructions.md file with these standards in the repositoryCConfigure a database trigger that rejects procedures without these patternsDUse a pre-commit hook that scans for missing patterns

Check Answer

🎬 Video coming soon

Next up: MCP: Connecting AI to Your Database — connect AI agents to your SQL Server and Fabric databases using Model Context Protocol.