Common Table Expressions or CTE

At work, I've created over 40 reports that run on a daily, weekly or monthly basis.  I've created these reports using a SQL Server tool called SQL Server Integration Services, or SSIS.  The one problem I've found with SSIS is that SQL won't allow the use of temporary tables (tables that are generated on the fly and are dropped as soon as your query is over).  Through my research, there is one option within SQL Server that allows for table called Common Table Expressions, or CTE.

Using Common Table Expressions

A CTE is much like a temporary table, it is generated on the fly and disappears as soon as your query is over.  Creating a CTE is pretty simple.  All you need to provide is the following:

• Expression Name (table name)
• Column name list (not required)
• CTE Query Definition (statement selecting your records)
• Your actual query on the new CTE

From there, a query can then be run off of the CTE data to summarize or join with other tables.  Here is an example:

WITH Sales_CTE (SalesPersonID, OrderID, SalesDate)

AS

(

    SELECT SalesPersonID, SalesOrderID, YEAR(SalesDate) AS SalesDate

    FROM Sales

    WHERE SalesPersonID IS NOT NULL

)

SELECT SalesPersonID, COUNT(OrderID) AS TotalSales, SalesDateFROM Sales_CTE

GROUP BY SalesDate, SalesPersonID

ORDER BY SalesPersonID, SalesDate

There are a few differences I've seen between CTE's and temporary tables that may discourage you from using them on a regular basis.

• No option to alter (add or remove columns) the CTE and it's contents
• No option to update or delete rows from the CTE

If you don't need to do either, then a CTE is your best option.  There are also many advantages to using CTE's

• Use the CTE multiple times within the same query, resulting in not having to write the same query over and over.
• Avoid making mistakes like referencing incorrect columns and/or tables.