Advanced T-SQL: CTEs, Windows, and Correlated Queries

Queries that go beyond SELECT…WHERE

Common Table Expressions (CTEs)

A CTE is a temporary, named result set defined within a single statement. Think of it as a “scratchpad query” you can reference in the main query.

-- Basic CTE: find customers who spent more than average
WITH CustomerSpending AS (
    SELECT CustomerId, SUM(Amount) AS TotalSpent
    FROM Orders
    GROUP BY CustomerId
)
SELECT c.FullName, cs.TotalSpent
FROM CustomerSpending cs
INNER JOIN Customers c ON cs.CustomerId = c.CustomerId
WHERE cs.TotalSpent > (SELECT AVG(TotalSpent) FROM CustomerSpending);

Recursive CTEs

Recursive CTEs reference themselves — perfect for hierarchical data like org charts, bill of materials, or category trees.

-- Walk the org chart from CEO down
WITH OrgChart AS (
    -- Anchor: start at the top (no manager)
    SELECT EmployeeId, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive: find direct reports of each level
    SELECT e.EmployeeId, e.Name, e.ManagerId, oc.Level + 1
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerId = oc.EmployeeId
)
SELECT Level, Name FROM OrgChart ORDER BY Level, Name;

💡 Exam tip: CTE vs subquery vs temp table

CTEs are not materialised — they are expanded inline like subqueries. If you reference the same CTE multiple times, SQL Server may execute it multiple times. For large intermediate results referenced repeatedly, a temp table or table variable may perform better.

Use CTEs for: readability, recursive queries, one-time intermediate results. Use temp tables for: large intermediate results referenced multiple times, when you need indexes on intermediate data.

Window functions

Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does.

-- Ranking functions
SELECT
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS OverallRank,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptRank,
    DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DeptDenseRank
FROM Employees;

ROW_NUMBER vs RANK vs DENSE_RANK

Salary	ROW_NUMBER	RANK	DENSE_RANK
100,000	1	1	1
90,000	2	2	2
90,000	3	2	2
80,000	4	4	3

• ROW_NUMBER: always unique (1, 2, 3, 4)
• RANK: ties get the same rank, then skips (1, 2, 2, 4)
• DENSE_RANK: ties get the same rank, no skip (1, 2, 2, 3)

Running totals and moving averages

-- Running total and previous month comparison
SELECT
    OrderMonth,
    MonthlyRevenue,
    SUM(MonthlyRevenue) OVER (ORDER BY OrderMonth) AS RunningTotal,
    LAG(MonthlyRevenue, 1) OVER (ORDER BY OrderMonth) AS PreviousMonth,
    MonthlyRevenue - LAG(MonthlyRevenue, 1) OVER (ORDER BY OrderMonth) AS MonthOverMonth
FROM MonthlySales;

Window functions — know when each is the right tool

Function	What It Does	Example Use
ROW_NUMBER()	Assigns unique sequential number	Pagination, deduplication
RANK()	Rank with gaps for ties	Leaderboards where ties share a position
DENSE_RANK()	Rank without gaps	Top-N queries where you want exactly N distinct ranks
SUM() OVER	Running total	Cumulative revenue, account balance
AVG() OVER	Moving average	Trend analysis over sliding windows
LAG()	Previous row value	Month-over-month comparison
LEAD()	Next row value	Predicting next event, gap analysis
FIRST_VALUE()	First value in the window	Baseline comparison
NTILE()	Divides rows into N equal groups	Quartile/percentile analysis

Correlated subqueries

A correlated subquery references the outer query — it runs once per outer row.

-- Find employees who earn more than their department average
SELECT e.Name, e.Department, e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.Department = e.Department  -- Correlated: references outer query
);

The inner query depends on e.Department from the outer query, so it re-executes for each employee. For large tables, this can be slow — a CTE or window function alternative is often better:

-- Same result using a window function (usually faster)
WITH DeptAvg AS (
    SELECT Name, Department, Salary,
           AVG(Salary) OVER (PARTITION BY Department) AS AvgSalary
    FROM Employees
)
SELECT Name, Department, Salary FROM DeptAvg WHERE Salary > AvgSalary;

Error handling: TRY…CATCH

Structured error handling ensures your transactions are clean and your errors are informative.

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Re-throw with context, or log and throw custom error
    DECLARE @Msg NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @Sev INT = ERROR_SEVERITY();
    DECLARE @State INT = ERROR_STATE();
    RAISERROR(@Msg, @Sev, @State);
END CATCH;

THROW vs RAISERROR

Feature	THROW	RAISERROR
Syntax	THROW 50001, 'message', 1;	RAISERROR('message', 16, 1);
Re-throw original error	THROW; (no parameters)	Must reconstruct with ERROR_MESSAGE()
Severity	Always 16 (unless re-throwing)	Configurable (0-25)
Terminates batch?	Yes (with XACT_ABORT ON)	Only at severity 20+
Recommended?	Yes — modern, simpler	Legacy, but still needed for variable severity

💡 Exam tip: SET XACT_ABORT ON

SET XACT_ABORT ON at the start of a stored procedure ensures that ANY error automatically rolls back the entire transaction. Without it, some errors leave the transaction open (partially committed), which can cause data corruption.

Best practice: always use SET XACT_ABORT ON with SET NOCOUNT ON at the top of stored procedures.

Question

What is the difference between ROW_NUMBER() and DENSE_RANK()?

Click or press Enter to reveal answer

Answer

ROW_NUMBER() assigns a unique number to every row (no ties). DENSE_RANK() assigns the same rank to tied rows and does not skip numbers. Example: salaries 100K, 90K, 90K, 80K get ROW_NUMBER 1,2,3,4 but DENSE_RANK 1,2,2,3.

Click to flip back

Question

What is a correlated subquery?

Click or press Enter to reveal answer

Answer

A subquery that references columns from the outer query. It executes once per row of the outer query (conceptually). Example: 'for each employee, find the average salary of their department.' Often slower than CTEs or window functions for large datasets.

Click to flip back

Question

When should you use THROW vs RAISERROR?

Click or press Enter to reveal answer

Answer

THROW is the modern approach — simpler syntax and supports re-throwing the original error with just 'THROW;'. Use RAISERROR when you need variable severity levels (0-25) or formatted messages with printf-style substitution. For new code, prefer THROW.

Click to flip back

Knowledge Check

Priya at Vault Bank needs to show each customer's transaction history with a running balance. Each row should show the transaction amount and the cumulative total up to that transaction, ordered by date. Which approach is correct?

AA GROUP BY query with SUM(Amount)BSUM(Amount) OVER (PARTITION BY CustomerId ORDER BY TransactionDate) as RunningBalanceCA correlated subquery that sums all previous transactionsDA recursive CTE that adds each transaction to the previous total

Check Answer

Knowledge Check

Dev at PixelForge Studios has a stored procedure that transfers credits between user accounts. If any step fails, the entire transfer must be rolled back. Currently, some errors leave the transaction partially committed. What should Dev add?

ASET XACT_ABORT ON at the start of the procedureBA TRY...CATCH block without XACT_ABORTCRAISERROR with severity 25 after each statementD@@ERROR checks after each statement

Check Answer

Next up: Pattern Power: Regular Expressions in T-SQL — use the new REGEXP functions to find, replace, and split text with pattern matching.