Table Variables Use TempDB Too

Derik Hammer proves that classic, non-memory-optimized table variables use disk:

Table variables use tempdb similar to how temporary tables use tempdb. Table variables are not in-memory constructs but can become them if you use memory optimized user defined table types. Often I find temporary tables to be a much better choice than table variables. The main reason for this is because table variables do not have statistics and, depending upon SQL Server version and settings, the row estimates work out to be 1 row or 100 rows. In both cases these are guesses and become detrimental pieces of misinformation in your query optimization process.

It’s worth the read.

Related Posts

Fun With Temp Tables

Kenneth Fisher answers a Brent Ozar pop quiz regarding temp tables: Go ahead and give it a shot .. I’ll wait. So? What do you think? Did you get it right? I did, but I wasn’t 100% certain, nor did I initially think through all of the implications. The question actually has more depth to […]

Read More

Cardinality Estimation On Memory-Optimzied Table Variables

Jack Li explains that the cardinality estimator works the same for memory-optimized table variables as it does for regular table variables: In a previous blog, I talked about memory optimized table consumes memory until end of the batch.   In this blog, I want to make you aware of cardinality estimate of memory optimized table as […]

Read More


April 2017
« Mar May »