In SQL Server 2005, Microsoft introduced the Common Table Expression (CTE).  CTEs share similarities with VIEWS and derived tables, but are really not the same as either.  Oracle SQL also supports CTEs and while the syntax is basically the same, some of the properties that we’ll discuss may be slightly different.

Let’s take a look at a very simple CTE to get started.

CREATE TABLE #Vehicles
(
    VehicleID     VARCHAR(5)
    ,VehicleType  VARCHAR(5)
    ,Location     VARCHAR(3)
    ,PRIMARY KEY (VehicleID)
);
INSERT INTO #Vehicles
VALUES ('12211', 'TRUCK', 'BKK'),('12212', 'CAR', 'BKK'),('12213', 'TRUCK', 'CNX')
    ,('12214', 'CAR', 'CNX'),('12215', 'TRUCK', 'HDY'),('12216', 'CAR', 'HDY');
WITH BKKVehicles AS
(
    SELECT VehicleID, VehicleType, Location
        ,rn=ROW_NUMBER() OVER (ORDER BY VehicleID)
    FROM #Vehicles
    WHERE Location = 'BKK'
)
SELECT VehicleID, VehicleType, Location
FROM BKKVehicles;

Our CTE begins with the keyword WITH and ends at the closing parenthesis.  Below the CTE is what I’ll call the “main query.”  This CTE retrieves only vehicles whose location is BKK and adds a ROW_NUMBER to that result:

VehicleID VehicleType  Location  rn
12211     TRUCK        BKK       1
12212     CAR          BKK       2

There is a widespread belief that CTEs can improve performance, but the truth is they neither improve nor detract from performance.  They are simply a way to make your code more readable, although they do offer a couple of things that may also make your life a bit easier.  Let’s look at some of the rules/properties of a CTE, comparing and contrasting with VIEWs and derived tables where appropriate.

• You must remember to terminate the statement preceding the CTE with a semicolon, otherwise SQL will throw this error message at you:

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

• You can rename the columns returned by the CTE by providing the column names between parentheses immediately after the name of the CTE (our CTE is named BKKVehicles) and before “AS.”
• A CTE must contain a SELECT and it may not contain INSERT, UPDATE, DELETE or MERGE statements.
• CTEs will inherit the indexing of the tables upon which they are based.
• CTEs are more like a derived table than a VIEW because they exist only for the life of the main query which follows them.  In order to reuse a CTE in a subsequent query, you must resupply the same code to the second query.
• You can use a CTE as source or target in UPDATE, INSERT, DELETE and MERGE queries, but there are some restrictions.  This is similar to a VIEW.
• You may have more than one CTE associated with a query.  When more than one CTE is defined, they are referred to as “cascaded” or “stacked” CTEs.  You may not however nest CTEs within CTEs.
• You may code CTEs within VIEWs, FUNCTIONs or Stored Procedures.
• You may refer to a CTE more than once in the main query.  Contrast this with a derived table, which if you’d like to use it more than once, must be coded as many times as you need it.
• You may refer to a CTE in another CTE as long as the CTE being referred to occurs above the CTE that is doing the referring in the CTE stack.
• CTEs support recursive queries.

CTEs are most often used with SELECT, but you can UPDATE through a CTE as in this example also.

WITH BKKVehicles AS
(
    SELECT VehicleID, VehicleType, Location
        ,rn=ROW_NUMBER() OVER (ORDER BY VehicleID)
    FROM #Vehicles
    WHERE Location = 'BKK'
)
UPDATE BKKVehicles
SET VehicleType = 'VAN'
WHERE Location = 'HDY';

In this example, no rows are updated because the table returned by the CTE does not contain any rows whose location is HDY.

Likewise you can delete through a CTE.

WITH BKKVehicles AS
(
    SELECT VehicleID, VehicleType, Location
        ,rn=ROW_NUMBER() OVER (ORDER BY VehicleID)
    FROM #Vehicles
    WHERE Location = 'BKK'
)
DELETE FROM BKKVehicles
WHERE rn > 1;
SELECT *
FROM #Vehicles;

After the DELETE runs, the rows remaining in our table are:

VehicleID  VehicleType  Location
12211      TRUCK        BKK
12213      TRUCK        CNX
12214      CAR          CNX
12215      TRUCK        HDY
12216      CAR          HDY

This is actually quite a useful method of deleting duplicate rows from a table.

We mentioned that when updating or deleting through a CTE, certain restrictions may apply.   Basically all that means is that the target rows in the target table must be unambiguous.  For example, if you happen to JOIN the target table with another table, the JOIN must be exact (no duplicate rows generated) otherwise the effort will likely fail.  Highly complex queries involving many JOINs, etc. may also confuse the compiler and make it unable to recognize the target table.

Here’s an example of using a CTE as the source for an INSERT, to generate some additional sample rows in our table.  It also demonstrates how you can name the columns generated by the CTE.

WITH MoreRows (VehicleID, VehicleType, Location) AS
(
    SELECT '12218','VAN','BKK'
    UNION ALL SELECT '12219','VAN','CNX'
    UNION ALL SELECT '12220','VAN','HDY'
)
INSERT INTO #Vehicles (VehicleID, VehicleType, Location)
SELECT VehicleID, VehicleType, Location
FROM MoreRows;

A CTE can also act as either source or target tables for a MERGE, but since MERGE is a topic that is deserving of consideration on its own, we’ll do a separate blog entry for that.

Final Remarks

We have demonstrated how a Common Table Expression can be used in SELECT, UPDATE, DELETE and INSERT statements.  CTEs are basically a way to improve the readability of the SQL code you produce, having no impact on their performance.

While we have mentioned that CTEs can be used for recursive queries, we haven’t provided any examples of this because it is quite an advanced topic.  However if you are interested in how to do this, you might want to take a look at Exploring Recursive CTEs by Example.

Follow me on Twitter: @DwainCSQL

Copyright © Dwain Camps 2014 All Rights Reserved