Tag: Databases

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

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

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

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

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

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

Why Data Encryption Matters

Modern organizations must comply with regulations such as:

GDPR
HIPAA
PCI-DSS
SOC 2
ISO 27001

Encryption protects data against:

Database theft
Unauthorized administrators
Lost backups
Insider threats
Network interception

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

SQL Server Encryption Technologies

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

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

Data at Rest vs Data in Transit vs Data in Use

A common exam objective is understanding these three states.

Data at Rest

Data stored on:

MDF files
LDF files
Backups
Storage disks

Protected using:

TDE
Column encryption
Always Encrypted

Data in Transit

Data traveling:

Client → SQL Server
SQL Server → Application

Protected using:

TLS (SSL)

Data in Use

Data currently being processed inside memory.

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

Transparent Data Encryption (TDE)

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

TDE encrypts:

Database files
Log files
Backups

Advantages:

No application changes
Easy to enable
Minimal performance overhead

Limitations:

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

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

Column-Level Encryption

Column-level encryption encrypts specific columns inside a table.

Example:

			
CreditCardNumber
SocialSecurityNumber
Salary

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

How Column-Level Encryption Works

SQL Server uses encryption functions such as:

ENCRYPTBYKEY
DECRYPTBYKEY
ENCRYPTBYPASSPHRASE
DECRYPTBYPASSPHRASE

Example:

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

		

Reading data:

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

Encryption Hierarchy

SQL Server uses multiple encryption layers.

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

		

Each level protects the one below it.

Symmetric Encryption

Uses one key for both:

Encryption
Decryption

Advantages

Fast
Efficient
Best for large datasets

Example

			
Encrypt → Key A
Decrypt → Key A

Asymmetric Encryption

Uses:

Public key
Private key

Advantages

Strong security
Digital signatures

Disadvantages

Slower

Usually used to protect symmetric keys.

Certificates

Certificates often protect symmetric keys.

Example:

			
Certificate
↓
Protects Symmetric Key
↓
Encrypts Customer Data

		

Always Encrypted

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

Unlike traditional encryption:

SQL Server never sees the plaintext values.

Encryption occurs inside the client application.

Why Always Encrypted Exists

Imagine a database administrator with full access.

With normal encryption:

DBA can decrypt data.

With Always Encrypted:

DBA cannot read encrypted values.

Only authorized client applications possess the encryption keys.

How Always Encrypted Works

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

		

SQL Server never performs decryption.

Benefits

Protects against:

Curious administrators
Database theft
Backup theft
Cloud administrators
Insider attacks

Key Components

Always Encrypted uses two key types.

Column Master Key (CMK)

Stored outside SQL Server.

Examples:

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

Purpose:

Protects Column Encryption Keys.

Column Encryption Key (CEK)

Stored inside SQL Server.

Purpose:

Encrypts actual column values.

Hierarchy:

			
CMK
↓
CEK
↓
Encrypted Data

		

Deterministic Encryption

Always produces the same ciphertext for identical values.

Example

			
"Florida"
↓
A91BCD
"Florida"
↓
A91BCD

		

Advantages

Supports:

Equality searches
Joins
GROUP BY
Indexes

Disadvantages

Repeated values are recognizable.

Randomized Encryption

Produces different ciphertext every time.

Example

			
Florida
↓
A91BCD
Florida
↓
XYZ123

		

Advantages

Maximum security.

Disadvantages

Cannot perform:

Equality comparisons
JOIN
GROUP BY
Index lookups

Deterministic vs Randomized

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

Creating a Column Master Key

Example:

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

		

Creating a Column Encryption Key

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

		

Encrypting a Column

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

		

Secure Enclaves

Always Encrypted originally limited many SQL operations.

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

Benefits:

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

Limitations of Always Encrypted

Developers should understand these limitations.

Not all SQL operations are supported.

Some restrictions include:

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

Client Driver Requirements

Always Encrypted requires supported drivers.

Examples:

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

Client drivers perform:

Encryption
Decryption
Key retrieval

Azure Key Vault Integration

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

Benefits:

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

Performance Considerations

Always Encrypted introduces overhead because:

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

However, it provides much stronger protection than standard encryption.

Best Practices

Microsoft recommends:

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

DP-800 Exam Tips

Be prepared to distinguish:

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

Practice Exam Questions

Question 1

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

A. Transparent Data Encryption (TDE)

B. Dynamic Data Masking

C. Row-Level Security

D. Always Encrypted

Answer: D

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

Question 2

Which key encrypts the actual column data in Always Encrypted?

A. Column Encryption Key

B. Database Master Key

C. Service Master Key

D. Column Master Key

Answer: A

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

Question 3

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

A. Randomized encryption

B. Transparent Data Encryption

C. Deterministic encryption

D. Dynamic Data Masking

Answer: C

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

Question 4

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

A. Always Encrypted

B. Column-Level Encryption

C. Dynamic Data Masking

D. Transparent Data Encryption

Answer: D

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

Question 5

Where is the Column Master Key typically stored?

A. Azure Storage Account

B. SQL Server system database

C. TempDB

D. Azure Key Vault or Windows Certificate Store

Answer: D

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

Question 6

Which encryption method provides the highest confidentiality for sensitive columns?

A. Deterministic encryption

B. Randomized encryption

C. Transparent Data Encryption

D. TLS encryption

Answer: B

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

Question 7

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

A. Column-Level Encryption

B. Transparent Data Encryption

C. Database snapshots

D. Always On Availability Groups

Answer: A

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

Question 8

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

A. Secure Enclaves

B. Dynamic Data Masking

C. PolyBase

D. Stretch Database

Answer: A

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

Question 9

Which data state is protected by TLS encryption?

A. Data at rest

B. Data in transit

C. Data in use

D. Archived data

Answer: B

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

Question 10

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

A. It automatically compresses encrypted data.

B. It encrypts entire databases.

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

D. It eliminates the need for encryption keys.

Answer: C

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

Go to the DP-800 Exam Prep Hub main page

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

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

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement SQL solutions by using AI-assisted tools
      --> Interpret security impact of using AI-assisted tools

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

Introduction

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

For the DP-800 exam, you should understand:

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

Why Security Matters When Using AI-Assisted Tools

Modern AI assistants such as:

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

can dramatically improve developer productivity.

However, they also introduce new risks.

AI systems often process:

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

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

Therefore:

AI should improve developer productivity—not weaken database security.

Primary Security Risks

The exam expects candidates to recognize several categories of risk.

1. Exposure of Sensitive Information

Never include confidential information inside prompts.

Examples include:

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

Bad example:

“Optimize this stored procedure that accesses CustomerCreditCards.”

Better:

Replace confidential objects with generic examples.

			
CustomerTable
OrderTable
SalesTable

instead of production names.

2. Leakage of Intellectual Property

Many organizations consider:

SQL code
stored procedures
business rules
AI models
algorithms
database architecture

to be proprietary.

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

3. AI Hallucinations

AI-generated code may:

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

Example:

AI may suggest:

GRANT CONTROL TO PUBLIC

This is almost never appropriate.

Always validate AI-generated SQL.

4. Insecure Code Generation

AI sometimes generates code that:

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

Example:

Unsafe:

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

Preferred:

sp_executesql

with parameters.

5. Compliance Violations

Many industries have regulations governing data usage.

Examples:

GDPR
HIPAA
PCI DSS
ISO 27001
SOC 2

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

Human Review is Required

One of the most important DP-800 concepts:

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

Every AI-generated recommendation should be reviewed for:

correctness
performance
security
compliance
maintainability

Human approval remains essential.

Secure Prompt Engineering

Prompt engineering also has security implications.

Good prompts avoid exposing sensitive information.

Instead of:

“Here’s our production database schema.”

Use:

“Here’s a simplified example schema.”

Good prompts:

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

Protecting Secrets

Never place secrets into AI prompts.

Examples include:

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

Instead:

<ConnectionString>

<MyAPIKey>

as placeholders.

Protect Customer Data

Sensitive customer information includes:

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

Instead of:

John Smith

Use:

Customer A

Instead of:

4111-1111-1111-1111

Use:

<CardNumber>

AI and Least Privilege

Generated SQL should follow the Principle of Least Privilege.

Avoid:

GRANT CONTROL

Prefer:

GRANT SELECT

GRANT EXECUTE

only when necessary.

AI suggestions should always be reviewed for excessive permissions.

Verify AI-Generated Security Recommendations

AI may recommend:

indexes
permissions
encryption
authentication methods
firewall rules

Always verify recommendations against:

Microsoft documentation
organizational standards
security policies
current SQL Server capabilities

Secure Development Lifecycle (SDL)

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

Typical workflow:

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

AI does not eliminate security reviews.

AI-Generated SQL Must Still Be Tested

Always test:

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

Never deploy AI-generated code without testing.

Microsoft Copilot Security

Microsoft enterprise AI offerings provide important security capabilities.

Examples include:

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

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

Governance of AI Usage

Organizations should establish governance policies that define:

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

Developers should follow organizational AI usage policies.

Common Security Best Practices

When using AI-assisted SQL development:

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

Exam Tips

Know the differences between:

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

DP-800 Exam Tips

Expect scenario-based questions such as:

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

The correct answer almost always favors:

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

Practice Exam Questions

Question 1

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

A. Sample table names

B. Production connection string containing credentials

C. Database version

D. Execution plan summary

Correct Answer: B

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

Question 2

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

A. Full administrative access

B. Principle of Least Privilege

C. Maximum compatibility

D. Public access

Correct Answer: B

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

Question 3

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

A. Deploy it because AI generated it

B. Ignore it

C. Replace it with parameterized SQL

D. Disable indexing

Correct Answer: C

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

Question 4

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

A. Replace customer information with anonymized sample data

B. Include production payment records

C. Upload an entire production database backup

D. Include customer names and addresses

Correct Answer: A

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

Question 5

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

A. AI-generated code is always optimized

B. AI may generate incorrect or insecure code

C. AI always follows organizational standards

D. AI automatically performs penetration testing

Correct Answer: B

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

Question 6

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

A. Encryption keys

B. Customer Social Security numbers

C. Generic sample schema with fictional table names

D. Production API tokens

Correct Answer: C

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

Question 7

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

A. Eliminating peer review

B. Skipping security testing

C. Removing code reviews

D. Performing security validation and testing

Correct Answer: D

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

Question 8

What is the primary purpose of organizational AI governance policies?

A. Increase CPU utilization

B. Define approved and secure use of AI tools

C. Eliminate documentation

D. Replace database administrators

Correct Answer: B

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

Question 9

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

A. Apply the recommendation immediately

B. Replace CONTROL with db_owner

C. Review whether a lower permission satisfies the requirement

D. Disable authentication

Correct Answer: C

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

Question 10

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

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

B. AI eliminates the need for security testing.

C. AI guarantees compliance with regulations.

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

Correct Answer: D

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

Go to the DP-800 Exam Prep Hub main page

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

Implement error handling (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Implement error handling

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

Introduction

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

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

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

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

Why Error Handling Matters

Without proper error handling:

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

Good error handling:

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

Common Types of SQL Errors

Examples include:

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

Example:

SELECT 100 / 0;

Produces:

Divide by zero error encountered.

TRY…CATCH

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

General syntax:

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

		

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

Simple TRY…CATCH Example

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

		

Output:

An error occurred.

Handling Insert Errors

Example:

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

		

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

Retrieving Error Information

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

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

Example:

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

		

ERROR_MESSAGE()

This function returns the descriptive text of the error.

Example:

SELECT ERROR_MESSAGE();

Possible output:

Divide by zero error encountered.

ERROR_NUMBER()

Returns SQL Server’s internal error number.

Example:

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

ERROR_LINE()

Returns the line where the error occurred.

Example:

This simplifies debugging of large stored procedures.

ERROR_PROCEDURE()

Returns the stored procedure that generated the error.

Example:

usp_ProcessOrder

Returns NULL if the error occurred outside a stored procedure.

THROW

THROW is the modern method for raising exceptions.

Syntax:

THROW;

Or:

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

Parameters:

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

Re-Throwing an Error

Inside a CATCH block:

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

		

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

THROW vs RAISERROR

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

Example:

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

		

Equivalent modern syntax:

			
THROW
50001,
'Invalid customer.',
1;

Comparing THROW and RAISERROR

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

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

Transactions and Error Handling

Errors often occur during transactions.

Example:

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

		

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

TRY…CATCH with Transactions

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

		

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

XACT_STATE()

XACT_STATE() determines whether the current transaction is usable.

Possible values:

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

Example:

			
IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;

Why Use XACT_STATE()?

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

Example:

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

This approach is safer than issuing an unconditional ROLLBACK.

SET XACT_ABORT

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

Example:

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

Benefits:

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

Logging Errors

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

Example:

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

		

Benefits include:

Simplified troubleshooting.
Historical analysis.
Compliance and auditing.

Nested TRY…CATCH Blocks

Complex procedures may use nested error handling.

Example:

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

		

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

Errors That Cannot Be Caught

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

Examples include:

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

Error Handling Best Practices

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

10 Practice Exam Questions

Question 1

Which T-SQL construct provides structured exception handling?

A. CASE...WHEN

B. TRY...CATCH

C. IF...ELSE

D. WHILE

Answer: B

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

Question 2

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

A. ERROR_NUMBER()

B. ERROR_MESSAGE()

C. ERROR_STATE()

D. ERROR_LINE()

Answer: B

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

Question 3

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

A. THROW

B. PRINT

C. RETURN

D. GOTO

Answer: A

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

Question 4

What is the purpose of XACT_STATE()?

A. It determines whether indexes are fragmented.

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

C. It displays the current isolation level.

D. It returns the current database compatibility level.

Answer: B

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

Question 5

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

A. 0

B. 1

C. 100

D. -1

Answer: D

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

Question 6

Which function returns the line number where an error occurred?

A. ERROR_PROCEDURE()

B. ERROR_STATE()

C. ERROR_LINE()

D. ERROR_SEVERITY()

Answer: C

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

Question 7

What is the primary benefit of using SET XACT_ABORT ON?

A. It automatically creates savepoints.

B. It automatically commits every transaction.

C. It disables constraint checking.

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

Answer: D

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

Question 8

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

A. ERROR_PROCEDURE()

B. ERROR_LINE()

C. ERROR_MESSAGE()

D. ERROR_NUMBER()

Answer: A

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

Question 9

Which statement about THROW and RAISERROR is correct?

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

B. THROW cannot be used inside a CATCH block.

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

D. THROW does not support custom error messages.

Answer: C

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

Question 10

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

A. To improve index performance.

B. To reduce memory usage.

C. To prevent SQL Server from generating error messages.

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

Answer: D

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

Go to the DP-800 Exam Prep Hub main page

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

Write correlated queries (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write correlated queries

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

Introduction

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

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

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

What Is a Correlated Query?

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

Because of this dependency, the subquery cannot execute independently.

General syntax:

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

		

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

How Correlated Queries Work

Execution occurs in this order:

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

Unlike regular subqueries, correlated queries are evaluated multiple times.

Correlated Query Example

Suppose two tables exist:

Customers

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders

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

Retrieve customers who have placed at least one order.

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

		

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

Result:

CustomerName
Alice
Bob

Charlie is excluded because no matching order exists.

Comparing Correlated and Non-Correlated Queries

Non-Correlated Query

Runs once.

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

		

The subquery is independent.

Correlated Query

Runs once for every outer row.

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

		

The subquery depends on P.ProductID.

EXISTS with Correlated Queries

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

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

Example:

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

		

Benefits:

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

NOT EXISTS

Returns rows where no matching records exist.

Example:

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

		

Result:

Customers without orders.

Correlated Aggregate Query

Correlated queries frequently use aggregate functions.

Example:

Return employees earning above their department average.

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

		

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

Correlated MAX Example

Find employees with the highest salary in each department.

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

		

Correlated MIN Example

Find products with the lowest price within each category.

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

		

Correlated COUNT Example

Return customers who placed more than three orders.

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

		

Correlated SUM Example

Find salespeople whose total sales exceed $100,000.

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

		

Correlated UPDATE

Correlated queries are not limited to SELECT statements.

Example:

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

		

Each product receives its own calculated average.

Correlated DELETE

Example:

Delete customers with no orders.

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

		

Correlated INSERT

Correlated logic can also appear during INSERT operations.

Example:

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

		

Using EXISTS vs IN

Both operators may return similar results.

EXISTS

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

Example:

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

		

IN

Works well for smaller lookup lists.

Example:

			
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
)

		

Correlated Queries vs Joins

Many correlated queries can be rewritten as joins.

Correlated query:

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

		

Equivalent join:

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

		

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

Correlated Queries vs Window Functions

Sometimes a window function is a better solution.

Correlated query:

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

		

Window function:

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

		

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

Performance Considerations

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

Performance depends on:

Number of rows
Index availability
Query complexity
Join selectivity
Execution plan

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

Optimizing Correlated Queries

Best practices include:

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

Common Business Scenarios

Correlated queries are commonly used for:

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

10 Practice Exam Questions

Question 1

What distinguishes a correlated subquery from a regular subquery?

A. It always returns multiple rows.

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

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

D. It cannot contain aggregate functions.

Answer: B

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

Question 2

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

A. LIKE

B. BETWEEN

C. EXISTS

D. UNION

Answer: C

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

Question 3

How many times is a correlated subquery typically evaluated?

A. Once for the entire query.

B. Once per database.

C. Once per table.

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

Answer: D

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

Question 4

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

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

		

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

		

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

		

			
SELECT *
FROM Customers
ORDER BY CustomerID;

Answer: C

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

Question 5

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

A. MAX()

B. MIN()

C. COUNT()

D. AVG()

Answer: D

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

Question 6

Which statement about correlated queries is true?

A. They cannot be used in UPDATE statements.

B. They cannot contain aggregate functions.

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

D. They always perform better than joins.

Answer: C

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

Question 7

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

A. EXISTS automatically creates indexes.

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

C. EXISTS sorts the results automatically.

D. EXISTS returns all matching rows.

Answer: B

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

Question 8

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

A. Temporary tables

B. Triggers

C. Foreign keys

D. Window functions

Answer: D

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

Question 9

Which factor most directly improves the performance of correlated queries?

A. Increasing the database compatibility level

B. Creating indexes on the correlated columns

C. Using larger transaction log files

D. Increasing the database recovery model

Answer: B

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

Question 10

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

A. Displaying all rows from a single table without filtering

B. Sorting products alphabetically

C. Finding the highest-paid employee within each department

D. Creating a new database

Answer: C

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

Go to the DP-800 Exam Prep Hub main page

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

Write queries that include fuzzy string matching functions, such as EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, and JARO_WINKLER_DISTANCE (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write queries that include fuzzy string matching functions, such as EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, and JARO_WINKLER_DISTANCE

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

Introduction

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

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

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

The primary fuzzy matching functions include:

EDIT_DISTANCE()
EDIT_DISTANCE_SIMILARITY()
JARO_WINKLER_DISTANCE()

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

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

What is Fuzzy String Matching?

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

For example:

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

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

Why Fuzzy Matching Matters

Organizations often receive data from multiple sources:

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

These data sources often contain spelling mistakes or inconsistent formatting.

Examples include:

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

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

Understanding Edit Distance

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

The allowed operations are:

Insert a character
Delete a character
Replace a character

Example:

			
CAT
CUT

Only one substitution is required:

A → U

Edit distance = 1

Another example:

			
Microsoft
Microsft

Only one missing letter (“o”).

Edit distance = 1

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

EDIT_DISTANCE()

Purpose

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

Syntax

EDIT_DISTANCE(string1, string2)

Example

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

Output

Example

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

Output

Example

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

Output

A larger number indicates the strings are less similar.

Common Uses of EDIT_DISTANCE()

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

EDIT_DISTANCE_SIMILARITY()

Purpose

Returns a similarity score rather than the number of edits.

Instead of measuring differences, this function measures similarity.

Syntax

			
EDIT_DISTANCE_SIMILARITY(
    string1,
    string2
)

The function returns a percentage-like similarity score.

Higher values indicate greater similarity.

Example

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

Possible output

Example

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

Output

Example

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

Possible output

Interpreting Similarity Scores

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

Developers commonly define thresholds depending on business requirements.

For example:

Similarity >= 90

might be considered an automatic match.

JARO_WINKLER_DISTANCE()

Purpose

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

This algorithm performs particularly well for:

Person names
Company names
City names
Street names

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

Example

			
John
Jon

Very high similarity.

Example

			
Jonathan
Jonathon

High similarity.

Example

			
Smith
Smyth

High similarity.

Syntax

			
JARO_WINKLER_DISTANCE(
    string1,
    string2
)

Example

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

Possible output

0.08

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

Edit Distance vs. Jaro-Winkler

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

Real-World Business Scenarios

Customer Deduplication

			
John Smith
Jon Smith

Likely the same customer.

Product Matching

			
Surface Laptop
Surface Laptp

Typographical error.

Address Matching

			
Main Street
Main St.

Likely identical location.

OCR Cleanup

OCR software may read:

Micr0soft

instead of

Microsoft

Fuzzy matching helps identify the intended value.

AI Output Validation

Large language models occasionally generate slight variations:

SQL Sever

instead of

SQL Server

Fuzzy matching can detect likely errors before data is stored.

AI-Enabled Database Scenarios

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

Examples include:

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

Performance Considerations

Fuzzy matching functions perform more computation than exact string comparisons.

Best practices include:

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

Best Practices

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

Common Exam Tips

Remember these key points for the DP-800 exam:

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

Practice Exam Questions

Question 1

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

A. EDIT_DISTANCE()

B. EDIT_DISTANCE_SIMILARITY()

C. JARO_WINKLER_DISTANCE()

D. LIKE

Answer: A

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

Question 2

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

A. REGEXP_LIKE()

B. JARO_WINKLER_DISTANCE()

C. EDIT_DISTANCE_SIMILARITY()

D. CHARINDEX()

Answer: C

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

Question 3

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

A. EDIT_DISTANCE()

B. PATINDEX()

C. LIKE

D. JARO_WINKLER_DISTANCE()

Answer: D

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

Question 4

What does an EDIT_DISTANCE() value of 0 indicate?

A. The strings are unrelated.

B. One string contains only numbers.

C. The strings are identical.

D. The comparison failed.

Answer: C

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

Question 5

Which scenario is the best candidate for fuzzy string matching?

A. Comparing integer primary keys.

B. Matching customer names entered manually.

C. Sorting dates.

D. Calculating sales totals.

Answer: B

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

Question 6

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

A. It prevents SQL injection.

B. It automatically creates indexes.

C. It reduces computational cost and improves query performance.

D. It guarantees exact matches.

Answer: C

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

Question 7

Which statement about JARO_WINKLER_DISTANCE() is correct?

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

B. It gives additional weight to matching prefixes.

C. It replaces text using regular expressions.

D. It returns the number of character edits.

Answer: B

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

Question 8

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

A. Creating clustered indexes.

B. Encrypting sensitive columns.

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

D. Managing SQL Server backups.

Answer: C

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

Question 9

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

A. The strings are completely different.

B. The strings have five character differences.

C. The comparison failed.

D. The strings are identical.

Answer: D

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

Question 10

Which statement best describes fuzzy string matching?

A. It requires strings to be identical.

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

C. It is designed exclusively for JSON processing.

D. It replaces SQL indexes.

Answer: B

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

Go to the DP-800 Exam Prep Hub main page

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

Write queries that include regular expressions, such as REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write queries that include regular expressions, such as REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE

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

Regular expressions (regex) are powerful pattern-matching expressions used to search, validate, extract, replace, and manipulate text. They have been available in many programming languages for years and are now available in SQL Server 2025 (17.x) Preview and Azure SQL Database through native T-SQL regular expression functions.

For developers, regex significantly simplifies many text-processing tasks that previously required combinations of LIKE, PATINDEX, CHARINDEX, SUBSTRING, REPLACE, and custom T-SQL logic.

For the DP-800: Developing AI-Enabled Database Solutions exam, understanding these functions is increasingly important because AI-enabled applications frequently process:

User prompts
Chat conversations
Log files
Emails
Product descriptions
Documents
JSON data
Metadata
Search indexes

Regular expressions allow SQL Server to efficiently validate, search, and transform this semi-structured text.

What is a Regular Expression?

A regular expression is a sequence of characters that defines a search pattern.

For example:

Pattern	Meaning
`\d`	Any digit
`[A-Z]`	Uppercase letter
`[a-z]`	Lowercase letter
`[A-Za-z]`	Any letter
`.`	Any character
`.*`	Zero or more characters
`+`	One or more occurrences
`?`	Optional occurrence
`^`	Beginning of string
`$`	End of string
`\s`	Whitespace
`\w`	Word character
`[^0-9]`	Anything except digits

Example:

^\d{5}$

Matches exactly five digits.

Examples:

			
12345 ✔
98765 ✔
1234 ✘
123456 ✘
ABCDE ✘

		

SQL Server Regular Expression Functions

The newest T-SQL regular expression functions include:

REGEXP_LIKE()
REGEXP_REPLACE()
REGEXP_SUBSTR()
REGEXP_INSTR()
REGEXP_COUNT()
REGEXP_MATCHES()
REGEXP_SPLIT_TO_TABLE()

Each function serves a different purpose.

REGEXP_LIKE()

Purpose

Tests whether text matches a regular expression.

Syntax

REGEXP_LIKE(expression, pattern)

Example

			
SELECT CustomerEmail
FROM Customers
WHERE REGEXP_LIKE(
    CustomerEmail,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

		

This returns only rows containing valid email addresses.

Common Uses

Validate email addresses
Validate ZIP codes
Validate phone numbers
Validate product codes
Validate license numbers
Check AI-generated output

REGEXP_REPLACE()

Purpose

Replaces matching text.

Syntax

REGEXP_REPLACE(expression, pattern, replacement)

Example

Remove non-numeric characters from a phone number.

			
SELECT REGEXP_REPLACE(
'(555) 123-4567',
'[^0-9]',
''
);

		

Output

5551234567

Example

Replace multiple spaces with one space.

			
SELECT REGEXP_REPLACE(
'John     Smith',
'\s+',
' '
);

		

Output

John Smith

Common Uses

Data cleansing
Standardization
Removing punctuation
Removing HTML tags
Cleaning AI responses

REGEXP_SUBSTR()

Purpose

Returns the first substring that matches a pattern.

Syntax

REGEXP_SUBSTR(expression, pattern)

Example

			
SELECT REGEXP_SUBSTR(
'Invoice #INV-2025-1045',
'INV-[0-9-]+'
);

Output

INV-2025-1045

Useful for extracting:

Invoice numbers
Tracking numbers
Product IDs
URLs
Dates

REGEXP_INSTR()

Purpose

Returns the starting position of a pattern.

Syntax

REGEXP_INSTR(expression, pattern)

Example

			
SELECT REGEXP_INSTR(
'Customer ID: 12345',
'\d+'
);

Output

If no match exists, the function returns 0.

REGEXP_COUNT()

Purpose

Counts how many times a pattern occurs.

Syntax

REGEXP_COUNT(expression, pattern)

Example

			
SELECT REGEXP_COUNT(
'cat dog cat bird cat',
'cat'
);

Output

Useful for:

Counting hashtags
Counting keywords
Counting repeated words
Measuring AI response quality

REGEXP_MATCHES()

Purpose

Returns all substrings that match a pattern.

Unlike REGEXP_SUBSTR(), which returns only the first match, REGEXP_MATCHES() returns every match.

Example

			
SELECT *
FROM REGEXP_MATCHES(
'Phone: 555-1111 Office: 555-2222',
'\d{3}-\d{4}'
);

		

Output

			
555-1111
555-2222

Common uses include:

Finding all phone numbers
Extracting URLs
Extracting hashtags
Finding dates

REGEXP_SPLIT_TO_TABLE()

Purpose

Splits text into rows using a regular expression delimiter.

Example

			
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'SQL,Azure,AI,Python',
','
);

		

Output

Value
SQL
Azure
AI
Python

Example

Split on one or more spaces.

			
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'SQL   Azure   AI',
'\s+'
);

		

Comparing the Functions

Function	Purpose
REGEXP_LIKE()	Test whether text matches a pattern
REGEXP_REPLACE()	Replace matching text
REGEXP_SUBSTR()	Return first matching substring
REGEXP_INSTR()	Return position of first match
REGEXP_COUNT()	Count matches
REGEXP_MATCHES()	Return all matches
REGEXP_SPLIT_TO_TABLE()	Split text into rows

Common Regular Expression Patterns

Email

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

US ZIP Code

^\d{5}$

ZIP+4

^\d{5}-\d{4}$

Phone Number

^\(?\d{3}\)?[- ]?\d{3}[- ]?\d{4}$

GUID

^[0-9A-Fa-f-]{36}$

URL

https?://.*

Integer

^\d+$

Decimal Number

^\d+\.\d+$

AI-Enabled Database Scenarios

Regular expressions are especially valuable when AI applications generate or consume semi-structured text.

Examples include:

Validating AI-generated email addresses
Extracting invoice numbers from chatbot responses
Cleaning OCR text
Removing HTML from generated content
Parsing metadata
Detecting URLs in AI responses
Validating JSON fragments
Finding sensitive information before storage
Identifying product codes
Processing vector search metadata

Performance Considerations

Regular expressions are more computationally expensive than simple string comparisons.

To improve performance:

Use simple patterns whenever possible.
Filter rows before applying regex functions.
Avoid leading wildcards when simpler predicates suffice.
Avoid unnecessarily complex nested expressions.
Consider computed columns for frequently evaluated values.
Benchmark regex queries on large datasets.
Use indexes to reduce the number of rows that require regex evaluation.

Best Practices

Keep patterns simple and readable.
Test regex thoroughly using representative data.
Escape special characters when needed.
Validate user-supplied patterns to prevent errors.
Use anchors (^ and $) when matching an entire string.
Use character classes instead of long OR conditions.
Prefer regex only when simpler string functions cannot meet the requirement.
Document complex expressions for maintainability.
Handle NULL values appropriately.
Monitor performance on large datasets.

Common Exam Tips

For the DP-800 exam, remember:

REGEXP_LIKE() validates or filters text.
REGEXP_REPLACE() modifies text.
REGEXP_SUBSTR() extracts the first match.
REGEXP_INSTR() returns the position of a match.
REGEXP_COUNT() counts pattern occurrences.
REGEXP_MATCHES() returns all matches.
REGEXP_SPLIT_TO_TABLE() converts delimited text into rows.
Regular expressions are ideal for processing semi-structured text used by AI-enabled applications.
Regex offers significantly more flexibility than LIKE and PATINDEX for complex pattern matching.

Practice Exam Questions

Question 1

A developer needs to validate that a column contains only properly formatted email addresses. Which function should be used?

A. REGEXP_REPLACE()

B. REGEXP_LIKE()

C. REGEXP_SUBSTR()

D. REGEXP_COUNT()

Answer: B

Explanation: REGEXP_LIKE() evaluates whether a string matches a regular expression and is the appropriate function for validating email formats.

Question 2

You need to remove all punctuation from customer phone numbers before storing them. Which function is most appropriate?

A. REGEXP_REPLACE()

B. REGEXP_INSTR()

C. REGEXP_MATCHES()

D. REGEXP_COUNT()

Answer: A

Explanation: REGEXP_REPLACE() replaces matching characters or patterns, making it ideal for removing punctuation or formatting characters.

Question 3

A product description contains multiple serial numbers, and you need to return every matching serial number. Which function should you use?

A. REGEXP_SUBSTR()

B. REGEXP_COUNT()

C. REGEXP_MATCHES()

D. REGEXP_LIKE()

Answer: C

Explanation: REGEXP_MATCHES() returns all occurrences that satisfy the specified regular expression rather than just the first match.

Question 4

You need to extract the first invoice number from a block of text. Which function is the best choice?

A. REGEXP_SPLIT_TO_TABLE()

B. REGEXP_INSTR()

C. REGEXP_SUBSTR()

D. REGEXP_REPLACE()

Answer: C

Explanation: REGEXP_SUBSTR() extracts and returns the first substring that matches the specified regular expression.

Question 5

Which function returns the character position where the first match begins?

A. REGEXP_INSTR()

B. REGEXP_COUNT()

C. REGEXP_MATCHES()

D. REGEXP_REPLACE()

Answer: A

Explanation: REGEXP_INSTR() returns the starting position of the first occurrence of a pattern within a string.

Question 6

A developer needs to determine how many times the word “error” appears in a log entry. Which function should be used?

A. REGEXP_MATCHES()

B. REGEXP_COUNT()

C. REGEXP_SUBSTR()

D. REGEXP_LIKE()

Answer: B

Explanation: REGEXP_COUNT() counts the number of occurrences of a pattern within a string.

Question 7

A comma-separated list stored in a column must be converted into one row per value. Which function is designed for this task?

A. REGEXP_REPLACE()

B. REGEXP_INSTR()

C. REGEXP_SPLIT_TO_TABLE()

D. REGEXP_SUBSTR()

Answer: C

Explanation: REGEXP_SPLIT_TO_TABLE() divides a string into multiple rows using a regular expression as the delimiter.

Question 8

Which regular expression pattern matches exactly five digits?

A. \d+

B. ^\d{5}$

C. \d{5,}

D. [0-9]*

Answer: B

Explanation: ^\d{5}$ anchors the match to the beginning and end of the string and requires exactly five digits.

Question 9

Why are regular expressions particularly valuable in AI-enabled database solutions?

A. They automatically train AI models.

B. They replace JSON processing.

C. They eliminate the need for SQL indexes.

D. They efficiently validate, extract, and transform semi-structured text generated by AI systems.

Answer: D

Explanation: AI applications frequently exchange semi-structured text, and regex functions simplify validation, extraction, cleansing, and transformation directly within SQL.

Question 10

When should you prefer regular expressions over simple string functions such as LIKE?

A. For every text comparison.

B. Only when searching numeric columns.

C. When complex pattern matching or text extraction is required.

D. Only when working with JSON data.

Answer: C

Explanation: Regular expressions are best suited for sophisticated pattern matching, validation, and extraction tasks that cannot be easily implemented using simpler string functions.

Go to the DP-800 Exam Prep Hub main page

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

Write queries that include JSON functions (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write queries that include JSON functions

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

JSON (JavaScript Object Notation) has become one of the most common formats for exchanging and storing structured data in modern applications. SQL Server and Azure SQL Database provide native JSON support that allows developers to parse, query, modify, and generate JSON data without requiring a separate document database.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand how to use T-SQL JSON functions to work with JSON documents stored in SQL Server tables or received from external applications and services. JSON capabilities are particularly valuable when integrating relational databases with REST APIs, cloud services, AI applications, and modern web applications.

Unlike XML, SQL Server does not have a dedicated JSON data type (in generally available releases covered by the current DP-800 learning content). Instead, JSON documents are typically stored in nvarchar columns and processed using built-in JSON functions.

This article covers the JSON functionality emphasized in the current Microsoft Learn curriculum, including:

Understanding JSON in SQL Server
Validating JSON documents
Extracting scalar values
Extracting objects and arrays
Parsing JSON into relational rows
Modifying JSON documents
Returning JSON from queries
Performance considerations
AI-enabled database scenarios
Best practices

Understanding JSON in SQL Server

JSON represents data as key-value pairs and arrays.

Example JSON document:

			
{
  "CustomerID": 1001,
  "Name": "John Smith",
  "Email": "john@contoso.com",
  "Orders": [
    {
      "OrderID": 501,
      "Amount": 250.00
    },
    {
      "OrderID": 502,
      "Amount": 120.00
    }
  ]
}

		

SQL Server stores JSON as plain text but provides functions that understand the JSON structure.

JSON Support in SQL Server

The primary JSON features include:

ISJSON()
JSON_VALUE()
JSON_QUERY()
JSON_MODIFY()
OPENJSON
FOR JSON

These functions allow developers to:

Validate JSON
Retrieve values
Retrieve arrays and objects
Update JSON documents
Convert JSON into relational tables
Generate JSON output

ISJSON()

ISJSON() determines whether a string contains valid JSON.

Syntax:

ISJSON(expression)

Example:

SELECT ISJSON('{"Name":"John"}');

Result:

Invalid JSON returns:

Common use cases include:

Data validation
Import validation
Preventing malformed JSON from entering the database

JSON_VALUE()

JSON_VALUE() extracts a single scalar value from a JSON document.

Syntax:

JSON_VALUE(expression, path)

Example:

			
SELECT JSON_VALUE(
'{
   "Customer":
   {
      "Name":"John Smith"
   }
}',
'$.Customer.Name');

		

Result:

John Smith

JSON_VALUE() returns values such as:

Strings
Numbers
Dates
Booleans

It does not return JSON objects or arrays.

JSON Path Expressions

JSON functions use path expressions to locate data.

Examples:

Path	Meaning
`$`	Root object
`$.Customer`	Customer object
`$.Customer.Name`	Name property
`$.Orders[0]`	First order
`$.Orders[1].Amount`	Amount of second order

Understanding JSON path syntax is an important DP-800 exam objective.

JSON_QUERY()

JSON_QUERY() extracts an object or an array instead of a scalar value.

Example:

			
SELECT JSON_QUERY(
'{
   "Orders":
   [
      {"OrderID":1},
      {"OrderID":2}
   ]
}',
'$.Orders');

		

Result:

			
[
   {"OrderID":1},
   {"OrderID":2}
]

Use JSON_QUERY() whenever the requested value is another JSON object or array.

JSON_VALUE() vs. JSON_QUERY()

JSON_VALUE()	JSON_QUERY()
Returns a scalar value	Returns an object or array
Returns text	Returns JSON
Used for individual properties	Used for nested objects and arrays

Choosing the correct function is a common exam topic.

OPENJSON

OPENJSON converts JSON into relational rows and columns.

Example:

			
DECLARE @Orders nvarchar(max) =
'[
    {"OrderID":101,"Amount":150},
    {"OrderID":102,"Amount":250}
]';
SELECT *
FROM OPENJSON(@Orders);

		

Result:

Key	Value	Type
0	{…}	5
1	{…}	5

OPENJSON WITH Clause

The WITH clause maps JSON properties to columns.

Example:

			
DECLARE @Orders nvarchar(max) =
'[
    {"OrderID":101,"Amount":150},
    {"OrderID":102,"Amount":250}
]';
SELECT *
FROM OPENJSON(@Orders)
WITH
(
    OrderID int,
    Amount decimal(10,2)
);

		

Result:

OrderID	Amount
101	150.00
102	250.00

This is the preferred method when importing structured JSON into SQL tables.

JSON_MODIFY()

JSON_MODIFY() updates a JSON document.

Example:

			
DECLARE @Customer nvarchar(max)=
'{"Name":"John","City":"Seattle"}';
SELECT JSON_MODIFY(
@Customer,
'$.City',
'Orlando');

		

Result:

			
{
   "Name":"John",
   "City":"Orlando"
}

JSON_MODIFY() can:

Update values
Insert properties
Delete properties by assigning NULL

FOR JSON

FOR JSON converts SQL query results into JSON.

Example:

			
SELECT
    CustomerID,
    Name
FROM Customers
FOR JSON AUTO;

		

Output:

			
[
  {
      "CustomerID":1,
      "Name":"John"
  },
  {
      "CustomerID":2,
      "Name":"Mary"
  }
]

		

FOR JSON AUTO vs. FOR JSON PATH

FOR JSON AUTO

Automatically generates JSON based on table structure.

Example:

			
SELECT CustomerID, Name
FROM Customers
FOR JSON AUTO;

Little customization is available.

FOR JSON PATH

Provides complete control over the generated JSON structure.

Example:

			
SELECT
CustomerID AS "Customer.ID",
Name AS "Customer.Name"
FROM Customers
FOR JSON PATH;

		

This allows nested objects and custom property names.

Working with Nested JSON

Example:

			
{
   "Customer":
   {
      "Name":"John",
      "Address":
      {
         "City":"Orlando"
      }
   }
}

		

Retrieve the city:

			
SELECT JSON_VALUE(
@Customer,
'$.Customer.Address.City');

Loading JSON into Tables

Example:

			
INSERT INTO Orders(OrderID, Amount)
SELECT OrderID, Amount
FROM OPENJSON(@Orders)
WITH
(
    OrderID int,
    Amount decimal(10,2)
);

		

This approach is frequently used when consuming REST APIs.

Returning JSON from Stored Procedures

Stored procedures often return JSON to client applications.

Example:

			
SELECT *
FROM Customers
FOR JSON PATH;

Applications can consume the JSON without additional transformation.

JSON and Azure Services

JSON is widely used with:

Azure Functions
Azure Logic Apps
Azure App Service
Azure API Management
REST APIs
Power Apps
Power Automate

JSON enables efficient communication between SQL databases and cloud-based applications.

AI-Enabled Database Scenarios

JSON plays a significant role in AI-enabled solutions because many AI services exchange information using JSON documents.

Common scenarios include:

Receiving prompts from client applications
Storing AI model responses
Logging chatbot conversations
Storing document metadata
Integrating Azure AI services
Consuming REST APIs
Passing structured data to Retrieval-Augmented Generation (RAG) pipelines
Returning AI-generated content to applications

For example, a SQL stored procedure might accept a JSON request from an application, extract values with JSON_VALUE() or OPENJSON, query relational data, and return results as JSON using FOR JSON PATH.

Emerging JSON Functions (SQL Server 2025 and Azure SQL Database)

Recent versions of Azure SQL Database and SQL Server introduce additional JSON functions that make it easier to construct, aggregate, and search JSON data directly within SQL queries. While these functions are newer than the core JSON functions covered earlier, they represent the future direction of SQL Server’s native JSON capabilities and are useful to understand.

These functions include:

JSON_OBJECT()
JSON_ARRAY()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
JSON_CONTAINS()

JSON_OBJECT()

JSON_OBJECT() creates a JSON object directly from key-value pairs.

Instead of manually concatenating strings, SQL Server automatically generates properly formatted JSON.

Syntax:

			
JSON_OBJECT(
    'key1': value1,
    'key2': value2
)

Example:

			
SELECT JSON_OBJECT(
    'CustomerID': CustomerID,
    'Name': CustomerName,
    'City': City
)
FROM Customers;

		

Possible output:

			
{
    "CustomerID": 101,
    "Name": "John Smith",
    "City": "Seattle"
}

		

Benefits

Simpler than string concatenation
Automatically escapes special characters
Produces valid JSON
Easier to read and maintain

JSON_ARRAY()

JSON_ARRAY() creates a JSON array from one or more values.

Syntax:

JSON_ARRAY(value1, value2, value3)

Example:

			
SELECT JSON_ARRAY(
    'SQL',
    'Azure',
    'AI',
    'JSON'
);

		

Output:

			
[
    "SQL",
    "Azure",
    "AI",
    "JSON"
]

		

Arrays may contain:

Strings
Numbers
Boolean values
NULL values
Nested JSON objects

This function is particularly useful when returning lists to applications and APIs.

JSON_ARRAYAGG()

JSON_ARRAYAGG() aggregates multiple rows into a single JSON array.

It performs a role similar to STRING_AGG(), but returns properly formatted JSON instead of plain text.

Example:

			
SELECT JSON_ARRAYAGG(CustomerName)
FROM Customers;

Output:

			
[
    "John",
    "Mary",
    "Susan",
    "David"
]

		

It can also aggregate JSON objects.

Example:

			
SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'ID': CustomerID,
        'Name': CustomerName
    )
)
FROM Customers;

		

Output:

			
[
    {
        "ID":101,
        "Name":"John"
    },
    {
        "ID":102,
        "Name":"Mary"
    }
]

		

Common Uses

REST API responses
AI service payloads
Returning collections of objects
Building hierarchical JSON documents

JSON_OBJECTAGG()

JSON_OBJECTAGG() aggregates multiple rows into a single JSON object.

Each row contributes a key-value pair.

Example:

			
SELECT JSON_OBJECTAGG(
    DepartmentName : EmployeeCount
)
FROM DepartmentSummary;

Possible output:

			
{
    "Sales":42,
    "Finance":18,
    "HR":11
}

		

This function is useful when applications require lookup-style JSON objects rather than arrays.

Common scenarios include:

Configuration settings
Summary statistics
Name/value collections
Metadata dictionaries

JSON_CONTAINS()

JSON_CONTAINS() determines whether a JSON document contains a specified value or object.

Example:

			
SELECT JSON_CONTAINS(
    '{"Skills":["SQL","Azure","AI"]}',
    '"Azure"',
    '$.Skills'
);

		

Result:

A return value of:

1 indicates the value exists.
0 indicates it does not exist.

Unlike JSON_VALUE(), which retrieves a value, JSON_CONTAINS() is intended for searching JSON documents.

Typical uses include:

Searching arrays
Validating configuration values
Checking permissions stored as JSON
Verifying tags or categories
Filtering semi-structured data

Comparing the JSON Functions

Function	Purpose	Returns
ISJSON()	Validate JSON	Integer
JSON_VALUE()	Retrieve a scalar value	Scalar
JSON_QUERY()	Retrieve an object or array	JSON
JSON_MODIFY()	Update JSON	JSON
OPENJSON	Convert JSON to rows	Table
FOR JSON	Generate JSON from query results	JSON
JSON_OBJECT()	Create a JSON object	JSON
JSON_ARRAY()	Create a JSON array	JSON
JSON_ARRAYAGG()	Aggregate rows into a JSON array	JSON
JSON_OBJECTAGG()	Aggregate rows into a JSON object	JSON
JSON_CONTAINS()	Test whether JSON contains a value	Boolean (1/0)

AI-Enabled Database Scenarios

These newer JSON functions are especially useful in AI-enabled database solutions because AI applications frequently exchange complex JSON payloads.

Examples include:

Creating structured prompts for large language models (LLMs)
Returning Retrieval-Augmented Generation (RAG) results as JSON arrays
Building JSON responses for Azure AI Foundry or Azure OpenAI applications
Aggregating search results into JSON collections for APIs
Constructing metadata objects for vector search and embeddings
Verifying whether AI-generated JSON responses contain required fields or values

By generating JSON natively within SQL Server, these functions reduce the need for application-side serialization and simplify integrations with cloud services and AI workflows.

Performance Considerations

Because JSON is stored as text, SQL Server must parse the document during queries.

Performance can be improved by:

Storing only necessary JSON data
Using computed columns that extract frequently queried properties
Creating indexes on persisted computed columns
Avoiding repeated parsing of large JSON documents
Using OPENJSON with a WITH clause for structured imports

Best Practices

Validate incoming JSON using ISJSON().
Use JSON_VALUE() for scalar values.
Use JSON_QUERY() for arrays and objects.
Use OPENJSON to convert JSON into relational rows.
Use JSON_MODIFY() to update JSON documents.
Use FOR JSON PATH when customized output is required.
Store JSON only when relational columns are not appropriate.
Index frequently queried JSON properties through computed columns.
Validate JSON path expressions during development.
Keep JSON documents reasonably sized to improve performance.

Common Exam Tips

For the DP-800 exam, remember the following:

SQL Server stores JSON in nvarchar columns.
ISJSON() validates JSON.
JSON_VALUE() returns scalar values.
JSON_QUERY() returns objects and arrays.
JSON_MODIFY() updates JSON documents.
OPENJSON converts JSON into relational rows.
OPENJSON WITH maps JSON properties to typed columns.
FOR JSON AUTO automatically formats query results.
FOR JSON PATH provides greater control over the JSON output.
JSON is commonly used when integrating SQL Server with cloud services, APIs, and AI applications.

Practice Exam Questions

Question 1

Which function validates whether a string contains properly formatted JSON?

A. JSON_QUERY()

B. JSON_MODIFY()

C. OPENJSON

D. ISJSON()

Answer: D

Explanation: ISJSON() returns 1 for valid JSON and 0 for invalid JSON, making it useful for validating incoming data.

Question 2

Which function should you use to extract a single scalar value such as a customer’s name from a JSON document?

A. JSON_QUERY()

B. JSON_VALUE()

C. OPENJSON()

D. FOR JSON

Answer: B

Explanation: JSON_VALUE() returns a single scalar value such as a string, number, or Boolean from a specified JSON path.

Question 3

A developer needs to return an entire JSON array from a document. Which function is appropriate?

A. JSON_QUERY()

B. JSON_VALUE()

C. ISJSON()

D. JSON_MODIFY()

Answer: A

Explanation: JSON_QUERY() returns JSON objects and arrays, whereas JSON_VALUE() returns only scalar values.

Question 4

Which T-SQL feature converts JSON data into relational rows and columns?

A. JSON_VALUE()

B. JSON_QUERY()

C. OPENJSON

D. FOR JSON PATH

Answer: C

Explanation: OPENJSON parses JSON text and returns rows that can be further mapped into relational columns using the WITH clause.

Question 5

Which statement about FOR JSON PATH is correct?

A. It validates JSON documents.

B. It converts JSON into relational tables.

C. It provides control over the structure of generated JSON output.

D. It can only return scalar values.

Answer: C

Explanation: FOR JSON PATH allows developers to customize property names and create nested JSON structures.

Question 6

What is the primary purpose of JSON_MODIFY()?

A. Validate JSON syntax.

B. Retrieve a scalar value.

C. Return an array.

D. Update or insert values within a JSON document.

Answer: D

Explanation: JSON_MODIFY() changes JSON content by updating, inserting, or deleting properties.

Question 7

When importing data from a REST API into SQL Server, which approach provides the most structured mapping between JSON properties and SQL columns?

A. JSON_QUERY()

B. OPENJSON with a WITH clause

C. ISJSON()

D. FOR JSON AUTO

Answer: B

Explanation: The WITH clause allows OPENJSON to map JSON properties directly into strongly typed SQL columns.

Question 8

Which JSON path expression returns the value of the Name property within the Customer object?

A. $.Name.Customer

B. Customer.Name

C. $.Customer.Name

D. $[Customer][Name]

Answer: C

Explanation: JSON path expressions begin at the root ($) and navigate through object properties using dot notation.

Question 9

Why are computed columns often used with JSON data?

A. They convert JSON into XML.

B. They eliminate the need for JSON functions.

C. They allow frequently accessed JSON values to be indexed for improved query performance.

D. They automatically validate JSON syntax.

Answer: C

Explanation: Persisted computed columns can extract JSON properties using JSON_VALUE(), enabling indexes to improve query performance.

Question 10

How are SQL Server JSON functions commonly used in AI-enabled database solutions?

A. They replace relational tables entirely.

B. They create machine learning models directly.

C. They eliminate the need for APIs.

D. They parse, transform, and generate structured JSON exchanged between SQL databases, AI services, REST APIs, and applications.

Answer: D

Explanation: AI services commonly exchange structured JSON payloads. SQL Server JSON functions enable applications to consume, transform, store, and return this data efficiently.

Go to the DP-800 Exam Prep Hub main page