Common Table Expressions Give Temporary Relief

Working with temporary result sets? Use CTEs to cut to the chase.

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.

Featured

  • Salesforce Buying Slack for $27 Billion To Bolster CRM Solution

    Salesforce on Tuesday announced the purchase of collaboration software-maker Slack for an estimated $27.7 billion.

  • Dark City Illustration

    The Night the Lights Went Out in the Cloud: Lessons from the AWS Outage

    Last week's AWS outage that broke the Internet showed how critical it is to build applications that can withstand transient failure. Here's what you need to know to design a resilient cloud app (and it doesn't involve multicloud).

  • 5 Steps To Fix Windows Indexing Problems

    The Windows indexing feature doesn't always deliver the correct results of a file search. Here are five troubleshooting steps you can take whenever Windows indexing acts up.

  • Microsoft Adding Simpler Microsoft 365 Admin Center Option for Small Businesses

    The Microsoft 365 Admin Center, used for setting up and managing various Microsoft services, is getting a more lightweight interface designed for "very small businesses," according to a Tuesday Microsoft announcement.

comments powered by Disqus