Why are temporary tables faster than variable tables for joins?

Question

Why are temporary tables faster than variable tables for joins in SQL Server?

NOTE. In both scenarios, the tables have PK, and the tables are connected to other "physical" tables through PK.

Answer 1

Both are saved in Tempdb; however, performance issues come into play because the optimizer does not support table variable statistics. This is problematic because the optimizer ALWAYS assumes that there is 1 row in your table variable. Obviously, this can really ruin the query plan, especially when there are a lot of rows in your table variable. I would not use a table variable to store just over 1000 rows; otherwise, performance may be unpredictable.

Answer 2

Temp tables are similar to tables, but when created, they are stored in tempdb, which means that the optimizer can create statistics on them, and table variables are like variables and there are no statistics on them. Usually, when comparing tmp tables and table variables, temp tables come first. The reason is that the query optimizer sometimes creates bad plans for table vars. This can be seen when there is a lot of data. The most I can do is that table variables are more likely to cause unpredictable execution plans compared to plans created for temporary tables. Temp tables, on the other hand, will cause a big recompilation.