Why are temporary tables faster than table variables for joins?

sql-server join table-variable temp-tables
Guillermo Gutiérrez picture Guillermo Gutiérrez · Jul 12, 2013 · Viewed 10.4k times · Source

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

NOTE: In both scenarios the tables have PK, and the tables are joined with other "physical" tables through the PK.

Answer

Adam Haines picture Adam Haines · Jul 12, 2013

Both are persisted in Tempdb; however, performance problems come into play because the optimizer does not maintain statistics on table variables. This is problematic because the optimizer is ALWAYS going to assume there is 1 row in your table variable. Obviously this can really screw up a query plan, especially when there are lot of rows in your table variable. I wouldn't use a table variable to store anything more than a 1000 or so rows; otherwise, the performance could be unpredictable.

Related questions

SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
Update a table using JOIN in SQL Server?

I want to update a column in a table making a join on other table e.g.: UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= …

Read More
Difference between JOIN and INNER JOIN

Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there any difference between the statements in performance …

Read More