Tag: Microsoft Certification

Data Governance, Data Security, DP-800, Microsoft Certification, SQL August 1, 2026

Design and implement Dynamic Data Masking (DDM) (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Design and implement Dynamic Data Masking

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.

What is Dynamic Data Masking?

Dynamic Data Masking (DDM) is a SQL Server and Azure SQL feature that limits the exposure of sensitive data by masking the results returned to non-privileged users without modifying the actual data stored in the database.

Unlike encryption, DDM does not change or encrypt the stored data. Instead, SQL Server dynamically replaces sensitive values with masked values when queries are executed by users who do not have permission to view the original data.

For example, the database may contain:

CustomerName	SSN	Email
John Smith	123-45-6789	john@email.com

A privileged user sees:

CustomerName	SSN	Email
John Smith	123-45-6789	john@email.com

A non-privileged user may see:

CustomerName	SSN	Email
John Smith	XXX-XX-6789	jXXX@XXXX.com

The underlying data never changes.

Why Use Dynamic Data Masking?

Organizations frequently store sensitive information such as:

Personally Identifiable Information (PII)
Social Security Numbers
Credit card numbers
Email addresses
Phone numbers
Employee salaries
Medical information

Not every user who queries the database should have unrestricted access to these values.

DDM allows developers to:

Reduce accidental data exposure
Protect sensitive fields
Simplify application development
Support compliance initiatives
Allow customer support personnel to work with realistic-looking data

How Dynamic Data Masking Works

When a user executes a query:

SQL Server checks whether the user has permission to view unmasked data.
If the user has the UNMASK permission, actual values are returned.
Otherwise, SQL Server substitutes masked values before sending the results.

The database itself remains unchanged.

Dynamic Data Masking Architecture

			
Database
│
├── Actual Data
│      987-65-4321
│
├── User A
│      Has UNMASK permission
│
│      Result:
│      987-65-4321
│
└── User B
       No UNMASK permission
       Result:
       XXX-XX-4321

		

Benefits of Dynamic Data Masking

DDM provides several important advantages.

Easy to Implement

Masking is configured using T-SQL without requiring application changes.

No Data Duplication

The original data remains stored only once.

Transparent to Applications

Applications continue issuing the same queries.

No application code changes are required.

Supports Least Privilege

Users receive only the information they need.

Helps Meet Compliance Requirements

Although DDM is not encryption, it helps organizations reduce unnecessary exposure of sensitive information.

Dynamic Data Masking vs Encryption

Dynamic Data Masking	Encryption
Masks query results	Encrypts stored data
Data remains unchanged	Data stored encrypted
Protects against accidental viewing	Protects against data theft
Transparent to applications	May require encryption keys
Does not secure backups	Protects stored data

Microsoft expects candidates to understand that DDM is not a replacement for encryption technologies such as Always Encrypted or Transparent Data Encryption (TDE).

Supported Masking Functions

SQL Server supports several built-in masking functions.

Default Mask

Masks data according to its data type.

Example:

Original:

John Smith

Masked:

XXXX

Syntax:

MASKED WITH (FUNCTION = 'default()')

Email Mask

Designed specifically for email addresses.

Original:

john.smith@email.com

Masked:

jXXX@XXXX.com

Syntax:

MASKED WITH (FUNCTION = 'email()')

Partial Mask

Reveals part of a string while masking the remainder.

Example:

Original:

555-123-4567

Masked:

XXX-XXX-4567

Syntax:

			
MASKED WITH
(
FUNCTION='partial(prefix,padding,suffix)'
)

Example:

			
MASKED WITH
(
FUNCTION='partial(0,"XXX-XXX-",4)'
)

Random Mask

Returns a random value within a specified numeric range.

Example:

Original Salary

Masked

Syntax

			
MASKED WITH
(
FUNCTION='random(1,100000)'
)

Useful when exact values should never be exposed.

Creating a Masked Column

Example:

			
CREATE TABLE Customers
(
    CustomerID INT,
    Name NVARCHAR(100),
    Email NVARCHAR(200)
    MASKED WITH (FUNCTION='email()'),
    SSN CHAR(11)
    MASKED WITH
    (
        FUNCTION='partial(0,"XXX-XX-",4)'
    )
);

		

Adding a Mask to an Existing Column

			
ALTER TABLE Customers
ALTER COLUMN Email
ADD MASKED
WITH (FUNCTION='email()');

Removing a Mask

			
ALTER TABLE Customers
ALTER COLUMN Email
DROP MASKED;

Granting UNMASK Permission

Privileged users may view actual values.

GRANT UNMASK TO HRManager;

Revoking Permission

REVOKE UNMASK FROM HRManager;

Viewing Mask Definitions

View masking metadata.

			
SELECT *
FROM sys.masked_columns;

Useful during administration and auditing.

DDM with Azure SQL Database

Dynamic Data Masking is fully supported in:

Azure SQL Database
Azure SQL Managed Instance
SQL Server

Azure SQL also provides portal-based configuration through the Azure Portal.

Developers can create masks without writing T-SQL.

Limitations of Dynamic Data Masking

Candidates should understand these limitations.

It Is Not Encryption

Anyone with sufficient permissions can retrieve actual values.

Database Administrators Can View Data

Members of powerful administrative roles can bypass masking.

Cannot Stop Inference Attacks

Users may infer values through repeated queries.

Not Intended for High-Security Scenarios

Highly confidential data should use:

Always Encrypted
Transparent Data Encryption
Row-Level Security
Proper access control

Expressions Return Masked Values

If a masked column is used in expressions, the expression also returns masked results for users without UNMASK permission.

Best Practices

Mask Only Sensitive Columns

Avoid unnecessary masking.

Combine with Other Security Features

Use together with:

Always Encrypted
Row-Level Security
Transparent Data Encryption
Microsoft Entra authentication
Least privilege access

Grant UNMASK Sparingly

Only trusted users should receive this permission.

Test Using Non-Privileged Accounts

Always verify what ordinary users actually see.

Audit Sensitive Access

Monitor who receives UNMASK permissions.

Dynamic Data Masking vs Row-Level Security

Dynamic Data Masking	Row-Level Security
Masks values	Filters rows
User sees all rows	User sees only authorized rows
Protects columns	Protects records
Works with SELECT results	Controls data visibility
Often used with RLS	Often combined with DDM

DP-800 Exam Tips

Candidates should be able to:

Explain what Dynamic Data Masking is.
Differentiate masking from encryption.
Identify supported masking functions.
Create masked columns using CREATE TABLE and ALTER TABLE.
Grant and revoke the UNMASK permission.
Understand when DDM is appropriate.
Recognize DDM limitations.
Choose DDM versus Always Encrypted, TDE, or Row-Level Security based on the security requirement.
Understand that DDM protects against accidental exposure, not malicious users with elevated privileges.

Practice Exam Questions

Question 1

A company wants customer support representatives to view only partially masked Social Security numbers while allowing HR staff to view the full values.

Which SQL Server feature best meets this requirement?

A. Transparent Data Encryption

B. Dynamic Data Masking

C. Always Encrypted

D. Data Compression

Answer: B

Explanation: Dynamic Data Masking displays masked values to unauthorized users while allowing authorized users with the appropriate permissions to see the original data.

Question 2

Which statement about Dynamic Data Masking is true?

A. It encrypts data stored on disk.

B. It permanently changes stored values.

C. It masks query results for users without UNMASK permission.

D. It replaces encryption.

Answer: C

Explanation: Dynamic Data Masking only alters the data presented in query results. The stored values remain unchanged.

Question 3

Which masking function is specifically designed for email addresses?

A. partial()

B. random()

C. default()

D. email()

Answer: D

Explanation: The email() masking function preserves the general format of an email address while obscuring most of the information.

Question 4

Which statement best describes the partial() masking function?

A. It encrypts selected characters.

B. It returns random values.

C. It permanently replaces data.

D. It reveals specified prefix and suffix characters while masking the middle.

Answer: D

Explanation: The partial() function exposes configurable leading and trailing characters while masking the remaining portion of the value.

Question 5

Which permission allows a user to view unmasked data?

A. SELECT

B. CONTROL

C. UNMASK

D. VIEW DEFINITION

Answer: C

Explanation: Users granted the UNMASK permission can view the original values instead of the masked representations.

Question 6

Which system catalog view displays information about masked columns?

A. sys.columns

B. sys.masked_columns

C. sys.tables

D. sys.database_permissions

Answer: B

Explanation: The sys.masked_columns catalog view contains metadata about all columns configured with Dynamic Data Masking.

Question 7

A database administrator wants to protect highly confidential financial information from administrators who manage the database server.

Which technology should be preferred over Dynamic Data Masking?

A. Always Encrypted

B. Dynamic Data Masking

C. Partial masking

D. Random masking

Answer: A

Explanation: Always Encrypted ensures that sensitive data remains encrypted even from database administrators because encryption and decryption occur on the client side.

Question 8

Which statement about Dynamic Data Masking and application code is generally correct?

A. Applications must always be rewritten.

B. DDM requires client-side decryption.

C. Existing queries usually continue to work without modification.

D. Applications cannot access masked tables.

Answer: C

Explanation: Dynamic Data Masking is transparent to most applications, allowing existing queries to function normally while returning masked data when appropriate.

Question 9

A developer executes the following statement:

GRANT UNMASK TO SalesManager;

What is the effect?

A. The SalesManager can modify masked columns.

B. The SalesManager can bypass row-level security.

C. The SalesManager can view original values in masked columns, provided they also have permission to access the data.

D. All users inherit the UNMASK permission.

Answer: C

Explanation: The UNMASK permission allows a user to see unmasked values but does not grant access to data that the user is otherwise unauthorized to read.

Question 10

Which security strategy provides the strongest protection for sensitive database columns?

A. Use only Dynamic Data Masking.

B. Use only Row-Level Security.

C. Use only Transparent Data Encryption.

D. Combine Dynamic Data Masking with encryption, least-privilege access, and other SQL Server security features.

Answer: D

Explanation: Dynamic Data Masking is most effective as part of a layered security strategy that also includes encryption, access controls, auditing, and other SQL Server security features.

Go to the DP-800 Exam Prep Hub main page

Data Governance, Data Security, DP-800, Microsoft Certification August 1, 2026

Design and implement data encryption, including Always Encrypted and column-level encryption (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Design and implement data encryption, including Always Encrypted and column-level encryption

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.

Data encryption is one of the most important security capabilities available in Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL Database. Encryption helps protect sensitive information such as personally identifiable information (PII), financial records, healthcare data, passwords, and confidential business information from unauthorized access.

For the DP-800: Developing AI-Enabled Database Solutions exam, you should understand not only how to implement encryption, but also when to use different encryption technologies, their limitations, performance implications, and how they interact with applications.

Why Data Encryption Matters

Modern organizations must comply with regulations such as:

GDPR
HIPAA
PCI-DSS
SOC 2
ISO 27001

Encryption protects data against:

Database theft
Unauthorized administrators
Lost backups
Insider threats
Network interception

SQL Server provides multiple encryption technologies, each solving a different security problem.

SQL Server Encryption Technologies

Understanding which technology solves which problem is critical for the exam.

Technology	Protects	Data State
Transparent Data Encryption (TDE)	Database files and backups	At rest
Always Encrypted	Sensitive columns from DBAs and attackers	In use and at rest
Column-Level Encryption	Individual columns	At rest
TLS/SSL	Network traffic	In transit
Dynamic Data Masking	Prevents accidental viewing	Query results
Row-Level Security	Limits rows returned	Query execution

Data at Rest vs Data in Transit vs Data in Use

A common exam objective is understanding these three states.

Data at Rest

Data stored on:

MDF files
LDF files
Backups
Storage disks

Protected using:

TDE
Column encryption
Always Encrypted

Data in Transit

Data traveling:

Client → SQL Server
SQL Server → Application

Protected using:

TLS (SSL)

Data in Use

Data currently being processed inside memory.

Only Always Encrypted protects sensitive data while SQL Server is processing queries.

Transparent Data Encryption (TDE)

Although the objective focuses on Always Encrypted and column-level encryption, you should understand how TDE differs.

TDE encrypts:

Database files
Log files
Backups

Advantages:

No application changes
Easy to enable
Minimal performance overhead

Limitations:

SQL Server decrypts data automatically.
Database administrators can still read data.

TDE protects storage—not the data itself from privileged users.

Column-Level Encryption

Column-level encryption encrypts specific columns inside a table.

Example:

			
CreditCardNumber
SocialSecurityNumber
Salary

Instead of encrypting the whole database, only selected columns are encrypted.

How Column-Level Encryption Works

SQL Server uses encryption functions such as:

ENCRYPTBYKEY
DECRYPTBYKEY
ENCRYPTBYPASSPHRASE
DECRYPTBYPASSPHRASE

Example:

			
OPEN SYMMETRIC KEY CustomerKey
DECRYPTION BY CERTIFICATE CustomerCert;
UPDATE Customers
SET SSN =
ENCRYPTBYKEY(KEY_GUID('CustomerKey'), '123-45-6789');

		

Reading data:

			
SELECT
CONVERT(varchar,
DECRYPTBYKEY(SSN))
FROM Customers;

Encryption Hierarchy

SQL Server uses multiple encryption layers.

			
Service Master Key
        ↓
Database Master Key
        ↓
Certificate
        ↓
Symmetric Key
        ↓
Encrypted Column

		

Each level protects the one below it.

Symmetric Encryption

Uses one key for both:

Encryption
Decryption

Advantages

Fast
Efficient
Best for large datasets

Example

			
Encrypt → Key A
Decrypt → Key A

Asymmetric Encryption

Uses:

Public key
Private key

Advantages

Strong security
Digital signatures

Disadvantages

Slower

Usually used to protect symmetric keys.

Certificates

Certificates often protect symmetric keys.

Example:

			
Certificate
↓
Protects Symmetric Key
↓
Encrypts Customer Data

		

Always Encrypted

Always Encrypted is one of the most important DP-800 topics.

Unlike traditional encryption:

SQL Server never sees the plaintext values.

Encryption occurs inside the client application.

Why Always Encrypted Exists

Imagine a database administrator with full access.

With normal encryption:

DBA can decrypt data.

With Always Encrypted:

DBA cannot read encrypted values.

Only authorized client applications possess the encryption keys.

How Always Encrypted Works

			
Application
↓
Encrypt value
↓
SQL Server stores ciphertext
↓
Application retrieves ciphertext
↓
Application decrypts

		

SQL Server never performs decryption.

Benefits

Protects against:

Curious administrators
Database theft
Backup theft
Cloud administrators
Insider attacks

Key Components

Always Encrypted uses two key types.

Column Master Key (CMK)

Stored outside SQL Server.

Examples:

Windows Certificate Store
Azure Key Vault
Hardware Security Module (HSM)

Purpose:

Protects Column Encryption Keys.

Column Encryption Key (CEK)

Stored inside SQL Server.

Purpose:

Encrypts actual column values.

Hierarchy:

			
CMK
↓
CEK
↓
Encrypted Data

		

Deterministic Encryption

Always produces the same ciphertext for identical values.

Example

			
"Florida"
↓
A91BCD
"Florida"
↓
A91BCD

		

Advantages

Supports:

Equality searches
Joins
GROUP BY
Indexes

Disadvantages

Repeated values are recognizable.

Randomized Encryption

Produces different ciphertext every time.

Example

			
Florida
↓
A91BCD
Florida
↓
XYZ123

		

Advantages

Maximum security.

Disadvantages

Cannot perform:

Equality comparisons
JOIN
GROUP BY
Index lookups

Deterministic vs Randomized

Feature	Deterministic	Randomized
Highest security	No	Yes
Equality search	Yes	No
JOIN	Yes	No
GROUP BY	Yes	No
Index seek	Yes	No

Creating a Column Master Key

Example:

			
CREATE COLUMN MASTER KEY CMK1
WITH
(
KEY_STORE_PROVIDER_NAME =
'MSSQL_CERTIFICATE_STORE',
KEY_PATH =
'CurrentUser/My/123456789'
);

		

Creating a Column Encryption Key

			
CREATE COLUMN ENCRYPTION KEY CEK1
WITH VALUES
(
COLUMN_MASTER_KEY = CMK1,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = ...
);

		

Encrypting a Column

			
CREATE TABLE Customers
(
CustomerID INT,
SSN CHAR(11)
COLLATE Latin1_General_BIN2
ENCRYPTED WITH
(
COLUMN_ENCRYPTION_KEY = CEK1,
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM =
'AEAD_AES_256_CBC_HMAC_SHA_256'
)
);

		

Secure Enclaves

Always Encrypted originally limited many SQL operations.

Secure Enclaves improve functionality by allowing protected computations within a secure hardware-based memory region.

Benefits:

Richer comparisons
Pattern matching
Range queries
In-place encryption
Better performance

Limitations of Always Encrypted

Developers should understand these limitations.

Not all SQL operations are supported.

Some restrictions include:

LIKE (without enclaves)
Pattern matching
Sorting randomized columns
Range comparisons
Certain aggregates
Some conversions

Client Driver Requirements

Always Encrypted requires supported drivers.

Examples:

Microsoft.Data.SqlClient
.NET Framework
ODBC Driver
JDBC Driver

Client drivers perform:

Encryption
Decryption
Key retrieval

Azure Key Vault Integration

A common enterprise deployment stores Column Master Keys inside Azure Key Vault.

Benefits:

Centralized key management
Hardware-backed security
Automatic auditing
Key rotation
Separation of duties

Performance Considerations

Always Encrypted introduces overhead because:

Client encrypts data
Client decrypts data
Keys must be managed
Network payloads increase

However, it provides much stronger protection than standard encryption.

Best Practices

Microsoft recommends:

Encrypt only sensitive columns.
Store CMKs outside SQL Server.
Use Azure Key Vault when possible.
Use deterministic encryption only when querying is required.
Use randomized encryption for maximum confidentiality.
Rotate encryption keys regularly.
Use TLS together with Always Encrypted.
Monitor application performance after enabling encryption.
Test query compatibility before production deployment.

DP-800 Exam Tips

Be prepared to distinguish:

TDE vs Always Encrypted
Column-Level Encryption vs Always Encrypted
Deterministic vs Randomized encryption
CMK vs CEK
Encryption at rest vs in transit vs in use
Azure Key Vault integration
Secure Enclaves
Encryption hierarchy
Performance implications
Client-side versus server-side encryption

Practice Exam Questions

Question 1

A company wants to ensure that database administrators cannot view customers’ Social Security numbers while still allowing applications to access the data. Which encryption technology should be implemented?

A. Transparent Data Encryption (TDE)

B. Dynamic Data Masking

C. Row-Level Security

D. Always Encrypted

Answer: D

Explanation: Always Encrypted performs encryption and decryption on the client side, preventing SQL Server and database administrators from viewing plaintext data.

Question 2

Which key encrypts the actual column data in Always Encrypted?

A. Column Encryption Key

B. Database Master Key

C. Service Master Key

D. Column Master Key

Answer: A

Explanation: The Column Encryption Key (CEK) encrypts the column values. The Column Master Key (CMK) protects the CEK.

Question 3

Which encryption type should you choose if users must frequently search by exact Social Security number?

A. Randomized encryption

B. Transparent Data Encryption

C. Deterministic encryption

D. Dynamic Data Masking

Answer: C

Explanation: Deterministic encryption produces the same ciphertext for identical values, enabling equality searches and index usage.

Question 4

Which SQL Server feature encrypts entire database files and backup files without requiring application changes?

A. Always Encrypted

B. Column-Level Encryption

C. Dynamic Data Masking

D. Transparent Data Encryption

Answer: D

Explanation: Transparent Data Encryption (TDE) encrypts database and backup files, protecting data at rest.

Question 5

Where is the Column Master Key typically stored?

A. Azure Storage Account

B. SQL Server system database

C. TempDB

D. Azure Key Vault or Windows Certificate Store

Answer: D

Explanation: Microsoft recommends storing Column Master Keys outside SQL Server, commonly in Azure Key Vault or the Windows Certificate Store.

Question 6

Which encryption method provides the highest confidentiality for sensitive columns?

A. Deterministic encryption

B. Randomized encryption

C. Transparent Data Encryption

D. TLS encryption

Answer: B

Explanation: Randomized encryption produces different ciphertext for identical values, making frequency analysis much more difficult.

Question 7

A developer wants to encrypt only the CreditCardNumber column while leaving the remainder of the table unchanged. Which approach is most appropriate?

A. Column-Level Encryption

B. Transparent Data Encryption

C. Database snapshots

D. Always On Availability Groups

Answer: A

Explanation: Column-level encryption targets individual columns rather than the entire database.

Question 8

Which SQL Server feature enhances Always Encrypted by allowing additional query operations on encrypted columns?

A. Secure Enclaves

B. Dynamic Data Masking

C. PolyBase

D. Stretch Database

Answer: A

Explanation: Secure Enclaves enable richer computations on encrypted data, including some range and pattern-matching operations.

Question 9

Which data state is protected by TLS encryption?

A. Data at rest

B. Data in transit

C. Data in use

D. Archived data

Answer: B

Explanation: TLS encrypts network communications between clients and SQL Server, protecting data while it is being transmitted.

Question 10

Why is Always Encrypted considered more secure than traditional column-level encryption?

A. It automatically compresses encrypted data.

B. It encrypts entire databases.

C. SQL Server never has access to plaintext values because encryption occurs on the client side.

D. It eliminates the need for encryption keys.

Answer: C

Explanation: Always Encrypted keeps encryption keys and plaintext data outside SQL Server, ensuring that even highly privileged users cannot view sensitive information.

Go to the DP-800 Exam Prep Hub main page

Data Development, DP-800, Microsoft Certification, SQL August 1, 2026August 1, 2026

Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse (DP-800 Exam Prep)

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
      --> Connect to MCP server endpoints, including Microsoft SQL Server and Fabric lakehouse

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

As AI-powered development tools continue to evolve, developers increasingly need AI assistants that can interact with live enterprise systems rather than relying solely on the knowledge contained within large language models. The Model Context Protocol (MCP) provides a standardized way for AI assistants, such as GitHub Copilot and Microsoft Copilot, to securely connect to external tools, databases, services, and applications.

For DP-800 candidates, understanding how MCP enables AI-assisted database development is becoming increasingly important. Rather than simply generating SQL code, AI assistants can use MCP to retrieve database metadata, inspect schemas, execute approved queries, explore Fabric Lakehouse data, and assist with troubleshooting in real time.

This article explains how MCP works, how to connect to MCP server endpoints, common use cases involving Microsoft SQL Server and Microsoft Fabric Lakehouse, and best practices for secure implementation.

Learning Objectives

After studying this topic, you should be able to:

Understand the purpose of the Model Context Protocol (MCP)
Explain the relationship between AI clients and MCP servers
Describe how GitHub Copilot and Microsoft Copilot use MCP
Connect AI assistants to SQL Server MCP endpoints
Connect AI assistants to Microsoft Fabric Lakehouse MCP endpoints
Understand authentication and authorization requirements
Follow security best practices
Troubleshoot common MCP connection issues

What is the Model Context Protocol (MCP)?

The Model Context Protocol (MCP) is an open protocol that standardizes communication between AI applications and external systems.

Instead of building custom integrations for every database or service, AI clients communicate with MCP servers using a consistent protocol.

Think of MCP as a standardized “USB-C connector” for AI applications.

Without MCP:

			
AI Client
   |
Custom SQL Connector
Custom Fabric Connector
Custom REST Connector
Custom File Connector

		

With MCP:

			
AI Client
      |
      MCP
      |
-------------------------------------
SQL Server
Fabric Lakehouse
REST APIs
Files
GitHub
Azure Services

		

This standardized approach simplifies integration while improving maintainability and interoperability.

Why MCP Matters

Traditional AI coding assistants only generate code based on:

User prompts
Training data
Conversation history

Using MCP, AI assistants can also access:

Database schemas
Table definitions
Views
Stored procedures
Lakehouse metadata
Files
Documentation
Business knowledge
External APIs

This enables AI to generate more accurate, context-aware responses.

MCP Architecture

An MCP solution consists of three primary components.

MCP Client

The MCP client is the AI application.

Examples include:

GitHub Copilot
Microsoft Copilot
Visual Studio Code
Visual Studio
Other MCP-compatible AI assistants

The client sends requests to one or more MCP servers.

MCP Server

The MCP server exposes tools and resources that AI assistants can access.

Examples:

SQL Server
Fabric Lakehouse
Azure services
GitHub repositories
File systems
REST APIs

The server determines which operations are available.

Resource or Tool

Resources exposed by an MCP server may include:

Database tables
Views
Stored procedures
SQL execution tools
Schema information
Lakehouse metadata
Documentation
APIs

MCP Communication Flow

A typical workflow is:

			
Developer
↓
GitHub Copilot
↓
MCP Server
↓
SQL Server
↓
Results
↓
GitHub Copilot
↓
Developer

		

The AI assistant acts as the intermediary, translating user requests into approved tool invocations.

Connecting to an MCP Server

Connecting to an MCP server typically involves:

Configuring the AI client
Registering the MCP endpoint
Authenticating
Discovering available tools
Authorizing access
Using the available resources

Authentication

Authentication verifies the identity of the user or application.

Common authentication methods include:

Microsoft Entra ID
OAuth
Personal Access Tokens (PATs)
API Keys (less common)
Managed Identity (Azure-hosted scenarios)

Authentication occurs before any tool or data is accessed.

Authorization

Authorization determines what operations the AI may perform.

For example:

Allowed:

Read schema
Execute SELECT statements
View metadata

Denied:

DROP TABLE
DELETE production data
ALTER DATABASE

Least privilege remains an essential security principle.

Connecting to Microsoft SQL Server

An SQL Server MCP server exposes database capabilities to AI assistants.

Common resources include:

Tables
Views
Stored procedures
Functions
Database metadata
Execution plans
Query execution tools

Example workflow:

Developer asks:

Show me the Sales schema.

Copilot sends an MCP request.

SQL Server returns:

Tables
Columns
Relationships

Copilot explains the schema.

SQL Server MCP Use Cases

Examples include:

Schema Discovery

Instead of guessing table names:

Copilot retrieves:

Customers
Orders
Products
Sales

The generated SQL becomes much more accurate.

Generate SQL

Developer:

Show total revenue by country.

Copilot:

Reads schema
Finds relationships
Generates correct JOIN statements

Explain Stored Procedures

Developer:

Explain usp_ProcessOrders.

Copilot retrieves:

Procedure definition
Parameters
Business logic

Then provides a detailed explanation.

Query Optimization

Copilot can:

Inspect indexes
Analyze execution plans
Suggest rewrites
Recommend indexing improvements

Connecting to Microsoft Fabric Lakehouse

Fabric Lakehouse combines:

Data Lake
Data Warehouse
Spark
Delta tables

Using MCP, Copilot can interact with Lakehouse metadata.

Available resources may include:

Delta tables
Shortcuts
SQL endpoint metadata
Semantic information
OneLake structure

Fabric Lakehouse Use Cases

Examples include:

Discover Tables

Developer:

List all sales tables.

Copilot queries metadata.

Generate SQL Analytics Queries

Developer:

Calculate monthly sales growth.

Copilot examines available tables.

Generates optimized SQL.

Explain Lakehouse Structure

Developer:

Explain this Lakehouse.

Copilot can describe:

Schemas
Delta tables
Relationships
Storage organization

Data Exploration

Developers can ask:

Which tables contain customer data?
Which columns contain dates?
Which datasets contain revenue?

MCP Tool Discovery

One advantage of MCP is automatic discovery.

After connecting, Copilot can identify available tools such as:

Execute SQL
Read schema
Read documentation
Search metadata
Retrieve files

The user does not need to manually configure every capability.

Multiple MCP Servers

An AI assistant may connect to multiple MCP servers simultaneously.

Example:

			
GitHub Copilot
├── SQL Server MCP
├── Fabric Lakehouse MCP
├── GitHub MCP
├── Azure MCP
└── Documentation MCP

		

This allows a single conversation to span multiple enterprise systems.

Security Considerations

Organizations should never allow unrestricted AI access to production databases.

Best practices include:

Read-only access whenever possible
Least privilege permissions
Entra ID authentication
Audit logging
Approval workflows for sensitive actions
Data classification awareness
Secure network connectivity
Encryption in transit
Regular permission reviews

Network Considerations

Successful MCP connections require:

Network connectivity
Firewall configuration
DNS resolution
TLS encryption
Endpoint availability

Connection failures often result from blocked network paths or invalid authentication.

Common Connection Issues

Common problems include:

Authentication Failure

Possible causes:

Expired token
Invalid credentials
Missing permissions

Authorization Failure

The user authenticates successfully but lacks permission to use a tool.

Endpoint Unavailable

Possible causes:

Incorrect URL
Server offline
Network outage

Firewall Restrictions

Corporate firewalls may block communication.

Tool Discovery Failure

Possible causes:

Unsupported MCP version
Server configuration issues
Missing capabilities

Best Practices

Microsoft recommends:

Connect only trusted MCP servers.
Use Microsoft Entra ID when available.
Apply least privilege permissions.
Validate AI-generated SQL before execution.
Audit AI tool usage.
Separate development and production environments.
Monitor server logs.
Keep MCP server software updated.
Limit write operations unless required.
Review AI responses for correctness before acting on them.

SQL Server vs. Fabric Lakehouse MCP Connections

Feature	SQL Server MCP	Fabric Lakehouse MCP
Primary purpose	Relational databases	Lakehouse analytics
Objects	Tables, views, procedures	Delta tables, SQL endpoints
Typical queries	OLTP and reporting	Analytics and big data
Metadata	Database schemas	Lakehouse metadata
AI assistance	SQL generation, optimization	Analytics, exploration, SQL generation

DP-800 Exam Tips

For the exam, remember these key points:

MCP is a standardized protocol for connecting AI applications to external tools and data sources.
GitHub Copilot and Microsoft Copilot can use MCP servers to access live enterprise resources.
SQL Server MCP servers expose relational database metadata and tools.
Fabric Lakehouse MCP servers expose Lakehouse metadata, Delta tables, and analytics resources.
Authentication verifies identity; authorization determines permitted actions.
AI assistants should operate with least privilege.
Developers remain responsible for validating all AI-generated code and database operations.
Organizations should use secure authentication, auditing, and network protections when deploying MCP-enabled AI solutions.

Summary

The Model Context Protocol (MCP) provides a standardized framework for connecting AI assistants with enterprise resources such as Microsoft SQL Server and Microsoft Fabric Lakehouse. By using MCP, GitHub Copilot and Microsoft Copilot can retrieve live metadata, understand database schemas, generate more accurate SQL, explain existing database objects, and assist with analytics. Proper authentication, authorization, auditing, and adherence to least privilege principles ensure that these powerful capabilities are implemented securely. As AI-assisted database development becomes more prevalent, understanding MCP connectivity and governance is an important skill for DP-800 candidates.

Practice Exam Questions

Question 1

A development team wants GitHub Copilot to retrieve SQL Server table definitions before generating SQL queries. Which technology enables this standardized communication?

A. SQL Server Integration Services (SSIS)

B. Model Context Protocol (MCP)

C. Open Database Connectivity (ODBC)

D. SQL Server Agent

Answer: B

Explanation: MCP provides a standardized protocol that enables AI clients to communicate with external systems such as SQL Server.

Question 2

What is the primary role of an MCP server?

A. Execute operating system updates

B. Store AI model weights

C. Expose tools and resources that AI clients can access

D. Replace Microsoft Entra ID authentication

Answer: C

Explanation: An MCP server exposes resources such as database schemas, SQL execution tools, documentation, and APIs to compatible AI clients.

Question 3

Which authentication mechanism is most commonly recommended for connecting GitHub Copilot to enterprise MCP servers?

A. Anonymous authentication

B. Basic authentication with shared passwords

C. FTP credentials

D. Microsoft Entra ID

Answer: D

Explanation: Microsoft Entra ID provides secure, enterprise-grade authentication with support for modern identity management.

Question 4

An AI assistant successfully authenticates to an SQL Server MCP endpoint but cannot execute a query because of insufficient permissions. Which security concept is responsible?

A. Encryption

B. Compression

C. Authorization

D. Serialization

Answer: C

Explanation: Authentication confirms identity, while authorization determines what actions an authenticated user is permitted to perform.

Question 5

Which capability is most likely exposed by a Microsoft SQL Server MCP server?

A. Reading database schema metadata

B. Azure virtual machine creation

C. Configuring Microsoft Teams

D. Managing Windows updates

Answer: A

Explanation: SQL Server MCP servers commonly expose database metadata, tables, views, stored procedures, and SQL execution tools.

Question 6

Why would an organization use least privilege when configuring MCP server access?

A. To minimize security risks by limiting allowed operations

B. To increase database storage capacity

C. To improve AI response speed

D. To reduce SQL Server licensing costs

Answer: A

Explanation: Least privilege ensures AI assistants receive only the permissions necessary to perform approved tasks.

Question 7

Which Fabric resource is most commonly explored through a Fabric Lakehouse MCP server?

A. Windows Registry

B. Delta tables and Lakehouse metadata

C. DNS records

D. Azure Firewall rules

Answer: B

Explanation: Fabric Lakehouse MCP servers expose Lakehouse metadata, Delta tables, SQL endpoints, and related analytics resources.

Question 8

A developer asks Copilot, “List every customer table in my Lakehouse.” What is the AI assistant most likely doing?

A. Guessing based on its training data

B. Downloading the entire database

C. Using an MCP server to retrieve live metadata

D. Reading Windows Event Logs

Answer: C

Explanation: MCP allows AI assistants to query live metadata rather than relying solely on pretrained knowledge.

Question 9

What is one major advantage of connecting GitHub Copilot to multiple MCP servers?

A. It permanently stores database credentials.

B. It allows a single AI conversation to access multiple enterprise systems and tools.

C. It eliminates the need for authentication.

D. It replaces source control systems.

Answer: B

Explanation: Multiple MCP servers enable AI assistants to work across databases, repositories, documentation, APIs, and other enterprise resources within one workflow.

Question 10

Which statement best reflects Microsoft’s guidance regarding AI-assisted database operations through MCP?

A. AI-generated SQL should be executed automatically without review.

B. Production databases should always grant AI assistants full administrative permissions.

C. MCP eliminates the need for database security controls.

D. Developers should review AI-generated code and queries before executing them.

Answer: D

Explanation: Although MCP provides rich contextual information, developers remain responsible for validating AI-generated code, ensuring correctness, security, and compliance before deployment or execution.

Go to the DP-800 Exam Prep Hub main page

AI, AI Strategy, Data Development, DP-800, Microsoft Certification, SQL August 1, 2026

Create and configure GitHub Copilot instruction files (DP-800 Exam Prep)

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:

Use schema-qualified names
Use explicit column lists
Avoid SELECT *
Use transactions appropriately
Always parameterize SQL
Avoid SQL injection
Use TRY…CATCH
Return meaningful errors
Comment complex logic
Format SQL consistently

Database Naming Standards

Instruction files frequently define naming conventions.

Example

Tables

			
SalesOrders
Customers
Invoices

Procedures

			
usp_GetOrders
usp_InsertCustomer

Views

vwCustomerSales

Functions

fnCalculateTax

Documentation Standards

Instructions often require:

Every procedure includes:

Purpose
Parameters
Return values
Modification history

Example

			
Purpose:
Returns active customers.
Parameters:
@Country
Returns:
Customer list.

		

Security Guidance

Instruction files often include security rules.

Examples:

Do not:

SELECT *

Do not:

EXEC(@SQL)

Do:

sp_executesql

Do:

Parameterized queries

Require:

Least privilege
Input validation
Data masking awareness
Sensitive data handling

Performance Guidance

Example instructions:

Prefer:

Set-based operations
Appropriate indexes
EXISTS
Window functions

Avoid:

Nested cursors
RBAR processing
Unnecessary DISTINCT
Scalar UDFs inside large queries

AI Prompt Consistency

Instead of writing:

			
Generate a procedure.
Use TRY...CATCH.
No SELECT *
Include comments.
Use PascalCase.

		

Simply write:

Generate a procedure.

Copilot automatically follows repository guidance.

Version Control Benefits

Instruction files are version controlled.

Benefits include:

Change history
Code reviews
Branch support
Rollback capability
Team collaboration

Team Collaboration

Instruction files help ensure:

Developer A

↓

Developer B

↓

Developer C

↓

Copilot

↓

Consistent code

Everyone receives similar recommendations.

Best Practices

Microsoft recommends:

Keep instructions concise.
Focus on project-specific guidance.
Store instruction files with the repository.
Update instructions as standards evolve.
Use clear, natural language.
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.

Go to the DP-800 Exam Prep Hub main page

AI, AI Strategy, DP-800, Microsoft Certification August 1, 2026

Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session – Part 3 (DP-800 Exam Prep)

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.

Go to the DP-800 Exam Prep Hub main page

AI, AI Strategy, DP-800, Microsoft Certification, SQL August 1, 2026

Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session – Part 2 (DP-800 Exam Prep)

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.

Only expose:

required databases
required APIs
required resources
approved tools

Avoid granting broad administrative access to MCP servers unless absolutely necessary.

Data Governance

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.

Go to the DP-800 Exam Prep Hub main page

AI, DP-800, Microsoft Certification, Microsoft Fabric August 1, 2026August 1, 2026

Configure model and Model Context Protocol (MCP) tool options in a GitHub Copilot or Copilot in Fabric chat session – Part 1 (DP-800 Exam Prep)

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,
    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.

Go to the DP-800 Exam Prep Hub main page

AI, Data Development, Databases, DP-800, Microsoft Certification August 1, 2026

Enable GitHub Copilot and Microsoft Copilot in Fabric (DP-800 Exam Prep)

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.

Go to the DP-800 Exam Prep Hub main page

AI, AI Governance, AI Security, Databases, DP-800, Microsoft Certification, security, SQL August 1, 2026August 1, 2026

Interpret the security impact of using AI-assisted tools (DP-800 Exam Prep)

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
      --> Interpret security impact of using AI-assisted tools

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

Unlike many traditional SQL development topics, this objective focuses less on writing T-SQL and more on understanding how AI coding assistants affect security, privacy, compliance, and governance throughout the software development lifecycle.

For the DP-800 exam, you should understand:

Security risks associated with AI coding assistants
Responsible use of AI-generated code
Protection of confidential data
Compliance considerations
Secure prompt engineering
Human review requirements
Organizational governance for AI-assisted development
Microsoft AI tooling security capabilities

Why Security Matters When Using AI-Assisted Tools

Modern AI assistants such as:

Microsoft Copilot
GitHub Copilot
Azure AI Foundry
Microsoft Fabric Copilot
SQL Database Copilot experiences
Azure Data Studio AI extensions
Visual Studio AI-assisted development

can dramatically improve developer productivity.

However, they also introduce new risks.

AI systems often process:

prompts
source code
database schema
stored procedures
configuration files
API definitions
infrastructure code
documentation

If developers expose sensitive information to an AI system, that information could violate organizational security policies.

Therefore:

AI should improve developer productivity—not weaken database security.

Primary Security Risks

The exam expects candidates to recognize several categories of risk.

1. Exposure of Sensitive Information

Never include confidential information inside prompts.

Examples include:

passwords
connection strings
API keys
access tokens
customer data
Personally Identifiable Information (PII)
Protected Health Information (PHI)
financial records
encryption keys

Bad example:

“Optimize this stored procedure that accesses CustomerCreditCards.”

Better:

Replace confidential objects with generic examples.

			
CustomerTable
OrderTable
SalesTable

instead of production names.

2. Leakage of Intellectual Property

Many organizations consider:

SQL code
stored procedures
business rules
AI models
algorithms
database architecture

to be proprietary.

Developers should avoid submitting confidential business logic into public AI services unless organizational policy permits it.

3. AI Hallucinations

AI-generated code may:

invent SQL syntax
generate nonexistent functions
misuse permissions
recommend deprecated features
introduce vulnerabilities

Example:

AI may suggest:

GRANT CONTROL TO PUBLIC

This is almost never appropriate.

Always validate AI-generated SQL.

4. Insecure Code Generation

AI sometimes generates code that:

lacks input validation
uses dynamic SQL unsafely
ignores least privilege
omits error handling
exposes excessive permissions

Example:

Unsafe:

			
SET @sql =
'SELECT * FROM Orders WHERE CustomerID=' + @CustomerID
EXEC(@sql)

Preferred:

sp_executesql

with parameters.

5. Compliance Violations

Many industries have regulations governing data usage.

Examples:

GDPR
HIPAA
PCI DSS
ISO 27001
SOC 2

Uploading regulated information into unauthorized AI services may violate compliance requirements.

Human Review is Required

One of the most important DP-800 concepts:

AI assists developers—it does not replace secure code review.

Every AI-generated recommendation should be reviewed for:

correctness
performance
security
compliance
maintainability

Human approval remains essential.

Secure Prompt Engineering

Prompt engineering also has security implications.

Good prompts avoid exposing sensitive information.

Instead of:

“Here’s our production database schema.”

Use:

“Here’s a simplified example schema.”

Good prompts:

remove customer data
remove passwords
remove secrets
anonymize identifiers
generalize business logic

Protecting Secrets

Never place secrets into AI prompts.

Examples include:

Azure SQL passwords
Azure Storage keys
SAS tokens
API keys
OAuth tokens
certificates
encryption keys

Instead:

<ConnectionString>

<MyAPIKey>

as placeholders.

Protect Customer Data

Sensitive customer information includes:

names
addresses
SSNs
passport numbers
emails
phone numbers
medical records
payment information

Instead of:

John Smith

Use:

Customer A

Instead of:

4111-1111-1111-1111

Use:

<CardNumber>

AI and Least Privilege

Generated SQL should follow the Principle of Least Privilege.

Avoid:

GRANT CONTROL

Prefer:

GRANT SELECT

GRANT EXECUTE

only when necessary.

AI suggestions should always be reviewed for excessive permissions.

Verify AI-Generated Security Recommendations

AI may recommend:

indexes
permissions
encryption
authentication methods
firewall rules

Always verify recommendations against:

Microsoft documentation
organizational standards
security policies
current SQL Server capabilities

Secure Development Lifecycle (SDL)

AI should support—not bypass—the Secure Development Lifecycle.

Typical workflow:

Developer writes prompt
AI generates code
Developer reviews
Static code analysis
Security scanning
Peer review
Testing
Deployment

AI does not eliminate security reviews.

AI-Generated SQL Must Still Be Tested

Always test:

SQL injection protection
permissions
transactions
rollback behavior
concurrency
performance
indexing
execution plans

Never deploy AI-generated code without testing.

Microsoft Copilot Security

Microsoft enterprise AI offerings provide important security capabilities.

Examples include:

enterprise authentication
Microsoft Entra ID integration
tenant isolation
role-based access control
compliance features
auditing
encryption
responsible AI safeguards

Organizations should understand which AI services are approved for handling sensitive information.

Governance of AI Usage

Organizations should establish governance policies that define:

approved AI tools
acceptable prompts
prohibited data types
review requirements
logging
auditing
approval workflows
compliance responsibilities

Developers should follow organizational AI usage policies.

Common Security Best Practices

When using AI-assisted SQL development:

Never share passwords or secrets.
Remove customer information from prompts.
Anonymize production schemas when possible.
Validate every AI-generated query.
Review permissions carefully.
Use parameterized queries instead of string concatenation.
Test AI-generated code before deployment.
Perform peer reviews.
Follow organizational governance policies.
Verify AI recommendations against Microsoft documentation.

Exam Tips

Know the differences between:

Concept	Key Point
AI Assistance	Improves productivity but requires review
Human Review	Always required before deployment
Sensitive Data	Never include in prompts
Compliance	AI usage must satisfy organizational regulations
Secrets	Never expose passwords, keys, or tokens
Least Privilege	AI-generated permissions should be minimal
Governance	Organizations define approved AI usage
Responsible AI	AI outputs must be validated for security and correctness

DP-800 Exam Tips

Expect scenario-based questions such as:

Is this prompt safe?
Which information should be removed before using Copilot?
Which AI recommendation should be rejected?
Which code introduces SQL injection?
Which permission follows least privilege?
How should confidential schemas be shared?
What review is still required after AI generates code?
Which compliance issue exists?
Which AI-generated recommendation is safest?
Which governance practice should be followed?

The correct answer almost always favors:

protecting sensitive information,
minimizing permissions,
validating AI-generated code,
following organizational security policies, and
requiring human review before deployment.

Practice Exam Questions

Question 1

A developer wants to use an AI coding assistant to optimize a stored procedure. Which information should NOT be included in the prompt?

A. Sample table names

B. Production connection string containing credentials

C. Database version

D. Execution plan summary

Correct Answer: B

Explanation:
Connection strings containing usernames, passwords, or other credentials are sensitive secrets and should never be shared with AI tools. Replace them with placeholders before submitting prompts.

Question 2

Which security principle should always be applied when reviewing AI-generated SQL permissions?

A. Full administrative access

B. Principle of Least Privilege

C. Maximum compatibility

D. Public access

Correct Answer: B

Explanation:
AI-generated code should grant only the permissions necessary to perform the required task. Avoid overly broad permissions such as CONTROL or db_owner unless absolutely required.

Question 3

An AI assistant generates a stored procedure that concatenates user input into a SQL statement. What should the developer do?

A. Deploy it because AI generated it

B. Ignore it

C. Replace it with parameterized SQL

D. Disable indexing

Correct Answer: C

Explanation:
Dynamic SQL created through string concatenation is vulnerable to SQL injection. Use parameterized queries or sp_executesql to safely pass user input.

Question 4

A company must comply with GDPR. Which prompt represents the safest practice?

A. Replace customer information with anonymized sample data

B. Include production payment records

C. Upload an entire production database backup

D. Include customer names and addresses

Correct Answer: A

Explanation:
Personally identifiable information should be removed or anonymized before using AI-assisted development tools to reduce compliance and privacy risks.

Question 5

Why should developers review AI-generated SQL before deploying it?

A. AI-generated code is always optimized

B. AI may generate incorrect or insecure code

C. AI always follows organizational standards

D. AI automatically performs penetration testing

Correct Answer: B

Explanation:
AI-generated code can contain logical errors, security vulnerabilities, deprecated syntax, or poor performance choices. Human review remains essential.

Question 6

Which item is generally appropriate to include in an AI prompt?

A. Encryption keys

B. Customer Social Security numbers

C. Generic sample schema with fictional table names

D. Production API tokens

Correct Answer: C

Explanation:
Generic schemas without confidential business information allow AI to provide useful assistance while protecting sensitive organizational data.

Question 7

Which activity remains part of the Secure Development Lifecycle even when AI generates most of the SQL code?

A. Eliminating peer review

B. Skipping security testing

C. Removing code reviews

D. Performing security validation and testing

Correct Answer: D

Explanation:
AI accelerates development but does not replace testing, peer reviews, static analysis, or security validation.

Question 8

What is the primary purpose of organizational AI governance policies?

A. Increase CPU utilization

B. Define approved and secure use of AI tools

C. Eliminate documentation

D. Replace database administrators

Correct Answer: B

Explanation:
Governance policies establish which AI tools are approved, what data may be shared, required review processes, auditing requirements, and compliance expectations.

Question 9

An AI assistant recommends granting CONTROL permissions to simplify application development. What should the developer do first?

A. Apply the recommendation immediately

B. Replace CONTROL with db_owner

C. Review whether a lower permission satisfies the requirement

D. Disable authentication

Correct Answer: C

Explanation:
Broad permissions should be carefully reviewed. Following the Principle of Least Privilege helps reduce security risks by granting only the minimum required permissions.

Question 10

Which statement best describes responsible use of AI-assisted database development?

A. AI-generated code is production-ready without review.

B. AI eliminates the need for security testing.

C. AI guarantees compliance with regulations.

D. AI improves productivity, but developers remain responsible for validating security, correctness, and compliance.

Correct Answer: D

Explanation:
AI is a productivity tool, not an autonomous developer. Developers remain accountable for verifying code quality, security, regulatory compliance, and organizational standards before deployment.

Go to the DP-800 Exam Prep Hub main page

Data Development, DP-800, Microsoft Certification, SQL August 1, 2026

Implement error handling (DP-800 Exam Prep)

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%)
   --> Write advanced T-SQL code
      --> Implement error handling

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

Robust database applications must be able to detect, handle, and recover from errors gracefully. Whether a stored procedure is inserting data, updating records, processing transactions, or calling external services, unexpected conditions such as constraint violations, deadlocks, conversion failures, or missing objects can occur. Proper error handling prevents data corruption, improves application reliability, and provides meaningful feedback to developers and users.

SQL Server provides several built-in mechanisms for implementing error handling, including:

TRY...CATCH
THROW
RAISERROR (legacy)
Error information functions
Transaction control (BEGIN TRANSACTION, COMMIT, ROLLBACK)
XACT_STATE()
SET XACT_ABORT

For the DP-800: Developing AI-Enabled Database Solutions exam, you should understand how to implement structured error handling, manage transactions during errors, retrieve error details, and determine when to use THROW versus RAISERROR.

Why Error Handling Matters

Without proper error handling:

Transactions may remain partially completed.
Data consistency may be compromised.
Applications may receive unhelpful error messages.
Resources may remain locked.
Troubleshooting becomes difficult.

Good error handling:

Preserves data integrity.
Simplifies debugging.
Improves user experience.
Supports logging and auditing.
Enables reliable transaction management.

Common Types of SQL Errors

Examples include:

Divide-by-zero errors
Constraint violations
Duplicate key violations
Invalid object names
Data conversion failures
Deadlocks
Arithmetic overflow
Permission errors
Transaction failures
Lock timeouts

Example:

SELECT 100 / 0;

Produces:

Divide by zero error encountered.

TRY…CATCH

The primary error handling construct in SQL Server is the TRY...CATCH block.

General syntax:

			
BEGIN TRY
    -- T-SQL statements
END TRY
BEGIN CATCH
    -- Error handling
END CATCH;

		

If an error occurs inside the TRY block, execution immediately transfers to the CATCH block.

Simple TRY…CATCH Example

			
BEGIN TRY
    SELECT 100 / 0;
END TRY
BEGIN CATCH
    PRINT 'An error occurred.';
END CATCH;

		

Output:

An error occurred.

Handling Insert Errors

Example:

			
BEGIN TRY
    INSERT INTO Customers(CustomerID)
    VALUES (1);
END TRY
BEGIN CATCH
    PRINT 'Insert failed.';
END CATCH;

		

If a duplicate key exists, execution moves to the CATCH block.

Retrieving Error Information

Within a CATCH block, SQL Server provides several built-in functions.

Function	Description
ERROR_NUMBER()	Returns the error number
ERROR_MESSAGE()	Returns the error text
ERROR_SEVERITY()	Returns severity level
ERROR_STATE()	Returns error state
ERROR_LINE()	Returns line number
ERROR_PROCEDURE()	Returns stored procedure name

Example:

			
BEGIN TRY
    SELECT 100 / 0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine;
END CATCH;

		

ERROR_MESSAGE()

This function returns the descriptive text of the error.

Example:

SELECT ERROR_MESSAGE();

Possible output:

Divide by zero error encountered.

ERROR_NUMBER()

Returns SQL Server’s internal error number.

Example:

Error numbers help identify specific issues and are useful for logging and troubleshooting.

ERROR_LINE()

Returns the line where the error occurred.

Example:

This simplifies debugging of large stored procedures.

ERROR_PROCEDURE()

Returns the stored procedure that generated the error.

Example:

usp_ProcessOrder

Returns NULL if the error occurred outside a stored procedure.

THROW

THROW is the modern method for raising exceptions.

Syntax:

THROW;

Or:

			
THROW
50001,
'Customer not found.',
1;

Parameters:

Error number (50000 or greater for user-defined errors)
Error message
State

Re-Throwing an Error

Inside a CATCH block:

			
BEGIN TRY
    SELECT 100 / 0;
END TRY
BEGIN CATCH
    THROW;
END CATCH;

		

This preserves the original error information, including the error number, message, severity, state, and line number.

THROW vs RAISERROR

RAISERROR is the older method for generating custom errors. It remains supported for backward compatibility but Microsoft recommends using THROW for new development.

Example:

			
RAISERROR
(
'Invalid customer.',
16,
1
);

		

Equivalent modern syntax:

			
THROW
50001,
'Invalid customer.',
1;

Comparing THROW and RAISERROR

Feature	THROW	RAISERROR
Recommended for new development	Yes	No (legacy)
Preserves original error when rethrowing	Yes	No
Supports user-defined messages	Yes	Yes
Introduced in	SQL Server 2012	Earlier versions
Requires predefined message	No	Optional

Exam Tip: Unless maintaining legacy code, prefer THROW over RAISERROR.

Transactions and Error Handling

Errors often occur during transactions.

Example:

			
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;
COMMIT;

		

If the second update fails, the first update may already have succeeded, resulting in inconsistent data unless the transaction is rolled back.

TRY…CATCH with Transactions

			
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE Accounts
    SET Balance = Balance - 100
    WHERE AccountID = 1;
    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE AccountID = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH;

		

This ensures that either all changes succeed or none are applied.

XACT_STATE()

XACT_STATE() determines whether the current transaction is usable.

Possible values:

Value	Meaning
1	Active and committable
-1	Active but uncommittable
0	No active transaction

Example:

			
IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;

Why Use XACT_STATE()?

Some errors leave a transaction in an uncommittable state. Attempting to commit such a transaction will fail.

Example:

			
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;
END CATCH;

This approach is safer than issuing an unconditional ROLLBACK.

SET XACT_ABORT

SET XACT_ABORT ON automatically rolls back the current transaction when most run-time errors occur.

Example:

			
SET XACT_ABORT ON;
BEGIN TRANSACTION;
-- Statements
COMMIT;

Benefits:

Simplifies transaction management.
Helps avoid partially committed transactions.
Particularly useful in batch processing.

Logging Errors

A common practice is to log errors to an audit table.

Example:

			
BEGIN CATCH
INSERT INTO ErrorLog
(
    ErrorNumber,
    ErrorMessage,
    ErrorDate
)
VALUES
(
    ERROR_NUMBER(),
    ERROR_MESSAGE(),
    GETDATE()
);
END CATCH;

		

Benefits include:

Simplified troubleshooting.
Historical analysis.
Compliance and auditing.

Nested TRY…CATCH Blocks

Complex procedures may use nested error handling.

Example:

			
BEGIN TRY
    BEGIN TRY
        -- Inner logic
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END TRY
BEGIN CATCH
    -- Outer handling
END CATCH;

		

Nested blocks allow localized handling while still propagating errors to higher-level logic.

Errors That Cannot Be Caught

Not every SQL Server error is trapped by TRY...CATCH.

Examples include:

Compile-time syntax errors.
Certain object resolution errors that occur before execution.
Severe errors (severity 20 or higher) that terminate the connection.
Client-side interruptions.

Error Handling Best Practices

Use TRY...CATCH in stored procedures.
Prefer THROW over RAISERROR for new development.
Roll back failed transactions.
Check XACT_STATE() before committing or rolling back.
Log important errors.
Return meaningful messages to calling applications.
Keep transactions as short as possible.
Avoid swallowing errors without logging or rethrowing them.
Use SET XACT_ABORT ON when appropriate for transactional workloads.
Test error-handling paths, not just successful execution paths.

Common Exam Tips

For the DP-800 exam, remember the following:

TRY...CATCH is SQL Server’s primary structured error-handling mechanism.
THROW is the preferred method for raising or rethrowing exceptions.
RAISERROR is a legacy feature retained for backward compatibility.
ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), and ERROR_STATE() provide detailed error information within a CATCH block.
Always manage transactions carefully when errors occur.
Use XACT_STATE() to determine the status of the current transaction.
SET XACT_ABORT ON automatically rolls back most failed transactions.
Logging errors improves troubleshooting and operational support.

10 Practice Exam Questions

Question 1

Which T-SQL construct provides structured exception handling?

A. CASE...WHEN

B. TRY...CATCH

C. IF...ELSE

D. WHILE

Answer: B

Explanation: TRY...CATCH is the primary mechanism for structured error handling in SQL Server. Statements in the TRY block execute normally, and any run-time error transfers control to the CATCH block.

Question 2

Which function returns the text description of the error that occurred?

A. ERROR_NUMBER()

B. ERROR_MESSAGE()

C. ERROR_STATE()

D. ERROR_LINE()

Answer: B

Explanation: ERROR_MESSAGE() returns the complete descriptive text associated with the error, making it useful for logging and displaying meaningful messages.

Question 3

Which statement is recommended for raising new user-defined errors in modern SQL Server development?

A. THROW

B. PRINT

C. RETURN

D. GOTO

Answer: A

Explanation: Microsoft recommends using THROW instead of RAISERROR for new development because it provides cleaner syntax and better preserves original error information.

Question 4

What is the purpose of XACT_STATE()?

A. It determines whether indexes are fragmented.

B. It checks whether a transaction is active and whether it can still be committed.

C. It displays the current isolation level.

D. It returns the current database compatibility level.

Answer: B

Explanation: XACT_STATE() returns 1, 0, or -1 to indicate whether a transaction is committable, absent, or uncommittable, respectively.

Question 5

Which value returned by XACT_STATE() indicates an uncommittable transaction?

A. 0

B. 1

C. 100

D. -1

Answer: D

Explanation: A value of -1 indicates that the transaction is active but cannot be committed and must be rolled back.

Question 6

Which function returns the line number where an error occurred?

A. ERROR_PROCEDURE()

B. ERROR_STATE()

C. ERROR_LINE()

D. ERROR_SEVERITY()

Answer: C

Explanation: ERROR_LINE() identifies the line number where the run-time error occurred, making it easier to locate and correct issues.

Question 7

What is the primary benefit of using SET XACT_ABORT ON?

A. It automatically creates savepoints.

B. It automatically commits every transaction.

C. It disables constraint checking.

D. It automatically rolls back most transactions when a run-time error occurs.

Answer: D

Explanation: SET XACT_ABORT ON helps ensure transactional consistency by automatically rolling back the current transaction when most run-time errors occur.

Question 8

Which error information function returns the name of the stored procedure that generated the error?

A. ERROR_PROCEDURE()

B. ERROR_LINE()

C. ERROR_MESSAGE()

D. ERROR_NUMBER()

Answer: A

Explanation: ERROR_PROCEDURE() returns the name of the stored procedure where the error originated, or NULL if the error occurred outside a stored procedure.

Question 9

Which statement about THROW and RAISERROR is correct?

A. RAISERROR is required for all user-defined errors.

B. THROW cannot be used inside a CATCH block.

C. THROW is the recommended approach for new SQL Server applications.

D. THROW does not support custom error messages.

Answer: C

Explanation: THROW is the preferred method for generating and rethrowing exceptions in modern SQL Server development, while RAISERROR is maintained primarily for backward compatibility.

Question 10

Why should transactions typically be rolled back when an error occurs during a multi-step operation?

A. To improve index performance.

B. To reduce memory usage.

C. To prevent SQL Server from generating error messages.

D. To maintain data consistency by ensuring that either all operations succeed or none are applied.

Answer: D

Explanation: Rolling back a failed transaction preserves database consistency by preventing partial updates that could leave related data in an invalid or inconsistent state.

Go to the DP-800 Exam Prep Hub main page