There are several really good blogs that already exists that explain the differences between Table Variable, Common Table Expressions (CTE) and Temp Tables and here are a few.

• Ben Snaidero’s Temp Table vs Table Variable Performance Testing
• Aaron Lowe’s Temp Table vs. Table Variable

However once you know the differences how do you decide on which ones to use?  Now I know there is the always dreaded it depends which is an answer that I do truly hate so instead I decided to put together some best practices to serve as a guideline on when to use what.  

CTE

In general what I like to say is only use a CTE if you need recursion, and yes I do realize that there is almost no difference between a CTE and a derived table.  However I say only use if you need recursion because there are some cases when joining a CTE multiple times can create a really nasty execution plan. 

Table Variable

Use a Table Variable in the following scenarios:

1. Small tables
2. Data is only inserted once
3. Data has no need to be updated
4. Known number of rows that doesn’t change

One of the reasons for the above recommendations is that a Table Variable is assumed to have a low number of rows usually 1 for generation of execution plans which can cause a cardinality issue.  If you read Ben Snaidero’s article above you will also see the performance hits for updates to temporary tables.

Temporary Table

Use a Table Variable in the following scenarios:

1. Large tables
2. Need for additional DDL, such as an index
3. Need to access data in child stored procedures
4. Need statistics
5. Data needs to be inserted multiple times or updated
6. Unknown Data size

Temporary tables as you can see have a much broader use and are more of the catch all for everything that doesn’t fall into the CTE or Temp Table bucket.  One thing to remember is that if you have a data set that needs to be generated that needs to be reused through a process of multiple stored procedures is that a Temp Table can be used in child stored procedures in the execution tree.  This is a great way to generate temporary data only once and access it in multiple stored procedures. 

Conclusion

Remember that these are just best practices or in other words a starting point and one that can be generally applied regardless of the skill set of the SQL developer.   As with anything there is always times where the data science will call for an exception to the rule and when it does that is when you go outside the best practices.