Tag: SQL

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

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

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

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

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

Introduction

GitHub Copilot is an AI-powered coding assistant that generates code, explains existing code, creates documentation, and assists with debugging. While Copilot is powerful out of the box, organizations often need the AI to follow company-specific standards instead of producing generic code.

GitHub Copilot instruction files provide persistent guidance to Copilot. Rather than repeatedly telling Copilot the same preferences during every chat session, developers can store instructions in version-controlled files inside the repository. They help ensure that AI-generated code follows an organization’s coding standards, security requirements, architectural patterns, naming conventions, and SQL development best practices. Candidates should understand not only how to create these files, but also how they influence Copilot’s responses.

Instruction files improve:

Consistency
Security
Coding standards
SQL development practices
Documentation quality
Team collaboration
AI response quality

For the DP-800 exam, understand:

What instruction files are
Where they are stored
What types of instructions they contain
How they affect Copilot responses
Best practices for SQL development

Why Use Instruction Files?

Without instruction files:

			
Developer:
Create a stored procedure.
Copilot:
Creates one using SELECT * and no error handling.

Next time:

			
Developer:
Remember to avoid SELECT *
Use TRY...CATCH
Use PascalCase
Include comments
Use parameters

		

The developer must continually repeat instructions.

With instruction files:

			
Repository contains instructions.
Copilot automatically follows them.

Every developer receives consistent AI assistance.

What Are GitHub Copilot Instruction Files?

Instruction files are Markdown files that contain natural-language guidance for Copilot.

They describe:

Coding style
Naming conventions
Architecture
Security practices
SQL standards
Documentation requirements
Testing expectations

Instead of writing prompts repeatedly, the repository permanently stores the instructions.

Benefits

Instruction files provide:

Consistency

Every developer receives similar AI suggestions.

Faster Development

Less prompt engineering.

Developers spend less time explaining requirements.

Higher Code Quality

Instructions encourage:

Proper formatting
Secure coding
Error handling
Documentation

Better Security

Organizations can require Copilot to:

Parameterize SQL
Avoid dynamic SQL
Validate input
Follow least privilege

Team Standards

New developers immediately receive guidance that matches experienced developers.

Repository-Level Instructions

Instruction files are stored with the project.

Example:

			
Repository
├── .github
│      copilot-instructions.md
│
├── Database
├── Procedures
├── Functions
└── Tables

		

The instructions become part of source control.

Everyone cloning the repository receives them.

What Can Instruction Files Contain?

Common guidance includes:

Coding conventions

Example

			
Use PascalCase for object names.
Avoid abbreviations.
Use descriptive variable names.

SQL Standards

Example

			
Never use SELECT *
Always qualify object names.
Always use schema prefixes.
Prefer explicit column lists.

Error Handling

Example

			
Always wrap stored procedures inside TRY...CATCH.
Log errors before rethrowing.

Documentation

Example

			
Document all procedures.
Include parameter descriptions.
Explain business rules.

Performance

Example

			
Avoid cursors.
Prefer set-based operations.
Use appropriate indexing.
Avoid unnecessary temp tables.

Security

Example

			
Always use parameterized queries.
Never concatenate SQL strings.
Validate inputs.
Follow least privilege.

SQL Example

Instruction:

			
Use schema dbo.
Always include SET NOCOUNT ON.
Use TRY...CATCH.
Document parameters.
Never use SELECT *.

		

Prompt:

Create a procedure to retrieve customers.

Generated procedure might include:

			
CREATE PROCEDURE dbo.GetCustomers
(
    @Country NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT
CustomerID,
CustomerName,
Country
FROM dbo.Customers
WHERE Country=@Country;
END TRY
BEGIN CATCH
THROW;
END CATCH
END;

		

The instructions influence the generated output.

SQL Development Standards Commonly Included

Organizations commonly include instructions such as:

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

Database Naming Standards

Instruction files frequently define naming conventions.

Example

Tables

			
SalesOrders
Customers
Invoices

Procedures

			
usp_GetOrders
usp_InsertCustomer

Views

vwCustomerSales

Functions

fnCalculateTax

Documentation Standards

Instructions often require:

Every procedure includes:

Purpose
Parameters
Return values
Modification history

Example

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

		

Security Guidance

Instruction files often include security rules.

Examples:

Do not:

SELECT *

Do not:

EXEC(@SQL)

Do:

sp_executesql

Do:

Parameterized queries

Require:

Least privilege
Input validation
Data masking awareness
Sensitive data handling

Performance Guidance

Example instructions:

Prefer:

Set-based operations
Appropriate indexes
EXISTS
Window functions

Avoid:

Nested cursors
RBAR processing
Unnecessary DISTINCT
Scalar UDFs inside large queries

AI Prompt Consistency

Instead of writing:

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

		

Simply write:

Generate a procedure.

Copilot automatically follows repository guidance.

Version Control Benefits

Instruction files are version controlled.

Benefits include:

Change history
Code reviews
Branch support
Rollback capability
Team collaboration

Team Collaboration

Instruction files help ensure:

Developer A

↓

Developer B

↓

Developer C

↓

Copilot

↓

Consistent code

Everyone receives similar recommendations.

Best Practices

Microsoft recommends:

Keep instructions concise.
Focus on project-specific guidance.
Store instruction files with the repository.
Update instructions as standards evolve.
Use clear, natural language.
Include coding, security, testing, and documentation expectations.
Review instruction files during pull requests.
Avoid contradictory instructions.
Combine repository instructions with task-specific prompts when necessary.
Regularly validate that generated code still meets organizational standards.

Common Mistakes

Avoid:

❌ Extremely long instruction files

❌ Conflicting rules

❌ Outdated architecture guidance

❌ Security rules that contradict current policy

❌ Generic instructions that provide little value

❌ Forgetting to update instructions after framework changes

❌ Assuming Copilot always follows instructions perfectly without human review

DP-800 Exam Tips

Candidates should know:

Instruction files provide persistent repository guidance.
They improve consistency across AI-generated code.
They are stored with the project and version controlled.
They can define coding standards, SQL conventions, security requirements, testing expectations, and documentation guidelines.
They reduce repetitive prompting.
They complement, rather than replace, user prompts.
Developers remain responsible for validating all AI-generated code.
Well-written instruction files improve code quality and team productivity.

Summary

GitHub Copilot instruction files are an important mechanism for guiding AI-generated code within a project. By defining repository-specific coding standards, security practices, documentation requirements, and SQL development conventions, organizations can improve consistency, reduce repetitive prompting, and ensure AI-generated code better aligns with business requirements. However, instruction files do not eliminate the need for developer review. AI-generated code should always be validated for correctness, performance, maintainability, and security before deployment.

Practice Exam Questions

Question 1

A development team wants GitHub Copilot to always generate SQL stored procedures that include SET NOCOUNT ON, TRY...CATCH blocks, and schema-qualified object names. What is the best way to accomplish this?

A. Add these requirements to a GitHub Copilot instruction file stored in the repository.

B. Modify SQL Server configuration settings.

C. Configure database compatibility level.

D. Enable Query Store.

Answer: A

Explanation: Repository instruction files provide persistent guidance that GitHub Copilot automatically considers when generating code.

Question 2

What is the primary purpose of a GitHub Copilot instruction file?

A. Improve SQL Server query performance.

B. Define repository-specific guidance that influences AI-generated code.

C. Store database credentials.

D. Configure Azure SQL firewall rules.

Answer: B

Explanation: Instruction files define coding conventions, security requirements, architectural guidance, and other project-specific expectations for Copilot.

Question 3

Which instruction would most directly reduce the likelihood of SQL injection vulnerabilities in AI-generated code?

A. Use uppercase SQL keywords.

B. Always include comments.

C. Always use parameterized queries and avoid dynamic SQL string concatenation.

D. Use table aliases.

Answer: C

Explanation: Parameterized queries are a primary defense against SQL injection attacks.

Question 4

A team updates its SQL naming conventions. What is the best way to ensure GitHub Copilot follows the new standards for all developers?

A. Send an email describing the new conventions.

B. Create a shared prompt document.

C. Ask every developer to memorize the standards.

D. Update the repository’s Copilot instruction file and commit the changes.

Answer: D

Explanation: Version-controlled instruction files distribute updated guidance to everyone working with the repository.

Question 5

Which guidance is most appropriate for inclusion in a GitHub Copilot instruction file?

A. Temporary debugging notes for one developer.

B. Personal keyboard shortcuts.

C. Repository-wide SQL coding standards and documentation requirements.

D. SQL Server service account passwords.

Answer: C

Explanation: Instruction files should contain reusable project guidance, never personal settings or sensitive information.

Question 6

Why are GitHub Copilot instruction files commonly stored in source control?

A. To improve SQL Server indexing.

B. To enable versioning, collaboration, and consistent AI guidance.

C. To reduce database storage.

D. To encrypt SQL scripts.

Answer: B

Explanation: Source control ensures instruction changes are tracked, reviewed, and shared across the team.

Question 7

Which statement about GitHub Copilot instruction files is correct?

A. They eliminate the need to review AI-generated code.

B. They guarantee every generated query is optimized.

C. They replace database security policies.

D. They supplement prompts by providing persistent project-specific guidance.

Answer: D

Explanation: Instruction files enhance Copilot responses but do not replace human review or additional task-specific prompting.

Question 8

A database team wants Copilot to avoid generating SELECT * statements. Where should this requirement be documented?

A. SQL Server Agent.

B. Azure Key Vault.

C. GitHub Copilot instruction file.

D. SQL Profiler.

Answer: C

Explanation: Coding conventions such as avoiding SELECT * are ideal candidates for repository instruction files.

Question 9

Which practice improves the long-term usefulness of GitHub Copilot instruction files?

A. Adding every possible coding preference.

B. Keeping instructions concise, current, and focused on project standards.

C. Storing passwords for easier AI access.

D. Avoiding updates after the initial creation.

Answer: B

Explanation: Effective instruction files are clear, maintainable, and updated as project standards evolve.

Question 10

A developer receives SQL code from GitHub Copilot that follows all repository instruction files. What should the developer do before committing the code?

A. Commit it immediately because instruction files guarantee correctness.

B. Only verify formatting.

C. Disable Copilot.

D. Review the code for correctness, performance, security, and compliance with business requirements.

Answer: D

Explanation: AI-generated code should always undergo human review, testing, and validation, even when instruction files are used.

Go to the DP-800 Exam Prep Hub main page

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

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

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

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

Introduction

The DP-800 exam expects candidates to understand how to enable, configure, and effectively use GitHub Copilot and Microsoft Copilot in Microsoft Fabric to improve SQL development productivity while maintaining security, governance, and responsible AI practices.

Unlike traditional SQL development topics, this objective focuses on using AI-assisted development tools rather than writing SQL syntax itself.

After studying this topic, you should be able to:

Understand the purpose of GitHub Copilot and Microsoft Copilot in Fabric.
Identify licensing and prerequisite requirements.
Enable GitHub Copilot in supported development environments.
Enable Copilot features within Microsoft Fabric.
Understand tenant, capacity, and workspace requirements.
Use AI assistants to generate SQL code.
Use AI to explain, optimize, and troubleshoot SQL.
Understand responsible AI and governance considerations.
Identify security best practices when using AI-assisted development.

What is GitHub Copilot?

GitHub Copilot is an AI-powered coding assistant that helps developers write software by generating code suggestions based on natural language prompts and existing code.

It can:

Generate SQL queries
Create stored procedures
Suggest table definitions
Generate JOIN statements
Explain SQL code
Generate comments and documentation
Help debug errors
Recommend code improvements
Convert natural language into SQL

GitHub Copilot is integrated into popular development environments, including:

Visual Studio
Visual Studio Code
GitHub.com
Azure Data Studio (where supported)
SQL development environments that support Copilot extensions

For DP-800, GitHub Copilot is primarily used to accelerate SQL database development.

What is Microsoft Copilot in Fabric?

Microsoft Copilot in Microsoft Fabric is an AI assistant built directly into the Microsoft Fabric platform.

Rather than only generating code, Fabric Copilot helps users:

Create SQL queries
Build Data Warehouses
Generate notebooks
Explain SQL statements
Create Dataflows
Build reports
Analyze datasets
Summarize data
Generate semantic model calculations
Create pipelines
Produce documentation

For SQL developers, Copilot can assist with creating and refining SQL scripts within Fabric Data Warehouse and SQL analytics experiences.

GitHub Copilot vs. Microsoft Copilot in Fabric

Feature	GitHub Copilot	Microsoft Copilot in Fabric
Primary purpose	AI coding assistant	AI assistant across Fabric workloads
SQL generation	Yes	Yes
Code explanations	Yes	Yes
Natural language prompts	Yes	Yes
Notebook assistance	Limited	Yes
Data Warehouse assistance	Yes	Yes
Power BI integration	No	Yes
Fabric workspace integration	No	Yes
Development IDE integration	Yes	Limited to Fabric experiences

GitHub Copilot Prerequisites

Before GitHub Copilot can be used, developers generally need:

A GitHub account
A GitHub Copilot subscription or enterprise license
A supported IDE (Visual Studio, Visual Studio Code, etc.)
Internet connectivity
Authentication with GitHub

Organizations may centrally manage Copilot licensing through GitHub Enterprise.

Enabling GitHub Copilot in Visual Studio Code

The general process includes:

Install Visual Studio Code.
Sign in to GitHub.
Install the GitHub Copilot extension.
Authenticate your GitHub account.
Verify that your organization permits Copilot usage.
Open a SQL file.
Begin typing or enter a natural language prompt.

Example:

			
-- Create a stored procedure that returns all orders placed during the last 30 days.

Copilot suggests SQL code that can then be reviewed and edited.

Enabling GitHub Copilot in Visual Studio

Visual Studio includes built-in support for GitHub Copilot after the extension is installed.

Developers typically:

Install the GitHub Copilot extension.
Sign in using GitHub credentials.
Enable Copilot in the IDE settings if required.
Open a SQL project.
Accept or reject AI-generated suggestions.

Microsoft Fabric Copilot Requirements

Copilot in Microsoft Fabric requires several prerequisites.

These commonly include:

A Microsoft Fabric tenant
An eligible Fabric capacity that supports Copilot features
Administrator approval for Copilot
Appropriate user licensing
A supported Fabric experience
Access to a Fabric workspace

Not every Fabric environment automatically has Copilot enabled.

Enabling Copilot in Microsoft Fabric

Fabric administrators control whether Copilot features are available within the organization.

Typical steps include:

Open the Fabric Admin Portal.
Navigate to Tenant Settings.
Locate Copilot and AI settings.
Enable Copilot for the organization or selected security groups.
Save configuration changes.
Assign users to workspaces with Copilot-enabled capacities.

Organizations may choose to enable Copilot only for specific departments or security groups.

Workspace Considerations

Users generally require:

Workspace access
Appropriate workspace role
Capacity that supports AI features

Having access to Fabric alone does not guarantee Copilot availability.

Security Permissions

Fabric administrators may control:

Who can use Copilot
Which workspaces allow AI
Which security groups receive access
Which users can create AI-assisted content

This supports governance and compliance requirements.

Using GitHub Copilot for SQL Development

GitHub Copilot can assist with:

Creating Tables

Example prompt:

Create a SQL table for storing customer orders.

Copilot generates a table definition including columns, data types, and constraints.

Generating Stored Procedures

Example prompt:

Create a stored procedure that returns orders by customer.

Copilot generates the T-SQL, which should then be reviewed before deployment.

Creating Functions

Developers can request:

Scalar functions
Table-valued functions
Aggregate calculations
String manipulation
Date calculations

Writing Complex Queries

Copilot can generate:

JOIN statements
CTEs
Window functions
Recursive queries
JSON queries
Graph queries
Regular expression queries
Error handling logic

Using Copilot in Fabric

Fabric Copilot supports natural language interactions.

Example:

Show the top ten customers by total sales during the last fiscal year.

Copilot may generate the corresponding SQL query automatically.

Explaining SQL Code

One valuable feature is code explanation.

Example prompt:

Explain this stored procedure.

Copilot can summarize:

joins
filters
business logic
aggregations
performance considerations

This is especially useful when maintaining legacy SQL code.

Optimizing SQL Queries

Copilot can suggest improvements such as:

adding indexes
eliminating unnecessary scans
simplifying joins
reducing nested queries
replacing cursors
improving readability

However, recommendations should always be validated using execution plans and performance testing.

AI-Assisted Documentation

Developers can use Copilot to generate:

procedure descriptions
function documentation
parameter explanations
inline comments
technical documentation

Good documentation improves maintainability and collaboration.

Responsible AI Considerations

Neither GitHub Copilot nor Fabric Copilot should be considered authoritative.

Developers remain responsible for:

correctness
performance
security
compliance
testing
deployment approval

AI accelerates development but does not replace engineering judgment.

Security Best Practices

When using AI assistants:

Never include passwords in prompts.
Do not paste connection strings.
Remove API keys.
Avoid sharing production customer data.
Use anonymized sample data whenever possible.
Review generated SQL for SQL injection vulnerabilities.
Verify permissions follow the Principle of Least Privilege.
Follow organizational AI governance policies.

Common Limitations

AI assistants may:

Generate inefficient SQL.
Hallucinate nonexistent syntax.
Recommend deprecated features.
Omit indexes.
Produce insecure dynamic SQL.
Misinterpret business requirements.

Always validate generated code before using it in production.

GitHub Copilot vs Manual Development

Task	Manual Development	GitHub Copilot
Create SQL	Fully manual	AI-assisted
Write documentation	Manual	AI-generated drafts
Generate stored procedures	Manual	AI-assisted
Explain existing code	Manual analysis	AI explanations
Query optimization suggestions	DBA experience	AI recommendations (review required)
Security validation	Developer responsibility	Developer responsibility

DP-800 Exam Tips

Be familiar with:

GitHub Copilot licensing prerequisites
Supported development environments
Fabric Copilot enablement requirements
Tenant settings that control Copilot
Workspace and capacity requirements
Appropriate use of AI-generated SQL
Responsible AI principles
Security and governance responsibilities
Human review of AI-generated code
Organizational approval for AI usage

Remember:

GitHub Copilot primarily assists developers inside coding environments, while Microsoft Copilot in Fabric provides AI assistance across multiple Fabric workloads, including SQL development, analytics, notebooks, and reporting.

Key Takeaways

GitHub Copilot is an AI-powered coding assistant that accelerates SQL development.
Microsoft Copilot in Fabric provides AI assistance throughout the Microsoft Fabric ecosystem.
Fabric administrators control Copilot availability through tenant settings and capacity configuration.
Developers need appropriate permissions, licensing, and workspace access.
AI-generated SQL should always be reviewed, tested, and validated.
Sensitive information should never be included in AI prompts.
AI improves productivity but does not replace secure software development practices.

Practice Exam Questions

Question 1

A database developer wants to use GitHub Copilot in Visual Studio Code. Which prerequisite is required before Copilot can provide code suggestions?

A. Install the GitHub Copilot extension and authenticate with a licensed GitHub account

B. Enable Microsoft Fabric capacity

C. Create a SQL Server Agent job

D. Install Azure Data Factory

Correct Answer: A

Explanation: GitHub Copilot requires a GitHub account, an appropriate Copilot license, installation of the GitHub Copilot extension, and authentication before AI-powered code suggestions become available.

Question 2

Who typically enables Microsoft Copilot features for an organization using Microsoft Fabric?

A. Every workspace member individually

B. SQL Server service account

C. Fabric administrator through tenant settings

D. Database owner

Correct Answer: C

Explanation: Microsoft Fabric administrators manage Copilot availability through tenant settings and can enable it for the entire organization or selected security groups.

Question 3

Which task is GitHub Copilot best suited to assist with?

A. Replacing SQL Server security auditing

B. Automatically approving production deployments

C. Generating SQL code and stored procedures from natural language prompts

D. Creating Azure subscriptions

Correct Answer: C

Explanation: GitHub Copilot is designed to help developers generate, explain, and improve code, including SQL statements, stored procedures, and database objects.

Question 4

A developer asks Copilot to optimize a SQL query. What should the developer do before deploying the suggested code?

A. Assume the generated code is correct

B. Skip performance testing

C. Disable indexes

D. Review, test, and validate the generated SQL

Correct Answer: D

Explanation: AI-generated code should always undergo testing, performance evaluation, security review, and validation before being used in production.

Question 5

Which Microsoft Fabric requirement is commonly necessary for users to access Copilot features?

A. Workspace access and a Copilot-supported Fabric capacity

B. SQL Server Express Edition

C. Windows Server Failover Clustering

D. SQL Server Agent enabled

Correct Answer: A

Explanation: Users generally require access to a Fabric workspace that resides on a capacity supporting Copilot features, along with the necessary permissions.

Question 6

What is an appropriate use of Microsoft Copilot in Fabric?

A. Automatically bypassing security reviews

B. Generating SQL queries from natural language requests

C. Granting database administrator privileges

D. Disabling tenant governance

Correct Answer: B

Explanation: Fabric Copilot can translate natural language requests into SQL queries and assist with other Fabric workloads, but it does not replace security or governance processes.

Question 7

Which statement best describes the relationship between GitHub Copilot and Microsoft Copilot in Fabric?

A. They perform exactly the same functions in every environment.

B. GitHub Copilot only works with Power BI.

C. Fabric Copilot replaces all integrated development environments.

D. GitHub Copilot primarily assists with coding, while Fabric Copilot assists across multiple Microsoft Fabric experiences.

Correct Answer: D

Explanation: GitHub Copilot focuses on AI-assisted software development within supported IDEs, whereas Fabric Copilot provides AI capabilities across data engineering, analytics, warehousing, notebooks, reporting, and SQL experiences.

Question 8

Which information should never be included in an AI prompt when requesting SQL assistance?

A. Sample table names

B. General business requirements

C. Production passwords and connection strings

D. Desired query output

Correct Answer: C

Explanation: Sensitive information such as passwords, connection strings, API keys, and confidential customer data should never be shared with AI tools.

Question 9

Which benefit does GitHub Copilot provide during SQL development?

A. It automatically deploys production databases.

B. It generates AI-assisted code suggestions that can improve developer productivity.

C. It permanently replaces code reviews.

D. It guarantees optimal query performance.

Correct Answer: B

Explanation: GitHub Copilot accelerates development by generating code suggestions, but developers remain responsible for testing, reviewing, and validating the generated code.

Question 10

Which statement reflects Microsoft’s recommended approach to AI-assisted database development?

A. AI-generated code should always be deployed without modification.

B. AI eliminates the need for peer reviews.

C. AI-generated code should be treated as a draft that developers validate for correctness, security, and performance.

D. AI guarantees compliance with organizational policies.

Correct Answer: C

Explanation: AI-generated code should be viewed as a productivity aid rather than authoritative output. Developers are responsible for verifying functionality, security, performance, compliance, and adherence to organizational standards before deployment.

Go to the DP-800 Exam Prep Hub main page

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

Implement error handling (DP-800 Exam Prep)

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

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

Introduction

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

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

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

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

Why Error Handling Matters

Without proper error handling:

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

Good error handling:

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

Common Types of SQL Errors

Examples include:

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

Example:

SELECT 100 / 0;

Produces:

Divide by zero error encountered.

TRY…CATCH

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

General syntax:

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

		

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

Simple TRY…CATCH Example

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

		

Output:

An error occurred.

Handling Insert Errors

Example:

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

		

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

Retrieving Error Information

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

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

Example:

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

		

ERROR_MESSAGE()

This function returns the descriptive text of the error.

Example:

SELECT ERROR_MESSAGE();

Possible output:

Divide by zero error encountered.

ERROR_NUMBER()

Returns SQL Server’s internal error number.

Example:

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

ERROR_LINE()

Returns the line where the error occurred.

Example:

This simplifies debugging of large stored procedures.

ERROR_PROCEDURE()

Returns the stored procedure that generated the error.

Example:

usp_ProcessOrder

Returns NULL if the error occurred outside a stored procedure.

THROW

THROW is the modern method for raising exceptions.

Syntax:

THROW;

Or:

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

Parameters:

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

Re-Throwing an Error

Inside a CATCH block:

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

		

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

THROW vs RAISERROR

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

Example:

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

		

Equivalent modern syntax:

			
THROW
50001,
'Invalid customer.',
1;

Comparing THROW and RAISERROR

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

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

Transactions and Error Handling

Errors often occur during transactions.

Example:

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

		

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

TRY…CATCH with Transactions

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

		

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

XACT_STATE()

XACT_STATE() determines whether the current transaction is usable.

Possible values:

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

Example:

			
IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;

Why Use XACT_STATE()?

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

Example:

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

This approach is safer than issuing an unconditional ROLLBACK.

SET XACT_ABORT

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

Example:

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

Benefits:

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

Logging Errors

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

Example:

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

		

Benefits include:

Simplified troubleshooting.
Historical analysis.
Compliance and auditing.

Nested TRY…CATCH Blocks

Complex procedures may use nested error handling.

Example:

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

		

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

Errors That Cannot Be Caught

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

Examples include:

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

Error Handling Best Practices

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

10 Practice Exam Questions

Question 1

Which T-SQL construct provides structured exception handling?

A. CASE...WHEN

B. TRY...CATCH

C. IF...ELSE

D. WHILE

Answer: B

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

Question 2

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

A. ERROR_NUMBER()

B. ERROR_MESSAGE()

C. ERROR_STATE()

D. ERROR_LINE()

Answer: B

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

Question 3

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

A. THROW

B. PRINT

C. RETURN

D. GOTO

Answer: A

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

Question 4

What is the purpose of XACT_STATE()?

A. It determines whether indexes are fragmented.

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

C. It displays the current isolation level.

D. It returns the current database compatibility level.

Answer: B

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

Question 5

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

A. 0

B. 1

C. 100

D. -1

Answer: D

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

Question 6

Which function returns the line number where an error occurred?

A. ERROR_PROCEDURE()

B. ERROR_STATE()

C. ERROR_LINE()

D. ERROR_SEVERITY()

Answer: C

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

Question 7

What is the primary benefit of using SET XACT_ABORT ON?

A. It automatically creates savepoints.

B. It automatically commits every transaction.

C. It disables constraint checking.

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

Answer: D

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

Question 8

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

A. ERROR_PROCEDURE()

B. ERROR_LINE()

C. ERROR_MESSAGE()

D. ERROR_NUMBER()

Answer: A

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

Question 9

Which statement about THROW and RAISERROR is correct?

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

B. THROW cannot be used inside a CATCH block.

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

D. THROW does not support custom error messages.

Answer: C

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

Question 10

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

A. To improve index performance.

B. To reduce memory usage.

C. To prevent SQL Server from generating error messages.

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

Answer: D

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

Go to the DP-800 Exam Prep Hub main page

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

Write correlated queries (DP-800 Exam Prep)

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

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

Introduction

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

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

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

What Is a Correlated Query?

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

Because of this dependency, the subquery cannot execute independently.

General syntax:

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

		

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

How Correlated Queries Work

Execution occurs in this order:

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

Unlike regular subqueries, correlated queries are evaluated multiple times.

Correlated Query Example

Suppose two tables exist:

Customers

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders

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

Retrieve customers who have placed at least one order.

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

		

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

Result:

CustomerName
Alice
Bob

Charlie is excluded because no matching order exists.

Comparing Correlated and Non-Correlated Queries

Non-Correlated Query

Runs once.

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

		

The subquery is independent.

Correlated Query

Runs once for every outer row.

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

		

The subquery depends on P.ProductID.

EXISTS with Correlated Queries

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

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

Example:

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

		

Benefits:

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

NOT EXISTS

Returns rows where no matching records exist.

Example:

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

		

Result:

Customers without orders.

Correlated Aggregate Query

Correlated queries frequently use aggregate functions.

Example:

Return employees earning above their department average.

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

		

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

Correlated MAX Example

Find employees with the highest salary in each department.

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

		

Correlated MIN Example

Find products with the lowest price within each category.

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

		

Correlated COUNT Example

Return customers who placed more than three orders.

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

		

Correlated SUM Example

Find salespeople whose total sales exceed $100,000.

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

		

Correlated UPDATE

Correlated queries are not limited to SELECT statements.

Example:

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

		

Each product receives its own calculated average.

Correlated DELETE

Example:

Delete customers with no orders.

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

		

Correlated INSERT

Correlated logic can also appear during INSERT operations.

Example:

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

		

Using EXISTS vs IN

Both operators may return similar results.

EXISTS

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

Example:

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

		

IN

Works well for smaller lookup lists.

Example:

			
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
)

		

Correlated Queries vs Joins

Many correlated queries can be rewritten as joins.

Correlated query:

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

		

Equivalent join:

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

		

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

Correlated Queries vs Window Functions

Sometimes a window function is a better solution.

Correlated query:

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

		

Window function:

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

		

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

Performance Considerations

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

Performance depends on:

Number of rows
Index availability
Query complexity
Join selectivity
Execution plan

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

Optimizing Correlated Queries

Best practices include:

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

Common Business Scenarios

Correlated queries are commonly used for:

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

10 Practice Exam Questions

Question 1

What distinguishes a correlated subquery from a regular subquery?

A. It always returns multiple rows.

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

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

D. It cannot contain aggregate functions.

Answer: B

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

Question 2

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

A. LIKE

B. BETWEEN

C. EXISTS

D. UNION

Answer: C

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

Question 3

How many times is a correlated subquery typically evaluated?

A. Once for the entire query.

B. Once per database.

C. Once per table.

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

Answer: D

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

Question 4

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

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

		

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

		

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

		

			
SELECT *
FROM Customers
ORDER BY CustomerID;

Answer: C

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

Question 5

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

A. MAX()

B. MIN()

C. COUNT()

D. AVG()

Answer: D

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

Question 6

Which statement about correlated queries is true?

A. They cannot be used in UPDATE statements.

B. They cannot contain aggregate functions.

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

D. They always perform better than joins.

Answer: C

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

Question 7

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

A. EXISTS automatically creates indexes.

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

C. EXISTS sorts the results automatically.

D. EXISTS returns all matching rows.

Answer: B

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

Question 8

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

A. Temporary tables

B. Triggers

C. Foreign keys

D. Window functions

Answer: D

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

Question 9

Which factor most directly improves the performance of correlated queries?

A. Increasing the database compatibility level

B. Creating indexes on the correlated columns

C. Using larger transaction log files

D. Increasing the database recovery model

Answer: B

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

Question 10

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

A. Displaying all rows from a single table without filtering

B. Sorting products alphabetically

C. Finding the highest-paid employee within each department

D. Creating a new database

Answer: C

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

Go to the DP-800 Exam Prep Hub main page

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

Write graph queries that use the MATCH operator (DP-800 Exam Prep)

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

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

Introduction

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

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

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

What Is a Graph Database?

A graph database represents information as:

Nodes – entities or objects
Edges – relationships between entities

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

Example:

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

		

In this example:

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

Graph Database Components

SQL Server graph databases consist of two primary object types:

Object	Purpose
Node Table	Stores entities
Edge Table	Stores relationships

Node Tables

Node tables represent entities.

Examples include:

Employees
Customers
Products
Cities
Departments
Suppliers

Example:

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

		

The AS NODE clause creates a graph node table.

Edge Tables

Edge tables represent relationships between nodes.

Example:

			
CREATE TABLE WorksWith
(
    SinceDate DATE
)
AS EDGE;

		

This table stores the relationship between two Person nodes.

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

Node and Edge Relationships

Suppose the following data exists:

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

Each person exists once in the node table.

Relationships exist separately in edge tables.

Why Use Graph Queries?

Traditional relational queries require joins.

Example:

			
Employee
↓
Manager
↓
Department

		

This often becomes:

			
Employee
JOIN Manager
JOIN Department
JOIN Office
JOIN Region

		

Graph queries simplify relationship traversal.

The MATCH Operator

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

Instead of writing multiple joins, developers specify graph patterns.

General syntax:

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

The pattern describes how nodes are connected.

Basic MATCH Query

Suppose the database contains:

Persons

John
Mary
Susan

Relationship

John → WorksWith → Mary

Query:

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

		

Result:

FullName	FullName
John	Mary

Understanding Graph Pattern Syntax

Example:

p1-(w)->p2

Meaning:

Start with node p1
Traverse edge w
Reach node p2

Arrow direction matters.

Reverse Direction

Example:

p1<-(w)-p2

Meaning:

p2 → p1

The relationship is traversed in the opposite direction.

Multiple Relationships

Suppose:

			
John → Mary
Mary → Susan

Query:

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

The MATCH operator follows multiple hops.

Multi-Hop Queries

Graph databases excel at traversing multiple relationships.

Example:

Find employees connected through two working relationships.

			
Employee
↓
WorksWith
↓
Employee
↓
WorksWith
↓
Employee

		

Without graphs this may require several joins.

With MATCH the relationship path is much easier to express.

Multiple Edge Types

Suppose the graph contains:

			
John
↓
WorksWith
↓
Mary
↓
LivesIn
↓
Seattle

		

Query:

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

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

Using MATCH with SELECT

Example:

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

		

Result

Person	City
John	Seattle
Mary	Orlando

Combining MATCH with WHERE

Additional filtering can be applied.

Example:

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

		

Graph traversal occurs first.

The remaining rows are filtered normally.

MATCH and JOINs

Graph queries can still use relational joins.

Example:

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

		

Graph features integrate with standard SQL.

Graph Queries for AI Applications

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

Examples include:

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

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

Knowledge Graph Example

Suppose an AI application stores:

			
Customer
↓
Purchased
↓
Product
↓
ManufacturedBy
↓
Company

		

The MATCH operator can quickly discover:

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

Fraud Detection

Graph databases are excellent for identifying suspicious relationships.

Example:

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

		

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

Recommendation Engines

Streaming services often recommend content based on relationships.

Example:

			
User
↓
Likes
↓
Movie
↓
DirectedBy
↓
Director

		

Graph queries efficiently discover similar users and related content.

Relationship Discovery

Graph databases make it easy to answer questions such as:

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

These scenarios are ideal for MATCH queries.

Performance Considerations

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

Best practices include:

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

Best Practices

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

Practice Exam Questions

Question 1

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

A. View

B. Node table

C. Edge table

D. Stored procedure

Answer: C

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

Question 2

Which clause is used when creating a graph node table?

AS GRAPH

AS NODE

AS ENTITY

AS OBJECT

Answer: B

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

Question 3

What is the primary purpose of the MATCH operator?

A. Perform full-text searches

B. Compare two strings

C. Traverse graph relationships between nodes

D. Create graph indexes

Answer: C

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

Question 4

In the graph pattern:

p1-(w)->p2

what does the arrow (->) indicate?

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

B. The relationship flows from p2 to p1.

C. The query performs an inner join.

D. The graph contains duplicate nodes.

Answer: A

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

Question 5

Which scenario is best suited for SQL Server graph queries?

A. Calculating monthly payroll totals

B. Traversing employee reporting relationships across multiple organizational levels

C. Sorting sales by date

D. Updating a single customer record

Answer: B

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

Question 6

Which statement about graph queries in SQL Server is true?

A. They cannot be combined with traditional SQL queries.

B. They require a separate graph database engine.

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

D. They replace foreign keys.

Answer: C

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

Question 7

Which of the following is represented by a node table?

A. A relationship between two customers

B. A connection between two products

C. A customer entity

D. A graph traversal path

Answer: C

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

Question 8

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

A. They automatically train language models.

B. They store only vector embeddings.

C. They eliminate the need for SQL queries.

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

Answer: D

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

Question 9

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

A. It encrypts graph data automatically.

B. It simplifies expressing relationship traversal patterns.

C. It automatically creates indexes.

D. It eliminates the need for edge tables.

Answer: B

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

Question 10

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

A. LIKE

B. GROUP BY

C. MERGE

D. MATCH

Answer: D

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

Go to the DP-800 Exam Prep Hub main page

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

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

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

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

Introduction

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

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

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

The primary fuzzy matching functions include:

EDIT_DISTANCE()
EDIT_DISTANCE_SIMILARITY()
JARO_WINKLER_DISTANCE()

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

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

What is Fuzzy String Matching?

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

For example:

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

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

Why Fuzzy Matching Matters

Organizations often receive data from multiple sources:

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

These data sources often contain spelling mistakes or inconsistent formatting.

Examples include:

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

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

Understanding Edit Distance

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

The allowed operations are:

Insert a character
Delete a character
Replace a character

Example:

			
CAT
CUT

Only one substitution is required:

A → U

Edit distance = 1

Another example:

			
Microsoft
Microsft

Only one missing letter (“o”).

Edit distance = 1

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

EDIT_DISTANCE()

Purpose

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

Syntax

EDIT_DISTANCE(string1, string2)

Example

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

Output

Example

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

Output

Example

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

Output

A larger number indicates the strings are less similar.

Common Uses of EDIT_DISTANCE()

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

EDIT_DISTANCE_SIMILARITY()

Purpose

Returns a similarity score rather than the number of edits.

Instead of measuring differences, this function measures similarity.

Syntax

			
EDIT_DISTANCE_SIMILARITY(
    string1,
    string2
)

The function returns a percentage-like similarity score.

Higher values indicate greater similarity.

Example

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

Possible output

Example

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

Output

Example

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

Possible output

Interpreting Similarity Scores

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

Developers commonly define thresholds depending on business requirements.

For example:

Similarity >= 90

might be considered an automatic match.

JARO_WINKLER_DISTANCE()

Purpose

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

This algorithm performs particularly well for:

Person names
Company names
City names
Street names

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

Example

			
John
Jon

Very high similarity.

Example

			
Jonathan
Jonathon

High similarity.

Example

			
Smith
Smyth

High similarity.

Syntax

			
JARO_WINKLER_DISTANCE(
    string1,
    string2
)

Example

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

Possible output

0.08

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

Edit Distance vs. Jaro-Winkler

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

Real-World Business Scenarios

Customer Deduplication

			
John Smith
Jon Smith

Likely the same customer.

Product Matching

			
Surface Laptop
Surface Laptp

Typographical error.

Address Matching

			
Main Street
Main St.

Likely identical location.

OCR Cleanup

OCR software may read:

Micr0soft

instead of

Microsoft

Fuzzy matching helps identify the intended value.

AI Output Validation

Large language models occasionally generate slight variations:

SQL Sever

instead of

SQL Server

Fuzzy matching can detect likely errors before data is stored.

AI-Enabled Database Scenarios

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

Examples include:

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

Performance Considerations

Fuzzy matching functions perform more computation than exact string comparisons.

Best practices include:

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

Best Practices

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

Common Exam Tips

Remember these key points for the DP-800 exam:

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

Practice Exam Questions

Question 1

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

A. EDIT_DISTANCE()

B. EDIT_DISTANCE_SIMILARITY()

C. JARO_WINKLER_DISTANCE()

D. LIKE

Answer: A

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

Question 2

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

A. REGEXP_LIKE()

B. JARO_WINKLER_DISTANCE()

C. EDIT_DISTANCE_SIMILARITY()

D. CHARINDEX()

Answer: C

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

Question 3

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

A. EDIT_DISTANCE()

B. PATINDEX()

C. LIKE

D. JARO_WINKLER_DISTANCE()

Answer: D

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

Question 4

What does an EDIT_DISTANCE() value of 0 indicate?

A. The strings are unrelated.

B. One string contains only numbers.

C. The strings are identical.

D. The comparison failed.

Answer: C

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

Question 5

Which scenario is the best candidate for fuzzy string matching?

A. Comparing integer primary keys.

B. Matching customer names entered manually.

C. Sorting dates.

D. Calculating sales totals.

Answer: B

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

Question 6

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

A. It prevents SQL injection.

B. It automatically creates indexes.

C. It reduces computational cost and improves query performance.

D. It guarantees exact matches.

Answer: C

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

Question 7

Which statement about JARO_WINKLER_DISTANCE() is correct?

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

B. It gives additional weight to matching prefixes.

C. It replaces text using regular expressions.

D. It returns the number of character edits.

Answer: B

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

Question 8

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

A. Creating clustered indexes.

B. Encrypting sensitive columns.

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

D. Managing SQL Server backups.

Answer: C

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

Question 9

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

A. The strings are completely different.

B. The strings have five character differences.

C. The comparison failed.

D. The strings are identical.

Answer: D

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

Question 10

Which statement best describes fuzzy string matching?

A. It requires strings to be identical.

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

C. It is designed exclusively for JSON processing.

D. It replaces SQL indexes.

Answer: B

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

Go to the DP-800 Exam Prep Hub main page

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

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

Write queries that include window 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 window 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

Window functions are among the most powerful features in Transact-SQL (T-SQL). They enable calculations across a set of rows related to the current row without collapsing the results into a single row, as traditional aggregate functions do. Window functions are widely used in reporting, analytics, business intelligence, financial analysis, and AI-enabled database solutions.

Unlike GROUP BY, which returns one row per group, window functions preserve the individual rows while providing additional calculated values based on a defined “window” of rows.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

What window functions are
The OVER clause
Partitioning data
Ordering data within windows
Aggregate window functions
Ranking functions
Offset functions
Window frames
Practical business scenarios
Performance considerations
Best practices

Window functions are heavily tested because they allow developers to perform sophisticated calculations efficiently while maintaining readable and maintainable SQL code.

What Is a Window Function?

A window function performs a calculation across a set of rows that are related to the current row.

Unlike aggregate functions used with GROUP BY, a window function does not reduce the number of rows returned.

General syntax:

			
Function(...) OVER
(
    [PARTITION BY column]
    [ORDER BY column]
)

		

The OVER clause defines the window over which the calculation occurs.

The OVER Clause

The OVER clause is required for window functions.

It can contain:

PARTITION BY
ORDER BY
Window frame definitions (ROWS or RANGE)

Example:

			
SELECT
    EmployeeID,
    Salary,
    AVG(Salary) OVER() AS AverageSalary
FROM HumanResources.Employee;

		

The average salary is calculated across all employees while each employee row remains visible.

PARTITION BY

PARTITION BY divides the result set into logical groups.

Example:

			
SELECT
    DepartmentID,
    EmployeeID,
    Salary,
    AVG(Salary)
        OVER(PARTITION BY DepartmentID)
        AS DepartmentAverage
FROM HumanResources.Employee;

		

Each department receives its own average salary.

ORDER BY Within OVER

The ORDER BY clause defines the order of rows within each partition.

Example:

			
SELECT
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

		

The highest salary receives row number 1.

Aggregate Window Functions

Many aggregate functions can operate as window functions.

Common examples include:

SUM()
AVG()
MIN()
MAX()
COUNT()

Example:

			
SELECT
    CustomerID,
    OrderDate,
    TotalAmount,
    SUM(TotalAmount)
        OVER(PARTITION BY CustomerID)
        AS CustomerTotal
FROM Sales.Orders;

		

Each order row displays the customer’s total sales without grouping the results.

Running Totals

A common use of window functions is calculating running totals.

Example:

			
SELECT
    OrderDate,
    TotalAmount,
    SUM(TotalAmount)
        OVER
        (
            ORDER BY OrderDate
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        )
        AS RunningTotal
FROM Sales.Orders;

		

Each row contains the cumulative total through the current row.

Moving Averages

Window functions simplify moving averages.

Example:

			
SELECT
    OrderDate,
    SalesAmount,
    AVG(SalesAmount)
        OVER
        (
            ORDER BY OrderDate
            ROWS BETWEEN 2 PRECEDING
            AND CURRENT ROW
        )
        AS ThreeDayAverage
FROM Sales.DailySales;

		

This example calculates a rolling average over three rows.

Ranking Functions

SQL Server includes several ranking window functions.

These include:

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()

ROW_NUMBER()

Assigns a unique sequential number.

Example:

			
SELECT
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER(ORDER BY Salary DESC)
        AS RowNum
FROM HumanResources.Employee;

		

Even rows with equal salaries receive different numbers.

RANK()

Assigns rankings while allowing gaps after ties.

Example:

			
SELECT
    EmployeeID,
    Salary,
    RANK()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

		

If two employees tie for first place, the next rank is 3.

DENSE_RANK()

Assigns rankings without gaps.

Example:

			
SELECT
    EmployeeID,
    Salary,
    DENSE_RANK()
        OVER(ORDER BY Salary DESC)
        AS SalaryRank
FROM HumanResources.Employee;

		

If two employees tie for first place, the next rank is 2.

ROW_NUMBER vs. RANK vs. DENSE_RANK

Function	Duplicate Values	Gaps in Ranking
ROW_NUMBER	No	No
RANK	Yes	Yes
DENSE_RANK	Yes	No

Understanding these differences is a common DP-800 exam objective.

NTILE()

NTILE() divides rows into approximately equal groups.

Example:

			
SELECT
    EmployeeID,
    Salary,
    NTILE(4)
        OVER(ORDER BY Salary DESC)
        AS Quartile
FROM HumanResources.Employee;

		

Employees are divided into four salary quartiles.

Offset Functions

Offset functions compare one row to another.

Common functions include:

LAG()
LEAD()

LAG()

Returns a value from a previous row.

Example:

			
SELECT
    OrderDate,
    SalesAmount,
    LAG(SalesAmount)
        OVER(ORDER BY OrderDate)
        AS PreviousDaySales
FROM Sales.DailySales;

		

LEAD()

Returns a value from a following row.

Example:

			
SELECT
    OrderDate,
    SalesAmount,
    LEAD(SalesAmount)
        OVER(ORDER BY OrderDate)
        AS NextDaySales
FROM Sales.DailySales;

		

FIRST_VALUE()

Returns the first value in the window.

Example:

			
SELECT
    EmployeeID,
    Salary,
    FIRST_VALUE(Salary)
        OVER(ORDER BY Salary DESC)
        AS HighestSalary
FROM HumanResources.Employee;

		

LAST_VALUE()

Returns the last value within the current window frame.

Example:

			
SELECT
    EmployeeID,
    Salary,
    LAST_VALUE(Salary)
        OVER
        (
            ORDER BY Salary
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND UNBOUNDED FOLLOWING
        )
        AS HighestSalary
FROM HumanResources.Employee;

		

Because LAST_VALUE() respects the current window frame, explicitly specifying the frame is often necessary to obtain the expected result.

Window Frames

Window frames define which rows participate in a calculation.

Common options include:

CURRENT ROW
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
n PRECEDING
n FOLLOWING

Example:

			
ROWS BETWEEN 5 PRECEDING
AND CURRENT ROW

This frame includes the current row plus the previous five rows.

ROWS vs. RANGE

ROWS	RANGE
Uses physical row positions	Uses logical value ranges
Predictable row counts	May include multiple tied rows
Often preferred for running totals	Useful for value-based calculations

For most reporting scenarios, ROWS provides more predictable behavior.

Combining PARTITION BY and ORDER BY

Example:

			
SELECT
    DepartmentID,
    EmployeeID,
    Salary,
    ROW_NUMBER()
        OVER
        (
            PARTITION BY DepartmentID
            ORDER BY Salary DESC
        )
        AS DepartmentRank
FROM HumanResources.Employee;

		

Ranking restarts for each department.

Practical Business Uses

Window functions are commonly used for:

Sales rankings
Running totals
Financial reporting
Trend analysis
Customer segmentation
Inventory analysis
Employee rankings
Time-series analysis
Rolling averages
Year-over-year comparisons

Performance Considerations

Window functions can require sorting operations.

Performance depends on:

Index design
Partition size
Number of rows
ORDER BY columns
Available memory

To improve performance:

Index columns used in PARTITION BY and ORDER BY.
Avoid unnecessary sorting.
Limit returned rows when appropriate.
Review execution plans.
Consider filtered datasets before applying window functions.

AI-Enabled Database Scenarios

Window functions are valuable for preparing data used by AI applications.

Examples include:

Ranking search results before intelligent retrieval
Identifying the latest customer interactions for Retrieval-Augmented Generation (RAG)
Calculating rolling metrics for machine learning features
Detecting trends in IoT sensor data
Selecting the top-N records for embedding generation
Comparing current values with previous observations using LAG() and LEAD()
Preparing time-series datasets for AI forecasting models

These capabilities help organize and enrich data before it is consumed by AI pipelines.

Best Practices

Always specify an appropriate ORDER BY clause when required.
Use PARTITION BY only when logical grouping is needed.
Understand the differences among ranking functions.
Specify window frames explicitly for running totals and functions such as LAST_VALUE().
Create indexes on frequently partitioned or sorted columns.
Test performance with production-sized datasets.
Avoid unnecessary nested window calculations.
Review execution plans for expensive sorts.

Common Exam Tips

For the DP-800 exam, remember these key points:

Window functions require the OVER clause.
PARTITION BY divides rows into logical groups.
ORDER BY defines the order within each window.
Window functions preserve individual rows.
ROW_NUMBER() always returns unique sequential numbers.
RANK() allows gaps after ties.
DENSE_RANK() does not leave gaps after ties.
LAG() retrieves values from previous rows.
LEAD() retrieves values from subsequent rows.
Running totals commonly use SUM() with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Practice Exam Questions

Question 1

Which clause is required for every SQL Server window function?

A. GROUP BY

B. HAVING

C. OVER

D. PARTITION

Answer: C

Explanation: Every window function must include the OVER clause, which defines the window over which the calculation is performed.

Question 2

What is the primary advantage of a window function over a traditional aggregate function?

A. It always executes faster.

B. It preserves individual rows while performing calculations across related rows.

C. It automatically creates indexes.

D. It eliminates the need for sorting.

Answer: B

Explanation: Unlike aggregate functions with GROUP BY, window functions return calculations while preserving each row in the result set.

Question 3

Which window function assigns a unique sequential number to every row, even when duplicate values exist?

A. RANK()

B. DENSE_RANK()

C. NTILE()

D. ROW_NUMBER()

Answer: D

Explanation: ROW_NUMBER() always assigns unique sequential numbers, regardless of duplicate values.

Question 4

Which ranking function assigns the same rank to tied rows without leaving gaps in subsequent rankings?

A. ROW_NUMBER()

B. NTILE()

C. DENSE_RANK()

D. RANK()

Answer: C

Explanation: DENSE_RANK() assigns the same rank to tied rows and continues with the next consecutive rank without gaps.

Question 5

What is the purpose of the PARTITION BY clause?

A. To permanently divide a table into partitions.

B. To group rows into logical partitions for window function calculations.

C. To sort the final result set.

D. To filter rows before processing.

Answer: B

Explanation: PARTITION BY creates logical groups within the result set so calculations are performed independently for each partition.

Question 6

Which function returns the value from the previous row within the defined window?

A. LEAD()

B. FIRST_VALUE()

C. LAST_VALUE()

D. LAG()

Answer: D

Explanation: LAG() retrieves a value from a preceding row within the same window.

Question 7

A developer needs to calculate a running total ordered by transaction date. Which feature is most appropriate?

A. SUM() with OVER and a window frame

B. GROUP BY

C. DISTINCT

D. UNION

Answer: A

Explanation: Running totals are typically calculated using SUM() with the OVER clause and a window frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Question 8

What is the default behavior of RANK() when multiple rows have the same value?

A. Each tied row receives a different rank.

B. Tied rows receive the same rank, and the next rank contains a gap.

C. Tied rows are ignored.

D. Tied rows receive sequential ranks without gaps.

Answer: B

Explanation: RANK() assigns identical ranks to tied rows and skips the next ranking number accordingly.

Question 9

Which statement about window frames is correct?

A. They are used only with ranking functions.

B. They define which rows participate in a window calculation.

C. They permanently partition a table.

D. They replace the ORDER BY clause.

Answer: B

Explanation: Window frames specify the subset of rows within the window that contribute to the calculation, making them especially useful for running totals and moving averages.

Question 10

How are window functions commonly used in AI-enabled database solutions?

A. They directly generate embeddings from text.

B. They replace vector indexes.

C. They prepare and enrich data by calculating rankings, rolling metrics, and historical comparisons before it is consumed by AI models, intelligent search, or Retrieval-Augmented Generation (RAG) pipelines.

D. They eliminate the need for data preprocessing.

Answer: C

Explanation: Window functions help organize and enrich datasets by calculating analytical metrics, rankings, and trends that serve as valuable inputs to AI workflows and machine learning processes.

Go to the DP-800 Exam Prep Hub main page