Temporary Tables – Part 5

Today, I am going to talking about common table expressions. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s.

The WITH key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored procedure — name of object, fields of object, AS keyword, and body of object (query). The only difference is the body is between two brackets () instead of a BEGIN and END. A CTE must be followed by a single DML statement such as SELECT, DELETE, INSERT, UPDATE, or MERGE.

The first example of a common table expression creates the car ownership table for person A. Please see the AUTOS database I created in a earlier article. The cool thing about this example is that the data is in-line VALUES and does not come from a table.

I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.

A common query pattern is to get the most recent id by date. Joining these results to the original table will bring back the most recent record by date.

The SELECT query below uses the last order by customer id as common table expression named MC. The main query uses the CTE to return customer name, sales id, sub-total and order date. This might be a query for the SALES team to cold call customers that have not ordered in the past x months.

I am going to create a duplicate of the department table from the Adventure Works database.
This table will be used in the examples below.

Common table expressions can be used as the target of an UPDATE statement. The query below updates the two Quality Assurance deparment names with a location ‘US’ as a prefix. We are doing this to get ready for making a ASIA department with the same job functions.

Common table expressions can be inserted into directly if they are based upon a simple table. Just like insertion rules for views, aggregation and joins can make a CTE non-deterministic. The query below INSERTs two new ASIA departments like their US counterparts.

Last but not least, derived tables can be used as the target of a DELETE statement.

In short, common table expressions (CTE’s) are useful when you want to combine the results of several CTE’s into one large result set. The real power of CTE’s comes with recursion. Traversing a hierarchial table structure can be accomplished with self referencing CTE’s. I did not talk about this topic since it is not related to storing results into a temporary table (object).

Next time, I want to analyze performance, disk space, and query plans of all five ways to create temporary objects to solve a business problem.