What are CTE’s?

I was chatting with someone last week and they wanted to know how much or how little I knew about T-SQL. They asked me what a CTE was. Now for a lot of people who have been around T-SQL for awhile, this might seem like a pretty elementary question, but for me, I had never been exposed to them, probably because the environments that I had worked in before were small enough that performance wasn’t as big of a consideration (jealous yet?). I’ll explain later in this article what a CTE has to do with performance. But first, let’s start with what a CTE is. But wait! Who of you knows what a CTE is?

Official Definition

CTE stands for Common Table Expression. A CTE is a temporary version of a SELECT query that is created at execution of the query that the CTE is contained in. CTE’s can be used in SELECT, INSERT, UPDATE, DELETE, and CREATE VIEW statements.

What makes a CTE different than a derived table or sub-query?

CTEs are more efficient than sub-queries and derived tables for a multitude of reasons. One distinction is, CTEs can be joined to themselves and be joined to multiple times unlike a derived table. Also, it is more efficient than writing a sub-query for the same use because a CTE is a stored set of data that can be continually referenced throughout a query, where-as a sub-query is not stored to be referenced and can make for high server overhead to complete.

Syntax for how to use CTE’s

You would create a create CTE as follows:

WITH CTE_NAME (column1, column2) AS

(SELECT column1, column2 FROM table1)

After the CTE is declared with the WITH statement, it can be referenced the same as any table or view within a query. The only requirement is that the statement referencing the CTE must be a part of the same executed SQL statement that declared the CTE because, like a derived column, once the statement is finished executing, the CTE result set disappears. For instance, see this example below:

WITH CTE_NAME (column1, column2) AS

(SELECT column1, column2 FROM table1)

SELECT column1, column2 FROM CTE_NAME

WHERE column2 > 100000

I would like to thank Kirk Kuykendall and the article he wrote Common Table Expressions. This helped me get the answers I need and gave me the background to write this article on my blog for my readers.

Please go ahead and like or comment on this article and let me know how I’m doing. Also, if you have a subject you would like me to blog on or questions about this article, you can also email me at joshua.luedeman@gmail.com.