Common Table Expression, Just a Name

the common table expression (cte) is a great tool in t-sql. the cte provides a mechanism to define a query that can be easily reused over and over within another query. the cte also provides a mechanism for recursion which, though a little dangerous and overused, is extremely handy for certain types of queries. however, the cte has a very unfortunate name. over and over i’ve had to walk people back from the “table” in common table expression. the cte is just a query. it’s not a table. it’s not providing a temporary storage space like a table variable or a temporary table. it’s just a query. think of it more like a temporary view, which is also just a query .

every time i explain this, there are people who don’t believe me. they point to the “table” in the name, “see. says so right there. it’s a table.”

it’s not and i can prove it. let’s create a relatively simple cte and use it in a query:

with    mycte
          as (select    c.customername,
                        cc.customercategoryname
              from      sales.customers as c
              join      sales.customercategories as cc
              on        cc.customercategoryid = c.customercategoryid
              where     c.customercategoryid = 4)
    select  *
    from    mycte;

now, i’m going to run the query within the cte and the cte together as two statements in a batch and capture the execution plans:

on the top, the cte, on the bottom, the query. you’ll note that the execution plans are identical. they each have the exact same query plan hash value in the properties, 0x88efd2b7c165e667, even though they have different query hash values, 0x192ffc125a08cc35 and 0xfeb7f2bcac853cd5, respectively. further, if i capture the query metrics using extended events, i get identical reads and, on average, identical execution times:

this is because, there is no table being created. the data is not treated differently. a cte is just a query, not some type of temporary storage.

heck, let’s do one more thing. let’s use the latest ssms plan comparison tool and highlight one of the operators to see what differences there are internally in the plan:

i don’t see a lot of differences. in fact, i don’t see any. that’s because the optimizer recognizes these two queries as identical. if it was loading data into temporary storage, you would see differences in something. we don’t. this is because, despite the somewhat unfortunate emphasis that gets placed on the table portion of the name, the emphasis of the name, common table expression, should be on the word expression.

i will point out an interesting difference, especially useful for those who plug in ctes everywhere, whether it’s needed or not. let’s look at the properties of the two plans:

you can see the similarities and differences that i pointed out earlier in the statement, query hash and query plan hash, as well as the estimated subtree cost and others. what’s truly interesting is that the compilecpu, compilememory and compiletime for the cte is higher than the regular query. while the cte is just a query, it’s a query that adds a non-zero overhead when used, and therefore, should only be used where appropriate (good gosh, i’ve seen people put it everwhere, on every single query, don’t do that).

hopefully, this is enough to establish, truly, completely, and thoroughly, that the common table expression is an expression, not a table.

yeah, i did this before , but it keeps coming up, so i tried a different approach. let’s see if the word gets out. your common table expression is not a table.

i love talking about execution plans and query tuning. i’ll be doing this at an all day pre-con at sqlserver geeks annual summit in bangalore india.