Sql-server – Why is @temp table performance some times worse than #temp table performance

sql-server-2008temporary-tables

I was recently working in a very slow stored procedure (took 5 minutes to run). I made a very small tweak from doing this:

declare @tempTable table
(
  ...
)
insert into @tempTable
select .....

to

select ... into #tempTable from someTable

The script then ran in ~2 seconds. What can explain this time difference?

Best Answer

Table Variables don't have statisics in the same way as Temp Tables normally they're assumed to have only 1 row. This incorrect estimate of rowcount will make a nested loop operation look like the best plan but when this is done for a larger amount of rows the cost can easier be greater than a table scan.

Related Question