Becoming overly enthusiastic about a new SQL Server feature can backfire if you don’t do some testing. One example is the table variable introduced with SQL Server 2000. At the time, there was a myth that table variables would always perform better than temp tables with the reasoning that, by definition, variables are stored in memory. It was common knowledge that temp tables are stored in tempdb, but many assumed that a variable would not be. It was easily proven that table variables are stored in tempdb just like temp tables.

I consulted at a couple of shops where developers had fully embraced the use of table variables to store intermediate results in stored procedures. At one customer, table variables were so prevalent that there must have been an entire project devoted to replacing temp tables with table variables in stored procedures regardless of how many rows involved. (There are other, better uses for table variables such as table-valued parameters and use in table-valued functions.)

Why are there some performance issues with table variables? While certain indexes can be included in the table variable definition due to primary keys and unique constraints, there are no distribution statistics on table variables. The optimizer estimates that all table variables have a cardinality of one row which could negatively affect the plan, especially when thousands of rows are involved. Even though indexes could be in place, they don’t matter with just one estimated row.

In 2014, Microsoft added the ability to create memory-optimized table variables. Finally, table variables could truly live in memory, but to do so requires some work on the SQL Server instance. There must be a filegroup in place for MEMORY_OPTIMIZED_DATA, and you must create a user-defined table type for each possible table variable schema. Assuming you have those prerequisites in place, you just need to declare a variable of the type and then populate it with rows. It’s easy if you have the types defined, but that’s going to take some planning!

Finally, in 2019, Microsoft took care of the cardinality issue as part of the Intelligent Query Processing features. This new feature is called “table variable deferred compilation.” Basically, the optimizer adjusts the plan based on the actual table variable row counts. There are still no statistics, but it’s more likely that it will choose a more appropriate plan based on the actual number of rows.

Table variables were enthusiastically embraced by lots of folks as the answer to many performance problems. The reality was quite different. Luckily, Microsoft has improved this feature over time, and it’s worth taking another look.