What is a Common Table Expression (CTE), and how is it used in SQL Server?

A Common Table Expression (CTE) is a temporary result set defined within the execution context of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is similar to a derived table, but with some added benefits. CTEs can simplify and enhance the readability of SQL code by allowing for a more modular approach to composing complex queries.

Along with its readability benefits, this feature also provides the ability to recursively refer to the CTE within itself, which can be useful for complex hierarchical queries.

To create a CTE, we use the ‘WITH‘ clause followed by a sequential definition of the CTEs. Each CTE is defined as a named subquery using a ‘SELECT‘ statement, and these subqueries can refer to each other or even to tables outside the CTEs. The syntax for creating a CTE is as follows:

WITH cte_name (column_list)
AS
(
    SELECT ...
)
SELECT ...

Here, ‘cte_name‘ is the name of the CTE, and ‘column_list‘ is the list of columns that the query returns for that CTE. The ‘SELECT...‘ statement can be any valid SQL query.

For example, here is a simple CTE that calculates the sum of all sales by region:

WITH RegionSales (Region, TotalSales)
AS
(
    SELECT Region, SUM(Sales) AS TotalSales
    FROM Sales
    GROUP BY Region
)
SELECT * FROM RegionSales;

The above example creates a CTE called ‘RegionSales‘ which summarises the total sales for each region. The final ‘SELECT‘ statement retrieves the results from this CTE.

CTEs can also be used to write self-recursive queries. For example, consider the following simple CTE that calculates the factorial of a given number:

WITH RecursiveFactorial (n, Factorial)
AS
(
    SELECT 1, 1
    UNION ALL
    SELECT n+1, (n+1)*Factorial
    FROM RecursiveFactorial
    WHERE n < 10
)
SELECT n, Factorial FROM RecursiveFactorial;

Here, the CTE called ‘RecursiveFactorial‘ uses a recursive query to calculate the factorial of a given number. The ‘UNION ALL‘ keyword connects the anchor member (the base case, ‘n‘ = 1 and ‘Factorial‘ = 1) with the recursive member, and the ‘WHERE‘ clause defines the recursion termination condition (‘n‘ < 10).

In conclusion, CTEs offer a powerful and flexible way to create modular and readable SQL code. They can be used to simplify the queries, enhance the readability of code, and even to handle complex scenarios such as self-recursion.