Home > Querying, SQL Server 2008R2 > Common Table Expressions (CTE) – What they are and how to use them

Common Table Expressions (CTE) – What they are and how to use them

Common Table Expressions are a nice tool in SQL Server that allow you to create Temporary Tables without using TempDB.  They only exist while the query runs, whereas temp tables live for the entire session.  They are a great tool when it comes to optimizing queries, as it also reduces the load on tables at runtime.  Today, we’ll discuss what they are and how they’re used.

A Common Table Expression (CTE) works by executing a query and storing the results in the memory at run time.  It’s exactly the same concept as using a temporary table (#Table), except it’s more efficient in that it doesn’t actually transfer the information to a new table.  The data can be manipulated and used exactly the same as a #Table however.

CTE’s are simple to code.  They are housed at the beginning of a query, just after the database selection (USE Database GO).  The simple way to remember is WITH Name (Column1, Column2…) AS (SELECT query).

Below is a basic CTE from the AdventureWorks2008R2 database.

USE AdventureWorks2008R2
GO

WITH AWContacts ( Name, Email )
AS ( SELECT p.FirstName + ‘ ‘ + p.LastName ,
e.EmailAddress
FROM Person.Person p
INNER JOIN Person.EmailAddress e ON p.BusinessEntityID = e.BusinessEntityID
)

SELECT *
FROM AWContacts

The above code will store the results from the Person and EmailAddress query and store it in memory at runtime to call it up later in the query.  The AWContacts CTE is called like any other table in the database.  Like I said earlier, it saves strain on the database because it doesn’t copy the information over to TempDB, it just uses it, then drops it.

Multiple CTE’s

Something to remember that trips some people up, if you are looking to use multiple CTE’s in a query, you must put a comma after the closing parenthesis, and not use the WITH.  I know, that sounds confusing, so here is an example:

WITH AWContacts ( Name, Email )
AS ( SELECT p.FirstName + ‘ ‘ + p.LastName ,
e.EmailAddress
FROM Person.Person p
INNER JOIN Person.EmailAddress e ON p.BusinessEntityID = e.BusinessEntityID
),
AWContacts2 ( Name, AddtlInfo )
AS ( SELECT p.FirstName + ‘ ‘ + p.LastName ,
p.AdditionalContactInfo
FROM Person.Person p
INNER JOIN Person.EmailAddress e ON p.BusinessEntityID = e.BusinessEntityID
)

CTE’s are very useful when writing large queries that move around a lot of data.  By eliminating #Tables, you’ll save both resources and execution time.

For more information about CTE’s, visit the MSDN Common Table Expressions page.

Advertisements
  1. Rick
    September 29, 2012 at 10:16 pm

    I think you are overstating the similarity to temp tables here. CTEs most closely resemble correlated subqueries. There is a very important distinction between temp tables and CTEs that might be lost by someone reading this article. Temp tables are real tables, whereas CTEs are more like a view. Where this can get you into trouble is if you refer to a CTE more than once, the underlying SELECT that defines the CTE will be executed each time. This has possible negative effects: 1) performance; if the CTE consumes significant resources, then multiple executions can be costly, and 2) unexpected differences in results.

    Here’s an example which demonstrates this difference. For the temp table, the code which populates it is executed only once, but for the CTE, each time it is called, a new guid is generated since SQL Server is actually running the sql that defines the CTE each time.

    CREATE TABLE #guid (a_guid_value uniqueidentifier)
    INSERT #guid VALUES (NEWID())

    — this will return the same value twice
    SELECT a_guid_value FROM #guid
    UNION ALL
    SELECT a_guid_value FROM #guid

    — each reference to guidcte will result in a different guid value since the CTE is being executed each time and NEWID() is generating a new value
    WITH guidcte AS (SELECT NEWID() a_guid_value)
    SELECT a_guid_value FROM guidcte
    UNION ALL
    SELECT a_guid_value FROM guidcte

    • September 30, 2012 at 2:02 pm

      You are correct. I touched on this a little bit in the Recursive Query bit I posted. CTE’s are excellent when used the right way; I didn’t mean to say that they should be used in every situation…I kind of let my personal love for CTE’s and hate for #Tables come out a little bit in this post.

  1. September 20, 2012 at 3:08 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: