Aaron Bertrand shows us Common Table Expressions:
A CTE is probably best described as a temporary inline view – in spite of its official name, it is not a table, and it is not stored (like a #temp table or @table variable). It operates more like a derived table or subquery, and can only be used for the duration of a single SELECT, UPDATE, INSERT, or DELETE statement (though it can be referenced multiple times within in that statement).
This is a great article on CTEs; give it a read, even if you’re familiar with them.
[…] answer is simple but powerful. Once you’ve read up on CTEs, you start to see the power of chaining CTEs. And then you go CTE-mad until you see the […]