Sql Server Common Table Expressions Ctes Complete Guide

Complete Examples, Step by Step for Beginners: SQL Server Common Table Expressions (CTEs)

Step 1: Basic CTE

Let's start with a basic example of a CTE. Suppose we have a table named Employees with the following columns:

• EmployeeID
• FirstName
• LastName
• ManagerID

We want to write a query to select all employees along with their manager's names.

Step 1.1: Create a Sample Table

-- Create a sample Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    ManagerID INT
);

-- Insert sample data into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, ManagerID) VALUES
(1, 'John', 'Doe', NULL),
(2, 'Jane', 'Smith', 1),
(3, 'Alice', 'Johnson', 1),
(4, 'Bob', 'Brown', 2),
(5, 'Charlie', 'Davis', 3);

Step 1.2: Write a Query Using CTE Now, let's write a query using a CTE to display each employee along with their manager's name.

WITH CTE AS (
    SELECT 
        E.EmployeeID,
        E.FirstName,
        E.LastName,
        E.ManagerID,
        M.FirstName AS ManagerFirstName,
        M.LastName AS ManagerLastName
    FROM 
        Employees E
    LEFT JOIN 
        Employees M ON E.ManagerID = M.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    ManagerFirstName,
    ManagerLastName
FROM 
    CTE;

Explanation:

1. The WITH keyword is used to define a CTE named CTE.
2. Inside the CTE, we select employee details and use a LEFT JOIN to get the manager's name using the ManagerID.
3. The result of the CTE is then selected in the outer query to get the final output.

Step 2: Recursive CTE

Recursive CTEs are used to perform tasks that involve hierarchical or recursive data, such as traversing an organizational tree.

Suppose we need to find all employees under a specific manager (e.g., manager with EmployeeID = 1).

Step 2.1: Write a Recursive CTE Query Here's how you can use a recursive CTE to solve this problem.

WITH RecursiveCTE AS (
    -- Anchor member: select the manager
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID
    FROM 
        Employees e
    WHERE 
        e.EmployeeID = 1 -- ManagerID

    UNION ALL

    -- Recursive member: find employees managed by previously selected employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID
    FROM 
        Employees e
    INNER JOIN 
        RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    ManagerID
FROM 
    RecursiveCTE
OPTION (MAXRECURSION 0); -- To remove the default recursion limit (32)

Explanation:

1. Anchor Member: The initial query selects the manager (EmployeeID = 1).
2. Recursive Member: This part of the CTE joins the Employees table with the CTE itself to find employees reporting to the previously selected employees.
3. UNION ALL: Combines the results of the anchor member and the recursive member.
4. OPTION (MAXRECURSION 0): Allows the recursion to go beyond the default limit of 32 to avoid a recursion error. Use with caution to prevent infinite loops.

Step 3: Common Use Cases for CTEs

CTEs can be used in various situations, including pagination, ranking, filtering, and more. Here are a few more examples.

Step 3.1: Ranking Employees by Hierarchy Level

Suppose we need to rank employees based on their level in the hierarchy (e.g., manager, direct report, etc.).

WITH RecursiveCTE AS (
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        1 AS Level -- Level 1 is the topmost manager
    FROM 
        Employees e
    WHERE 
        e.ManagerID IS NULL -- Assuming NULL ManagerID is the topmost manager

    UNION ALL

    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        r.Level + 1 AS Level -- Increment level for each hierarchy level
    FROM 
        Employees e
    INNER JOIN 
        RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    ManagerID,
    Level
FROM 
    RecursiveCTE;

Step 3.2: Pagination Using CTE

CTEs can be used to implement pagination, a common requirement in web applications.

DECLARE @PageNumber INT = 1;
DECLARE @PageSize INT = 10;

WITH RankedEmployees AS (
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        ROW_NUMBER() OVER (ORDER BY e.FirstName, e.LastName) AS RowNum
    FROM 
        Employees e
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    ManagerID
FROM 
    RankedEmployees
WHERE 
    RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize;

Explanation:

1. ROW_NUMBER(): Assigns a unique row number to each row based on the specified order.
2. BETWEEN: Filters the rows based on the page number and page size.

Summary