Common Table Expressions Give Temporary Relief
Working with temporary result sets? Use CTEs to cut to the chase.
- By Eric Johnson
- 03/29/2010
If you work with T-SQL a lot, you may already be aware of Common Table Expression. CTEs were added to T-SQL with SQL Server 2005 and they offer a great way to work with temporary result sets. You can use a CTE in place of sub queries, derived tables or even views. In short, you derive a CTE by using a single query that then can be queried like any other object.
Let's look at a quick example. This code defines a CTE as SalesTotal and selects data from the derived dataset:
;WITH SalesTotal(CustomerID, TotalSales) AS
(
SELECT customerid, SUM(SALES)
FROM SALES
GROUP BY customerid
)
SELECT * FROM SalesTotal
RESULTS:
CustomerID TotalSales
----------- ---------------------
1 53216433.00
2 35773898523.00
3 925702994.00
The code is pretty straight forward; you define the name of the CTE in the first line followed by a list of the columns names you want the CTE to contain. The query in the AS() section has to return the correct number of columns; as you can see, the names do not have to match. Finally you write a query that references the CTE by the derived name, in this case SalesTotal. Here, you can join other objects or even perform additional aggregation.
Take the following query as an example. Here we used the same query as in the first example, except we also applied a sum to the final query:
;WITH SalesTotal(CustomerID, TotalSales) AS
(
SELECT customerid, SUM(SALES) Sales_TOT
FROM SALES
GROUP BY customerid
)
SELECT AVG(TotalSales) AvgSales FROM SalesTotal
RESULTS:
AvgSales
---------------------
12250939316.6666
In additon to what we have seen, you can also have multiple CTEs in one statement. It's pretty simple; just separate each definition with a comma:
;WITH SalesTotal(CustomerID, TotalSales) AS
(
SELECT customerid, SUM(SALES) Sales_TOT
FROM SALES
GROUP BY customerid
)
,SalesTotal2(CustomerID, TotalSales) AS
(
SELECT customerid, SUM(SALES) Sales_TOT
FROM SALES
GROUP BY customerid
)
I realized that the queries are both the same here, and all that changed was the derived name, but you get the idea.
One last note: You may be wondering why there is a semicolon before the WITH in each CTE. Since WITH is a key word for other things in T-SQL, the semicolon acts as a batch separator telling the compiler that this is the first statement in a batch and that the WITH is a CTE definition instead of an option of a previous statement. If your CTE is truly the first statement in a batch, this semicolon won't be necessary. Still, it never hurts to add the semicolon so if you make it a habit you won't get burned.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.