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

  • Spaceflight Training in the Middle of a Pandemic

    Surprisingly, the worldwide COVID-19 lockdown has hardly slowed down the space training process for Brien. In fact, it has accelerated it.

  • Surface and ARM: Why Microsoft Shouldn't Follow Apple's Lead and Dump Intel

    Microsoft's current Surface flagship, the Surface Pro X, already runs on ARM. But as the ill-fated Surface RT showed, going all-in on ARM never did Microsoft many favors.

  • IT Security Isn't Supposed To Be Easy

    Joey explains why it's worth it to endure a little inconvenience for the long-term benefits of a password manager and multifactor authentication.

  • Microsoft Makes It Easier To Self-Provision PCs via Windows Autopilot When VPNs Are Used

    Microsoft announced this week that the Windows Autopilot service used with Microsoft Intune now supports enrolling devices, even in cases where virtual private networks (VPNs) might get in the way.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.