Did you ever wonder if you could create a temporary result set and use that within a single SELECT, INSERT, UPDATE or DELETE statement? I bet you have, and before SQL Server 2005 came along, I am sure that you used either sub-queries, cursors or temporary tables to achieve the desired results.
Starting SQL Server 2005, we have a much better, and much more efficient method of achieving this – the common table expression, or the CTE. Much has been written about CTEs and their benefits, and hence I will be mostly presenting a summary of all the great articles and posts on the topic that I have chanced upon till date:
- An introduction to CTEs – http://www.simple-talk.com/content/article.aspx?article=260
- The things that you can do with a CTE – http://msdn.microsoft.com/en-us/library/ms175972.aspx
- A few data points on CTE – http://msdn.microsoft.com/en-us/magazine/cc163346.aspx#S3
Something interesting – CTE in a CREATE VIEW statement
Yes, CTEs can be used in a CREATE VIEW statement as is demonstrated by the queries below:
USE AdventureWorks2008R2; GO CREATE VIEW vSalesCTE AS -- Define the CTE expression name and column list. WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. ( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL ) SELECT Sales_CTE.SalesPersonID, Sales_CTE.SalesOrderID, Sales_CTE.SalesYear FROM Sales_CTE; GO --Use the VIEW containing a CTE SELECT vcte.SalesPersonID, COUNT(vcte.SalesOrderID) AS TotalSales, vcte.SalesYear FROM vSalesCTE vcte GROUP BY vcte.SalesYear, vcte.SalesPersonID ORDER BY vcte.SalesPersonID, vcte.SalesYear
Don’t overestimate CTEs – don’t use them multiple times within the same query
As is the case with everything, CTEs in excess can also cause more harm than good. CTEs are more of “expandable” blocks of code, and hence, if you use them more than once in a query, you will end up with performance issues. This is because the entire data set is populated the number of times a CTE is used. For cases where you need to use the same data set again and again, use temporary tables or table variables instead.
I hope that CTE’s opened up a whole new programming paradigm for all. If you have not started experimenting with CTEs, I recommend that you start doing so – it will be fun.
Until we meet next time,
Be courteous. Drive responsibly.