SQL Interview: 91 Table variables and memory

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

A developer in your team has observed that your database servers are very I/O bound. He thinks the I/O is simply too slow.

What he’s suggesting is that you replace all use of temporary tables with table variables. His argument is that they are memory-based instead of disk-based and that this should help to reduce the I/O load. Your applications make heavy use of temporary tables, and most have large numbers of rows.

What would you advise?

Answer:

I’ve often heard table-variables referred to as memory-based tables. They aren’t. It’s a common, but incorrect myth. The assumption is that variables are in-memory structures, so table-variables would be as well.

But table-variables with a reasonable number of rows are going to end up in tempdb, just like temporary tables do.

More concerning though, is that there is limited cardinality data available for table-variables, when compared to temporary tables, so you could end up with some very poor execution plan choices if you made this change.

2025-11-25