, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?

So as always the answer is very simple. It depends on your usage. I have personally uses Temp table most of the time. I will try to explain the differences here in detail.

1. The primary difference between table variables and temporary tables is that table variables do not have statistics. Temporary tables do have statistics on them.

2. So, when you are manipulating data within temporary tables it can lead to recompiles. Using a table variable avoids the recompiles, possibly speeding up your processes. That’s regardless of the number of rows.

3. But, if you are then using a JOIN or a WHERE clause on a table variable, they have no statistics, so the optimizer will assume a set number of rows (1 for all versions prior to 2014 and 100 for 2014 and 2016). This is fine if you have only a few rows, but if you have more than the set number, it can lead to really bad performance caused by bad choices by the optimizer.

4. So, the limit is, how are you using the table variable? If you’re loading some data into it, but then you don’t have to filter it using a WHERE clause or JOIN against it, great. It’ll help with recompiles. If you’re loading data into it and then you’re doing an UPDATE so that you have to use a WHERE clause or a JOIN, you’re usually better off using a temporary tables except in the case where you have a very small number of rows, say, less than 200.

I hope you have enjoyed the article. In the next post I will explain where CTEs are stored ?. Thanks for reading !

-Pawan Khowal