This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren's editorial on SQLServerCentral.com.

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:

1. An introduction to CTEs - http://www.simple-talk.com/content/article.aspx?article=260
2. The things that you can do with a CTE - http://msdn.microsoft.com/en-us/library/ms175972.aspx
3. 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.