Tag: T-SQL

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

Implement error handling (DP-800 Exam Prep)

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

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

Introduction

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

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

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

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

Why Error Handling Matters

Without proper error handling:

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

Good error handling:

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

Common Types of SQL Errors

Examples include:

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

Example:

SELECT 100 / 0;

Produces:

Divide by zero error encountered.

TRY…CATCH

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

General syntax:

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

		

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

Simple TRY…CATCH Example

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

		

Output:

An error occurred.

Handling Insert Errors

Example:

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

		

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

Retrieving Error Information

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

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

Example:

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

		

ERROR_MESSAGE()

This function returns the descriptive text of the error.

Example:

SELECT ERROR_MESSAGE();

Possible output:

Divide by zero error encountered.

ERROR_NUMBER()

Returns SQL Server’s internal error number.

Example:

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

ERROR_LINE()

Returns the line where the error occurred.

Example:

This simplifies debugging of large stored procedures.

ERROR_PROCEDURE()

Returns the stored procedure that generated the error.

Example:

usp_ProcessOrder

Returns NULL if the error occurred outside a stored procedure.

THROW

THROW is the modern method for raising exceptions.

Syntax:

THROW;

Or:

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

Parameters:

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

Re-Throwing an Error

Inside a CATCH block:

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

		

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

THROW vs RAISERROR

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

Example:

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

		

Equivalent modern syntax:

			
THROW
50001,
'Invalid customer.',
1;

Comparing THROW and RAISERROR

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

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

Transactions and Error Handling

Errors often occur during transactions.

Example:

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

		

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

TRY…CATCH with Transactions

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

		

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

XACT_STATE()

XACT_STATE() determines whether the current transaction is usable.

Possible values:

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

Example:

			
IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;

Why Use XACT_STATE()?

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

Example:

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

This approach is safer than issuing an unconditional ROLLBACK.

SET XACT_ABORT

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

Example:

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

Benefits:

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

Logging Errors

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

Example:

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

		

Benefits include:

Simplified troubleshooting.
Historical analysis.
Compliance and auditing.

Nested TRY…CATCH Blocks

Complex procedures may use nested error handling.

Example:

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

		

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

Errors That Cannot Be Caught

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

Examples include:

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

Error Handling Best Practices

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

10 Practice Exam Questions

Question 1

Which T-SQL construct provides structured exception handling?

A. CASE...WHEN

B. TRY...CATCH

C. IF...ELSE

D. WHILE

Answer: B

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

Question 2

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

A. ERROR_NUMBER()

B. ERROR_MESSAGE()

C. ERROR_STATE()

D. ERROR_LINE()

Answer: B

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

Question 3

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

A. THROW

B. PRINT

C. RETURN

D. GOTO

Answer: A

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

Question 4

What is the purpose of XACT_STATE()?

A. It determines whether indexes are fragmented.

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

C. It displays the current isolation level.

D. It returns the current database compatibility level.

Answer: B

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

Question 5

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

A. 0

B. 1

C. 100

D. -1

Answer: D

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

Question 6

Which function returns the line number where an error occurred?

A. ERROR_PROCEDURE()

B. ERROR_STATE()

C. ERROR_LINE()

D. ERROR_SEVERITY()

Answer: C

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

Question 7

What is the primary benefit of using SET XACT_ABORT ON?

A. It automatically creates savepoints.

B. It automatically commits every transaction.

C. It disables constraint checking.

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

Answer: D

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

Question 8

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

A. ERROR_PROCEDURE()

B. ERROR_LINE()

C. ERROR_MESSAGE()

D. ERROR_NUMBER()

Answer: A

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

Question 9

Which statement about THROW and RAISERROR is correct?

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

B. THROW cannot be used inside a CATCH block.

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

D. THROW does not support custom error messages.

Answer: C

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

Question 10

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

A. To improve index performance.

B. To reduce memory usage.

C. To prevent SQL Server from generating error messages.

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

Answer: D

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

Go to the DP-800 Exam Prep Hub main page

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

Write correlated queries (DP-800 Exam Prep)

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

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

Introduction

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

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

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

What Is a Correlated Query?

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

Because of this dependency, the subquery cannot execute independently.

General syntax:

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

		

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

How Correlated Queries Work

Execution occurs in this order:

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

Unlike regular subqueries, correlated queries are evaluated multiple times.

Correlated Query Example

Suppose two tables exist:

Customers

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders

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

Retrieve customers who have placed at least one order.

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

		

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

Result:

CustomerName
Alice
Bob

Charlie is excluded because no matching order exists.

Comparing Correlated and Non-Correlated Queries

Non-Correlated Query

Runs once.

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

		

The subquery is independent.

Correlated Query

Runs once for every outer row.

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

		

The subquery depends on P.ProductID.

EXISTS with Correlated Queries

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

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

Example:

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

		

Benefits:

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

NOT EXISTS

Returns rows where no matching records exist.

Example:

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

		

Result:

Customers without orders.

Correlated Aggregate Query

Correlated queries frequently use aggregate functions.

Example:

Return employees earning above their department average.

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

		

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

Correlated MAX Example

Find employees with the highest salary in each department.

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

		

Correlated MIN Example

Find products with the lowest price within each category.

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

		

Correlated COUNT Example

Return customers who placed more than three orders.

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

		

Correlated SUM Example

Find salespeople whose total sales exceed $100,000.

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

		

Correlated UPDATE

Correlated queries are not limited to SELECT statements.

Example:

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

		

Each product receives its own calculated average.

Correlated DELETE

Example:

Delete customers with no orders.

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

		

Correlated INSERT

Correlated logic can also appear during INSERT operations.

Example:

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

		

Using EXISTS vs IN

Both operators may return similar results.

EXISTS

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

Example:

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

		

IN

Works well for smaller lookup lists.

Example:

			
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Orders
)

		

Correlated Queries vs Joins

Many correlated queries can be rewritten as joins.

Correlated query:

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

		

Equivalent join:

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

		

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

Correlated Queries vs Window Functions

Sometimes a window function is a better solution.

Correlated query:

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

		

Window function:

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

		

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

Performance Considerations

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

Performance depends on:

Number of rows
Index availability
Query complexity
Join selectivity
Execution plan

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

Optimizing Correlated Queries

Best practices include:

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

Common Business Scenarios

Correlated queries are commonly used for:

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

Common Exam Tips

For the DP-800 exam, remember the following:

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

10 Practice Exam Questions

Question 1

What distinguishes a correlated subquery from a regular subquery?

A. It always returns multiple rows.

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

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

D. It cannot contain aggregate functions.

Answer: B

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

Question 2

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

A. LIKE

B. BETWEEN

C. EXISTS

D. UNION

Answer: C

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

Question 3

How many times is a correlated subquery typically evaluated?

A. Once for the entire query.

B. Once per database.

C. Once per table.

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

Answer: D

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

Question 4

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

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

		

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

		

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

		

			
SELECT *
FROM Customers
ORDER BY CustomerID;

Answer: C

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

Question 5

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

A. MAX()

B. MIN()

C. COUNT()

D. AVG()

Answer: D

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

Question 6

Which statement about correlated queries is true?

A. They cannot be used in UPDATE statements.

B. They cannot contain aggregate functions.

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

D. They always perform better than joins.

Answer: C

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

Question 7

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

A. EXISTS automatically creates indexes.

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

C. EXISTS sorts the results automatically.

D. EXISTS returns all matching rows.

Answer: B

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

Question 8

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

A. Temporary tables

B. Triggers

C. Foreign keys

D. Window functions

Answer: D

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

Question 9

Which factor most directly improves the performance of correlated queries?

A. Increasing the database compatibility level

B. Creating indexes on the correlated columns

C. Using larger transaction log files

D. Increasing the database recovery model

Answer: B

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

Question 10

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

A. Displaying all rows from a single table without filtering

B. Sorting products alphabetically

C. Finding the highest-paid employee within each department

D. Creating a new database

Answer: C

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

Go to the DP-800 Exam Prep Hub main page

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

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

DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Identify and resolve T-SQL errors (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Monitor and optimize an analytics solution (30–35%)
   --> Identify and resolve errors
      --> Identify and resolve T-SQL errors

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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

T-SQL (Transact-SQL) is one of the primary languages used in Microsoft Fabric for querying, transforming, loading, and managing data within Warehouses, SQL analytics endpoints, and other SQL-based workloads. As organizations increasingly use Fabric Warehouses and Lakehouses for analytics, data engineers must be able to identify, troubleshoot, and resolve T-SQL errors efficiently.

For the DP-700 exam, you should understand common T-SQL error types, methods for diagnosing failures, troubleshooting techniques, query optimization considerations, and best practices for preventing errors before they occur.

Understanding T-SQL Errors

A T-SQL error occurs when SQL code cannot execute successfully due to syntax problems, data issues, permissions, resource constraints, or logical mistakes.

Errors generally fall into several categories:

Syntax errors
Object-related errors
Data type conversion errors
Constraint violations
Permission errors
Runtime errors
Query performance issues
Transaction-related errors

Successful troubleshooting requires identifying which category the error belongs to.

Syntax Errors

Syntax errors occur when SQL statements violate T-SQL language rules.

Example

			
SELECT CustomerID CustomerName
FROM Customers

In this example, the comma between columns is missing.

Correct version:

			
SELECT CustomerID, CustomerName
FROM Customers

Common Syntax Issues

Missing commas
Missing parentheses
Incorrect keyword order
Misspelled SQL commands
Unclosed quotation marks
Invalid aliases

Troubleshooting Tips

Read the error message carefully.
Verify SQL keyword spelling.
Check punctuation.
Format code for readability.
Validate parentheses and quotes.

Object Name Errors

These occur when SQL references objects that do not exist or cannot be found.

Example

			
SELECT *
FROM CustomerData

If CustomerData does not exist:

Invalid object name 'CustomerData'

Common Causes

Incorrect table names
Misspelled object names
Dropped tables
Wrong schema references

Example:

			
SELECT *
FROM Sales.CustomerData

instead of:

			
SELECT *
FROM dbo.CustomerData

Troubleshooting Tips

Verify object existence.
Check schema names.
Review recent deployments.
Validate database context.

Column Name Errors

These occur when queries reference nonexistent columns.

Example

			
SELECT CustomerAge
FROM Customers

If CustomerAge does not exist:

Invalid column name 'CustomerAge'

Common Causes

Renamed columns
Typographical errors
Schema changes
Incorrect aliases

Resolution

Review table definitions and confirm column names.

Data Type Conversion Errors

These errors occur when SQL cannot convert data between incompatible types.

Example

SELECT CAST('ABC' AS INT)

Result:

Conversion failed when converting value 'ABC' to data type int.

Common Causes

Invalid numeric values
Incorrect date formats
String-to-number conversions
String-to-date conversions

Safer Approach

Use:

SELECT TRY_CAST('ABC' AS INT)

Result:

NULL

instead of an error.

Best Practice

Use:

TRY_CAST()
TRY_CONVERT()
Data validation logic

Null-Related Errors

Null values frequently cause unexpected query behavior.

Example

			
SELECT Revenue / Quantity
FROM Sales

If Quantity contains zero or NULL values:

Divide-by-zero errors
Unexpected NULL results

Resolution

Use defensive coding:

			
SELECT Revenue / NULLIF(Quantity,0)
FROM Sales

SELECT ISNULL(Quantity,1)

when appropriate.

Constraint Violations

Constraints enforce data integrity.

Common constraints:

Primary keys
Foreign keys
Unique constraints
Check constraints
NOT NULL constraints

Example

			
INSERT INTO Customers
(CustomerID)
VALUES (100)

If CustomerID already exists:

Violation of PRIMARY KEY constraint

Resolution

Check existing data.
Validate uniqueness.
Use MERGE or UPSERT patterns.

Foreign Key Errors

Example

Orders table references Customers table.

Attempting to insert:

			
INSERT INTO Orders
(CustomerID)
VALUES (9999)

when CustomerID 9999 does not exist produces:

Foreign key constraint violation

Resolution

Load parent tables first.

Verify referential integrity before loading.

Permission Errors

Users may not have required access rights.

Example

			
SELECT *
FROM SalesData

Error:

The SELECT permission was denied.

Common Causes

Missing permissions
Incorrect roles
Revoked access
Workspace security changes

Troubleshooting

Verify:

Workspace roles
SQL permissions
Object-level permissions

Runtime Errors

Runtime errors occur while queries execute successfully syntactically but fail during processing.

Examples:

Divide-by-zero
Overflow errors
Resource exhaustion
Timeout failures

Example

SELECT 100 / 0

Produces:

Divide by zero error encountered.

Resolution

Validate input values before execution.

Transaction Errors

Transactions ensure consistency during data modifications.

Example

			
BEGIN TRANSACTION
UPDATE Inventory
SET Quantity = Quantity - 10
COMMIT

If an error occurs before COMMIT, the transaction may remain open.

Best Practice

Use:

			
BEGIN TRY
   BEGIN TRANSACTION
   -- work here
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
END CATCH

		

This pattern is commonly tested on certification exams.

Query Timeout Errors

Long-running queries may exceed execution limits.

Symptoms:

Query never completes
Timeout messages
Resource throttling

Common causes:

Large table scans
Missing filters
Excessive joins
Poor query design

Troubleshooting

Review:

Execution plans
Join strategies
Data volume
Filtering logic

Resource and Capacity Issues

Fabric workloads share compute resources.

Symptoms include:

Slow execution
Query failures
Capacity throttling

Common causes:

Insufficient capacity
Excessive concurrency
Large transformations

Resolution

Scale capacity
Optimize queries
Reduce unnecessary processing

Troubleshooting T-SQL Errors Systematically

A structured approach is essential.

Step 1: Read the Error Message

Many errors explicitly identify:

Object names
Column names
Data types
Constraint violations

Step 2: Identify the Error Category

Determine whether the issue is:

Syntax
Permissions
Data
Performance
Transaction-related

Step 3: Reproduce the Problem

Use smaller datasets when possible.

Step 4: Isolate the Failure

Test:

Individual joins
Filters
Aggregations
Conversions

Step 5: Validate Assumptions

Confirm:

Tables exist
Columns exist
Data types match
Permissions are correct

Using TRY…CATCH for Error Handling

T-SQL supports structured exception handling.

Example:

			
BEGIN TRY
    SELECT 100 / 0
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH

		

Benefits:

Better diagnostics
Controlled error handling
Cleaner ETL workflows

Performance-Related Error Diagnosis

Not all issues generate explicit errors.

Poor performance may indicate:

Missing filters
Excessive joins
Cartesian products
Inefficient aggregations

Watch for:

Long-running queries
Excessive scans
Resource bottlenecks

Common DP-700 Exam Scenarios

You may encounter questions involving:

Invalid object names
Data conversion failures
Permission denials
Constraint violations
Query timeouts
Transaction rollbacks
Divide-by-zero errors
Schema changes breaking SQL code
TRY_CAST versus CAST behavior
TRY…CATCH implementation

Best Practices

Validate Data Before Loading

Prevent conversion failures.

Use TRY_CAST

Avoid runtime conversion errors.

Implement Error Handling

Use TRY…CATCH blocks.

Load Data in Correct Order

Prevent foreign key violations.

Follow Naming Standards

Reduce object-reference errors.

Monitor Query Performance

Identify bottlenecks early.

Test Incrementally

Validate code before production deployment.

Document Schema Changes

Prevent downstream query failures.

DP-700 Exam Tips

Remember:

Syntax errors occur before execution.
Runtime errors occur during execution.
TRY_CAST returns NULL rather than failing.
Foreign key errors typically indicate missing parent records.
Permission errors require security review.
TRY…CATCH provides structured error handling.
Constraint violations protect data integrity.
Timeout errors often indicate performance problems.
Transaction handling should include rollback logic.
Many troubleshooting questions begin by examining the exact error message.

Practice Exam Questions

Question 1

A query returns the error:

Invalid object name 'SalesData'

What is the most likely cause?

A. The referenced table does not exist or is incorrectly named.

B. A primary key violation occurred.

C. The query exceeded memory limits.

D. A data type conversion failed.

Correct Answer: A

Explanation: This error indicates SQL cannot locate the referenced object. Verify table names, schemas, and database context.

Question 2

What is the primary advantage of using TRY_CAST instead of CAST?

A. It executes faster.

B. It automatically creates indexes.

C. It prevents duplicate records.

D. It returns NULL when conversion fails instead of generating an error.

Correct Answer: D

Explanation: TRY_CAST safely handles invalid conversions by returning NULL rather than stopping query execution.

Question 3

A query produces:

Invalid column name 'CustomerAge'

What should you check first?

A. Query timeout settings

B. Whether the referenced column exists in the table

C. Capacity utilization

D. Transaction isolation level

Correct Answer: B

Explanation: Invalid column errors typically indicate a misspelled, renamed, or nonexistent column.

Question 4

Which type of constraint prevents duplicate values from being inserted into a key column?

A. Foreign key constraint

B. Check constraint

C. NOT NULL constraint

D. Primary key constraint

Correct Answer: D

Explanation: Primary key constraints enforce uniqueness and prevent duplicate key values.

Question 5

A user receives:

The SELECT permission was denied.

What is the most likely cause?

A. Missing access permissions

B. Invalid syntax

C. Data type mismatch

D. Foreign key violation

Correct Answer: A

Explanation: Permission errors occur when a user lacks required access rights.

Question 6

Which statement is most likely to generate a divide-by-zero error?

SELECT COUNT(*)

SELECT Revenue / Quantity

where Quantity contains zero values.

SELECT TOP 10 *

SELECT CustomerID

Correct Answer: B

Explanation: Dividing by a value of zero generates a runtime error.

Question 7

A data engineer wants transactions to automatically roll back when an error occurs. Which approach is recommended?

A. Use nested views

B. Use temporary tables

C. Use TRY…CATCH with ROLLBACK TRANSACTION

D. Use SELECT DISTINCT

Correct Answer: C

Explanation: TRY…CATCH combined with rollback logic is a standard error-handling pattern.

Question 8

A foreign key violation occurs during an INSERT operation. What is the most likely explanation?

A. A referenced parent record does not exist.

B. A column name is misspelled.

C. A query timeout occurred.

D. An index is fragmented.

Correct Answer: A

Explanation: Foreign key constraints require matching parent records.

Question 9

A query executes successfully but takes several minutes to complete. Which category best describes the issue?

A. Syntax error

B. Constraint violation

C. Permission error

D. Performance problem

Correct Answer: D

Explanation: Long execution times generally indicate optimization or resource issues rather than functional errors.

Question 10

What should be your first troubleshooting step when a T-SQL query fails?

A. Rebuild all indexes

B. Read and analyze the error message

C. Increase Fabric capacity

D. Delete and recreate the table

Correct Answer: B

Explanation: The error message often identifies the exact source of the problem and should always be reviewed first.

Go to the DP-700 Exam Prep Hub main page.

DP-700, Microsoft Certification, Microsoft Fabric June 3, 2026

Choose Between Dataflows Gen2, Notebooks, KQL, and T-SQL for data transformation (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Ingest and transform data (30–35%)
   --> Ingest and transform batch data
      --> Choose Between Dataflows Gen2, Notebooks, KQL, and T-SQL for data transformation

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 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

Microsoft Fabric provides multiple technologies for transforming data. One of the most common challenges for a Data Engineer is determining which transformation tool is best suited for a specific business requirement.

The DP-700 exam frequently tests your ability to select the appropriate transformation technology based on:

Data volume
Data complexity
Required programming skills
Data source type
Performance requirements
Real-time versus batch processing needs
User expertise
Maintainability

The four most important transformation technologies covered in the exam are:

Dataflows Gen2
Notebooks
KQL
T-SQL

Although all four can transform data, they are optimized for different workloads and use cases.

Understanding their strengths, limitations, and ideal scenarios is critical for success on the DP-700 exam.

Overview of Transformation Technologies

Technology	Primary Purpose	Best For
Dataflows Gen2	Low-code ETL	Business-friendly transformations
Notebooks	Advanced engineering and Spark processing	Large-scale data engineering
T-SQL	Relational transformations	Warehouses and SQL workloads
KQL	Real-time analytics and telemetry processing	Logs and streaming data

Dataflows Gen2

What Are Dataflows Gen2?

Dataflows Gen2 are low-code data transformation tools within Microsoft Fabric that use Power Query.

They allow users to:

Connect to data sources
Clean data
Transform data
Load data into Fabric destinations

without writing significant amounts of code.

Transformation Engine

Dataflows Gen2 use:

Power Query
M Language (behind the scenes)

Most transformations are performed through a graphical interface.

Typical Transformations

Examples include:

Renaming columns
Removing duplicates
Filtering rows
Merging datasets
Splitting columns
Data type conversions
Calculated columns

When to Use Dataflows Gen2

Choose Dataflows Gen2 when:

Low-code development is desired
Data volumes are moderate
Business analysts participate in development
Transformations are relatively straightforward
Self-service data preparation is required

Examples:

Preparing Excel data
Cleaning CSV files
Combining multiple business datasets
Standard ETL processes

Advantages

Low-Code Experience

Minimal coding required.

Large Connector Library

Supports numerous source systems.

Easy Maintenance

Visual transformation steps are easier to understand.

Integration with Fabric

Loads directly into:

Lakehouses
Warehouses
Other Fabric destinations

Limitations

Less Flexible

Complex logic may become difficult.

Not Ideal for Very Large Data Volumes

Spark-based solutions often scale better.

Limited Advanced Programming

Compared to notebooks.

Notebooks

What Are Notebooks?

Notebooks are code-based development environments that support:

PySpark
Python
Scala
Spark SQL
R

within Microsoft Fabric.

Transformation Engine

Notebooks execute on Spark clusters.

This enables:

Distributed processing
Parallel execution
Large-scale transformations

Typical Transformations

Examples:

Complex joins
Data enrichment
Machine learning preparation
Feature engineering
Data quality validation
Custom business logic

When to Use Notebooks

Choose notebooks when:

Large data volumes exist
Spark processing is required
Advanced transformations are needed
Custom programming is necessary
Machine learning integration is planned

Examples:

Processing billions of records
Data science workflows
Medallion architecture pipelines
Complex transformations

Advantages

Massive Scalability

Handles large datasets efficiently.

Flexible Programming

Supports multiple languages.

Machine Learning Integration

Works with Spark ML libraries.

Advanced Data Engineering

Ideal for enterprise-scale pipelines.

Limitations

Requires Coding Skills

Less accessible for business users.

More Complex Development

Compared to Dataflows Gen2.

T-SQL

What Is T-SQL?

T-SQL (Transact-SQL) is Microsoft’s extension of SQL.

Fabric Warehouses and SQL endpoints support T-SQL for:

Querying
Transforming
Managing relational data

Transformation Techniques

Common operations include:

			
SELECT
JOIN
GROUP BY
CASE
CTE
MERGE
WINDOW FUNCTIONS

		

When to Use T-SQL

Choose T-SQL when:

Data resides in a Warehouse
Relational transformations are required
SQL expertise already exists
Dimensional models are being built

Examples:

Fact table loading
Dimension updates
Data warehouse ETL
Reporting data preparation

Advantages

Familiar Language

Widely used by data professionals.

Excellent Relational Processing

Optimized for structured data.

Strong Performance

Particularly for warehouse workloads.

Easy Integration

Works naturally with BI tools.

Limitations

Less Suitable for Unstructured Data

Not ideal for files and raw data.

Limited Distributed Processing

Compared to Spark.

KQL

What Is KQL?

Kusto Query Language (KQL) is designed for:

Log analytics
Telemetry analysis
Real-time data processing
Event analytics

KQL is commonly used in:

KQL Databases
Eventhouse
Real-Time Intelligence

Typical Transformations

Examples include:

Filtering events
Aggregations
Pattern detection
Time-series analysis
Stream transformations

When to Use KQL

Choose KQL when:

Working with telemetry data
Processing logs
Analyzing streaming events
Building real-time dashboards

Examples:

Sensor monitoring
Application logs
Security analytics
Operational monitoring

Advantages

Optimized for Time-Series Data

Excellent for event-driven workloads.

Fast Query Performance

Handles large event volumes efficiently.

Real-Time Analytics

Supports low-latency analysis.

Limitations

Not a General ETL Tool

Less suitable for traditional batch ETL.

Not Designed for Dimensional Modeling

Warehouses are generally better for reporting models.

Comparing Transformation Technologies

Requirement	Dataflows Gen2	Notebooks	T-SQL	KQL
Low-Code Development	Excellent	Poor	Moderate	Moderate
Large-Scale Processing	Moderate	Excellent	Good	Excellent
Relational Transformations	Moderate	Good	Excellent	Limited
Streaming Analytics	Limited	Moderate	Poor	Excellent
Machine Learning Support	Poor	Excellent	Poor	Limited
Telemetry Analytics	Poor	Moderate	Poor	Excellent
Business User Friendly	Excellent	Poor	Moderate	Moderate
Advanced Programming	Limited	Excellent	Moderate	Limited

Decision Framework

Choose Dataflows Gen2 When:

Low-code ETL is preferred
Business users are involved
Data volumes are moderate
Transformations are straightforward

Choose Notebooks When:

Spark processing is required
Data volumes are large
Complex transformations exist
Machine learning is involved

Choose T-SQL When:

Working with a Warehouse
Building dimensional models
SQL skills are available
Data is highly structured

Choose KQL When:

Processing logs
Analyzing telemetry
Supporting streaming analytics
Building operational monitoring solutions

Common DP-700 Scenario Questions

Scenario 1

A business analyst needs to combine Excel spreadsheets and remove duplicate rows using a visual interface.

Best choice:

Dataflows Gen2

Scenario 2

A data engineer must transform billions of records stored in a Lakehouse.

Best choice:

Notebook

Scenario 3

A warehouse team must populate fact and dimension tables.

Best choice:

T-SQL

Scenario 4

An operations team analyzes millions of application log events each hour.

Best choice:

KQL

Scenario 5

A machine learning team requires custom Python transformations.

Best choice:

Notebook

Exam Tips

Many DP-700 questions are not asking what can perform a transformation, but what should perform the transformation.

Remember these associations:

Requirement	Best Choice
Visual ETL	Dataflows Gen2
Spark processing	Notebook
Data warehouse transformations	T-SQL
Telemetry and logs	KQL
Machine learning preparation	Notebook
Self-service data preparation	Dataflows Gen2
Streaming analytics	KQL

Practice Exam Questions

Question 1

A business analyst needs to cleanse CSV files using a graphical interface with minimal coding. Which transformation technology should be used?

A. T-SQL

B. Notebook

C. KQL

D. Dataflows Gen2

Answer: D

Explanation

Dataflows Gen2 provide a low-code, visual interface that is ideal for business users and simple ETL processes.

Question 2

A data engineer must process several billion records stored in a Lakehouse using distributed computing.

Which option should be selected?

A. Notebook

B. Dataflows Gen2

C. T-SQL

D. KQL

Answer: A

Explanation

Notebooks leverage Spark for distributed processing and are designed for large-scale data transformations.

Question 3

Which technology is specifically optimized for transforming and analyzing telemetry and log data?

A. Dataflows Gen2

B. Notebook

C. KQL

D. T-SQL

Answer: C

Explanation

KQL is designed for log analytics, telemetry processing, and real-time operational analytics.

Question 4

A team is loading dimension and fact tables within a Fabric Warehouse.

Which transformation technology is most appropriate?

A. Notebook

B. Dataflows Gen2

C. KQL

D. T-SQL

Answer: D

Explanation

T-SQL is the preferred technology for relational transformations in Fabric Warehouses.

Question 5

A company requires machine learning feature engineering using Python libraries.

Which technology should be selected?

A. Notebook

B. Dataflows Gen2

C. T-SQL

D. KQL

Answer: A

Explanation

Notebooks support Python, Spark, and machine learning frameworks, making them ideal for feature engineering.

Question 6

Which technology relies primarily on Power Query transformations?

A. Notebook

B. Dataflows Gen2

C. T-SQL

D. KQL

Answer: B

Explanation

Dataflows Gen2 use Power Query and the M language behind the scenes for data transformations.

Question 7

An operations team needs to perform real-time aggregations on streaming sensor data.

Which option should be used?

A. Dataflows Gen2

B. Notebook

C. KQL

D. T-SQL

Answer: C

Explanation

KQL is optimized for real-time event processing and telemetry analysis.

Question 8

A data engineer needs maximum flexibility to implement custom business logic across multiple data sources.

Which technology is most appropriate?

A. KQL

B. Dataflows Gen2

C. T-SQL

D. Notebook

Answer: D

Explanation

Notebooks provide the highest degree of customization through programming languages such as Python and PySpark.

Question 9

A team already has extensive SQL expertise and needs to transform highly structured relational data in a Warehouse.

Which option is best?

A. Notebook

B. T-SQL

C. Dataflows Gen2

D. KQL

Answer: B

Explanation

T-SQL is optimized for relational transformations and leverages existing SQL skills.

Question 10

Which technology is generally the most business-user-friendly option for creating batch data transformation processes?

A. Notebook

B. KQL

C. T-SQL

D. Dataflows Gen2

Answer: D

Explanation

Dataflows Gen2 provide a visual, low-code experience that is easier for business users and citizen developers than code-based solutions.

DP-700 Exam Summary

When deciding between transformation technologies, focus on the primary workload:

Dataflows Gen2 → Low-code ETL and self-service data preparation
Notebooks → Spark, large-scale processing, advanced engineering, and machine learning
T-SQL → Relational transformations and warehouse development
KQL → Telemetry, logs, time-series analytics, and real-time event processing

A common DP-700 exam strategy is to identify the keywords in the scenario: