Table expression and table variable/temporary table persistence

Question

From what I understand, table expressions do not persist, meaning every reference to it in the outer code is a repeated call to the definition. This is where table variable/temp tables comme into the picture, to persist the table and avoid re-evaluating every single time. It seems to me table variables/temp tables are better choice to avoid repetition. I made an anology to programming, where instead of calling method

myMethod()

each time, I would rather assign a variable to it

int res = myMethod()

to avoid calling myMethod() multiple times. myMethod() is analogous to table expression and res is table variable/temp table.

So why would we use table expressions? In which scenarios must we use table expressions instead of the other two? And vice versa?

Thank you.

CTE's add recursive capabilities, and also make code easier to read than derived tables for a lot of folks. — Tab Alleman, May 08 '15 at 12:25
An interesting opinion http://sqlserverplanet.com/design/ctes-vs-temp-tables-an-opinion — Paolo Costa, May 08 '15 at 12:43

Ray Krungkaew · Accepted Answer · 2015-05-11T01:28:48.043

Speaking from my experience

Imagine you insert 10 million records into temp table and use it for only one time. It will be sheer waste of time, memory and cpu. To use huge temp table more efficiently you also need to create index on top of the temp table. While, CTE use index from existing table.
When you want to group column from scalar expression. E.g. group the row_number() column.
More readable for complex query
And of course it allow us to write recursive query which is very cool

Table expression and table variable/temporary table persistence

1 Answers1