Category: Data Development

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

Identify and resolve query performance issues, including blocking and deadlocks – Part 3 (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%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks

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.

Best Practices for Preventing Performance Problems

The DP-800 exam emphasizes preventing problems rather than simply reacting to them.

Good database design, indexing, and application coding practices significantly reduce blocking, deadlocks, and poor query performance.

Design Tables Properly

Avoid:

excessively wide rows
unnecessary nullable columns
poor normalization
over-normalization requiring many joins

Good schema design leads to:

smaller pages
fewer logical reads
shorter lock durations

Use Appropriate Data Types

Poor choices increase memory usage.

Instead of:

NVARCHAR(MAX)

use

NVARCHAR(50)

when appropriate.

Benefits include:

reduced I/O
better index efficiency
improved cache utilization

Keep Transactions Short

One of the biggest causes of blocking is long-running transactions.

Bad:

			
BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
WAITFOR DELAY '00:05:00';
COMMIT;

		

Locks remain active for five minutes.

Better:

			
BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
COMMIT;

Commit Frequently

Instead of updating millions of rows in one transaction:

			
UPDATE LargeTable
SET Status = 'Complete';

process smaller batches.

Example:

			
WHILE 1=1
BEGIN
UPDATE TOP (1000) LargeTable
SET Status='Complete'
WHERE Status='Pending';
IF @@ROWCOUNT=0
BREAK;
END

		

Benefits:

shorter locks
reduced log growth
less blocking

Create Effective Indexes

Missing indexes often lead to:

table scans
excessive logical reads
blocking
CPU spikes

Create indexes on:

frequently filtered columns
join columns
ORDER BY columns

Example:

			
CREATE INDEX IX_OrderDate
ON Sales(OrderDate);

Avoid Too Many Indexes

Indexes improve reads.

Indexes slow:

INSERT
UPDATE
DELETE

Every modification updates every affected index.

Balance read performance against write performance.

Maintain Indexes

Over time indexes fragment.

Use:

			
ALTER INDEX ALL
ON Sales
REBUILD;

			
ALTER INDEX ALL
ON Sales
REORGANIZE;

Generally:

REORGANIZE for moderate fragmentation
REBUILD for heavy fragmentation

Write Efficient Queries

Avoid:

SELECT *

Use:

			
SELECT CustomerID,
       CustomerName

Benefits:

less network traffic
narrower execution plans
smaller memory grants

Filter Early

Instead of processing entire tables:

			
SELECT *
FROM Sales;

Use:

			
SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01';

Avoid Functions on Indexed Columns

Bad:

WHERE YEAR(OrderDate)=2025

This prevents index seeks.

Better:

			
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Use EXISTS Instead of IN When Appropriate

Example:

			
WHERE EXISTS
(
SELECT *
FROM Orders
WHERE Orders.CustomerID=Customers.CustomerID
)

		

Often performs better on large datasets.

Parameter Sniffing

Parameter sniffing occurs when SQL Server optimizes a stored procedure using the first parameter values it receives.

Example:

EXEC GetOrders 1;

The plan is cached.

Later:

EXEC GetOrders 100000;

The same plan may perform poorly.

Possible solutions:

OPTION (RECOMPILE)
OPTIMIZE FOR
local variables
Query Store plan forcing

Monitor Wait Statistics

Wait statistics reveal what SQL Server spends time waiting on.

Common waits include:

Wait Type	Meaning
PAGEIOLATCH	Waiting for disk I/O
CXPACKET	Parallelism
LCK_M_X	Exclusive lock
LCK_M_S	Shared lock
WRITELOG	Log write bottleneck
SOS_SCHEDULER_YIELD	CPU pressure

Query:

			
SELECT *
FROM sys.dm_os_wait_stats;

Wait statistics help identify the true bottleneck before making changes.

Monitor Resource Usage

Useful DMVs include:

CPU:

sys.dm_exec_query_stats

Memory:

sys.dm_os_memory_clerks

Locks:

sys.dm_tran_locks

Sessions:

sys.dm_exec_sessions

Requests:

sys.dm_exec_requests

Query Store Best Practices

Enable Query Store on production databases.

Benefits:

captures historical plans
tracks regressions
compares runtime statistics
forces known good plans

Avoid disabling Query Store unless troubleshooting specific issues.

Azure SQL Automatic Performance Features

Azure SQL Database provides automatic tuning.

Features include:

Automatic index creation
Automatic index removal
Automatic plan correction
Automatic plan regression detection

These features reduce administrative effort.

Common DP-800 Exam Tips

Know the differences between:

Topic	Key Point
Blocking	Waiting for locks
Deadlock	Circular blocking; one transaction is terminated
Query Store	Historical performance monitoring
DMVs	Real-time diagnostic information
Execution Plans	Explain how SQL executes queries
Missing Index DMVs	Recommend useful indexes
Automatic Tuning	Azure SQL self-optimization
Snapshot Isolation	Reduces reader/writer blocking
Extended Events	Modern tracing tool
Parameter Sniffing	Cached plans may not fit all parameters

Summary

To excel in the DP-800 exam, you should be able to:

Interpret execution plans and identify expensive operators.
Use Query Store to identify regressions and force stable plans.
Query DMVs to diagnose slow-running queries, blocking, waits, and resource consumption.
Recognize and resolve blocking by shortening transactions, adding indexes, or using appropriate isolation levels.
Detect deadlocks with Extended Events, deadlock graphs, and system health sessions.
Understand common wait types and how they relate to CPU, I/O, memory, and locking issues.
Apply indexing, statistics maintenance, and efficient query-writing techniques to prevent performance problems.
Explain how Azure SQL automatic tuning can improve query performance and reduce administrative overhead.
Identify parameter sniffing scenarios and select appropriate mitigation strategies.

Practice Exam Questions

Question 1

A stored procedure performs well for some parameter values but poorly for others because SQL Server reuses a cached execution plan. Which performance issue is occurring?

A. Lock escalation

B. Parameter sniffing

C. Deadlocking

D. Page compression

Answer: B

Explanation:
Parameter sniffing occurs when SQL Server generates and caches an execution plan based on the first parameter values used. Subsequent executions with significantly different parameter values may reuse an inefficient plan, resulting in poor performance.

Question 2

A database administrator wants to reduce blocking caused by long-running UPDATE statements that affect millions of rows. Which approach is most effective?

A. Increase the database compatibility level

B. Disable Query Store

C. Process updates in smaller batches and commit frequently

D. Force all queries to use parallel execution

Answer: C

Explanation:
Breaking large modifications into smaller batches shortens transaction duration, releases locks more quickly, reduces transaction log growth, and minimizes blocking for other sessions.

Question 3

Which query is more likely to prevent SQL Server from performing an index seek on an indexed OrderDate column?

WHERE OrderDate >= '2025-01-01'

WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31'

WHERE YEAR(OrderDate) = 2025

WHERE OrderDate < '2026-01-01'

Answer: C

Explanation:
Applying a function such as YEAR() to an indexed column makes the predicate non-SARGable, often preventing SQL Server from using an index seek and forcing an index or table scan instead.

Question 4

Which DMV provides information about current lock resources held by transactions?

A. sys.dm_exec_query_stats

B. sys.dm_os_wait_stats

C. sys.dm_exec_sessions

D. sys.dm_tran_locks

Answer: D

Explanation:
sys.dm_tran_locks displays active lock information, including lock types, resources, and owning sessions, making it valuable when investigating blocking.

Question 5

Why should developers avoid using SELECT * in production queries whenever possible?

A. It always causes deadlocks.

B. It automatically disables indexes.

C. It retrieves unnecessary columns, increasing I/O and network traffic.

D. It prevents Query Store from capturing execution statistics.

Answer: C

Explanation:
Selecting only the required columns reduces disk reads, network traffic, memory usage, and execution costs while allowing SQL Server to generate more efficient execution plans.

Question 6

A SQL Server database contains heavily fragmented indexes after months of frequent updates. Which maintenance task should typically be performed when fragmentation is high?

A. Update statistics only

B. Rebuild the indexes

C. Shrink the database

D. Clear the plan cache

Answer: B

Explanation:
An index rebuild recreates the index structure, removes fragmentation, and updates index statistics. It is generally recommended when fragmentation is significant.

Question 7

A developer notices frequent LCK_M_X waits in SQL Server. What do these waits indicate?

A. CPU saturation

B. Memory allocation failures

C. Sessions waiting for exclusive locks

D. Network latency

Answer: C

Explanation:
LCK_M_X wait types indicate sessions waiting to acquire exclusive locks that are currently held by other transactions, suggesting blocking.

Question 8

Which Azure SQL feature can automatically detect a query plan regression and restore a previously better-performing execution plan?

A. Intelligent Insights

B. Automatic Plan Correction

C. Azure Monitor Alerts

D. Elastic Jobs

Answer: B

Explanation:
Automatic Plan Correction, part of Azure SQL automatic tuning, identifies query regressions and can force a previously efficient execution plan automatically.

Question 9

Which practice best helps prevent blocking in high-concurrency OLTP systems?

A. Keep transactions as short as possible.

B. Disable indexes during business hours.

C. Increase page size.

D. Use SELECT * in all reporting queries.

Answer: A

Explanation:
Short transactions reduce the amount of time locks are held, allowing other sessions to access data sooner and minimizing blocking.

Question 10

A DBA wants to determine whether SQL Server is primarily waiting on disk I/O, locking, or CPU scheduling before making performance changes. Which diagnostic information should be examined first?

A. Database file sizes

B. Transaction log backup history

C. Wait statistics

D. Server collation settings

Answer: C

Explanation:
Wait statistics provide a high-level overview of where SQL Server spends its time waiting, making them one of the best starting points for diagnosing performance bottlenecks before making tuning decisions.

Go to the DP-800 Exam Prep Hub main page

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

Identify and resolve query performance issues, including blocking and deadlocks – Part 1 (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%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks

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

Efficient query performance is one of the most important responsibilities of a SQL developer. Regardless of whether a database is hosted in SQL Server, Azure SQL Database, Azure SQL Managed Instance, or Microsoft Fabric SQL Database, applications depend on queries executing quickly while maintaining data consistency and supporting concurrent users.

Poor-performing queries can cause excessive CPU usage, memory pressure, storage bottlenecks, long response times, and application outages. Likewise, poorly managed concurrency can result in blocking and deadlocks that significantly impact user productivity.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how SQL Server manages concurrent transactions, recognize common performance issues, detect blocking and deadlocks, and apply best practices to resolve these problems.

Learning Objectives

After completing this article, you should be able to:

Explain why query performance optimization is important.
Identify common causes of poor query performance.
Understand SQL Server locking behavior.
Explain blocking and deadlocks.
Recognize how transaction isolation levels affect concurrency.
Detect blocking sessions.
Detect deadlocks.
Apply techniques to reduce blocking and deadlocks.
Troubleshoot real-world concurrency problems.

Why Query Performance Matters

Every SQL query consumes system resources. Poorly optimized queries consume more resources than necessary and may affect every user connected to the database.

Common consequences include:

Slow application response times
High CPU utilization
Excessive memory consumption
Increased disk I/O
Long-running transactions
Lock contention
Blocking
Deadlocks
Reduced scalability

Database performance is not solely about executing a single query quickly—it is about enabling thousands of users to work simultaneously without interfering with each other.

Common Causes of Poor Query Performance

Many performance problems originate from inefficient query design.

Common causes include:

Missing Indexes

Without appropriate indexes, SQL Server performs table scans rather than index seeks.

Instead of reading a few rows:

CustomerID = 1205

SQL Server may need to scan millions of rows.

Symptoms include:

High logical reads
High physical reads
Increased CPU usage
Long execution times

Poor Index Design

Too many indexes can slow writes.

Too few indexes slow reads.

Poor index design includes:

Incorrect clustered indexes
Missing covering indexes
Duplicate indexes
Unused indexes
Highly fragmented indexes

Returning More Data Than Necessary

Instead of:

			
SELECT *
FROM Sales.Orders;

Use:

			
SELECT OrderID,
       CustomerID,
       OrderDate
FROM Sales.Orders;

Benefits include:

Reduced network traffic
Less memory usage
Faster execution
Smaller execution plans

Non-SARGable Queries

SARGable means Search Argument Able.

Bad example:

WHERE YEAR(OrderDate) = 2025

Because SQL Server must calculate YEAR() for every row.

Better:

			
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Now an index on OrderDate can be used.

Implicit Data Type Conversions

Example:

WHERE CustomerID = '100'

if CustomerID is an integer.

SQL Server may convert every value before comparison.

Better:

WHERE CustomerID = 100

Outdated Statistics

Statistics help the optimizer estimate row counts.

Outdated statistics lead to:

Poor cardinality estimates
Incorrect join choices
Bad execution plans
Longer execution times

Parameter Sniffing

Stored procedures reuse cached execution plans.

A plan optimized for:

CustomerID = 1

may perform poorly for:

CustomerID = 999999

DP-800 candidates should understand that parameter sniffing can sometimes degrade performance and that techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, or query hints may be used selectively to address it.

Understanding Locking

SQL Server uses locks to ensure:

Data consistency
Transaction isolation
Integrity during concurrent access

Locks prevent conflicting operations from occurring simultaneously.

Example:

User A updates:

OrderID = 100

Before User A commits,

User B attempts to update the same row.

SQL Server places User B into a waiting state until User A completes.

This waiting is called blocking.

Types of Locks

Several lock types are important for the DP-800 exam.

Shared (S)

Used for reading.

Multiple users may hold Shared locks simultaneously.

Example:

SELECT

Exclusive (X)

Used for modifications.

Example:

			
UPDATE
DELETE
INSERT

Only one Exclusive lock can exist on a resource.

Update (U)

Used during updates.

Prevents certain deadlock scenarios.

Typically upgraded to an Exclusive lock when data is modified.

Intent Locks

Used internally.

Examples include:

These indicate SQL Server intends to place locks at lower levels.

Schema Locks

Protect database object definitions.

Examples:

ALTER TABLE

CREATE INDEX

Lock Granularity

SQL Server can lock at multiple levels.

Row
Key
Page
Extent
Table
Database

Smaller locks improve concurrency.

Larger locks reduce overhead but may increase blocking.

Lock Escalation

SQL Server may automatically replace many row locks with a table lock.

Example:

Instead of:

20,000 row locks

SQL Server escalates to:

One table lock

Benefits:

Lower memory usage

Drawback:

More blocking

Understanding Blocking

Blocking occurs when one session waits for another session to release a lock.

Example

Session 1:

			
BEGIN TRANSACTION;
UPDATE Products
SET Price = Price * 1.05
WHERE ProductID = 5;

Transaction remains open.

Session 2:

			
SELECT *
FROM Products
WHERE ProductID = 5;

Session 2 waits.

This is normal behavior.

Blocking protects data consistency.

When Blocking Becomes a Problem

Short blocking is expected.

Long blocking causes:

Slow applications
Timeouts
User frustration
Connection pooling issues
Increased resource usage

Common causes include:

Long-running transactions
User interaction inside transactions
Large batch updates
Missing indexes
Table scans
Poor query design

Understanding Deadlocks

A deadlock occurs when two or more sessions permanently wait for each other.

Example

Session A

Locks:

Customers

Needs:

Orders

Session B

Locks:

Orders

Needs:

Customers

Neither session can continue.

SQL Server automatically detects the deadlock.

One transaction becomes the deadlock victim.

Its transaction is rolled back.

The other transaction continues.

Deadlock Example

Transaction A

			
BEGIN TRANSACTION;
UPDATE Customers
SET CreditLimit = 1000
WHERE CustomerID = 1;
UPDATE Orders
SET Status = 'Approved'
WHERE OrderID = 100;
COMMIT;

		

Transaction B

			
BEGIN TRANSACTION;
UPDATE Orders
SET Status = 'Pending'
WHERE OrderID = 100;
UPDATE Customers
SET CreditLimit = 900
WHERE CustomerID = 1;
COMMIT;

		

If both transactions execute simultaneously:

Transaction A locks Customers
Transaction B locks Orders
Each waits for the other’s lock

SQL Server detects the cycle and terminates one transaction.

Blocking vs. Deadlocks

Blocking	Deadlock
Temporary waiting	Circular waiting
Usually resolves automatically	Requires SQL Server intervention
No transaction rollback	One transaction rolled back
Normal behavior	Undesirable behavior
Caused by incompatible locks	Caused by cyclic lock dependencies

Transaction Isolation Levels

Isolation levels determine how transactions interact.

They directly affect:

Blocking
Concurrency
Consistency
Performance

READ UNCOMMITTED

Lowest isolation.

Allows dirty reads.

Almost no blocking.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages

Very fast

Disadvantages

Reads uncommitted data

READ COMMITTED (Default)

Most common.

Prevents dirty reads.

Allows non-repeatable reads.

Balanced performance and consistency.

REPEATABLE READ

Protects rows already read.

Increases locking.

More blocking.

SERIALIZABLE

Highest isolation.

Maximum consistency.

Most locking.

Greatest blocking potential.

SNAPSHOT Isolation

Uses row versioning.

Readers do not block writers.

Writers do not block readers.

Advantages:

High concurrency
Fewer blocking issues
Better scalability

Requires enabling snapshot isolation in the database.

Choosing the Appropriate Isolation Level

Isolation Level	Dirty Reads	Blocking	Concurrency
READ UNCOMMITTED	Yes	Very Low	Very High
READ COMMITTED	No	Moderate	Good
REPEATABLE READ	No	Higher	Moderate
SERIALIZABLE	No	Highest	Lowest
SNAPSHOT	No	Low	Excellent

Detecting Blocking

Several tools can identify blocking.

Common methods include:

SQL Server Management Studio Activity Monitor
Dynamic Management Views (DMVs)
Extended Events
SQL Server Profiler (legacy)
Azure SQL monitoring tools
Microsoft Fabric monitoring experiences

One useful DMV query is:

			
SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

		

This displays:

Waiting session
Blocking session
Wait type
Wait duration
Locked resource

Detecting Deadlocks

SQL Server automatically detects deadlocks.

Detection methods include:

Extended Events
System Health session
SQL Server Profiler (legacy)
Azure SQL Intelligent Insights
Deadlock graphs
SQL Server error logs (when configured)

Deadlock graphs visually display:

Victim process
Lock owners
Waiting processes
Resources involved

These graphs are invaluable for identifying the exact sequence of events that caused the deadlock.

Best Practices to Prevent Blocking and Deadlocks

Microsoft recommends several strategies to minimize concurrency issues:

Keep transactions as short as possible.
Commit or roll back transactions promptly.
Access tables in a consistent order across all applications.
Create appropriate indexes to reduce scan times.
Avoid user interaction while a transaction is open.
Use the lowest appropriate isolation level for the workload.
Consider Snapshot Isolation or Read Committed Snapshot Isolation (RCSI) for read-heavy environments.
Break large updates into smaller batches.
Regularly maintain indexes and statistics.
Monitor blocking trends and deadlock frequency proactively.

Real-World Troubleshooting Scenarios

Scenario 1: Long-Running Transaction

A reporting application begins a transaction and leaves it open while waiting for user input. Meanwhile, hundreds of users attempting to update the same data experience delays.

Resolution: Redesign the application so that user interaction occurs before the transaction begins or after it commits, minimizing the transaction’s duration.

Scenario 2: Deadlocks During Order Processing

Two stored procedures update the Customers and Orders tables but access them in different sequences.

Resolution: Standardize the order in which tables are accessed (for example, always update Customers before Orders) to eliminate the circular dependency that causes deadlocks.

Scenario 3: Blocking Caused by Table Scans

A frequently executed query scans millions of rows because no suitable index exists. The scan holds locks long enough to block other sessions.

Resolution: Create an appropriate nonclustered index and rewrite the query to be SARGable so that SQL Server can perform index seeks instead of table scans.

DP-800 Exam Tips

Understand the difference between blocking and deadlocks.
Know how transaction isolation levels affect concurrency and locking behavior.
Recognize that blocking is a normal mechanism to preserve consistency, whereas deadlocks are abnormal conditions that SQL Server resolves by selecting a victim transaction.
Be familiar with common lock types, including Shared, Exclusive, Update, Intent, and Schema locks.
Know that Snapshot Isolation and Read Committed Snapshot Isolation (RCSI) use row versioning to reduce reader-writer blocking.
Understand that long-running transactions, missing indexes, inconsistent object access order, and poor query design are common causes of blocking and deadlocks.
Be comfortable using DMVs and monitoring tools to identify blocking sessions before moving on to advanced analysis with execution plans and Query Store (covered in Part 2).

Go to the DP-800 Exam Prep Hub main page

Data Development, Databases, DP-800, SQL August 1, 2026

Preserve data integrity and consistency by using transaction isolation levels and concurrency controls (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%)
   --> Optimize database performance
      --> Preserve data integrity and consistency by using transaction isolation levels and concurrency controls

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

A SQL AI Developer must understand how SQL Server and Azure SQL Database maintain data consistency while allowing many users and applications to access the database simultaneously. Proper use of transactions, isolation levels, row versioning, locking, and concurrency controls is critical for building scalable, high-performance, and reliable database applications.

The DP-800 exam expects candidates to understand:

Transaction ACID properties
SQL Server transaction isolation levels
Locking behavior
Row versioning
Optimistic vs. pessimistic concurrency
Deadlocks and blocking
Snapshot isolation
Read Committed Snapshot Isolation (RCSI)
Best practices for balancing performance with consistency

Why Transaction Isolation Matters

Modern applications rarely have only one user connected to a database.

Examples include:

Thousands of customers placing online orders
Banking applications processing transfers
Hospital systems updating patient records
AI applications reading operational data while transactions occur

Without concurrency controls, users could:

Read incomplete data
Overwrite each other’s changes
Produce incorrect calculations
Corrupt business data

SQL Server solves these problems through:

Transactions
Locking
Isolation levels
Versioning

Understanding Transactions

A transaction is a sequence of one or more SQL statements treated as a single unit of work.

Example:

			
BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 100;
UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 200;
COMMIT;

		

If either statement fails:

ROLLBACK;

ensures neither account is changed.

ACID Properties

Every SQL transaction follows the ACID principles.

Atomicity

Everything succeeds or everything rolls back.

Example:

Money should never disappear because only one UPDATE executed.

Consistency

Database rules remain valid before and after the transaction.

Examples include:

Foreign keys
Check constraints
Unique keys
Business rules

Isolation

Concurrent transactions should not interfere improperly with one another.

Isolation levels determine exactly how much interaction is allowed.

Durability

Once committed:

data survives crashes
power failures
server restarts

SQL Server accomplishes this through the transaction log.

What Is Transaction Isolation?

Isolation controls how much one transaction can “see” changes made by another transaction.

Higher isolation:

Better consistency
More locking
Less concurrency

Lower isolation:

Higher concurrency
Better performance
Greater risk of inconsistent reads

Choosing the correct isolation level is an important design decision.

SQL Server Isolation Levels

SQL Server supports five primary isolation levels.

Isolation Level	Dirty Reads	Nonrepeatable Reads	Phantom Reads
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Snapshot	No	No	No
Serializable	No	No	No

Read Uncommitted

Lowest isolation level.

Allows reading data that has not yet been committed.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages:

Minimal locking
Highest concurrency

Disadvantages:

Dirty reads
Incorrect results
Inconsistent reporting

Equivalent to:

			
SELECT *
FROM Orders WITH (NOLOCK);

The DP-800 exam often tests that NOLOCK allows dirty reads and should not be used when data accuracy is required.

Dirty Reads

A dirty read occurs when Transaction B reads data modified by Transaction A before Transaction A commits.

Example:

Transaction A:

			
UPDATE Products
SET Price = 200;

Before commit:

Transaction B reads:

Price = 200

Transaction A rolls back.

Actual value:

Price = 100

Transaction B used data that never officially existed.

Read Committed (Default)

Default SQL Server isolation level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Characteristics:

Prevents dirty reads
Allows nonrepeatable reads
Allows phantom rows

Most OLTP applications use this level.

Nonrepeatable Reads

Occurs when:

A transaction reads the same row twice.

Another transaction updates the row between reads.

Example:

First query:

Salary = 80,000

Another transaction updates:

Salary = 90,000

Second query:

Salary = 90,000

The same row produced different values.

Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Prevents:

Dirty reads
Nonrepeatable reads

Still allows:

Phantom rows

Rows read remain locked until the transaction completes.

Phantom Reads

A phantom read occurs when:

The same query returns additional rows.

Example:

First query:

			
SELECT *
FROM Orders
WHERE Status='Pending';

Returns:

20 rows

Another transaction inserts a pending order.

Running the same query again returns:

21 rows

The extra row is called a phantom row.

Serializable

Highest isolation level.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Prevents:

Dirty reads
Nonrepeatable reads
Phantom reads

SQL Server places range locks.

Advantages:

Maximum consistency

Disadvantages:

Significant blocking
Lower throughput
Reduced scalability

Often used for:

Financial systems
Inventory management
Reservation systems

Snapshot Isolation

Snapshot Isolation uses row versioning instead of shared locks for reads.

Enable:

			
ALTER DATABASE SalesDB
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Benefits:

Readers never block writers
Writers never block readers
Consistent transaction snapshot

Ideal for:

Reporting
Analytics
AI workloads

Read Committed Snapshot Isolation (RCSI)

RCSI changes the default Read Committed behavior to use row versioning.

Enable:

			
ALTER DATABASE SalesDB
SET READ_COMMITTED_SNAPSHOT ON;

Benefits:

Greatly reduces blocking
Maintains Read Committed semantics
No application code changes required

Azure SQL Database enables RCSI by default for many workloads because it improves concurrency.

Locking

SQL Server uses locks to maintain consistency.

Common lock types include:

Lock	Purpose
Shared (S)	Reading data
Exclusive (X)	Updating data
Update (U)	Preparing to modify data
Intent (IS, IX)	Indicates lower-level locks
Schema (Sch-S, Sch-M)	Protect schema changes

Lock Granularity

Locks may occur at different levels:

Row
Page
Table
Partition
Database

SQL Server automatically chooses appropriate granularity.

Large operations may trigger lock escalation, converting many row locks into a table lock to reduce memory overhead.

Blocking

Blocking occurs when:

One transaction waits for another transaction to release its locks.

Example:

Transaction A:

			
UPDATE Products
SET Price = 50;

Transaction B:

			
SELECT *
FROM Products;

Transaction B waits until Transaction A commits.

Blocking is normal and protects consistency, but excessive blocking can reduce throughput.

Deadlocks

A deadlock occurs when:

Transaction A waits for Transaction B.

Transaction B waits for Transaction A.

Neither transaction can continue.

SQL Server automatically selects one transaction as the deadlock victim and rolls it back.

Example:

Transaction A:

Locks Table A

Needs Table B

Transaction B:

Locks Table B

Needs Table A

Result:

Deadlock.

Minimizing Deadlocks

Best practices include:

Keep transactions short.
Access tables in a consistent order.
Create proper indexes.
Avoid unnecessary user interaction inside transactions.
Commit as soon as possible.
Reduce lock duration.

Optimistic Concurrency

Optimistic concurrency assumes conflicts are uncommon.

Instead of locking rows, applications detect changes before updating.

Common implementation:

rowversion

or timestamp columns.

Example:

			
UPDATE Products
SET Price = 100
WHERE ProductID = 1
AND RowVersion = @OriginalVersion;

If zero rows are updated:

Another user modified the row first.

Pessimistic Concurrency

Assumes conflicts are likely.

Locks data immediately.

Advantages:

Prevents conflicts

Disadvantages:

More blocking
Reduced concurrency

Used in:

Banking
Airline reservations
Inventory systems

Row Versioning

Snapshot Isolation and RCSI maintain previous row versions inside tempdb (or the persisted version store in databases that support Accelerated Database Recovery).

Readers access previous committed versions without blocking writers.

Benefits include:

Improved concurrency
Reduced blocking
Better reporting performance

Transaction Best Practices

Keep Transactions Short

Avoid:

User prompts
Long loops
Waiting for external APIs

Commit Promptly

Release locks quickly.

Use Appropriate Isolation Levels

Do not always choose Serializable.

Choose the lowest level that still satisfies business requirements.

Index Frequently Queried Columns

Better indexes reduce:

Scan duration
Lock duration
Blocking

Retry Deadlock Victims

Applications should retry transactions after deadlock errors because SQL Server automatically rolls back the victim transaction.

Avoid NOLOCK for Critical Data

Dirty reads can lead to:

Incorrect reports
AI model training errors
Financial inaccuracies

Isolation Level Selection Guide

Scenario	Recommended Isolation
Financial transfers	Serializable
General OLTP	Read Committed
Reporting	Snapshot
Azure SQL workloads	RCSI
Large analytical queries	Snapshot
High-contention inventory systems	Serializable or carefully designed Repeatable Read
Temporary diagnostic queries	Read Uncommitted (use cautiously)

DP-800 Exam Tips

Remember these frequently tested points:

Read Committed is SQL Server’s default isolation level.
Dirty reads occur only under Read Uncommitted (or NOLOCK).
Snapshot Isolation uses row versioning instead of shared locks.
RCSI reduces reader/writer blocking while preserving Read Committed semantics.
Serializable provides the highest consistency but can significantly reduce concurrency.
Deadlocks occur when two or more transactions wait on each other, and SQL Server automatically selects a deadlock victim.
Optimistic concurrency commonly uses a rowversion column to detect conflicts rather than locking data.

Practice Exam Questions

Question 1

A banking application must guarantee that account balances remain accurate even when multiple users transfer funds simultaneously. Which transaction isolation level provides the highest level of protection against concurrency anomalies?

A. Read Committed
B. Snapshot
C. Serializable
D. Read Uncommitted

Correct Answer: C

Explanation: Serializable prevents dirty reads, nonrepeatable reads, and phantom reads by using range locks. It offers the highest level of transaction isolation and is well suited for critical financial operations.

Question 2

A developer executes the following statement:

SELECT * FROM Sales WITH (NOLOCK);

What behavior should the developer expect?

A. The query will prevent all concurrent updates.
B. The query may read uncommitted data.
C. The query automatically enables Snapshot Isolation.
D. The query uses Repeatable Read isolation.

Correct Answer: B

Explanation: The NOLOCK hint is equivalent to Read Uncommitted isolation and allows dirty reads, meaning rows may be read before transactions commit.

Question 3

A reporting application experiences blocking because long-running SELECT queries interfere with update operations. Which feature is most appropriate?

A. Repeatable Read
B. Serializable
C. Snapshot Isolation
D. Exclusive locking

Correct Answer: C

Explanation: Snapshot Isolation uses row versioning so readers do not block writers and writers do not block readers, making it ideal for reporting workloads.

Question 4

Which concurrency problem occurs when a transaction reads the same row twice and receives different values because another transaction updated the row?

A. Nonrepeatable read
B. Phantom read
C. Lock escalation
D. Dirty read

Correct Answer: A

Explanation: A nonrepeatable read occurs when the same row returns different values within the same transaction due to another committed update.

Question 5

What is the primary purpose of a rowversion column in optimistic concurrency control?

A. Encrypt row data
B. Compress large tables
C. Detect whether a row has changed since it was read
D. Prevent index fragmentation

Correct Answer: C

Explanation: Applications compare the original rowversion value during updates. If it has changed, another transaction modified the row, allowing the application to detect concurrency conflicts.

Question 6

Which SQL Server feature reduces reader and writer blocking while maintaining Read Committed behavior?

A. Read Committed Snapshot Isolation (RCSI)
B. Table hints
C. Lock escalation
D. Read Uncommitted

Correct Answer: A

Explanation: RCSI uses row versioning for Read Committed transactions, significantly reducing blocking without requiring application code changes.

Question 7

Two transactions each hold a lock that the other requires, causing both to wait indefinitely. What is this situation called?

A. Blocking
B. Lock escalation
C. Phantom read
D. Deadlock

Correct Answer: D

Explanation: A deadlock occurs when transactions wait on each other’s resources. SQL Server automatically selects one transaction as the deadlock victim and rolls it back.

Question 8

Which ACID property ensures that either all statements in a transaction succeed or none of them are applied?

A. Consistency
B. Isolation
C. Atomicity
D. Durability

Correct Answer: C

Explanation: Atomicity guarantees that a transaction is treated as a single unit of work. If any part fails, the entire transaction is rolled back.

Question 9

A database administrator wants to reduce the likelihood of deadlocks. Which practice is recommended?

A. Keep transactions open for longer periods.
B. Access tables in a consistent order across transactions.
C. Use Serializable isolation for every workload.
D. Disable indexes on frequently accessed tables.

Correct Answer: B

Explanation: Accessing resources in a consistent order reduces circular dependencies between transactions, decreasing the likelihood of deadlocks.

Question 10

Which statement best describes Snapshot Isolation?

A. It allows dirty reads to improve performance.
B. It relies exclusively on shared locks for readers.
C. It prevents writers from modifying data during reads.
D. It provides each transaction with a consistent version of committed data by using row versioning.

Correct Answer: D

Explanation: Snapshot Isolation stores previous committed versions of rows, allowing transactions to view a consistent snapshot of the database without blocking concurrent updates.

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

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

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

Write correlated queries (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
      --> Write correlated queries

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

Correlated queries are among the most important advanced T-SQL concepts tested on the DP-800: Developing AI-Enabled Database Solutions certification exam. They allow a query to compare data from one row with data from another table or from the same table by referencing values from the outer query. Correlated queries are commonly used for row-by-row comparisons, filtering, existence checks, aggregate comparisons, and complex business logic.

Unlike standard subqueries, correlated queries are dependent on the outer query and are evaluated repeatedly—once for each row processed by the outer query. Although they can be more computationally expensive than non-correlated queries, they provide elegant solutions to many complex querying problems.

For the DP-800 exam, you should understand how correlated queries work, when to use them, how to optimize them, and how they compare to joins and window functions.

What Is a Correlated Query?

A correlated query (also called a correlated subquery) is a subquery that references one or more columns from the outer query.

Because of this dependency, the subquery cannot execute independently.

General syntax:

			
SELECT columns
FROM TableA A
WHERE expression
(
    SELECT ...
    FROM TableB B
    WHERE B.Column = A.Column
);

		

The subquery references A.Column, which belongs to the outer query.

How Correlated Queries Work

Execution occurs in this order:

SQL Server reads one row from the outer query.
The correlated subquery executes using values from that row.
SQL Server evaluates the result.
The process repeats for every row returned by the outer query.

Unlike regular subqueries, correlated queries are evaluated multiple times.

Correlated Query Example

Suppose two tables exist:

Customers

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders

OrderID	CustomerID	TotalAmount
101	1	500
102	1	800
103	2	250

Retrieve customers who have placed at least one order.

			
SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

		

The subquery references C.CustomerID, making it a correlated query.

Result:

CustomerName
Alice
Bob

Charlie is excluded because no matching order exists.

Comparing Correlated and Non-Correlated Queries

Non-Correlated Query

Runs once.

			
SELECT *
FROM Products
WHERE CategoryID IN
(
    SELECT CategoryID
    FROM Categories
);

		

The subquery is independent.

Correlated Query

Runs once for every outer row.

			
SELECT *
FROM Products P
WHERE EXISTS
(
    SELECT *
    FROM Inventory I
    WHERE I.ProductID=P.ProductID
);

		

The subquery depends on P.ProductID.

EXISTS with Correlated Queries

EXISTS is one of the most common operators used with correlated queries.

It returns TRUE when the subquery finds at least one row.

Example:

			
SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
);

		

Benefits:

Stops after finding the first matching row.
Often performs better than IN for large datasets.
Excellent for existence checks.

NOT EXISTS

Returns rows where no matching records exist.

Example:

			
SELECT CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
);

		

Result:

Customers without orders.

Correlated Aggregate Query

Correlated queries frequently use aggregate functions.

Example:

Return employees earning above their department average.

			
SELECT EmployeeName,
       Salary
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID=E.DepartmentID
);

		

For every employee, SQL Server calculates the average salary within that employee’s department.

Correlated MAX Example

Find employees with the highest salary in each department.

			
SELECT EmployeeName,
       Salary
FROM Employees E
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
    WHERE DepartmentID=E.DepartmentID
);

		

Correlated MIN Example

Find products with the lowest price within each category.

			
SELECT ProductName,
       Price
FROM Products P
WHERE Price =
(
    SELECT MIN(Price)
    FROM Products
    WHERE CategoryID=P.CategoryID
);

		

Correlated COUNT Example

Return customers who placed more than three orders.

			
SELECT CustomerName
FROM Customers C
WHERE
(
    SELECT COUNT(*)
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
) > 3;

		

Correlated SUM Example

Find salespeople whose total sales exceed $100,000.

			
SELECT SalesPersonName
FROM SalesPeople S
WHERE
(
    SELECT SUM(TotalAmount)
    FROM Orders O
    WHERE O.SalesPersonID=S.SalesPersonID
) > 100000;

		

Correlated UPDATE

Correlated queries are not limited to SELECT statements.

Example:

			
UPDATE Products
SET AveragePrice =
(
    SELECT AVG(UnitPrice)
    FROM Sales
    WHERE Sales.ProductID=Products.ProductID
);

		

Each product receives its own calculated average.

Correlated DELETE

Example:

Delete customers with no orders.

			
DELETE
FROM Customers
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders
    WHERE Orders.CustomerID=Customers.CustomerID
);

		

Correlated INSERT

Correlated logic can also appear during INSERT operations.

Example:

			
INSERT INTO VIPCustomers
SELECT *
FROM Customers C
WHERE
(
    SELECT SUM(TotalAmount)
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
) > 50000;

		

Using EXISTS vs IN

Both operators may return similar results.

EXISTS

Stops after first match.
Efficient on large datasets.
Ideal for correlated queries.

Example:

			
WHERE EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
)

		

IN

Works well for smaller lookup lists.

Example:

			
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
)

		

Correlated Queries vs Joins

Many correlated queries can be rewritten as joins.

Correlated query:

			
SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
);

		

Equivalent join:

			
SELECT DISTINCT
    C.CustomerName
FROM Customers C
INNER JOIN Orders O
ON C.CustomerID=O.CustomerID;

		

Both produce similar results, but performance depends on indexes, data volume, and execution plans.

Correlated Queries vs Window Functions

Sometimes a window function is a better solution.

Correlated query:

			
SELECT EmployeeName
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID=E.DepartmentID
);

		

Window function:

			
SELECT EmployeeName,
       Salary
FROM
(
    SELECT *,
           AVG(Salary)
           OVER(PARTITION BY DepartmentID) AS AvgSalary
    FROM Employees
) E
WHERE Salary > AvgSalary;

		

Window functions often perform better because the aggregate is calculated once per partition instead of once per row.

Performance Considerations

Correlated queries can become expensive because the inner query executes repeatedly.

Performance depends on:

Number of rows
Index availability
Query complexity
Join selectivity
Execution plan

SQL Server’s optimizer may transform some correlated queries into more efficient execution plans automatically.

Optimizing Correlated Queries

Best practices include:

Create indexes on correlated columns.
Use EXISTS instead of COUNT(*) > 0 when checking for existence.
Avoid unnecessary correlated calculations.
Review execution plans for repeated scans.
Replace correlated aggregates with window functions when appropriate.
Rewrite some queries as joins if performance improves.
Filter outer rows before executing the correlated subquery.
Avoid scalar user-defined functions inside correlated subqueries.

Common Business Scenarios

Correlated queries are commonly used for:

Customers with orders
Employees earning above department averages
Highest-priced products in each category
Duplicate detection
Missing related records
Parent-child relationships
Inventory validation
Sales performance analysis
Financial reporting
Data quality checks

Common Exam Tips

For the DP-800 exam, remember the following:

A correlated query references columns from the outer query.
The correlated subquery executes once for each outer row.
EXISTS and NOT EXISTS are common correlated query operators.
Correlated queries are frequently used with aggregate functions such as AVG, SUM, COUNT, MIN, and MAX.
Correlated queries can appear in SELECT, UPDATE, DELETE, and INSERT statements.
Some correlated queries can be rewritten as joins or window functions for better performance.
Proper indexing significantly improves correlated query performance.

10 Practice Exam Questions

Question 1

What distinguishes a correlated subquery from a regular subquery?

A. It always returns multiple rows.

B. It references one or more columns from the outer query.

C. It can only be used with the EXISTS operator.

D. It cannot contain aggregate functions.

Answer: B

Explanation: A correlated subquery depends on values from the outer query by referencing its columns, causing it to execute in the context of each outer row.

Question 2

Which operator is most commonly used to determine whether related rows exist in a correlated query?

A. LIKE

B. BETWEEN

C. EXISTS

D. UNION

Answer: C

Explanation: EXISTS evaluates to TRUE when the correlated subquery returns at least one row and is optimized for existence checks.

Question 3

How many times is a correlated subquery typically evaluated?

A. Once for the entire query.

B. Once per database.

C. Once per table.

D. Once for each row processed by the outer query.

Answer: D

Explanation: Because the subquery references values from the current outer row, it is evaluated repeatedly as each outer row is processed.

Question 4

Which correlated query returns customers who have never placed an order?

			
SELECT *
FROM Customers C
WHERE EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
);

		

			
SELECT *
FROM Customers
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
);

		

			
SELECT *
FROM Customers C
WHERE NOT EXISTS
(
    SELECT *
    FROM Orders O
    WHERE O.CustomerID=C.CustomerID
);

		

			
SELECT *
FROM Customers
ORDER BY CustomerID;

Answer: C

Explanation: NOT EXISTS returns rows from the outer query for which the correlated subquery finds no matching records.

Question 5

Which aggregate function is commonly used in a correlated query to find employees earning more than the average salary in their department?

A. MAX()

B. MIN()

C. COUNT()

D. AVG()

Answer: D

Explanation: AVG() calculates the departmental average salary, allowing comparison against each employee’s salary.

Question 6

Which statement about correlated queries is true?

A. They cannot be used in UPDATE statements.

B. They cannot contain aggregate functions.

C. They can be used in SELECT, UPDATE, DELETE, and INSERT statements.

D. They always perform better than joins.

Answer: C

Explanation: Correlated subqueries are supported in multiple DML statements and are often used to calculate or validate row-specific values.

Question 7

When checking whether matching rows exist, why is EXISTS often preferred over COUNT(*) > 0?

A. EXISTS automatically creates indexes.

B. EXISTS stops searching after finding the first matching row.

C. EXISTS sorts the results automatically.

D. EXISTS returns all matching rows.

Answer: B

Explanation: EXISTS can stop processing as soon as a qualifying row is found, reducing unnecessary work.

Question 8

Which feature can often replace correlated aggregate queries while improving performance?

A. Temporary tables

B. Triggers

C. Foreign keys

D. Window functions

Answer: D

Explanation: Window functions calculate aggregates across partitions in a single pass, often making them more efficient than repeatedly executing correlated aggregate subqueries.

Question 9

Which factor most directly improves the performance of correlated queries?

A. Increasing the database compatibility level

B. Creating indexes on the correlated columns

C. Using larger transaction log files

D. Increasing the database recovery model

Answer: B

Explanation: Indexes on the columns used to correlate the outer and inner queries allow SQL Server to locate matching rows much more efficiently.

Question 10

Which business scenario is a good use case for a correlated query?

A. Displaying all rows from a single table without filtering

B. Sorting products alphabetically

C. Finding the highest-paid employee within each department

D. Creating a new database

Answer: C

Explanation: Correlated queries are well suited for row-by-row comparisons against aggregates or related data, such as identifying the highest-paid employee in each department.

Go to the DP-800 Exam Prep Hub main page

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

Write graph queries that use the MATCH operator (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
      --> Write graph queries that use the MATCH operator

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

Many business problems involve relationships between entities rather than simple rows and columns. Examples include social networks, organizational hierarchies, fraud detection, recommendation engines, transportation networks, supply chains, and knowledge graphs. While relational databases excel at storing structured data, querying complex relationships often requires multiple self-joins that become increasingly difficult to write and maintain.

To address these scenarios, SQL Server and Azure SQL Database support graph databases through node tables, edge tables, and the MATCH operator. These capabilities allow developers to model and query relationships using graph patterns while continuing to leverage the relational database engine.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand how to create graph objects and write graph queries using the MATCH operator.

What Is a Graph Database?

A graph database represents information as:

Nodes – entities or objects
Edges – relationships between entities

Instead of focusing solely on tables and foreign keys, graph databases emphasize how data is connected.

Example:

			
Alice ---- WorksWith ---- Bob
  |
LivesIn
  |
Orlando

		

In this example:

Alice, Bob, and Orlando are nodes
WorksWith and LivesIn are edges

Graph Database Components

SQL Server graph databases consist of two primary object types:

Object	Purpose
Node Table	Stores entities
Edge Table	Stores relationships

Node Tables

Node tables represent entities.

Examples include:

Employees
Customers
Products
Cities
Departments
Suppliers

Example:

			
CREATE TABLE Person
(
    PersonID INT PRIMARY KEY,
    FullName NVARCHAR(100)
)
AS NODE;

		

The AS NODE clause creates a graph node table.

Edge Tables

Edge tables represent relationships between nodes.

Example:

			
CREATE TABLE WorksWith
(
    SinceDate DATE
)
AS EDGE;

		

This table stores the relationship between two Person nodes.

SQL Server automatically maintains hidden graph metadata for node and edge tables.

Node and Edge Relationships

Suppose the following data exists:

			
John ---- WorksWith ---- Mary
Mary ---- WorksWith ---- Susan
John ---- Manages ---- David

Each person exists once in the node table.

Relationships exist separately in edge tables.

Why Use Graph Queries?

Traditional relational queries require joins.

Example:

			
Employee
↓
Manager
↓
Department

		

This often becomes:

			
Employee
JOIN Manager
JOIN Department
JOIN Office
JOIN Region

		

Graph queries simplify relationship traversal.

The MATCH Operator

The MATCH operator is the primary mechanism for querying graph relationships.

Instead of writing multiple joins, developers specify graph patterns.

General syntax:

			
SELECT ...
FROM ...
WHERE MATCH(pattern);

The pattern describes how nodes are connected.

Basic MATCH Query

Suppose the database contains:

Persons

John
Mary
Susan

Relationship

John → WorksWith → Mary

Query:

			
SELECT
    p1.FullName,
    p2.FullName
FROM Person p1,
     WorksWith w,
     Person p2
WHERE MATCH
(
    p1-(w)->p2
);

		

Result:

FullName	FullName
John	Mary

Understanding Graph Pattern Syntax

Example:

p1-(w)->p2

Meaning:

Start with node p1
Traverse edge w
Reach node p2

Arrow direction matters.

Reverse Direction

Example:

p1<-(w)-p2

Meaning:

p2 → p1

The relationship is traversed in the opposite direction.

Multiple Relationships

Suppose:

			
John → Mary
Mary → Susan

Query:

			
WHERE MATCH
(
    John-(WorksWith)->Mary-(WorksWith)->Susan
);

The MATCH operator follows multiple hops.

Multi-Hop Queries

Graph databases excel at traversing multiple relationships.

Example:

Find employees connected through two working relationships.

			
Employee
↓
WorksWith
↓
Employee
↓
WorksWith
↓
Employee

		

Without graphs this may require several joins.

With MATCH the relationship path is much easier to express.

Multiple Edge Types

Suppose the graph contains:

			
John
↓
WorksWith
↓
Mary
↓
LivesIn
↓
Seattle

		

Query:

John-(WorksWith)->Mary-(LivesIn)->Seattle

The MATCH operator supports multiple relationship types within a single query.

Using MATCH with SELECT

Example:

			
SELECT
    p.FullName,
    c.CityName
FROM Person p,
     LivesIn l,
     City c
WHERE MATCH
(
    p-(l)->c
);

		

Result

Person	City
John	Seattle
Mary	Orlando

Combining MATCH with WHERE

Additional filtering can be applied.

Example:

			
SELECT
    p.FullName
FROM Person p,
     WorksWith w,
     Person p2
WHERE MATCH
(
    p-(w)->p2
)
AND p2.Department='Sales';

		

Graph traversal occurs first.

The remaining rows are filtered normally.

MATCH and JOINs

Graph queries can still use relational joins.

Example:

			
SELECT
    p.FullName,
    d.DepartmentName
FROM Person p,
     WorksWith w,
     Person p2
JOIN Department d
ON p2.DepartmentID=d.DepartmentID
WHERE MATCH
(
    p-(w)->p2
);

		

Graph features integrate with standard SQL.

Graph Queries for AI Applications

Graph databases are becoming increasingly valuable for AI applications because they naturally represent relationships between people, documents, products, concepts, and events.

Examples include:

Knowledge graphs
Recommendation systems
Fraud detection
Supply chain analysis
Social networks
Customer relationship analysis
Semantic search
Retrieval-Augmented Generation (RAG)
Entity linking
Relationship discovery

Large Language Models (LLMs) often benefit from graph data because relationships provide richer context than isolated rows.

Knowledge Graph Example

Suppose an AI application stores:

			
Customer
↓
Purchased
↓
Product
↓
ManufacturedBy
↓
Company

		

The MATCH operator can quickly discover:

Which products customers purchased
Which companies manufacture them
Similar purchasing relationships
Connected entities

Fraud Detection

Graph databases are excellent for identifying suspicious relationships.

Example:

			
Customer
↓
Owns
↓
Account
↓
TransfersMoneyTo
↓
Account
↓
OwnedBy
↓
Customer

		

MATCH queries can identify complex money-transfer networks that would require many joins in a traditional relational model.

Recommendation Engines

Streaming services often recommend content based on relationships.

Example:

			
User
↓
Likes
↓
Movie
↓
DirectedBy
↓
Director

		

Graph queries efficiently discover similar users and related content.

Relationship Discovery

Graph databases make it easy to answer questions such as:

Who works with whom?
Which customers purchased similar products?
Which suppliers serve the same regions?
Which employees report to the same manager?
Which products share common components?

These scenarios are ideal for MATCH queries.

Performance Considerations

Graph queries can outperform complex self-joins when relationship traversal is the primary objective.

Best practices include:

Keep node and edge tables appropriately indexed.
Filter data before traversing large graphs when possible.
Avoid unnecessary relationship hops.
Use graph queries only when relationships are central to the problem.
Continue using relational tables for highly tabular data.

Best Practices

Model entities as node tables.
Model relationships as edge tables.
Use descriptive edge names.
Keep graph models simple.
Combine MATCH with relational filtering when appropriate.
Choose graph queries only when relationship traversal is required.
Avoid replacing relational designs unnecessarily.
Document graph relationships clearly.
Test graph queries with realistic datasets.
Consider graph databases for AI-powered relationship analysis.

Common Exam Tips

For the DP-800 exam, remember the following:

Graph databases store entities as nodes and relationships as edges.
Node tables are created using AS NODE.
Edge tables are created using AS EDGE.
The MATCH operator traverses graph relationships.
Arrow direction (-> and <-) determines relationship direction.
MATCH can traverse multiple relationships in a single query.
Graph queries integrate with standard SQL statements.
Graph databases are well suited for knowledge graphs, recommendation engines, fraud detection, supply chains, and AI-enabled applications that rely on relationship analysis.

Practice Exam Questions

Question 1

Which SQL Server object stores relationships between entities in a graph database?

A. View

B. Node table

C. Edge table

D. Stored procedure

Answer: C

Explanation: Edge tables store the relationships between nodes and are created using the AS EDGE clause.

Question 2

Which clause is used when creating a graph node table?

AS GRAPH

AS NODE

AS ENTITY

AS OBJECT

Answer: B

Explanation: A graph node table is created by appending the AS NODE clause to a CREATE TABLE statement.

Question 3

What is the primary purpose of the MATCH operator?

A. Perform full-text searches

B. Compare two strings

C. Traverse graph relationships between nodes

D. Create graph indexes

Answer: C

Explanation: MATCH specifies graph traversal patterns, allowing SQL Server to navigate relationships represented by edge tables.

Question 4

In the graph pattern:

p1-(w)->p2

what does the arrow (->) indicate?

A. The relationship flows from p1 through edge w to p2.

B. The relationship flows from p2 to p1.

C. The query performs an inner join.

D. The graph contains duplicate nodes.

Answer: A

Explanation: The arrow indicates the direction of traversal from the starting node (p1) through the edge (w) to the destination node (p2).

Question 5

Which scenario is best suited for SQL Server graph queries?

A. Calculating monthly payroll totals

B. Traversing employee reporting relationships across multiple organizational levels

C. Sorting sales by date

D. Updating a single customer record

Answer: B

Explanation: Graph queries excel at traversing complex relationships, such as organizational hierarchies and reporting structures.

Question 6

Which statement about graph queries in SQL Server is true?

A. They cannot be combined with traditional SQL queries.

B. They require a separate graph database engine.

C. They can be combined with relational filtering and joins.

D. They replace foreign keys.

Answer: C

Explanation: SQL Server graph queries integrate with standard T-SQL and can be combined with joins, filters, and other relational features.

Question 7

Which of the following is represented by a node table?

A. A relationship between two customers

B. A connection between two products

C. A customer entity

D. A graph traversal path

Answer: C

Explanation: Node tables represent entities such as customers, employees, products, or cities, while edge tables represent the relationships between them.

Question 8

Why are graph databases valuable for Retrieval-Augmented Generation (RAG) and other AI solutions?

A. They automatically train language models.

B. They store only vector embeddings.

C. They eliminate the need for SQL queries.

D. They model and query rich relationships that provide additional context for AI systems.

Answer: D

Explanation: Graph databases capture connections among entities, allowing AI applications to retrieve contextual information that improves reasoning and search results.

Question 9

What is the advantage of using the MATCH operator instead of multiple self-joins?

A. It encrypts graph data automatically.

B. It simplifies expressing relationship traversal patterns.

C. It automatically creates indexes.

D. It eliminates the need for edge tables.

Answer: B

Explanation: MATCH provides a concise, intuitive syntax for traversing relationships that would otherwise require numerous joins.

Question 10

A database models employees, departments, and managers as graph nodes connected by edge tables. Which query feature should be used to find employees connected to a specific manager through defined relationships?

A. LIKE

B. GROUP BY

C. MERGE

D. MATCH

Answer: D

Explanation: The MATCH operator is specifically designed for traversing relationships in SQL Server graph databases and is the appropriate choice for this type of query.

Go to the DP-800 Exam Prep Hub main page

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

Write queries that include fuzzy string matching functions, such as EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, and JARO_WINKLER_DISTANCE (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
      --> Write queries that include fuzzy string matching functions, such as EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, and JARO_WINKLER_DISTANCE

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

Traditional string comparisons in SQL use operators such as = and LIKE, which require an exact or pattern-based match. However, real-world data is often inconsistent. Misspellings, abbreviations, typographical errors, and formatting differences frequently occur in customer names, product descriptions, addresses, emails, and other text fields.

To address these challenges, SQL Server 2025 (17.x) Preview and Azure SQL Database introduce native fuzzy string matching functions. These functions measure how similar two strings are rather than requiring them to match exactly.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, understanding fuzzy matching is valuable because AI-enabled applications frequently work with imperfect or human-generated text. Fuzzy matching can improve search accuracy, data quality, duplicate detection, and entity matching.

The primary fuzzy matching functions include:

EDIT_DISTANCE()
EDIT_DISTANCE_SIMILARITY()
JARO_WINKLER_DISTANCE()

These functions allow developers to compare strings and determine how closely they resemble one another.

Exam Note: These fuzzy matching functions are new capabilities introduced in SQL Server 2025 (17.x) Preview and Azure SQL Database. They represent Microsoft’s modern approach to intelligent text processing and may appear in newer versions of the DP-800 exam.

What is Fuzzy String Matching?

Fuzzy string matching compares two strings and determines how similar they are, even if they are not identical.

For example:

String 1	String 2	Similar?
Microsoft	Microsoft	Yes
Jon Smith	John Smith	Yes
Contoso	Contoso Ltd	Yes
Database	Databaze	Yes
Azure	Amazon	No

Unlike an equality comparison (=), fuzzy matching recognizes that many differences are minor typographical variations.

Why Fuzzy Matching Matters

Organizations often receive data from multiple sources:

Customer registration forms
Web applications
Mobile apps
AI chatbots
OCR (Optical Character Recognition)
Voice transcription
External APIs
CSV imports

These data sources often contain spelling mistakes or inconsistent formatting.

Examples include:

Original	Variation
Jonathan	Jonathon
Katherine	Catherine
Microsoft	Microsft
Orlando	Orlando
SQL Server	SQLServer

Traditional SQL comparisons fail to recognize these values as similar, whereas fuzzy matching functions can identify likely matches.

Understanding Edit Distance

The Edit Distance (also known as the Levenshtein distance) measures the minimum number of operations required to transform one string into another.

The allowed operations are:

Insert a character
Delete a character
Replace a character

Example:

			
CAT
CUT

Only one substitution is required:

A → U

Edit distance = 1

Another example:

			
Microsoft
Microsft

Only one missing letter (“o”).

Edit distance = 1

The lower the edit distance, the more similar the strings.

EDIT_DISTANCE()

Purpose

Returns the minimum number of character edits required to convert one string into another.

Syntax

EDIT_DISTANCE(string1, string2)

Example

			
SELECT EDIT_DISTANCE(
    'Microsoft',
    'Microsft'
);

Output

Example

			
SELECT EDIT_DISTANCE(
    'Database',
    'Databaze'
);

Output

Example

			
SELECT EDIT_DISTANCE(
    'Azure',
    'Amazon'
);

Output

A larger number indicates the strings are less similar.

Common Uses of EDIT_DISTANCE()

Duplicate customer detection
Name matching
Address matching
Product matching
AI-generated text validation
OCR correction
Search suggestions
Data cleansing

EDIT_DISTANCE_SIMILARITY()

Purpose

Returns a similarity score rather than the number of edits.

Instead of measuring differences, this function measures similarity.

Syntax

			
EDIT_DISTANCE_SIMILARITY(
    string1,
    string2
)

The function returns a percentage-like similarity score.

Higher values indicate greater similarity.

Example

			
SELECT EDIT_DISTANCE_SIMILARITY(
    'Jonathan',
    'Jonathon'
);

Possible output

Example

			
SELECT EDIT_DISTANCE_SIMILARITY(
    'SQL Server',
    'SQL Server'
);

Output

Example

			
SELECT EDIT_DISTANCE_SIMILARITY(
    'Azure',
    'Amazon'
);

Possible output

Interpreting Similarity Scores

Similarity	Meaning
100	Exact match
90–99	Nearly identical
75–89	Likely match
50–74	Possibly related
Below 50	Usually unrelated

Developers commonly define thresholds depending on business requirements.

For example:

Similarity >= 90

might be considered an automatic match.

JARO_WINKLER_DISTANCE()

Purpose

Measures similarity using the Jaro-Winkler algorithm, which gives additional weight to matching prefixes.

This algorithm performs particularly well for:

Person names
Company names
City names
Street names

Because many spelling variations occur toward the end of words, Jaro-Winkler favors strings that begin similarly.

Example

			
John
Jon

Very high similarity.

Example

			
Jonathan
Jonathon

High similarity.

Example

			
Smith
Smyth

High similarity.

Syntax

			
JARO_WINKLER_DISTANCE(
    string1,
    string2
)

Example

			
SELECT JARO_WINKLER_DISTANCE(
    'Jonathan',
    'Jonathon'
);

Possible output

0.08

Lower values indicate the strings are more alike (with 0 representing an exact match).

Edit Distance vs. Jaro-Winkler

Feature	EDIT_DISTANCE	JARO_WINKLER_DISTANCE
Measures	Character edits	Overall similarity
Best for	General text	Names
Handles typos	Excellent	Excellent
Considers prefixes	No	Yes
Duplicate detection	Yes	Yes
Name matching	Good	Excellent

Real-World Business Scenarios

Customer Deduplication

			
John Smith
Jon Smith

Likely the same customer.

Product Matching

			
Surface Laptop
Surface Laptp

Typographical error.

Address Matching

			
Main Street
Main St.

Likely identical location.

OCR Cleanup

OCR software may read:

Micr0soft

instead of

Microsoft

Fuzzy matching helps identify the intended value.

AI Output Validation

Large language models occasionally generate slight variations:

SQL Sever

instead of

SQL Server

Fuzzy matching can detect likely errors before data is stored.

AI-Enabled Database Scenarios

These functions are especially useful in AI-powered database solutions.

Examples include:

Matching chatbot responses to known products
Detecting duplicate support tickets
Matching customer names across systems
Validating OCR-generated text
Comparing AI-generated summaries
Detecting near-duplicate documents
Matching vector-search metadata
Intelligent search suggestions
Auto-correcting user input
Identity resolution

Performance Considerations

Fuzzy matching functions perform more computation than exact string comparisons.

Best practices include:

Filter data before applying fuzzy matching.
Use indexes to reduce the number of candidate rows.
Avoid comparing every row to every other row.
Use similarity thresholds to eliminate weak matches.
Test performance on production-sized datasets.
Consider precomputing or caching similarity scores for frequently compared values.
Use fuzzy matching only when exact matching is insufficient.

Best Practices

Normalize text before comparison (trim spaces, consistent casing, remove unnecessary punctuation).
Use exact matching whenever possible for better performance.
Choose appropriate similarity thresholds for your business requirements.
Use EDIT_DISTANCE() when you need the number of edits.
Use EDIT_DISTANCE_SIMILARITY() when you need an intuitive similarity score.
Use JARO_WINKLER_DISTANCE() for names and identity matching.
Validate results before automatically merging records.
Benchmark fuzzy matching against realistic datasets.

Common Exam Tips

Remember these key points for the DP-800 exam:

Fuzzy matching compares similarity rather than exact equality.
EDIT_DISTANCE() returns the number of edits needed to transform one string into another.
Smaller edit distances indicate greater similarity.
EDIT_DISTANCE_SIMILARITY() returns a normalized similarity score, where higher values represent more similar strings.
JARO_WINKLER_DISTANCE() emphasizes matching prefixes and is particularly effective for comparing names.
Fuzzy matching is useful for data quality, duplicate detection, AI-generated content validation, OCR cleanup, and intelligent search.
Because fuzzy matching is computationally intensive, use it selectively and after narrowing the candidate set when possible.

Practice Exam Questions

Question 1

A company imports customer records from multiple systems. Which function is best suited to determine the minimum number of character changes required to transform one customer name into another?

A. EDIT_DISTANCE()

B. EDIT_DISTANCE_SIMILARITY()

C. JARO_WINKLER_DISTANCE()

D. LIKE

Answer: A

Explanation: EDIT_DISTANCE() calculates the minimum number of insertions, deletions, and substitutions needed to transform one string into another.

Question 2

Which fuzzy matching function returns a normalized similarity score where higher values indicate more similar strings?

A. REGEXP_LIKE()

B. JARO_WINKLER_DISTANCE()

C. EDIT_DISTANCE_SIMILARITY()

D. CHARINDEX()

Answer: C

Explanation: EDIT_DISTANCE_SIMILARITY() converts the edit distance into a similarity score, making it easier to establish matching thresholds.

Question 3

A database developer is comparing customer names such as “John” and “Jon.” Which function is generally most appropriate?

A. EDIT_DISTANCE()

B. PATINDEX()

C. LIKE

D. JARO_WINKLER_DISTANCE()

Answer: D

Explanation: Jaro-Winkler is particularly effective for comparing names because it gives additional weight to matching prefixes.

Question 4

What does an EDIT_DISTANCE() value of 0 indicate?

A. The strings are unrelated.

B. One string contains only numbers.

C. The strings are identical.

D. The comparison failed.

Answer: C

Explanation: An edit distance of zero means no insertions, deletions, or substitutions are required because the strings are identical.

Question 5

Which scenario is the best candidate for fuzzy string matching?

A. Comparing integer primary keys.

B. Matching customer names entered manually.

C. Sorting dates.

D. Calculating sales totals.

Answer: B

Explanation: Fuzzy matching is designed to compare imperfect text, such as names entered by users that may contain spelling variations.

Question 6

Why should fuzzy matching generally be applied after filtering candidate rows?

A. It prevents SQL injection.

B. It automatically creates indexes.

C. It reduces computational cost and improves query performance.

D. It guarantees exact matches.

Answer: C

Explanation: Fuzzy matching algorithms are more expensive than exact comparisons, so reducing the candidate set improves performance.

Question 7

Which statement about JARO_WINKLER_DISTANCE() is correct?

A. It counts the number of vowels in a string.

B. It gives additional weight to matching prefixes.

C. It replaces text using regular expressions.

D. It returns the number of character edits.

Answer: B

Explanation: The Jaro-Winkler algorithm favors strings that share the same beginning, making it particularly useful for matching names.

Question 8

Which of the following is a common AI-enabled use case for fuzzy string matching?

A. Creating clustered indexes.

B. Encrypting sensitive columns.

C. Detecting likely duplicate support tickets generated by AI systems.

D. Managing SQL Server backups.

Answer: C

Explanation: AI-generated text may contain slight wording differences, making fuzzy matching valuable for identifying duplicate or highly similar records.

Question 9

A similarity score of 100 returned by EDIT_DISTANCE_SIMILARITY() most likely indicates:

A. The strings are completely different.

B. The strings have five character differences.

C. The comparison failed.

D. The strings are identical.

Answer: D

Explanation: A score of 100 represents an exact match between the two strings.

Question 10

Which statement best describes fuzzy string matching?

A. It requires strings to be identical.

B. It compares the similarity between strings, even when they contain typographical errors.

C. It is designed exclusively for JSON processing.

D. It replaces SQL indexes.

Answer: B

Explanation: Fuzzy matching measures similarity rather than exact equality, making it useful for handling misspellings, abbreviations, and other textual variations.

Go to the DP-800 Exam Prep Hub main page