Pete On Software

RSS Feed

Common Table Expressions

CTE (Common Table Expression) syntax can be a little tricky at first, but once you have a little primer, you will love using them as much as I do. Examine the following code below that uses a derived table from which to select. (Please realize that this is a contrived example and you could of course just join the tables in the query directly.)

The query between the parentheses creates a table structure (that I aliased as “c” and “t” for my two) that only lasts for the life of the query. I can then reference those tables in the query in any way that I would a regular table. However, that syntax can make it very difficult to read, especially in long queries with several complicated derived tables.

Sql Server 2005 introduced a new syntax to be used instead called Common Table Expressions (CTEs). Using a CTE, the above query would be written like this

Alternatively, you can also explictly declare you column names in the CTE definition and not have to alias the columns in the query itself.

One “gotcha” is that the CTE – beginning with the WITH keyword – must be the first statement that is encountered. All previous statements above it must be terminated with a GO or a semicolon. If you ran the following

You’d get the errors shown below (or something very close)

Some people even just make a habit of actually prefixing the WITH with a semicolon every time to make sure that everything goes off without a hitch. If I do run this script instead there are no issues.

This is just a quick primer on CTEs, but I hope that you can find it useful. I really find that it cleans my code up quite a bit.

One Comment

Pete on Software  on January 15th, 2010

[…] that same section of code again to read like this and then run the script. Note, I am using the CTE syntax […]

Leave a Comment