Category: SQL

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

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

Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight (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
      --> Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

One of the primary responsibilities of a SQL AI Developer is ensuring that database queries execute efficiently. Slow queries can increase response times, consume excessive CPU and memory, cause blocking, reduce scalability, and negatively affect AI-powered applications that rely on timely access to data.

The DP-800 exam expects candidates to know how to:

Analyze query execution plans
Identify inefficient query operators
Interpret estimated and actual execution plans
Use Dynamic Management Views (DMVs) to monitor performance
Use Query Store to identify and resolve performance regressions
Use Query Performance Insight in Azure SQL Database
Recommend performance improvements based on collected metrics

Why Query Performance Matters

Database performance directly affects application performance.

Poorly optimized queries can lead to:

Slow application response times
High CPU utilization
Excessive memory consumption
Long-running transactions
Locking and blocking
Deadlocks
Reduced scalability
Increased Azure SQL costs

For AI-enabled applications, inefficient queries can delay:

Retrieval-Augmented Generation (RAG)
Semantic searches
Vector searches
AI model inference
Data preparation pipelines

Performance tuning is therefore an essential database development skill.

SQL Server Query Processing

Before SQL Server executes a query, it performs several steps:

Parse the T-SQL statement
Validate syntax and object names
Optimize the query
Generate an execution plan
Execute the plan

The Query Optimizer determines the most efficient execution strategy based on:

Statistics
Available indexes
Estimated row counts
Predicate selectivity
Join order
Available memory
Parallelism

What Is an Execution Plan?

An execution plan is a graphical or textual representation of how SQL Server executes a query.

It shows:

Operators
Join methods
Index usage
Estimated cost
Actual row counts
Warnings
Parallel operations

Execution plans are among the most valuable tools for diagnosing performance issues.

Estimated vs. Actual Execution Plans

SQL Server can generate two types of execution plans.

Estimated Execution Plan

Generated before execution.

Shows:

Estimated row counts
Estimated operator costs
Chosen indexes
Join methods

Does not execute the query.

In SQL Server Management Studio (SSMS):

Display Estimated Execution Plan (Ctrl + L)

Actual Execution Plan

Generated after query execution.

Shows:

Actual row counts
Actual execution statistics
Actual execution time
Memory usage
Runtime warnings
Actual operator behavior

Enable in SSMS:

Include Actual Execution Plan (Ctrl + M)

The DP-800 exam frequently tests the distinction between estimated and actual execution plans.

Understanding Execution Plan Operators

Execution plans contain operators representing individual processing steps.

Common operators include:

Operator	Purpose
Table Scan	Reads every row in a table
Clustered Index Scan	Scans an entire clustered index
Index Seek	Efficiently locates matching rows
Key Lookup	Retrieves additional columns from a clustered index
Nested Loops	Efficient join for small result sets
Merge Join	Efficient for sorted data
Hash Match	Efficient for large unsorted datasets
Sort	Orders rows
Compute Scalar	Calculates expressions
Filter	Applies predicates

Index Seek vs. Index Scan

One of the most frequently tested concepts.

Index Seek

Efficient.

Reads only qualifying rows.

Example:

			
SELECT *
FROM Customers
WHERE CustomerID = 125;

If an index exists on CustomerID:

Execution Plan:

Index Seek

Index Scan

Reads many or all index pages.

Example:

			
SELECT *
FROM Customers
WHERE YEAR(OrderDate)=2025;

Because the function prevents index usage, SQL Server often performs an Index Scan.

A scan is not always bad. If a query retrieves most rows in a table, a scan may be the most efficient choice.

Table Scans

A table scan reads every row.

Usually indicates:

Missing indexes
Non-selective predicates
Small tables
Poor query design

Table scans on very large tables often signal optimization opportunities.

Join Operators

SQL Server selects join algorithms based on estimated costs.

Nested Loops

Best for:

Small inputs
Indexed lookups

Merge Join

Best for:

Large sorted datasets

Requires sorted input.

Hash Match

Best for:

Large unsorted datasets

Uses more memory but often performs well for analytical workloads.

Cost Percentage

Execution plans display estimated operator costs.

Example:

			
Hash Match
85%
Index Seek
10%
Sort
5%

		

Important exam point:

Cost percentages are optimizer estimates—not actual elapsed execution time.

Execution Plan Warnings

Execution plans may display warnings such as:

Missing indexes
Implicit conversions
Spills to tempdb
Missing statistics
Excessive memory grants

Warnings often identify the root cause of performance issues.

Missing Index Recommendations

Execution plans sometimes recommend indexes.

Example:

Missing Index (Impact 98%)

These recommendations can significantly improve performance but should be evaluated carefully rather than implemented automatically, because they don’t consider overall workload or maintenance costs.

Dynamic Management Views (DMVs)

DMVs expose real-time information about SQL Server’s internal state.

They are invaluable for monitoring:

Active requests
Query statistics
Index usage
Wait statistics
Sessions
Transactions
Memory usage
Cached execution plans

Common Performance DMVs

sys.dm_exec_query_stats

Provides cumulative statistics for cached query plans.

Useful columns include:

Total CPU time
Total logical reads
Total elapsed time
Execution count

Example:

			
SELECT TOP 10
    total_worker_time,
    execution_count
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

		

sys.dm_exec_sql_text()

Returns the SQL text associated with cached plans.

Often joined with:

sys.dm_exec_query_stats

sys.dm_exec_query_plan()

Returns XML execution plans.

Useful for automated analysis.

sys.dm_exec_requests

Shows currently executing requests.

Useful for identifying:

Blocking
Long-running queries
Wait types

sys.dm_exec_sessions

Shows active user sessions.

Useful for monitoring connected users.

sys.dm_os_wait_stats

Displays cumulative wait statistics.

Common waits include:

PAGEIOLATCH
CXPACKET
LCK_M_X
WRITELOG

Wait statistics often reveal the primary performance bottleneck.

sys.dm_db_index_usage_stats

Shows how indexes are used.

Helps identify:

Unused indexes
Frequently used indexes
Missing optimization opportunities

Query Store

Query Store is one of SQL Server’s most valuable performance features.

Introduced in SQL Server 2016.

It automatically captures:

Query text
Execution plans
Runtime statistics
Wait statistics
Plan history

Unlike DMVs, Query Store persists data across server restarts.

Benefits of Query Store

Query Store helps developers:

Identify slow queries
Detect regressions
Compare execution plans
Force known-good execution plans
Analyze historical performance
Monitor workload changes

It is widely used for production performance tuning.

Query Store Architecture

Query Store stores:

Query text
Multiple execution plans
Runtime statistics
Wait statistics
Historical performance

This historical information makes it much easier to diagnose intermittent issues.

Detecting Query Regressions

A query regression occurs when a query suddenly becomes slower.

Common causes include:

Updated statistics
New indexes
Parameter sniffing
Schema changes
Data growth

Query Store can compare previous and current execution plans to identify regressions.

Forcing Execution Plans

If SQL Server selects an inefficient plan, Query Store allows administrators to force a previously successful plan.

Benefits include:

Immediate performance stabilization
Reduced troubleshooting time

Forced plans should still be monitored because future schema or workload changes may make a different plan more appropriate.

Query Store Wait Statistics

Modern versions of SQL Server also capture wait statistics per query.

Examples include:

CPU waits
Lock waits
I/O waits
Memory waits

This makes troubleshooting significantly easier.

Query Performance Insight

Query Performance Insight is an Azure SQL Database performance monitoring feature available in the Azure portal.

It provides visual dashboards that display:

Top resource-consuming queries
CPU utilization
Duration
Execution count
Database workload trends
Historical performance

It simplifies performance analysis without requiring T-SQL queries.

Benefits of Query Performance Insight

Advantages include:

Visual performance analysis
Historical trends
Easy identification of expensive queries
Azure portal integration
Supports Azure SQL Database

It is especially useful for cloud database administrators.

Common Performance Problems

Missing Indexes

Symptoms:

Table scans
High logical reads

Solution:

Create appropriate indexes after evaluating workload impact.

Outdated Statistics

Symptoms:

Poor execution plans
Incorrect row estimates

Solution:

Update statistics.

UPDATE STATISTICS Sales;

Parameter Sniffing

Occurs when SQL Server caches an execution plan optimized for one parameter value that performs poorly for others.

Possible solutions include:

Query Store plan forcing
OPTION (RECOMPILE)
OPTIMIZE FOR
Query rewriting

Implicit Conversions

Example:

WHERE CustomerID='100'

if CustomerID is an integer.

Implicit conversions may prevent index seeks.

Use matching data types whenever possible.

Excessive Key Lookups

Frequent Key Lookup operators may indicate that a covering index would improve performance.

Best Practices

Use Actual Execution Plans

Actual plans reveal runtime behavior and often expose problems that estimated plans cannot.

Review Missing Index Recommendations Carefully

Evaluate:

Existing indexes
Maintenance overhead
Duplicate indexes

Do not automatically implement every recommendation.

Monitor Query Store Regularly

Review:

Regressions
Forced plans
Runtime statistics
Wait statistics

Monitor Wait Statistics

Focus on the largest waits rather than individual slow queries.

Wait analysis often identifies system-wide bottlenecks.

Update Statistics

Accurate statistics enable the optimizer to generate better execution plans.

Remove Unused Indexes

Too many indexes:

Increase storage
Slow inserts
Slow updates
Slow deletes

DMVs help identify unused indexes.

Keep Statistics Current

Automatic statistics are helpful but may not always update quickly enough for rapidly changing data.

Performance Tuning Workflow

A common performance tuning process is:

Identify a slow query.
Capture the actual execution plan.
Review Query Store history.
Check DMVs for CPU, I/O, and wait statistics.
Identify inefficient operators.
Evaluate indexing opportunities.
Update statistics if needed.
Test improvements.
Monitor results.

DP-800 Exam Tips

Remember these key points for the exam:

Actual execution plans contain runtime statistics, while estimated execution plans do not execute the query.
An Index Seek is generally more efficient than an Index Scan when retrieving a small subset of rows.
DMVs provide real-time diagnostic information but generally reset when SQL Server restarts or the execution plan cache is cleared.
Query Store retains historical query performance information across restarts.
Query Store can detect query regressions and force a previous execution plan.
Query Performance Insight provides Azure portal dashboards for Azure SQL Database performance analysis.
Execution plan cost percentages are optimizer estimates, not measurements of actual elapsed time.
Missing index recommendations should be evaluated carefully rather than applied automatically.

Practice Exam Questions

Question 1

A database administrator wants to determine how SQL Server actually executed a query, including runtime row counts and operator statistics. Which tool should be used?

A. Actual Execution Plan
B. Estimated Execution Plan
C. Query Performance Insight
D. sys.dm_db_index_usage_stats

Correct Answer: A

Explanation: The Actual Execution Plan executes the query and records runtime information such as actual row counts, memory usage, and operator performance. Estimated plans only predict how the query will execute.

Question 2

A query retrieves a single customer by using a highly selective indexed column. Which execution plan operator would typically provide the best performance?

A. Table Scan
B. Clustered Index Scan
C. Index Seek
D. Hash Match

Correct Answer: C

Explanation: An Index Seek efficiently navigates directly to the qualifying rows within an index, minimizing I/O and improving performance for selective queries.

Question 3

Which Dynamic Management View (DMV) provides cumulative performance statistics for cached query plans?

A. sys.dm_exec_query_stats
B. sys.dm_exec_sessions
C. sys.dm_db_index_usage_stats
D. sys.dm_exec_requests

Correct Answer: A

Explanation: sys.dm_exec_query_stats stores cumulative statistics such as total worker time, logical reads, elapsed time, and execution count for cached query plans.

Question 4

A developer wants to analyze historical query performance and compare execution plans before and after a deployment. Which feature should be used?

A. Activity Monitor
B. SQL Server Profiler
C. Dynamic Management Views
D. Query Store

Correct Answer: D

Explanation: Query Store stores historical query text, execution plans, runtime statistics, and wait statistics, allowing developers to compare performance across deployments.

Question 5

Which statement about Query Store is true?

A. It only stores data until SQL Server restarts.
B. It automatically captures query history and execution plans.
C. It replaces execution plans entirely.
D. It only works with Azure SQL Database.

Correct Answer: B

Explanation: Query Store automatically captures query text, execution plans, runtime statistics, and historical performance data. Unlike many DMVs, its data persists across restarts.

Question 6

Which Azure SQL Database feature provides graphical dashboards that identify high-resource queries and workload trends?

A. Database Mail
B. Query Performance Insight
C. SQL Trace
D. Extended Events

Correct Answer: B

Explanation: Query Performance Insight provides Azure portal dashboards that visualize CPU usage, query duration, execution counts, and historical performance trends.

Question 7

An execution plan displays a warning indicating a “Missing Index (Impact 96%).” What is the best course of action?

A. Immediately create the recommended index without review.
B. Ignore the recommendation because SQL Server recommendations are unreliable.
C. Evaluate the recommendation alongside the overall workload before deciding whether to implement it.
D. Rebuild every existing index first.

Correct Answer: C

Explanation: Missing index recommendations are useful starting points, but developers should consider existing indexes, maintenance overhead, and workload characteristics before implementation.

Question 8

Which situation most commonly causes an Index Scan instead of an Index Seek?

A. Searching by a primary key value
B. Filtering with a function applied to an indexed column, such as YEAR(OrderDate)
C. Using an equality predicate on an indexed column
D. Retrieving a single row by a unique index

Correct Answer: B

Explanation: Applying functions to indexed columns often makes predicates non-SARGable, preventing efficient index seeks and causing SQL Server to scan the index instead.

Question 9

A developer wants to identify currently executing queries that are waiting on locks or consuming excessive resources. Which DMV is most appropriate?

A. sys.dm_exec_requests
B. sys.dm_exec_query_plan
C. sys.dm_db_index_usage_stats
D. sys.dm_os_wait_stats

Correct Answer: A

Explanation: sys.dm_exec_requests displays currently executing requests, including wait types, blocking information, CPU usage, elapsed time, and execution status.

Question 10

Why are database statistics important for query optimization?

A. They permanently eliminate table scans.
B. They encrypt execution plans.
C. They reduce transaction log size.
D. They help the Query Optimizer estimate row counts and choose efficient execution plans.

Correct Answer: D

Explanation: SQL Server relies on statistics to estimate data distribution and row counts. Accurate statistics allow the Query Optimizer to select efficient join methods, indexes, and execution strategies, resulting in better overall query performance.

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 Governance, Data Security, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Implement auditing – 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%)
   --> Implement data security and compliance
      --> Implement auditing

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.

In Parts 1 and 2, you learned how SQL Server auditing works, how Azure SQL auditing integrates with Azure services, and how auditing supports compliance, monitoring, and forensic investigations. This final section summarizes the topic, compares auditing with related security features, presents real-world scenarios, and concludes with 10 DP-800-style practice exam questions.

Auditing vs. Other SQL Security Features

Understanding the differences between SQL Server security features is critical for the DP-800 exam.

Feature	Purpose	Protects Data?	Records Activity?
SQL Server Audit	Records security events	No	Yes
Dynamic Data Masking	Obscures sensitive data	Yes	No
Row-Level Security	Restricts row access	Yes	No
Always Encrypted	Encrypts sensitive columns	Yes	No
Transparent Data Encryption (TDE)	Encrypts database files	Yes	No
SQL Server Permissions	Controls access	Yes	No
Microsoft Defender for SQL	Detects suspicious activity	Indirectly	Partially

A common exam question is determining which technology satisfies a particular requirement:

Need to record who accessed payroll data? → Auditing
Need to hide Social Security numbers? → Dynamic Data Masking
Need to encrypt credit card numbers? → Always Encrypted
Need users to see only their own records? → Row-Level Security
Need protection for database files at rest? → Transparent Data Encryption

SQL Server Audit Workflow

A simplified auditing workflow is shown below.

			
User Action
     │
     ▼
SQL Server
     │
     ▼
Audit Specification
(Server or Database)
     │
     ▼
SQL Server Audit
     │
     ▼
Audit Target
(File, Azure Storage,
Log Analytics, Event Hub)
     │
     ▼
Investigation /
Compliance Reporting

		

Common Audited Events

Organizations commonly audit:

Authentication

Successful logins
Failed logins
Password changes
Login creation
Login deletion

Administrative Changes

CREATE DATABASE
DROP DATABASE
ALTER DATABASE
CREATE LOGIN
ALTER LOGIN
Server role changes

Security Changes

GRANT
DENY
REVOKE
Permission changes
Role membership changes

Data Access

SELECT
INSERT
UPDATE
DELETE
EXECUTE

Typically, organizations only audit access to sensitive tables rather than every table in the database.

Schema Changes

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE PROCEDURE
ALTER PROCEDURE
CREATE VIEW

Real-World Scenario 1

A healthcare provider stores patient records in Azure SQL Database.

Requirements:

Record every UPDATE made to patient records.
Retain logs for seven years.
Alert security personnel when permission changes occur.

Real-World Scenario 2

A financial institution experiences unauthorized data modifications.

Requirements:

Determine who modified account balances.
Determine when modifications occurred.
Review executed SQL statements.

Solution:

Query audit logs using:

sys.fn_get_audit_file() (SQL Server)
Log Analytics (Azure SQL)
Azure Storage audit files

Review:

Login name
Timestamp
Statement
Database
Object
Session ID

Real-World Scenario 3

A company wants to monitor privileged users only.

Instead of auditing every database action:

Audit:

Login events
Role changes
Permission changes
ALTER statements
DROP statements

This minimizes performance impact while providing meaningful security visibility.

Compliance Mapping

Requirement	SQL Auditing Helps?
Determine who accessed sensitive data	Yes
Record failed logins	Yes
Detect unauthorized permission changes	Yes
Track schema modifications	Yes
Recover deleted data	No
Encrypt stored data	No
Prevent unauthorized access	No (permissions control access)

Remember:

Auditing provides evidence, not protection.

Performance Best Practices

For production environments:

✔ Audit only important events.

✔ Avoid auditing every SELECT statement unless required.

✔ Archive logs regularly.

✔ Protect audit files with appropriate permissions.

✔ Monitor storage consumption.

✔ Review audit logs routinely.

✔ Test audit configurations before production deployment.

✔ Separate audit storage from transaction log storage whenever practical.

DP-800 Exam Tips

Be comfortable answering questions about:

Server Audit vs. Database Audit Specification
Azure SQL auditing
Audit destinations
Log Analytics
Azure Storage
Event Hubs
Microsoft Sentinel
Azure Monitor
Compliance scenarios
Investigating suspicious activity
Performance implications of auditing

Quick Review

Remember these key concepts:

Topic	Key Point
SQL Server Audit	Defines where audit data is stored
Server Audit Specification	Audits server-level events
Database Audit Specification	Audits database-level events
Azure Storage	Long-term audit storage
Log Analytics	Search and analyze audit events
Event Hubs	Stream audit events
Azure Monitor	Alerting and dashboards
Microsoft Sentinel	SIEM and threat investigation
Defender for SQL	Threat detection
`sys.fn_get_audit_file()`	Reads SQL Server audit files

Common DP-800 Pitfalls

Avoid these misconceptions:

Auditing does not encrypt data.
Auditing does not prevent unauthorized access.
Auditing is not a replacement for backups.
Auditing does not replace Microsoft Defender for SQL.
Dynamic Data Masking does not record access.
Always Encrypted does not log who viewed data.

Practice Exam Questions

Question 1

A company must determine who modified salary information in the Employees table. Which SQL Server feature should be implemented?

A. Transparent Data Encryption

B. SQL Server Audit

C. Dynamic Data Masking

D. Row-Level Security

Answer: B

Explanation:

SQL Server Audit records database activity, including UPDATE operations, allowing administrators to identify who modified data, when the modification occurred, and which statement was executed. The other options protect or restrict data but do not record user activity.

Question 2

Which SQL Server object specifies where audit records are written?

A. Database Audit Specification

B. Server Audit Specification

C. SQL Server Audit

D. Audit Action Group

Answer: C

Explanation:

The SQL Server Audit object defines the audit destination, such as a file, Windows Security Log, or Windows Application Log. Audit specifications determine which events are captured.

Question 3

An organization wants to search audit logs using Kusto Query Language (KQL). Which Azure service should store the audit data?

A. Azure Storage

B. Event Hubs

C. Log Analytics Workspace

D. Azure Key Vault

Answer: C

Explanation:

Log Analytics stores audit data in a format that supports KQL queries, dashboards, alerts, and Azure Monitor integration. Azure Storage is intended for long-term retention rather than interactive querying.

Question 4

Which audit specification captures database-level activities such as SELECT, UPDATE, and DELETE?

A. Server Audit

B. Database Audit Specification

C. Audit Target

D. Server Audit Specification

Answer: B

Explanation:

Database Audit Specifications capture actions performed within a database, including DML operations and permission changes. Server Audit Specifications capture server-level activities.

Question 5

Which Azure service is primarily intended for streaming audit events to external monitoring systems in near real time?

A. Azure Storage

B. Azure Files

C. Log Analytics

D. Azure Event Hubs

Answer: D

Explanation:

Azure Event Hubs provides scalable event streaming for integration with SIEM platforms, custom monitoring solutions, and security tools. It is optimized for real-time event ingestion.

Question 6

Which function is commonly used to read SQL Server audit files?

A. OPENROWSET()

B. sys.fn_get_audit_file()

C. sp_readaudit

D. sys.fn_audit_log()

Answer: B

Explanation:

sys.fn_get_audit_file() is the built-in table-valued function used to read SQL Server audit files and return audit events in a queryable format.

Question 7

A security administrator needs immediate notification whenever database permissions change. Which solution best meets this requirement?

A. Configure auditing with Log Analytics and Azure Monitor alerts.

B. Disable auditing and use transaction logs.

C. Store audit files only in Azure Storage.

D. Enable Transparent Data Encryption.

Answer: A

Explanation:

Auditing records permission changes, while Azure Monitor can generate alerts based on those audit events stored in Log Analytics. Azure Storage alone does not provide real-time alerting.

Question 8

Which statement correctly describes SQL Server auditing?

A. It encrypts sensitive columns.

B. It prevents unauthorized access to data.

C. It automatically restores deleted records.

D. It records security-related database and server activity.

Answer: D

Explanation:

Auditing records activities for monitoring, compliance, and investigation. It does not encrypt data, restore deleted records, or enforce permissions.

Question 9

Which audit target is generally recommended by Microsoft for most on-premises production SQL Server environments?

A. File

B. Windows Security Log

C. Windows Application Log

D. Azure Event Hubs

Answer: A

Explanation:

File targets provide excellent performance, scalability, and flexibility. They are the recommended destination for most production SQL Server deployments.

Question 10

Which Microsoft security service uses audit information to help detect suspicious database activity and investigate incidents?

A. Azure Backup

B. Microsoft Sentinel

C. SQL Server Agent

D. Azure Resource Manager

Answer: B

Explanation:

Microsoft Sentinel consumes audit logs from services such as Azure SQL Database to correlate events, detect threats, automate investigations, and assist security analysts. It complements auditing by providing advanced security analytics rather than simply recording events.

Final DP-800 Takeaways

For the DP-800 exam, remember these core principles:

SQL Server Audit defines where audit records are stored.
Server Audit Specifications capture server-level activities such as logins and server role changes.
Database Audit Specifications capture database-level activities such as data access and schema changes.
Azure Storage is ideal for long-term retention.
Log Analytics enables interactive querying, dashboards, and Azure Monitor alerts.
Azure Event Hubs supports real-time streaming to external systems.
Microsoft Sentinel extends auditing with SIEM capabilities, threat detection, and incident response.
Auditing provides accountability, supports compliance, and enables forensic investigations, but it does not replace encryption, access control, or threat protection technologies.

Go to the DP-800 Exam Prep Hub main page

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

Implement auditing – Part 2 (DP-800 Exam Prep)

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

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

In Part 1, you learned about the SQL Server Audit architecture, audit specifications, audit targets, audit action groups, and how to configure and manage audits in SQL Server. In this section, we’ll examine how auditing works in Azure SQL services, how audit data integrates with Azure monitoring solutions, and the performance and operational considerations that are especially relevant for the DP-800 exam.

Auditing in Azure SQL Database

Azure SQL Database includes built-in auditing capabilities that are designed for cloud-native environments. Unlike on-premises SQL Server, Azure SQL Database can automatically integrate with Azure services for centralized monitoring and compliance.

Azure SQL auditing records database events such as:

Successful and failed logins
Database schema changes
Permission modifications
Data access (SELECT)
Data modifications (INSERT, UPDATE, DELETE)
Stored procedure execution
Security configuration changes
Administrative operations

Auditing can be configured at two levels:

Server level
Individual database level

Server-level auditing provides a consistent policy across all databases on the logical SQL server, while database-level auditing allows different auditing configurations for specific databases.

Azure SQL Auditing Architecture

			
Azure SQL Database
        │
        ▼
    SQL Auditing
        │
 ┌──────┼────────┐
 ▼      ▼        ▼
Storage Log Analytics Event Hub
Account Workspace

		

One audit configuration can send events to one or more Azure services.

Audit Destinations in Azure

Unlike SQL Server, Azure SQL Database supports several cloud-based audit destinations.

Azure Storage Account

The most common destination.

Benefits include:

Low-cost storage
Long-term retention
Backup
Archive capabilities
Easy export
Compliance support

Organizations frequently retain audit logs in Storage Accounts for multiple years.

Log Analytics Workspace

Many organizations choose Log Analytics because it supports:

Interactive searches
Kusto Query Language (KQL)
Dashboards
Alerting
Workbooks
Azure Monitor integration

Example investigations include:

Failed login trends
Privileged user activity
Permission changes
Suspicious DELETE operations

Azure Event Hubs

Event Hubs allows organizations to stream audit events in near real time.

Typical integrations include:

SIEM platforms
Security monitoring solutions
Custom monitoring applications
Third-party security tools

Configuring Azure SQL Auditing

Auditing can be enabled through:

Azure Portal
Azure CLI
PowerShell
ARM templates
Bicep
Terraform
Azure REST API

Within the Azure Portal, the configuration typically involves:

Select the SQL Server or database.
Open Auditing under the Security section.
Enable auditing.
Choose one or more destinations.
Configure retention settings.
Save the configuration.

Retention Policies

Azure Storage destinations support configurable retention periods.

Examples include:

90 days
180 days
1 year
Multiple years

Retention should match organizational compliance requirements.

Examples:

Regulation	Typical Retention
PCI DSS	At least one year
HIPAA	Several years (organization-specific)
SOX	Often seven years
Internal security policies	Varies

Azure SQL Managed Instance Auditing

Azure SQL Managed Instance supports auditing capabilities similar to SQL Server while integrating with Azure services.

Supported destinations include:

Azure Storage
Log Analytics
Event Hubs

Managed Instance also supports many SQL Server auditing features, making it easier to migrate on-premises workloads to Azure without redesigning security monitoring.

Microsoft Fabric SQL Auditing Considerations

Microsoft Fabric SQL databases and SQL analytics endpoints are integrated into the broader Microsoft Fabric governance ecosystem.

Rather than relying solely on traditional SQL Server Audit objects, Fabric environments also benefit from:

Microsoft Purview governance
Activity monitoring
Workspace monitoring
Capacity monitoring
Microsoft Fabric Activity Log
Azure Monitor integration
Microsoft Defender integration

For the DP-800 exam, understand that auditing in Fabric emphasizes cloud-native monitoring and governance rather than traditional SQL Server Audit files.

Viewing Audit Logs

Azure Portal

Administrators can review:

Audit status
Destination
Retention
Recent activity

The portal provides quick access to Log Analytics and Storage Accounts where audit records reside.

Log Analytics

Audit records become searchable using Kusto Query Language (KQL).

Example:

			
AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where statement_s contains "DELETE"

This query returns DELETE statements captured by SQL auditing.

Storage Account

Audit files stored in Azure Storage can be:

Downloaded
Archived
Imported
Processed by external tools
Loaded into Power BI
Queried with Azure Data Explorer

Integrating Auditing with Azure Monitor

Azure Monitor provides centralized monitoring across Azure resources.

Audit logs can generate:

Alerts
Dashboards
Metrics
Workbooks
Notifications

Example alert:

Notify the security team whenever more than ten failed login attempts occur within five minutes.

Microsoft Sentinel Integration

Microsoft Sentinel is Microsoft’s cloud-native Security Information and Event Management (SIEM) platform.

Audit logs can be streamed into Sentinel where security analysts can:

Detect attacks
Investigate incidents
Correlate events
Create analytics rules
Build hunting queries
Automate responses

Example scenario:

Repeated failed logins
Successful privileged login
Mass DELETE operations

Sentinel correlates these events into a potential security incident.

Microsoft Defender for SQL

Auditing and Microsoft Defender for SQL complement one another.

Auditing	Defender for SQL
Records activity	Detects threats
Supports compliance	Uses behavioral analytics
Captures events	Generates security alerts
Used during investigations	Identifies suspicious behavior

For example:

Auditing records that a user executed a large number of DELETE statements, while Defender for SQL may identify that behavior as anomalous and raise a security alert.

Performance Considerations

Auditing introduces some performance overhead because every audited event must be written to an audit target.

The impact depends on factors such as:

Number of audited events
Frequency of activity
Storage performance
Audit destination
Network latency (Azure)

Fortunately, SQL Server auditing is highly optimized and generally has minimal impact when configured appropriately.

Reducing Performance Overhead

Microsoft recommends several strategies.

Audit Only Necessary Events

Avoid auditing every possible action.

Instead, focus on:

Logins
Permission changes
Sensitive table access
Administrative operations

Avoid Excessive SELECT Auditing

High-volume transactional systems may execute millions of SELECT statements daily.

Auditing every SELECT can:

Increase storage consumption
Generate enormous audit files
Reduce performance

Instead, audit only access to sensitive tables.

Separate Audit Storage

Whenever possible:

Store audit files on separate disks.
Use dedicated Azure Storage Accounts.
Avoid sharing storage with transaction logs.

Archive Older Logs

Large audit repositories become difficult to search.

Implement:

Automatic archiving
Lifecycle management
Long-term storage
Periodic cleanup

Monitoring Audit Health

Administrators should routinely verify that auditing is functioning correctly.

Check:

Audit status
Storage availability
Remaining storage capacity
Failed audit writes
Log Analytics ingestion
Event Hub connectivity
Audit retention settings

Monitoring helps prevent gaps in audit coverage.

Common Auditing Scenarios

Scenario 1

A hospital must record every update to patient records.

Recommended approach:

Database auditing
Audit UPDATE operations
Store logs in Azure Storage
Retain logs according to healthcare regulations

Scenario 2

A bank wants immediate notification when administrators change permissions.

Recommended approach:

Audit permission changes
Send events to Log Analytics
Create Azure Monitor alerts
Forward alerts to Microsoft Sentinel

Scenario 3

A company wants to investigate suspicious DELETE statements after a potential insider attack.

Recommended approach:

Query audit logs
Identify user accounts
Review timestamps
Correlate activity with authentication logs

Common Mistakes

Candidates often confuse several related security technologies.

Feature	Purpose
Auditing	Records activity
Dynamic Data Masking	Hides data
Row-Level Security	Filters rows
Always Encrypted	Encrypts data
Transparent Data Encryption	Encrypts database files
Microsoft Defender for SQL	Detects threats

Remember:

Auditing records activity.
It does not prevent activity.
It does not encrypt data.
It does not mask data.

DP-800 Exam Tips

Expect scenario-based questions such as:

Which audit destination should be selected?
Which service enables security investigations?
Which Azure service should receive audit logs?
How should audits be configured for compliance?
Which audit events should be enabled?
How can auditing be integrated with Azure Monitor?

Also remember:

Azure Storage is commonly used for long-term retention.
Log Analytics is best for querying and analysis.
Event Hubs is designed for real-time event streaming.
Microsoft Sentinel builds on audit logs to provide advanced threat detection and incident response.
Microsoft Defender for SQL complements auditing by detecting suspicious behavior rather than simply recording it.

Best Practices Summary

Enable auditing for all production databases.
Audit only security-relevant events to minimize overhead.
Prefer centralized monitoring using Azure Monitor and Log Analytics.
Protect audit logs from unauthorized modification or deletion.
Configure retention policies that satisfy organizational and regulatory requirements.
Integrate auditing with Microsoft Sentinel for security operations.
Periodically review audit logs and validate that auditing remains enabled after deployments or configuration changes.
Document audit policies and test recovery procedures for audit data.

Go to the DP-800 Exam Prep Hub main page

Data Security, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Implement secure database access, including passwordless (DP-800 Exam Prep)

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

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

One of the primary responsibilities of a SQL AI Developer is ensuring that applications and users access databases securely. As organizations move toward cloud-native architectures and zero-trust security models, traditional username-and-password authentication is increasingly being replaced by more secure alternatives such as passwordless authentication, Microsoft Entra ID (formerly Azure Active Directory), managed identities, and service principals.

The DP-800 exam expects candidates to understand how to design secure authentication and authorization strategies for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL solutions. Candidates should also understand when to use SQL authentication versus Microsoft Entra authentication, how passwordless authentication works, and how applications securely connect to databases without embedding secrets.

Authentication vs. Authorization

A common exam objective is distinguishing authentication from authorization.

Authentication answers the question:

Who are you?

Authentication verifies the identity of a user or application.

Examples include:

Microsoft Entra ID login
SQL login
Windows Authentication
Managed Identity
Service Principal

Authorization answers the question:

What are you allowed to do?

Authorization determines permissions after authentication succeeds.

Examples include:

SELECT permission
EXECUTE permission
Database roles
Row-Level Security (RLS)
Object-level permissions

Authentication always occurs before authorization.

Types of Database Authentication

SQL Server supports multiple authentication methods.

Authentication Method	Typical Usage
Windows Authentication	On-premises Active Directory environments
SQL Authentication	Username and password stored in SQL Server
Microsoft Entra Authentication	Azure SQL Database and Fabric
Managed Identity	Azure-hosted services
Service Principal	Automated applications and DevOps
Passwordless Authentication	Microsoft Entra authentication without passwords

SQL Authentication

SQL Authentication uses a SQL login and password stored by SQL Server.

Example:

			
CREATE LOGIN SalesUser
WITH PASSWORD = 'StrongPassword123!';

Advantages:

Easy to configure
Supported by virtually every SQL client
Independent of Active Directory

Disadvantages:

Password management required
Password rotation required
Secrets must often be stored in applications
Higher risk of credential theft

Microsoft recommends minimizing the use of SQL authentication whenever possible, particularly in Azure environments.

Windows Authentication

Windows Authentication uses Active Directory credentials.

Advantages:

Integrated security
Single sign-on (SSO)
Centralized identity management
Kerberos authentication
Password policies enforced automatically

Common connection string:

Integrated Security=True;

This is the preferred authentication method for on-premises SQL Server environments.

Microsoft Entra Authentication

Microsoft Entra ID is Microsoft’s cloud identity provider and is the preferred authentication mechanism for Azure SQL services.

Benefits include:

Single Sign-On (SSO)
Multi-Factor Authentication (MFA)
Conditional Access
Centralized identity management
Passwordless authentication support
Identity governance
Integration with Microsoft Fabric

Users authenticate through Microsoft Entra instead of SQL logins.

Example workflow:

			
User
     │
Microsoft Entra ID
     │
Azure SQL Database

		

Passwordless Authentication

Passwordless authentication eliminates traditional passwords while maintaining strong identity verification.

Instead of passwords, authentication may use:

Windows Hello for Business
Microsoft Authenticator
FIDO2 Security Keys
Passkeys
Biometric authentication
Managed Identities
Microsoft Entra tokens

Benefits include:

Eliminates password theft
Prevents password reuse
Reduces phishing attacks
Removes password rotation requirements
Improves user experience

Microsoft strongly recommends passwordless authentication whenever possible.

How Passwordless Authentication Works

Instead of sending a password:

			
Application
      │
Obtains Microsoft Entra access token
      │
Azure SQL Database validates token
      │
Connection established

		

The database trusts Microsoft Entra rather than validating a stored password.

Managed Identity

Managed Identity is one of the most important DP-800 topics.

A Managed Identity is an identity automatically managed by Azure for Azure resources.

Examples:

Azure App Service
Azure Functions
Azure Virtual Machines
Azure Container Apps
Azure Kubernetes Service
Azure Logic Apps

Instead of storing credentials:

			
Application
        ↓
Managed Identity
        ↓
Microsoft Entra ID
        ↓
Azure SQL Database

		

No passwords are stored.

Advantages of Managed Identity

Benefits include:

No stored passwords
Automatic credential rotation
Short-lived access tokens
Integrated with Microsoft Entra
Easier compliance
Reduced security risk

This is Microsoft’s recommended approach for Azure-hosted applications.

Service Principals

A Service Principal represents an application rather than a person.

Common uses include:

CI/CD pipelines
Azure DevOps
GitHub Actions
Background services
Automation scripts

Service principals authenticate through Microsoft Entra and can access Azure SQL databases securely.

Access Tokens

Modern Azure SQL authentication uses OAuth access tokens.

Instead of:

			
Username
Password

Applications obtain:

Microsoft Entra Access Token

The token:

Has a limited lifetime
Cannot be reused indefinitely
Reduces credential theft
Supports Conditional Access policies

Configuring Microsoft Entra Authentication

Typical steps include:

Configure a Microsoft Entra administrator for the SQL server.
Create Microsoft Entra users or groups.
Create contained database users.
Assign database roles.
Grant required permissions.

Example:

			
CREATE USER [Alice@contoso.com]
FROM EXTERNAL PROVIDER;

Grant role:

			
ALTER ROLE db_datareader
ADD MEMBER [Alice@contoso.com];

No SQL password is required.

Contained Database Users

Contained database users simplify authentication.

Advantages:

No SQL login required
Database portability
Simplified Azure SQL deployments
Works well with Microsoft Entra identities

Example:

			
CREATE USER [Developers]
FROM EXTERNAL PROVIDER;

Secure Connection Strings

Avoid storing:

			
Server=myserver;
User ID=admin;
Password=Password123;

Instead, use Microsoft Entra authentication.

Example (.NET):

Authentication=Active Directory Default;

The application automatically acquires an access token using the available identity.

Connection Security

Authentication should be combined with encrypted network connections.

Best practices include:

Require TLS encryption
Validate server certificates
Encrypt all client-server communication
Disable legacy protocols

Azure SQL encrypts client connections by default.

Principle of Least Privilege

Applications should receive only the permissions they require.

Example:

Application needs:

Execute stored procedures

Application does not need:

ALTER DATABASE
CONTROL
db_owner

Using least privilege minimizes security risks.

Passwordless Authentication with Azure Services

Many Azure services automatically support Managed Identity.

Example:

			
Azure Function
       │
Managed Identity
       │
Microsoft Entra
       │
Azure SQL Database

		

No secrets are stored in code or configuration files.

Microsoft Fabric Integration

Microsoft Fabric integrates closely with Microsoft Entra ID.

Fabric workloads support:

Microsoft Entra authentication
Single Sign-On
Role-based access
Passwordless identity
Unified identity management

DP-800 candidates should understand that Fabric relies heavily on Microsoft Entra identities rather than SQL logins.

Security Best Practices

Microsoft recommends:

Prefer Microsoft Entra authentication over SQL authentication.
Use passwordless authentication whenever possible.
Enable Multi-Factor Authentication (MFA).
Use Managed Identity for Azure-hosted applications.
Use Service Principals for automation.
Avoid embedding credentials in source code.
Store secrets in Azure Key Vault if passwords or keys are unavoidable.
Rotate credentials regularly when passwords must be used.
Use TLS encryption for all database connections.
Follow the principle of least privilege.
Audit authentication events regularly.
Use Conditional Access policies to protect administrative accounts.

Common DP-800 Exam Scenarios

You may be asked to determine:

Which authentication method is most secure.
When to use Managed Identity.
When to use Microsoft Entra authentication.
How passwordless authentication works.
When SQL Authentication is appropriate.
How applications connect without passwords.
How service principals authenticate.
How contained database users simplify Azure SQL deployments.
How to eliminate secrets from connection strings.
How to secure Azure-hosted AI applications accessing SQL databases.

DP-800 Exam Tips

Remember these key points:

Microsoft Entra ID is the preferred authentication mechanism for Azure SQL.
Passwordless authentication reduces phishing and credential theft.
Managed Identities eliminate stored passwords.
Service Principals authenticate applications and automation.
SQL Authentication still exists but is less secure.
Authentication verifies identity; authorization controls permissions.
Use least privilege for both users and applications.
Azure SQL supports OAuth access tokens instead of passwords.
Fabric uses Microsoft Entra authentication extensively.

Practice Exam Questions

Question 1

Which authentication method is Microsoft’s recommended approach for Azure-hosted applications connecting to Azure SQL Database?

A. Managed Identity

B. SQL Authentication

C. Windows Authentication

D. Shared SQL Administrator account

Correct Answer: A

Explanation:
Managed Identity eliminates the need to store credentials, automatically manages identity, and integrates with Microsoft Entra ID, making it Microsoft’s preferred authentication method for Azure-hosted applications.

Question 2

What is the primary purpose of passwordless authentication?

A. Improve query performance

B. Eliminate traditional passwords while securely verifying identity

C. Replace authorization

D. Encrypt database backups

Correct Answer: B

Explanation:
Passwordless authentication replaces passwords with stronger authentication mechanisms such as biometrics, security keys, Microsoft Authenticator, or access tokens, reducing the risk of credential theft.

Question 3

Which statement correctly distinguishes authentication from authorization?

A. Authentication determines database roles; authorization creates logins.

B. Authentication encrypts data; authorization decrypts it.

C. Authentication verifies identity, while authorization determines what actions are permitted.

D. Authentication assigns object permissions, while authorization validates passwords.

Correct Answer: C

Explanation:
Authentication confirms who a user or application is, whereas authorization determines what resources and operations that authenticated identity may access.

Question 4

A development team wants to eliminate database passwords from application configuration files. Which solution best meets this requirement?

A. Store SQL passwords in source code.

B. Use SQL Authentication with stronger passwords.

C. Share one administrator account among all applications.

D. Use Microsoft Entra authentication with Managed Identity.

Correct Answer: D

Explanation:
Managed Identity allows applications to authenticate without storing passwords or secrets, significantly improving security and simplifying credential management.

Question 5

Which authentication method is commonly used for automated CI/CD pipelines and background services?

A. Windows Authentication

B. Service Principal

C. SQL Authentication

D. Database Owner account

Correct Answer: B

Explanation:
Service Principals represent applications rather than users and are commonly used by automation tools such as Azure DevOps and GitHub Actions.

Question 6

Which feature is automatically provided by Managed Identity?

A. Automatic query tuning

B. Automatic index creation

C. Automatic credential rotation

D. Automatic data encryption

Correct Answer: C

Explanation:
Managed Identity automatically handles credential creation and rotation, eliminating the need for administrators or developers to manage passwords.

Question 7

Which SQL statement creates a Microsoft Entra user in an Azure SQL Database?

CREATE LOGIN Alice WITH PASSWORD='Password123';

CREATE USER Alice WITHOUT LOGIN;

CREATE USER [Alice@contoso.com] FROM EXTERNAL PROVIDER;

CREATE ROLE Alice;

Correct Answer: C

Explanation:
The FROM EXTERNAL PROVIDER clause creates a contained database user that authenticates through Microsoft Entra ID rather than a SQL login.

Question 8

Which security principle recommends granting only the permissions required for a user or application to perform its work?

A. Ownership chaining

B. Principle of least privilege

C. Password complexity

D. Data masking

Correct Answer: B

Explanation:
Least privilege minimizes security risks by limiting permissions to only those necessary for the required tasks.

Question 9

Which authentication mechanism does Azure SQL Database use with Microsoft Entra authentication?

A. Static passwords

B. Kerberos tickets only

C. SQL login hashes

D. OAuth access tokens

Correct Answer: D

Explanation:
Microsoft Entra authentication relies on OAuth access tokens, which are short-lived and securely validated by Azure SQL Database.

Question 10

Why is Microsoft Entra authentication generally preferred over SQL Authentication for Azure SQL Database?

A. It requires longer passwords.

B. It supports centralized identity management, MFA, Conditional Access, and passwordless authentication.

C. It eliminates database roles.

D. It removes the need for database permissions.

Correct Answer: B

Explanation:
Microsoft Entra authentication provides enterprise-grade identity management features, including Single Sign-On, Multi-Factor Authentication, Conditional Access, centralized administration, and support for passwordless authentication, making it more secure than traditional SQL Authentication.

Go to the DP-800 Exam Prep Hub main page

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

Design and implement object-level permissions (DP-800 Exam Prep)

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

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

Securing data is one of the most important responsibilities of a SQL developer. While server-level and database-level permissions determine who can connect to SQL Server and access databases, object-level permissions determine what users can do with individual database objects such as tables, views, stored procedures, functions, sequences, and schemas.

The DP-800 certification expects candidates to understand how to implement the principle of least privilege, ensuring that users receive only the permissions required to perform their jobs.

Object-level permissions are a fundamental component of SQL Server security and are widely used in:

Microsoft SQL Server
Azure SQL Database
Azure SQL Managed Instance
Microsoft Fabric SQL Database
SQL Database in Fabric Warehouses (where supported)

Understanding how permissions are inherited, granted, denied, revoked, and combined with roles is essential for designing secure database solutions.

What Are Object-Level Permissions?

Object-level permissions control access to individual database objects rather than the entire database.

For example, one user might:

Read data from a table
Execute a stored procedure
Update rows in another table
View metadata
Create indexes

while another user has completely different permissions.

Unlike database-level permissions, object permissions provide very granular security.

Example:

			
Sales.Customers
Sales.Orders
Sales.Products
HR.Employees

A salesperson may have access to Sales tables but no access to HR tables.

Common Database Objects That Can Be Secured

Permissions can be assigned to numerous SQL Server objects, including:

Tables
Views
Stored procedures
Functions
Schemas
Sequences
Synonyms
External tables
User-defined types
XML schema collections
Service Broker objects

DP-800 focuses primarily on:

Tables
Views
Stored procedures
Functions
Schemas

Permission Hierarchy

Permissions exist at several levels.

			
Server
    ↓
Database
    ↓
Schema
    ↓
Object

		

Example:

			
Database
    Sales
Schema
    Sales
Table
    Orders

		

Permissions granted on the schema may automatically apply to objects within that schema.

Common Object Permissions

The most commonly used permissions include:

Permission	Purpose
SELECT	Read rows
INSERT	Add rows
UPDATE	Modify rows
DELETE	Remove rows
EXECUTE	Run stored procedures/functions
REFERENCES	Create foreign keys
ALTER	Modify an object
CONTROL	Full control over an object
TAKE OWNERSHIP	Change ownership
VIEW DEFINITION	View object definition

GRANT

GRANT gives permissions.

Example

			
GRANT SELECT
ON Sales.Orders
TO SalesUser;

The user can now query the table.

Example

			
GRANT INSERT, UPDATE
ON Sales.Orders
TO SalesUser;

Multiple permissions can be granted simultaneously.

Grant execute permission

			
GRANT EXECUTE
ON dbo.usp_ProcessOrders
TO SalesUser;

The user may execute the procedure without having direct table permissions.

DENY

DENY explicitly prevents access.

Example

			
DENY DELETE
ON Sales.Orders
TO SalesUser;

Even if another role grants DELETE, DENY overrides it.

This is one of the most important security concepts on the DP-800 exam.

REVOKE

REVOKE removes previously granted or denied permissions.

Example

			
REVOKE SELECT
ON Sales.Orders
FROM SalesUser;

REVOKE does not deny access.

It simply removes the explicit permission.

GRANT vs DENY vs REVOKE

Command	Effect
GRANT	Allows access
DENY	Explicitly blocks access
REVOKE	Removes a GRANT or DENY

Permission Precedence

SQL Server evaluates permissions using precedence rules.

Highest priority:

DENY

Lower priority:

GRANT

Example

User belongs to:

SalesRole

SalesRole:

GRANT SELECT

Another role:

DENY SELECT

Result:

User cannot SELECT.

DENY wins.

Granting Permissions to Roles

Best practice is to grant permissions to roles rather than directly to users.

Example

CREATE ROLE SalesReaders;

Grant permission

			
GRANT SELECT
ON Sales.Orders
TO SalesReaders;

Add user

			
ALTER ROLE SalesReaders
ADD MEMBER Alice;

This greatly simplifies administration.

Schema-Level Permissions

Instead of granting access to each table individually, permissions may be granted on an entire schema.

Example

			
GRANT SELECT
ON SCHEMA::Sales
TO SalesReaders;

The role receives SELECT permission on all objects within the Sales schema.

Stored Procedure Permissions

Applications often use stored procedures instead of direct table access.

Example

			
GRANT EXECUTE
ON dbo.usp_GetCustomerOrders
TO AppUser;

Users execute the procedure without needing direct permissions on the underlying tables (ownership chaining permitting).

Benefits include:

Better security
Reduced attack surface
Easier auditing
Centralized business logic

View Permissions

Views frequently expose only selected columns or rows.

Example

			
GRANT SELECT
ON Sales.vCustomerSummary
TO SalesReaders;

Applications query the view rather than the underlying table.

Advantages include:

Hide sensitive columns
Simplify queries
Provide logical security boundaries

Function Permissions

Scalar and table-valued functions also require EXECUTE permission.

Example

			
GRANT EXECUTE
ON dbo.fn_CalculateDiscount
TO SalesUser;

Ownership Chaining

Ownership chaining occurs when objects owned by the same owner access one another.

Example

			
User
   ↓
Stored Procedure
   ↓
Table

		

If both objects share the same owner:

SQL Server does not perform additional permission checks on the table.

Benefits:

Simplifies application security
Eliminates unnecessary table permissions
Improves manageability

DP-800 frequently tests this concept.

Least Privilege Principle

One of Microsoft’s most important security recommendations.

Users should receive:

Only the permissions required
Nothing more

Poor example

db_owner

Better example

			
SELECT
EXECUTE

Grant only what is necessary.

Avoid Granting db_owner

Many organizations incorrectly solve permission issues by granting db_owner.

Problems:

Full database control
Can drop objects
Can change security
Can alter schemas
Increased security risk

Instead:

Create custom roles
Grant only required permissions

Object Permissions and AI Applications

Modern AI-enabled SQL solutions frequently access databases through:

APIs
Stored procedures
Semantic search
Retrieval-Augmented Generation (RAG)
Microsoft Fabric
Copilot applications

Best practice:

AI applications should never connect using highly privileged accounts.

Instead:

Create service accounts.
Grant only EXECUTE on required procedures or SELECT on approved views.
Avoid direct access to sensitive tables.
Combine object permissions with Row-Level Security (RLS), Dynamic Data Masking (DDM), and Always Encrypted where appropriate.

This approach reduces the risk of exposing sensitive information through AI-assisted applications.

Best Practices

Microsoft recommends:

Grant permissions through roles.
Follow least privilege.
Prefer views over direct table access.
Use stored procedures for data modifications.
Avoid granting db_owner.
Regularly audit permissions.
Remove unused permissions.
Use schema-based permissions when appropriate.
Minimize explicit DENY statements unless required.
Combine object permissions with other SQL Server security features.

DP-800 Exam Tips

Candidates should know how to:

Grant object permissions
Revoke permissions
Deny permissions
Understand permission inheritance
Secure stored procedures
Secure views
Grant schema permissions
Use database roles
Explain ownership chaining
Apply least privilege
Understand permission precedence
Determine the effect of GRANT, DENY, and REVOKE
Design secure access models for AI-enabled database applications

Practice Exam Questions

Question 1

A database developer wants users to read data from the Sales.Orders table but prevent any modifications. Which permission should be granted?

A. EXECUTE

B. SELECT

C. ALTER

D. CONTROL

Correct Answer: B

Explanation:
The SELECT permission allows users to read rows from a table without permitting INSERT, UPDATE, or DELETE operations.

Question 2

A user belongs to two database roles. One role grants SELECT permission on a table, while the other role explicitly denies SELECT permission. What is the result?

A. SQL Server ignores the DENY.

B. SQL Server randomly selects one permission.

C. The user can still read the table.

D. The user cannot read the table.

Correct Answer: D

Explanation:
DENY takes precedence over GRANT. An explicit DENY overrides any granted permissions from other roles.

Question 3

Which statement is the recommended method for assigning permissions to multiple users?

A. Grant permissions directly to every user.

B. Add every user to db_owner.

C. Create database roles and grant permissions to the roles.

D. Use only server-level permissions.

Correct Answer: C

Explanation:
Assigning permissions to roles simplifies administration, improves consistency, and aligns with Microsoft security best practices.

Question 4

Which command removes a previously granted permission without explicitly denying access?

REVOKE

DENY

REMOVE

DROP

Correct Answer: A

Explanation:
REVOKE removes an existing GRANT or DENY. It does not prohibit future access unless another permission remains in effect.

Question 5

An application should execute a stored procedure but should not have direct access to the underlying tables. Which permission should be granted?

A. SELECT on every table

B. CONTROL on the database

C. EXECUTE on the stored procedure

D. ALTER on the schema

Correct Answer: C

Explanation:
Granting EXECUTE on the stored procedure allows users to perform approved operations without direct table access, leveraging ownership chaining when applicable.

Question 6

Which permission allows a user to modify the definition of an existing table?

A. ALTER

B. SELECT

C. EXECUTE

D. REFERENCES

Correct Answer: A

Explanation:
The ALTER permission enables changes to an object’s definition, such as adding or removing columns from a table.

Question 7

A database administrator grants SELECT permission on an entire schema. What is the primary benefit?

A. It encrypts every table in the schema.

B. It automatically creates new users.

C. It applies permissions to objects within the schema, simplifying administration.

D. It replaces Row-Level Security.

Correct Answer: C

Explanation:
Schema-level permissions reduce administrative effort by applying permissions to objects contained within the schema, rather than requiring individual grants on each object.

Question 8

Which principle recommends granting users only the permissions they require to perform their jobs?

A. Defense in depth

B. Separation of duties

C. Ownership chaining

D. Least privilege

Correct Answer: D

Explanation:
The principle of least privilege minimizes security risks by limiting permissions to only those necessary for a user’s responsibilities.

Question 9

Why is granting the db_owner role to application accounts generally discouraged?

A. It prevents applications from executing stored procedures.

B. It provides unnecessary administrative privileges and increases security risk.

C. It disables ownership chaining.

D. It prevents schema-level permissions from working.

Correct Answer: B

Explanation:
The db_owner role grants full control over the database, which violates the principle of least privilege and can expose the database to accidental or malicious changes.

Question 10

Which database object permission is required to run a user-defined function?

A. SELECT

B. UPDATE

C. EXECUTE

D. ALTER

Correct Answer: C

Explanation:
User-defined functions, like stored procedures, require the EXECUTE permission to be invoked by users or applications.

Go to the DP-800 Exam Prep Hub main page

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

Design and implement Row-Level Security (RLS) (DP-800 Exam Prep)

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

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

What is Row-Level Security (RLS)?

Row-Level Security (RLS) is a SQL Server and Azure SQL Database feature that restricts which rows a user can access based on a security policy. Rather than controlling access to an entire table, RLS filters data so that users see only the rows they are authorized to view.

For example, a Sales table might contain data for all sales regions:

SalesPerson	Region	Sales
Alice	East	125000
Bob	West	98000
Carol	North	143000
David	South	110000

With RLS enabled:

Alice sees only East region rows.
Bob sees only West region rows.
Regional managers see only their assigned regions.
Executives may see all rows.

The application continues to query the entire table, but SQL Server automatically filters the results.

Why Use Row-Level Security?

Many organizations have users who should share the same tables while viewing different subsets of the data.

Common scenarios include:

Multi-tenant Software-as-a-Service (SaaS) applications
Regional sales reporting
Department-specific HR records
Healthcare systems where providers access only their patients
Educational systems where instructors see only their own students
Financial institutions with branch-specific records

Without RLS, developers often implement filtering within application code. RLS centralizes these security rules inside the database, reducing development effort and improving security.

How Row-Level Security Works

RLS works by attaching a security policy to a table.

When a query executes:

SQL Server identifies the current user.
A predicate function evaluates each row.
Only rows that satisfy the predicate are returned.

This occurs automatically without modifying application queries.

Row-Level Security Architecture

			
Application
      │
      ▼
SELECT * FROM Orders
      │
      ▼
Security Policy
      │
      ▼
Predicate Function
      │
      ▼
Only Authorized Rows Returned

		

The application does not need to include a WHERE clause because SQL Server applies the filtering automatically.

Components of Row-Level Security

RLS consists of three primary components:

1. Predicate Function

A predicate function determines whether a row should be visible.

Typically, this is an inline table-valued function.

Example:

			
CREATE FUNCTION Security.fn_FilterSales
(
    @SalesRegion NVARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS fn_result
WHERE @SalesRegion = USER_NAME();

		

This function allows users to see rows only when the SalesRegion value matches their database user name.

2. Security Policy

The security policy associates the predicate function with a table.

Example:

			
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE
Security.fn_FilterSales(SalesRegion)
ON dbo.Sales
WITH (STATE = ON);

		

Once enabled, every query against the Sales table automatically uses the filter.

3. Protected Table

The protected table contains the actual business data.

Applications continue to issue normal SELECT, UPDATE, DELETE, and MERGE statements while SQL Server enforces the policy.

Types of Security Predicates

SQL Server supports two predicate types.

Filter Predicate

A filter predicate limits which rows users can read.

Example:

			
SELECT *
FROM Sales;

The query returns only rows authorized by the security policy.

This is the most commonly used predicate.

Block Predicate

A block predicate prevents unauthorized modifications.

It can prevent:

INSERT
UPDATE
DELETE

Example:

A user may be allowed to read only West region rows and may also be prevented from inserting East region records.

Block Predicate Types

Block predicates can be applied:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE

This provides fine-grained control over data modifications.

Example: Multi-Tenant Application

Imagine a SaaS application storing customer records.

CustomerID	TenantID	CustomerName
101	TenantA	ABC Company
102	TenantB	XYZ Industries
103	TenantA	Contoso Ltd

Instead of creating separate databases for every customer, one database stores all tenants.

The predicate function filters rows by TenantID so that:

TenantA users see only TenantA records.
TenantB users see only TenantB records.

Applications require no additional filtering logic.

Example: Sales Regions

Sales table:

Employee	Region
Alice	East
Bob	West
Carol	East
David	South

Logged-in user:

EastManager

Predicate:

WHERE Region = USER_NAME()

Result:

Employee	Region
Alice	East
Carol	East

Other regions are invisible.

Creating an RLS Policy

Step 1: Create Schema

CREATE SCHEMA Security;

Step 2: Create Predicate Function

			
CREATE FUNCTION Security.fn_FilterRegion
(
    @Region NVARCHAR(50)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1
WHERE @Region = USER_NAME();

		

Step 3: Create Security Policy

			
CREATE SECURITY POLICY RegionFilter
ADD FILTER PREDICATE
Security.fn_FilterRegion(Region)
ON dbo.Sales
WITH (STATE = ON);

		

The policy immediately begins protecting the table.

Disabling a Security Policy

			
ALTER SECURITY POLICY RegionFilter
WITH (STATE = OFF);

The policy remains defined but no longer filters data.

Re-enabling the Policy

			
ALTER SECURITY POLICY RegionFilter
WITH (STATE = ON);

Dropping a Security Policy

DROP SECURITY POLICY RegionFilter;

Security Context Functions

RLS frequently uses identity functions.

Common examples include:

Function	Purpose
`USER_NAME()`	Current database user
`SUSER_SNAME()`	Login name
`SESSION_CONTEXT()`	Session-specific values
`ORIGINAL_LOGIN()`	Original login before impersonation

These functions allow security decisions based on the current user or application context.

SESSION_CONTEXT()

Many enterprise applications use SESSION_CONTEXT() rather than database usernames.

Example:

			
EXEC sp_set_session_context
@key='TenantID',
@value='TenantA';

Predicate:

			
WHERE
@TenantID =
SESSION_CONTEXT(N'TenantID');

This approach works well in web applications where many users connect using a shared database login.

Benefits of Row-Level Security

Centralized Security

Rules exist inside the database instead of multiple applications.

Transparent to Applications

Applications issue normal SQL statements.

No code changes are typically required.

Consistent Enforcement

Every query is filtered automatically.

Developers cannot accidentally omit security filters.

Simplifies Development

No need to duplicate WHERE clauses throughout application code.

Improved Maintainability

Security policies can be updated without changing application logic.

Limitations

Not a Replacement for Authentication

Users must still authenticate.

RLS determines only which rows are visible.

Does Not Encrypt Data

Use:

Always Encrypted
Transparent Data Encryption (TDE)

when encryption is required.

Does Not Mask Data

Use:

Dynamic Data Masking

when users should see masked values instead of hidden rows.

Predicate Performance

Complex predicate functions can reduce query performance.

Predicate functions should remain efficient.

RLS vs Dynamic Data Masking

Row-Level Security	Dynamic Data Masking
Hides rows	Masks column values
User cannot see unauthorized records	User sees rows but masked data
Controls access to records	Controls visibility of sensitive columns
Based on predicates	Based on masking functions
Often used with DDM	Often combined with RLS

RLS vs Always Encrypted

Row-Level Security	Always Encrypted
Controls visible rows	Encrypts stored values
Server evaluates predicates	Client decrypts data
Data remains readable by authorized users	Database cannot read encrypted values without client-side decryption
Access control	Confidentiality protection

Best Practices

Keep Predicate Functions Simple

Simple predicates improve query performance.

Use SCHEMABINDING

Predicate functions should use:

WITH SCHEMABINDING

This prevents changes that could invalidate the security policy.

Use SESSION_CONTEXT() for Web Applications

This scales better than relying solely on database usernames.

Test with Non-Administrative Accounts

Database administrators often bypass normal security scenarios.

Always validate RLS using standard user accounts.

Combine with Other Security Features

For comprehensive protection, combine RLS with:

Dynamic Data Masking
Always Encrypted
Transparent Data Encryption
Microsoft Entra authentication
Least-privilege permissions
SQL auditing

DP-800 Exam Tips

Candidates should be able to:

Explain the purpose of Row-Level Security.
Differentiate filter predicates from block predicates.
Understand the role of predicate functions and security policies.
Create RLS using inline table-valued functions.
Enable, disable, and drop security policies.
Use USER_NAME(), SUSER_SNAME(), and SESSION_CONTEXT() in predicate functions.
Differentiate RLS from Dynamic Data Masking and Always Encrypted.
Identify common scenarios such as multi-tenant SaaS applications.
Recognize that RLS is transparent to application code.

Practice Exam Questions

Question 1

A company stores sales records for all regions in a single table. Regional managers should view only the rows for their assigned region.

Which SQL Server feature should you implement?

A. Transparent Data Encryption

B. Row-Level Security

C. Dynamic Data Masking

D. Always Encrypted

Answer: B

Explanation: Row-Level Security filters rows based on a security policy so users automatically see only the records they are authorized to access.

Question 2

Which object determines whether a row is visible to a user in Row-Level Security?

A. Security predicate function

B. Database trigger

C. View

D. Stored procedure

Answer: A

Explanation: An inline table-valued predicate function evaluates each row and determines whether it should be returned.

Question 3

Which statement about Row-Level Security is correct?

A. It encrypts rows before storage.

B. It permanently removes unauthorized rows.

C. It automatically filters query results according to a security policy.

D. It masks sensitive column values.

Answer: C

Explanation: RLS evaluates a security policy during query execution and returns only authorized rows without modifying the stored data.

Question 4

Which type of security predicate prevents unauthorized INSERT, UPDATE, or DELETE operations?

A. Filter predicate

B. Access predicate

C. Security predicate

D. Block predicate

Answer: D

Explanation: Block predicates prevent users from performing unauthorized data modifications.

Question 5

Which function is commonly used in web applications to store tenant-specific information for Row-Level Security?

A. CURRENT_USER

B. SESSION_CONTEXT()

C. USER_ID()

D. DB_NAME()

Answer: B

Explanation: SESSION_CONTEXT() stores key-value pairs for the current session, making it ideal for multi-tenant applications.

Question 6

A developer creates the following policy:

			
ADD FILTER PREDICATE
Security.fn_FilterRegion(Region)
ON dbo.Sales;

What is the effect?

A. Rows are encrypted.

B. Columns are masked.

C. Unauthorized rows are automatically filtered from query results.

D. The table becomes read-only.

Answer: C

Explanation: A filter predicate restricts which rows are returned based on the predicate function.

Question 7

Which statement best describes the relationship between applications and Row-Level Security?

A. Applications must include special WHERE clauses.

B. Applications require encryption libraries.

C. Applications typically require no changes because SQL Server applies filtering automatically.

D. Applications cannot use SELECT * statements.

Answer: C

Explanation: RLS is transparent to applications. SQL Server automatically applies the filtering logic defined in the security policy.

Question 8

Which feature is most appropriate when users should see every row but sensitive values should be partially hidden?

A. Row-Level Security

B. Always Encrypted

C. Transparent Data Encryption

D. Dynamic Data Masking

Answer: D

Explanation: Dynamic Data Masking hides sensitive column values while still allowing users to access all authorized rows.

Question 9

Which statement is true regarding Row-Level Security?

A. It replaces authentication.

B. It determines which rows a user can access after authentication.

C. It encrypts the database backup.

D. It compresses tables.

Answer: B

Explanation: Authentication establishes the user’s identity, while RLS determines which rows that authenticated user is allowed to access.

Question 10

Which practice is recommended when designing Row-Level Security policies?

A. Use complex scalar functions to maximize flexibility.

B. Disable SCHEMABINDING to simplify maintenance.

C. Keep predicate functions simple and efficient to minimize performance overhead.

D. Place all filtering logic in application code instead of the database.

Answer: C

Explanation: Efficient predicate functions help reduce the performance impact of Row-Level Security while maintaining centralized, database-enforced access control.

Go to the DP-800 Exam Prep Hub main page

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

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

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

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

What is Dynamic Data Masking?

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

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

For example, the database may contain:

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

A privileged user sees:

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

A non-privileged user may see:

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

The underlying data never changes.

Why Use Dynamic Data Masking?

Organizations frequently store sensitive information such as:

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

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

DDM allows developers to:

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

How Dynamic Data Masking Works

When a user executes a query:

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

The database itself remains unchanged.

Dynamic Data Masking Architecture

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

		

Benefits of Dynamic Data Masking

DDM provides several important advantages.

Easy to Implement

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

No Data Duplication

The original data remains stored only once.

Transparent to Applications

Applications continue issuing the same queries.

No application code changes are required.

Supports Least Privilege

Users receive only the information they need.

Helps Meet Compliance Requirements

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

Dynamic Data Masking vs Encryption

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

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

Supported Masking Functions

SQL Server supports several built-in masking functions.

Default Mask

Masks data according to its data type.

Example:

Original:

John Smith

Masked:

XXXX

Syntax:

MASKED WITH (FUNCTION = 'default()')

Email Mask

Designed specifically for email addresses.

Original:

john.smith@email.com

Masked:

jXXX@XXXX.com

Syntax:

MASKED WITH (FUNCTION = 'email()')

Partial Mask

Reveals part of a string while masking the remainder.

Example:

Original:

555-123-4567

Masked:

XXX-XXX-4567

Syntax:

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

Example:

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

Random Mask

Returns a random value within a specified numeric range.

Example:

Original Salary

Masked

Syntax

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

Useful when exact values should never be exposed.

Creating a Masked Column

Example:

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

		

Adding a Mask to an Existing Column

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

Removing a Mask

			
ALTER TABLE Customers
ALTER COLUMN Email
DROP MASKED;

Granting UNMASK Permission

Privileged users may view actual values.

GRANT UNMASK TO HRManager;

Revoking Permission

REVOKE UNMASK FROM HRManager;

Viewing Mask Definitions

View masking metadata.

			
SELECT *
FROM sys.masked_columns;

Useful during administration and auditing.

DDM with Azure SQL Database

Dynamic Data Masking is fully supported in:

Azure SQL Database
Azure SQL Managed Instance
SQL Server

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

Developers can create masks without writing T-SQL.

Limitations of Dynamic Data Masking

Candidates should understand these limitations.

It Is Not Encryption

Anyone with sufficient permissions can retrieve actual values.

Database Administrators Can View Data

Members of powerful administrative roles can bypass masking.

Cannot Stop Inference Attacks

Users may infer values through repeated queries.

Not Intended for High-Security Scenarios

Highly confidential data should use:

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

Expressions Return Masked Values

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

Best Practices

Mask Only Sensitive Columns

Avoid unnecessary masking.

Combine with Other Security Features

Use together with:

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

Grant UNMASK Sparingly

Only trusted users should receive this permission.

Test Using Non-Privileged Accounts

Always verify what ordinary users actually see.

Audit Sensitive Access

Monitor who receives UNMASK permissions.

Dynamic Data Masking vs Row-Level Security

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

DP-800 Exam Tips

Candidates should be able to:

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

Practice Exam Questions

Question 1

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

Which SQL Server feature best meets this requirement?

A. Transparent Data Encryption

B. Dynamic Data Masking

C. Always Encrypted

D. Data Compression

Answer: B

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

Question 2

Which statement about Dynamic Data Masking is true?

A. It encrypts data stored on disk.

B. It permanently changes stored values.

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

D. It replaces encryption.

Answer: C

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

Question 3

Which masking function is specifically designed for email addresses?

A. partial()

B. random()

C. default()

D. email()

Answer: D

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

Question 4

Which statement best describes the partial() masking function?

A. It encrypts selected characters.

B. It returns random values.

C. It permanently replaces data.

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

Answer: D

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

Question 5

Which permission allows a user to view unmasked data?

A. SELECT

B. CONTROL

C. UNMASK

D. VIEW DEFINITION

Answer: C

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

Question 6

Which system catalog view displays information about masked columns?

A. sys.columns

B. sys.masked_columns

C. sys.tables

D. sys.database_permissions

Answer: B

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

Question 7

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

Which technology should be preferred over Dynamic Data Masking?

A. Always Encrypted

B. Dynamic Data Masking

C. Partial masking

D. Random masking

Answer: A

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

Question 8

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

A. Applications must always be rewritten.

B. DDM requires client-side decryption.

C. Existing queries usually continue to work without modification.

D. Applications cannot access masked tables.

Answer: C

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

Question 9

A developer executes the following statement:

GRANT UNMASK TO SalesManager;

What is the effect?

A. The SalesManager can modify masked columns.

B. The SalesManager can bypass row-level security.

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

D. All users inherit the UNMASK permission.

Answer: C

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

Question 10

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

A. Use only Dynamic Data Masking.

B. Use only Row-Level Security.

C. Use only Transparent Data Encryption.

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

Answer: D

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

Go to the DP-800 Exam Prep Hub main page

Data 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