CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?
CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?
Answer
As I already said in my comment: IT DEPENDS!
It really does depend on your query, your data (how much is there? What kind is it??) and much more.
A few bullet points to remember, though:
a CTE is an "inline view" valid only for the next statement; if possible, SQL Server will keep a temporary result in memory
Temp Tables come in two flavours: those visible to your connection only (
create table #temp), or those that are globally visible to all connections (create table ##temp); both will be auto-dropped when no connection is using them anymore. You can define indices on temp tables, and they're part of transactionsTable variables don't allow you to create indices on them, nor do they participate in the transactional dance - this can be a pro or a con - just be aware of it! A
ROLLBACKhas no effect on the data contained in a table variable....