When to use Common Table Expression (CTE)

sql sql-server tsql common-table-expression
imak picture imak · Jan 19, 2011 · Viewed 356.3k times · Source

I have begun reading about Common Table Expression and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well? Can someone give me a simple example of limitations with regular select, derived or temp table queries to make the case of CTE? Any simple examples would be highly appreciated.

Answer

John Sansom picture John Sansom · Jan 19, 2011

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

An example of self referencing is recursion: Recursive Queries Using CTE

For exciting Microsoft definitions Taken from Books Online:

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

  • Reference the resulting table multiple times in the same statement.

Related questions

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

In this excellent SO question, differences between CTE and sub-queries were discussed. I would like to specifically ask: In what circumstance is each of the following more efficient/faster? CTE Sub-Query Temporary Table Table Variable Traditionally, I've used lots of …

Read More
Update records in table from CTE

I have the following CTE that will give me the DocTotal for the entire invoice. ;WITH CTE_DocTotal AS ( SELECT SUM(Sale + VAT) AS DocTotal FROM PEDI_InvoiceDetail GROUP BY InvoiceNumber ) UPDATE PEDI_InvoiceDetail SET DocTotal = CTE_DocTotal.DocTotal Now …

Read More
Can you create nested WITH clauses for Common Table Expressions?

WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work.

Read More