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

  • Microsoft Drops 'Solorigate' for 'Nobelium' in Ongoing SolarWinds Attack Investigations

    Microsoft this week described "three new pieces" of malware that were used in the SolarWinds Orion espionage attacks dubbed "Solorigate," although Microsoft security researches are now calling it "Nobelium."

  • Microsoft Universal Print Service Commercially Released

    Microsoft announced on Tuesday that its Universal Print service is now commercially released at the "general availability" stage worldwide.

  • Restoring a Backup to Dissimilar Hardware: 3 Things To Watch Out For

    Getting a new desktop looking and feeling like the old one used to take a long time, but modern backup applications have greatly streamlined the process. Still, there are a few things to keep in mind to avoid potential issues.

  • Black Box

    Microsoft Releases Windows Server 2022 Preview

    Microsoft announced during its Ignite event that Window Server 2022 is currently availability at the preview stage.

comments powered by Disqus