When Do You Reach For A #temp Table?

Disclaimer: Rather than spending time writing a test scenario from scratch, I used Claude Code to generate the test scenarios for this article. I wanted a certain behavior to demonstrate a couple of differences between #temp and @table. You can download the test scripts from my public GitHub repo (see info at the bottom of this post).

#temp tables can be useful in so many ways. I hadn’t really given them much thought beyond using them when a situation called for it, until I saw the invitation for T-SQL Tuesday #201 Invitation: Temp Tables, Friend or Foe? This month’s T-SQL Tuesday is hosted by Jeff Taylor (b).

For this post, I needed to do some research because there is more to #temp tables than simply creating one and using it. As I started digging into the topic, I realized there are a lot of discussions around #temp tables and @table variables, especially around when one might be a better choice over the other.

One of the common arguments is that #temp tables go to disk while @table variables stay in memory. Another argument is that this is a myth. That got me curious about what the actual differences are and when they really matter. Discussions like this almost always starts with where the data is stored (disk vs memory).

Both #temp tables and @table variables use tempdb. Both allocate pages that are managed by the buffer pool. Depending on the size of the data, memory availability, and other factors, those pages may or may not ever be written to disk. If that’s the case, then maybe “memory versus disk” isn’t really the question we should be asking.

Let’s take a look at a couple of scenarios.

Test SQL: SQL Server 2022 CU25 (16.0.4255.1) container (Orbstack on Macbook pro)
CPU: 8 (schedulers)
MAXDOP: 0
Cost Threshold for Parallelism: Default 5 (for parallelism test scenario)

The difference is statistics

One of the biggest differences between #temp tables and @table variables is statistics. #temp tables can have statistics created and maintained by SQL Server, which gives the optimizer more information when generating an execution plan. @table variables do not have column statistics, although newer versions of SQL Server have improved their cardinality estimates through deferred compilation.

#temp tables can have statistics. Those statistics include histograms that help the optimizer understand how the data is distributed and make better estimates.

A table variable does not get column statistics. Even with newer versions of SQL Server and improvements like deferred compilation, the optimizer still does not have the same level of information that it has with a #temp table.

This is where many of the differences start to show up. Things like indexes, constraints, recompiles, and parallelism are often related to the fact that a #temp table is a temporary table with metadata that the optimizer can use, while a table variable has different behavior.

The rule I am starting to use from this research is simple:

Use a #temp table when the optimizer needs more information about the data. Use a table variable when the amount of data is small, the usage is simple, or you specifically need the behavior that a table variable provides.

Let’s take a look at a simple test.

I wanted to keep this as fair as possible. Both objects have the same structure, the same data, and the same nonclustered index. All the test scripts can be found in the Github repo (see link at the bottom of this post).

CREATE TABLE #temptable 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

DECLARE @tablevariable TABLE 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

CREATE TABLE #temptable 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

DECLARE @tablevariable TABLE 
(
    id int NOT NULL, 
    grp int NOT NULL, 
    INDEX ix_grp NONCLUSTERED (grp)
);

The data is intentionally skewed. grp = 1 has 9,000 rows, while grp = 2 has only 10 rows.

When I checked the estimated row counts, the difference was obvious.

The #temp table had statistics, so the optimizer had information about the data distribution. The estimates were close to the actual row counts.

The table variable was different. Even though it had the same index, the optimizer did not have the same information available. The index gave it something to seek on, but it did not tell the optimizer how the data was distributed.

That distinction matters because estimates influence the rest of the execution plan. Row estimates affect join choices, memory grants, and other optimizer decisions. If SQL Server estimates 100 rows but the query actually returns 9,000 rows, the optimizer may choose a plan that isn’t optimal for the actual workload. That can also lead to memory spills to tempdb if the memory grant is too small.

One thing I noticed while testing this is that the estimate for a table variable is not always the same. For example, removing the index can change the estimate. The important part is that the optimizer still has limited information about the data inside a table variable.

So what about SQL Server 2019 and table variable deferred compilation?

Deferred compilation helps because SQL Server can see the table variable row count before compiling the statement. This improves cardinality estimates in many scenarios.

However, it does not create statistics. Knowing that a table variable has 10,000 rows is different from knowing how those 10,000 rows are distributed. A row count can help, but it does not replace a histogram.

Table variables migh have a problem with parallelism

Note: It is quite complicated to test paralellism on my test environment. Some tests results returned “inconclusive”. Your mileage varies (depending on your config basically).

I also looked at how #temp tables and @table variables behave when it comes to parallelism.

A common statement is: “@table variables are always serial.” That may not be completely accurate. But again, I have not tested this extensively. FYI.

The restriction is related to modifying the table variable, not reading from it. For example, a regular SELECT or a query that reads from a table variable can still use parallelism. The limitation shows up when SQL Server is inserting into or modifying the table variable. I need to test this with bigger workload when I get the chance.

Again, this is not conclusive.

Download the test scripts to see how this looks in your environment. Link to my Github repo is at the bottom of this post.

The impact really depends on how much data you are working with.

If you are inserting a small number of rows into a table variable and reading from it later, the lack of parallelism during the insert probably does not matter. The difference between a serial and parallel insert for a small dataset is not something you will likely notice.

Where this becomes important is when you start loading a large amount of data. A table variable that is used as a staging area for thousands or millions of rows can limit the insert operation to a serial plan when a #temp table could take advantage of parallelism.

For small amounts of data, use whichever option makes the code easier to understand. For larger data loads, a #temp table is usually worth considering.

The test scenario used in this post covers only a couple of the differences between #temp tables and @table variables. If you want to experiment with the scenarios yourself, you can grab the test scripts from my GitHub repository and run them in your own environment.

https://github.com/MarlonRibunal/sqlserver-demos/tree/main/tempdb

The post When Do You Reach For A #temp Table? first appeared on SQL, Code, Coffee, Etc..