Tag: DP-800

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

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

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

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

Introduction

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

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

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

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

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

What Are Object-Level Permissions?

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

For example, one user might:

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

while another user has completely different permissions.

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

Example:

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

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

Common Database Objects That Can Be Secured

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

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

DP-800 focuses primarily on:

Tables
Views
Stored procedures
Functions
Schemas

Permission Hierarchy

Permissions exist at several levels.

			
Server
    ↓
Database
    ↓
Schema
    ↓
Object

		

Example:

			
Database
    Sales
Schema
    Sales
Table
    Orders

		

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

Common Object Permissions

The most commonly used permissions include:

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

GRANT

GRANT gives permissions.

Example

			
GRANT SELECT
ON Sales.Orders
TO SalesUser;

The user can now query the table.

Example

			
GRANT INSERT, UPDATE
ON Sales.Orders
TO SalesUser;

Multiple permissions can be granted simultaneously.

Grant execute permission

			
GRANT EXECUTE
ON dbo.usp_ProcessOrders
TO SalesUser;

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

DENY

DENY explicitly prevents access.

Example

			
DENY DELETE
ON Sales.Orders
TO SalesUser;

Even if another role grants DELETE, DENY overrides it.

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

REVOKE

REVOKE removes previously granted or denied permissions.

Example

			
REVOKE SELECT
ON Sales.Orders
FROM SalesUser;

REVOKE does not deny access.

It simply removes the explicit permission.

GRANT vs DENY vs REVOKE

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

Permission Precedence

SQL Server evaluates permissions using precedence rules.

Highest priority:

DENY

Lower priority:

GRANT

Example

User belongs to:

SalesRole

SalesRole:

GRANT SELECT

Another role:

DENY SELECT

Result:

User cannot SELECT.

DENY wins.

Granting Permissions to Roles

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

Example

CREATE ROLE SalesReaders;

Grant permission

			
GRANT SELECT
ON Sales.Orders
TO SalesReaders;

Add user

			
ALTER ROLE SalesReaders
ADD MEMBER Alice;

This greatly simplifies administration.

Schema-Level Permissions

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

Example

			
GRANT SELECT
ON SCHEMA::Sales
TO SalesReaders;

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

Stored Procedure Permissions

Applications often use stored procedures instead of direct table access.

Example

			
GRANT EXECUTE
ON dbo.usp_GetCustomerOrders
TO AppUser;

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

Benefits include:

Better security
Reduced attack surface
Easier auditing
Centralized business logic

View Permissions

Views frequently expose only selected columns or rows.

Example

			
GRANT SELECT
ON Sales.vCustomerSummary
TO SalesReaders;

Applications query the view rather than the underlying table.

Advantages include:

Hide sensitive columns
Simplify queries
Provide logical security boundaries

Function Permissions

Scalar and table-valued functions also require EXECUTE permission.

Example

			
GRANT EXECUTE
ON dbo.fn_CalculateDiscount
TO SalesUser;

Ownership Chaining

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

Example

			
User
   ↓
Stored Procedure
   ↓
Table

		

If both objects share the same owner:

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

Benefits:

Simplifies application security
Eliminates unnecessary table permissions
Improves manageability

DP-800 frequently tests this concept.

Least Privilege Principle

One of Microsoft’s most important security recommendations.

Users should receive:

Only the permissions required
Nothing more

Poor example

db_owner

Better example

			
SELECT
EXECUTE

Grant only what is necessary.

Avoid Granting db_owner

Many organizations incorrectly solve permission issues by granting db_owner.

Problems:

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

Instead:

Create custom roles
Grant only required permissions

Object Permissions and AI Applications

Modern AI-enabled SQL solutions frequently access databases through:

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

Best practice:

AI applications should never connect using highly privileged accounts.

Instead:

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

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

Best Practices

Microsoft recommends:

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

DP-800 Exam Tips

Candidates should know how to:

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

Practice Exam Questions

Question 1

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

A. EXECUTE

B. SELECT

C. ALTER

D. CONTROL

Correct Answer: B

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

Question 2

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

A. SQL Server ignores the DENY.

B. SQL Server randomly selects one permission.

C. The user can still read the table.

D. The user cannot read the table.

Correct Answer: D

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

Question 3

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

A. Grant permissions directly to every user.

B. Add every user to db_owner.

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

D. Use only server-level permissions.

Correct Answer: C

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

Question 4

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

REVOKE

DENY

REMOVE

DROP

Correct Answer: A

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

Question 5

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

A. SELECT on every table

B. CONTROL on the database

C. EXECUTE on the stored procedure

D. ALTER on the schema

Correct Answer: C

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

Question 6

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

A. ALTER

B. SELECT

C. EXECUTE

D. REFERENCES

Correct Answer: A

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

Question 7

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

A. It encrypts every table in the schema.

B. It automatically creates new users.

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

D. It replaces Row-Level Security.

Correct Answer: C

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

Question 8

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

A. Defense in depth

B. Separation of duties

C. Ownership chaining

D. Least privilege

Correct Answer: D

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

Question 9

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

A. It prevents applications from executing stored procedures.

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

C. It disables ownership chaining.

D. It prevents schema-level permissions from working.

Correct Answer: B

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

Question 10

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

A. SELECT

B. UPDATE

C. EXECUTE

D. ALTER

Correct Answer: C

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

Go to the DP-800 Exam Prep Hub main page

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

Data Development, Databases, Microsoft Certification, SQL August 1, 2026

Write Common Table Expressions (CTEs) (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 common table expressions (CTEs)

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

Introduction

A Common Table Expression (CTE) is a temporary, named result set that exists only for the duration of a single SQL statement. CTEs simplify complex queries by breaking them into logical, readable components. They can be referenced in SELECT, INSERT, UPDATE, DELETE, and MERGE statements and are particularly useful for hierarchical queries, recursive operations, and improving query readability.

Unlike temporary tables or table variables, CTEs are not physically stored in the database. They are defined using the WITH keyword and exist only during the execution of the immediately following statement.

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

What CTEs are
How to create and use CTEs
Nonrecursive CTEs
Recursive CTEs
Multiple CTEs
Using CTEs with DML statements
Recursive query patterns
Performance considerations
CTE limitations
Best practices
AI-enabled database scenarios

Understanding CTEs is important because they are commonly used in enterprise SQL development to organize complex logic, traverse hierarchical data, and prepare datasets for reporting and AI workloads.

What Is a Common Table Expression?

A Common Table Expression is a temporary named query defined immediately before another SQL statement.

General syntax:

			
WITH CTE_Name AS
(
    SELECT ...
)
SELECT *
FROM CTE_Name;

		

The CTE is available only to the statement immediately following its definition.

Benefits of CTEs

CTEs offer several advantages:

Improve readability
Simplify complex queries
Replace deeply nested subqueries
Enable recursive queries
Make SQL easier to debug
Encourage modular query design
Improve maintainability
Support DML operations

Creating a Simple CTE

Example:

			
WITH HighValueOrders AS
(
    SELECT
        OrderID,
        CustomerID,
        TotalAmount
    FROM Sales.Orders
    WHERE TotalAmount > 5000
)
SELECT *
FROM HighValueOrders;

		

The CTE filters orders before the final query executes.

Referencing a CTE

A CTE behaves similarly to a temporary result set.

Example:

			
WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT *
FROM CustomerTotals
WHERE TotalSales > 10000;

		

CTEs and Query Readability

Without a CTE:

			
SELECT *
FROM
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS SalesTotals;

		

Using a CTE often makes the query easier to understand, particularly when multiple steps are involved.

Multiple CTEs

Multiple CTEs can be defined within a single WITH clause.

Example:

			
WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
),
TopCustomers AS
(
    SELECT *
    FROM CustomerTotals
    WHERE TotalSales > 10000
)
SELECT *
FROM TopCustomers;

		

Each CTE can reference earlier CTEs defined in the same WITH clause.

Recursive CTEs

A recursive CTE repeatedly references itself until a termination condition is met.

It consists of:

An anchor member
A recursive member

Example:

			
WITH Numbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT *
FROM Numbers;

		

Result:

Recursive CTE Structure

A recursive CTE has two parts:

Anchor member

Returns the initial result.

SELECT 1 AS Number

Recursive member

References the CTE itself.

			
SELECT Number + 1
FROM Numbers
WHERE Number < 10

The recursion ends when no additional rows are returned.

Hierarchical Queries

Recursive CTEs are ideal for hierarchical data such as:

Organizational charts
Employee-manager relationships
Bill of materials
Folder structures
Product categories

Example:

			
WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName
    FROM HumanResources.Employee e
        INNER JOIN EmployeeHierarchy h
            ON e.ManagerID = h.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

		

Using MAXRECURSION

SQL Server limits recursion to 100 levels by default.

To override the limit:

OPTION (MAXRECURSION 500);

Unlimited recursion:

OPTION (MAXRECURSION 0);

Using unlimited recursion should be done cautiously to avoid infinite loops.

CTEs with INSERT

Example:

			
WITH LargeOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE TotalAmount > 10000
)
INSERT INTO Sales.ArchiveOrders
SELECT *
FROM LargeOrders;

		

CTEs with UPDATE

Example:

			
WITH CustomerDiscounts AS
(
    SELECT
        CustomerID,
        Discount
    FROM Sales.Customers
    WHERE Discount < 0.05
)
UPDATE CustomerDiscounts
SET Discount = 0.05;

		

The CTE provides an updateable result set because it references a single base table without disqualifying constructs.

CTEs with DELETE

Example:

			
WITH OldOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE OrderDate < '2023-01-01'
)
DELETE
FROM OldOrders;

		

CTEs with MERGE

CTEs can simplify complex merge operations.

Example:

			
WITH UpdatedCustomers AS
(
    SELECT *
    FROM Sales.CustomerImport
)
MERGE Sales.Customers AS Target
USING UpdatedCustomers AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE
    SET CustomerName = Source.CustomerName
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName)
    VALUES (Source.CustomerID, Source.CustomerName);

		

CTEs vs. Subqueries

Feature	CTE	Subquery
Readability	High	Moderate
Supports recursion	Yes	No
Reusable within statement	Yes	Limited
Multiple logical steps	Excellent	Difficult
Hierarchical queries	Yes	No

CTEs vs. Temporary Tables

Feature	CTE	Temporary Table
Stored physically	No	Yes (tempdb)
Exists beyond one statement	No	Yes
Supports indexes	No	Yes
Good for complex multi-step processing	Sometimes	Yes
Good for readability	Excellent	Moderate

CTEs vs. Table Variables

Feature	CTE	Table Variable
Temporary object	Logical only	Physical object in tempdb
Exists after statement	No	Yes
Supports indexes	No (directly)	Limited (via constraints/indexes in newer versions)
Recursive queries	Yes	No

Performance Considerations

Although CTEs improve readability, they do not automatically improve performance.

Consider the following:

CTEs are expanded into the execution plan by the optimizer rather than materialized by default.
Large CTEs referenced multiple times may be re-evaluated.
Recursive CTEs can become expensive for deep hierarchies.
Temporary tables may outperform CTEs for large intermediate result sets reused across multiple statements.
Proper indexing on underlying tables remains critical.

Always review the execution plan when optimizing complex queries.

CTE Limitations

Developers should understand these limitations:

Scope is limited to one statement.
Cannot be referenced by subsequent statements.
Cannot include an ORDER BY clause unless used with TOP, OFFSET/FETCH, or FOR XML.
Cannot create indexes on a CTE.
Recursive CTEs require a termination condition.
Excessive recursion can impact performance or lead to errors if recursion limits are exceeded.

AI-Enabled Database Scenarios

CTEs are frequently used in AI-enabled database solutions to prepare data before AI processing.

Examples include:

Cleaning and filtering text before embedding generation
Building hierarchical product catalogs for Retrieval-Augmented Generation (RAG)
Preparing conversation histories for prompt construction
Identifying duplicate records before vectorization
Aggregating customer interactions for AI analysis
Transforming datasets before intelligent search indexing
Organizing graph-like relationships that feed AI models

CTEs provide a readable way to express complex transformations commonly required before AI workflows.

Best Practices

Give CTEs meaningful names.
Use CTEs to simplify complex queries.
Prefer CTEs over deeply nested subqueries.
Use recursive CTEs only when recursion is required.
Always include a termination condition in recursive CTEs.
Test recursive queries with realistic datasets.
Review execution plans for large queries.
Consider temporary tables for large reusable intermediate results.
Keep CTE definitions focused on a single logical task.
Avoid excessive nesting of multiple CTEs.

Common Exam Tips

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

CTEs begin with the WITH keyword.
A CTE exists only for the immediately following statement.
Recursive CTEs consist of an anchor member and a recursive member.
Recursive CTEs are commonly used for hierarchical data.
SQL Server limits recursion to 100 levels by default.
OPTION (MAXRECURSION n) changes the recursion limit.
CTEs can be used with SELECT, INSERT, UPDATE, DELETE, and MERGE.
CTEs are not stored as database objects.
CTEs improve readability but do not guarantee better performance.
Recursive CTEs must include a termination condition.

Practice Exam Questions

Question 1

Which keyword is used to define a Common Table Expression?

A. WITH

B. TEMP

C. DEFINE

D. AS

Answer: A

Explanation: Every Common Table Expression begins with the WITH keyword followed by the CTE name and query definition.

Question 2

How long does a Common Table Expression exist?

A. Until the database connection closes

B. Until the current transaction completes

C. Only for the execution of the immediately following SQL statement

D. Until it is explicitly dropped

Answer: C

Explanation: A CTE exists only for the single statement immediately following its definition.

Question 3

Which capability distinguishes a recursive CTE from a nonrecursive CTE?

A. It can reference itself.

B. It creates a permanent table.

C. It automatically creates indexes.

D. It stores intermediate results in tempdb.

Answer: A

Explanation: Recursive CTEs reference themselves to repeatedly process data until a termination condition is reached.

Question 4

Which type of data is most appropriate for a recursive CTE?

A. Monthly sales totals

B. Customer invoices

C. Product pricing

D. Organizational hierarchies

Answer: D

Explanation: Recursive CTEs are commonly used for hierarchical data such as organizational charts, bill of materials, and category trees.

Question 5

Which statement about recursive CTEs is correct?

A. They do not require an anchor member.

B. They require both an anchor member and a recursive member.

C. They can only return numeric data.

D. They cannot use UNION ALL.

Answer: B

Explanation: Every recursive CTE contains an anchor member that produces the initial rows and a recursive member that references the CTE itself.

Question 6

What is the default maximum recursion level in SQL Server?

A. 10

B. 50

C. 100

D. Unlimited

Answer: C

Explanation: SQL Server limits recursive CTE execution to 100 levels by default unless the MAXRECURSION query hint is specified.

Question 7

Which statement correctly describes CTE performance?

A. CTEs always execute faster than temporary tables.

B. CTEs are always materialized into temporary storage.

C. CTEs automatically create indexes.

D. CTEs primarily improve query readability, while performance depends on the execution plan and underlying data.

Answer: D

Explanation: CTEs improve readability and maintainability, but the query optimizer determines how they are executed. They do not inherently improve performance.

Question 8

Which DML operation can use a Common Table Expression?

A. SELECT only

B. SELECT and INSERT only

C. SELECT, INSERT, UPDATE, DELETE, and MERGE

D. UPDATE only

Answer: C

Explanation: CTEs can precede and be referenced by SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Question 9

When should a temporary table typically be preferred over a CTE?

A. When a readable single-statement query is needed

B. When recursion is required

C. When the intermediate result set must be reused across multiple statements or indexed

D. When querying hierarchical data

Answer: C

Explanation: Temporary tables persist beyond a single statement, can be indexed, and are often more efficient when intermediate results are reused multiple times.

Question 10

How are CTEs commonly used in AI-enabled database solutions?

A. They directly generate vector embeddings.

B. They replace vector indexes.

C. They eliminate the need for application logic.

D. They simplify complex data preparation tasks such as filtering, aggregating, and organizing data before embedding generation, intelligent search, or Retrieval-Augmented Generation (RAG) workflows.

Answer: D

Explanation: CTEs are commonly used to prepare and transform datasets before downstream AI processing, improving readability and maintainability of complex SQL used in AI-enabled database solutions.

Go to the DP-800 Exam Prep Hub main page

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

Create triggers (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%)
   --> Implement programmability objects
      --> Create triggers

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

Triggers are special types of stored procedures that automatically execute (or “fire”) in response to specific database events. Unlike stored procedures, which must be executed explicitly by a user or application, triggers are invoked automatically by SQL Server when certain Data Manipulation Language (DML), Data Definition Language (DDL), or logon events occur.

Triggers are commonly used to enforce complex business rules, maintain audit trails, synchronize related data, validate changes, and perform automated actions that occur whenever data or database objects are modified. While triggers are powerful, they should be used judiciously because they can add complexity and affect database performance if not carefully designed.

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

What triggers are
DML triggers
DDL triggers
AFTER and INSTEAD OF triggers
The inserted and deleted logical tables
Creating, altering, disabling, enabling, and dropping triggers
Nested and recursive triggers
Performance considerations
Best practices
AI-enabled database scenarios

Understanding triggers is important because they provide automatic execution of business logic while helping maintain data integrity and automate administrative tasks.

What Is a Trigger?

A trigger is a database object that automatically executes when a specified event occurs.

Triggers are associated with:

Tables
Views
Databases
SQL Server instances (for certain DDL and logon events)

Triggers cannot be executed directly using the EXEC statement.

Instead, SQL Server executes them automatically when the triggering event occurs.

Types of Triggers

SQL Server supports several types of triggers:

DML triggers
DDL triggers
Logon triggers

The DP-800 exam primarily focuses on DML and DDL triggers.

DML Triggers

Data Manipulation Language (DML) triggers fire when data is modified.

They respond to:

INSERT
UPDATE
DELETE

Typical uses include:

Auditing data changes
Enforcing business rules
Validating updates
Synchronizing tables
Recording historical information

AFTER Triggers

An AFTER trigger executes only after the triggering statement completes successfully.

Example:

			
CREATE TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
    INSERT INTO Sales.CustomerAudit
    (
        CustomerID,
        AuditDate
    )
    SELECT
        CustomerID,
        GETDATE()
    FROM inserted;
END;

		

The trigger records newly inserted customers after the insert operation succeeds.

INSTEAD OF Triggers

An INSTEAD OF trigger executes in place of the triggering action.

Example:

			
CREATE TRIGGER trgPreventDelete
ON Sales.Customers
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Deleting customers is not permitted.';
END;

		

The DELETE statement never executes because the trigger replaces it.

INSTEAD OF triggers are commonly used on:

Views
Complex update scenarios
Custom validation logic

DDL Triggers

DDL triggers respond to schema changes.

Common events include:

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

Example:

			
CREATE TRIGGER trgAuditDDL
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    PRINT 'A table was created.';
END;

		

DDL triggers help monitor or prevent unauthorized schema modifications.

Logon Triggers

Logon triggers execute when a user establishes a SQL Server session.

Typical uses include:

Restricting connections
Recording login activity
Enforcing security policies

Logon triggers are created at the server level and are not supported in Azure SQL Database.

The inserted Logical Table

Whenever rows are inserted or updated, SQL Server creates a temporary logical table named inserted.

It contains the new version of affected rows.

Example:

			
SELECT *
FROM inserted;

The inserted table exists only during trigger execution.

The deleted Logical Table

Whenever rows are deleted or updated, SQL Server creates a logical table named deleted.

It contains the original version of affected rows.

Example:

			
SELECT *
FROM deleted;

For UPDATE operations:

deleted contains old values.
inserted contains new values.

Auditing Changes

Triggers are frequently used to create audit trails.

Example:

			
CREATE TRIGGER trgAuditSalary
ON HumanResources.Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO HumanResources.SalaryAudit
    (
        EmployeeID,
        OldSalary,
        NewSalary,
        ChangeDate
    )
    SELECT
        d.EmployeeID,
        d.Salary,
        i.Salary,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i
        ON d.EmployeeID = i.EmployeeID;
END;

		

This trigger records salary changes for auditing purposes.

Enforcing Business Rules

Triggers can prevent invalid operations.

Example:

			
CREATE TRIGGER trgNoNegativeInventory
ON Inventory.Products
AFTER UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM inserted
        WHERE Quantity < 0
    )
    BEGIN
        RAISERROR
        (
            'Inventory cannot be negative.',
            16,
            1
        );
        ROLLBACK TRANSACTION;
    END;
END;

		

The trigger rolls back the transaction if inventory becomes negative.

Multi-Row Operations

Triggers execute once per SQL statement, not once per affected row.

For example:

			
UPDATE Sales.Customers
SET City = 'Miami';

If 10,000 rows are updated, the trigger executes only once.

The inserted and deleted tables contain all affected rows.

Developers should always write triggers using set-based logic, not assumptions that only one row is affected.

Nested Triggers

A trigger can cause another trigger to fire.

Example:

Trigger A updates Table B.
Table B has Trigger B.
Trigger B executes automatically.

This behavior is called nested triggers.

SQL Server supports nested triggers up to a configurable limit.

Recursive Triggers

A recursive trigger fires itself either directly or indirectly.

Example:

Trigger updates its own table.
That update causes the same trigger to execute again.

Recursive triggers are disabled by default in many environments and should be used with caution to avoid infinite loops.

Enabling and Disabling Triggers

Disable a trigger:

			
DISABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Enable it:

			
ENABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Disabling a trigger preserves its definition while preventing it from firing.

Modifying a Trigger

Use ALTER TRIGGER.

Example:

			
ALTER TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
    PRINT 'Customer inserted.';
END;

		

Deleting a Trigger

Use:

DROP TRIGGER trgCustomerAudit;

Viewing Trigger Definitions

Developers can inspect a trigger using:

sp_helptext 'trgCustomerAudit';

Or:

			
SELECT OBJECT_DEFINITION
(
    OBJECT_ID('trgCustomerAudit')
);

Triggers vs. Stored Procedures

Feature	Trigger	Stored Procedure
Executes automatically	Yes	No
Invoked by `EXEC`	No	Yes
Responds to database events	Yes	No
Accepts parameters	No	Yes
Returns result sets	Not intended for callers	Yes

Triggers vs. Constraints

Feature	Trigger	Constraint
Enforces simple rules	Possible	Yes
Enforces complex business logic	Yes	Limited
Can reference multiple tables	Yes	Limited
Executes automatically	Yes	Yes

Constraints should generally be preferred for simple validation rules because they are simpler and often more efficient.

Performance Considerations

Triggers execute within the same transaction as the triggering statement.

Poorly designed triggers can:

Increase transaction duration
Increase locking
Reduce concurrency
Consume additional CPU resources
Introduce blocking
Increase deadlock risk

Best practices include:

Keep trigger logic simple.
Use set-based operations.
Avoid unnecessary queries.
Avoid long-running operations.
Minimize external dependencies.
Do not assume only one row is affected.

Security Considerations

Triggers can:

Audit sensitive changes
Prevent unauthorized updates
Enforce compliance policies
Record administrative activity
Restrict schema modifications using DDL triggers

Proper permissions should be applied because trigger code executes in the database context.

AI-Enabled Database Scenarios

Triggers can support AI-enabled database solutions by automating actions whenever data changes.

Examples include:

Recording changes that require new embeddings to be generated
Logging modifications to AI training datasets
Flagging rows for downstream vectorization processes
Updating AI metadata tables after inserts or updates
Capturing prompt history for auditing
Initiating workflows that prepare data for intelligent search or Retrieval-Augmented Generation (RAG)

Although triggers cannot directly invoke external AI services, they can populate work queues or status tables that downstream applications or services process.

Best Practices

Prefer constraints for simple validation.
Use triggers only when automatic behavior is required.
Write triggers using set-based logic.
Minimize execution time.
Avoid recursive logic unless absolutely necessary.
Test triggers with multi-row operations.
Document business rules implemented by triggers.
Avoid unnecessary nested trigger chains.
Monitor trigger performance.
Audit only the information that is required.

Common Exam Tips

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

Triggers execute automatically in response to events.
DML triggers respond to INSERT, UPDATE, and DELETE statements.
DDL triggers respond to schema changes.
AFTER triggers execute after the triggering statement completes successfully.
INSTEAD OF triggers replace the triggering action.
inserted contains new row values.
deleted contains original row values.
Triggers fire once per statement, not once per row.
Use ALTER TRIGGER to modify a trigger.
Use DISABLE TRIGGER, ENABLE TRIGGER, and DROP TRIGGER to manage trigger lifecycle.

Practice Exam Questions

Question 1

A developer wants database logic to execute automatically whenever rows are inserted into a table. Which database object should be used?

A. Stored procedure

B. Trigger

C. View

D. Scalar function

Answer: B

Explanation: Triggers automatically execute in response to specified database events such as INSERT, UPDATE, or DELETE operations.

Question 2

Which type of trigger executes only after the triggering statement has completed successfully?

A. BEFORE trigger

B. INSTEAD OF trigger

C. AFTER trigger

D. LOGON trigger

Answer: C

Explanation: An AFTER trigger fires only after the triggering DML statement has completed successfully and any associated constraints have been processed.

Question 3

During an UPDATE operation, which logical table contains the original values of the modified rows?

A. inserted

B. updated

C. original

D. deleted

Answer: D

Explanation: During an UPDATE, the deleted logical table contains the original row values, while the inserted table contains the new values.

Question 4

Which trigger type replaces the original INSERT, UPDATE, or DELETE operation?

A. AFTER trigger

B. DDL trigger

C. INSTEAD OF trigger

D. Recursive trigger

Answer: C

Explanation: An INSTEAD OF trigger executes instead of the triggering statement, allowing custom processing or validation.

Question 5

A trigger is written assuming that only one row is updated at a time. Why is this a problem?

A. SQL Server executes one trigger for every row.

B. Triggers always execute asynchronously.

C. Triggers execute once per SQL statement and may process many affected rows.

D. UPDATE statements cannot affect multiple rows.

Answer: C

Explanation: SQL Server fires DML triggers once per statement, so developers must use set-based logic to correctly process all affected rows.

Question 6

Which statement disables a trigger while preserving its definition?

A. REMOVE TRIGGER

B. DROP TRIGGER

C. ALTER TRIGGER

D. DISABLE TRIGGER

Answer: D

Explanation: DISABLE TRIGGER prevents a trigger from firing without deleting it, allowing it to be re-enabled later.

Question 7

Which statement best describes a DDL trigger?

A. It responds to changes in table data.

B. It responds to schema modification events such as CREATE, ALTER, or DROP statements.

C. It executes only during user logins.

D. It replaces the execution of stored procedures.

Answer: B

Explanation: DDL triggers respond to schema-related events, making them useful for auditing or preventing structural database changes.

Question 8

Which object is generally preferred for enforcing a simple rule such as ensuring a value is greater than zero?

A. AFTER trigger

B. CHECK constraint

C. DDL trigger

D. Stored procedure

Answer: B

Explanation: CHECK constraints are simpler, easier to maintain, and generally more efficient than triggers for straightforward validation rules.

Question 9

Which statement correctly describes nested triggers?

A. They occur only with DDL triggers.

B. They allow a trigger to execute dynamic SQL.

C. They occur when one trigger causes another trigger to fire.

D. They are required whenever inserted and deleted tables are referenced.

Answer: C

Explanation: Nested triggers occur when the actions performed by one trigger cause another trigger to execute.

Question 10

How can triggers support AI-enabled database solutions?

A. They automatically generate embeddings by calling AI models directly.

B. They replace vector indexes.

C. They eliminate the need for application code.

D. They automatically detect data changes and populate work queues, audit tables, or status records that downstream AI processes use to generate embeddings, update indexes, or prepare RAG data.

Answer: D

Explanation: Triggers are well suited for detecting data changes and initiating downstream workflows by recording changes or updating processing queues. External applications or services can then consume these queues to perform AI-related tasks such as embedding generation or intelligent indexing.

Go to the DP-800 Exam Prep Hub main page