Microsoft SQL 2005 introduced a new construct called CTEs (common table expressions). CTEs provide a way to break down complex queries into simpler chunks and offer more readable queries. UDFs, derived tables, and temp tables are all constructs that can be employed to help break up complex queries, however they often muddy up your SQL script. They also exist beyond the context of the one SQL statement you may have desired to use it for. CTEs also provide the ability for recursion without the need for recursive stored procedures.
Therefore, at a high level CTEs are used for:
1) Improving readability of your SQL scripts.
2) Simplifying complex queries.
3) Recursion.
A Simple CTE Example:
1: WITH OrderTotals (OrderID, OrderTotal) AS (
2: SELECT OrderDetails.OrderID, SUM(((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount)) AS OrderTotal
3: FROM dbo.[Order Details] AS OrderDetails WITH (NOLOCK)
4: GROUP BY OrderDetails.OrderID
5: )
6: SELECT * FROM dbo.Orders WITH (NOLOCK)
7: INNER JOIN OrderTotals ON OrderTotals.OrderID = Orders.OrderID
8: WHERE OrderTotals.OrderTotal > 100
In this first simple example, I used the Northwind database to find all orders which exceeded $100. The first step is to use the keyword WITH and then name my CTE, define my columns, then enclose what SQL makes up the CTE within parenthesis. I then proceed to use the CTE in the final query. Notice how the final query is much simpler to read without the GROUP BY, SUM, etc logic in the CTE.
Two CTEs Example:
1: WITH OrderTotals (OrderID, OrderTotal) AS (
2: SELECT OrderDetails.OrderID, SUM(((OrderDetails.UnitPrice * OrderDetails.Quantity) - OrderDetails.Discount)) AS OrderTotal
3: FROM dbo.[Order Details] AS OrderDetails WITH (NOLOCK)
4: GROUP BY OrderDetails.OrderID
5: ), OrdersMoreThan100 (OrderID, OrderTotal) AS (
6: SELECT OrderTotals.* FROM OrderTotals WHERE OrderTotals.OrderTotal > 100
7: )
8: SELECT * FROM dbo.Orders WITH (NOLOCK)
9: INNER JOIN OrdersMoreThan100 ON OrdersMoreThan100.OrderID = Orders.OrderID;
In this example, we get a little more complex with our CTEs. Here we can see that you can comma delimit as many CTEs as you want. I also reference the first CTE within my second CTE and simplify our final SQL statement even further.
CTE Gotchas/Notes:
1) Always terminate SQL with a “;” before you declare a CTE.
2) You can use CTEs with SELECT, UPDATE, INSERT, DELETE queries.
3) CTEs can only be used by the final SQL statement (the statement following the CTE definitions). Subsequent SQL queries will not be able to use your CTE definitions.