Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 .....


select ... into #tempTable from someTable

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

share|improve this question
We can validate MartinC's answer if you post the execution plans for both versions of your stored proc. – Nick Chammas Jan 19 '12 at 21:01
@NickChammas - I'd love to, but I don't recall which stored procedure it was now :(. – O.O Jan 19 '12 at 21:03
up vote 12 down vote accepted

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.

share|improve this answer

Just to add to @MartinC's answer that the row count for table variables is maintained in tempdb.sys.partitions and OPTION(RECOMPILE) can cause this to be used but it doesn't have any more granular statistics to use so will need to fall back on guesses based on that.

You have only shown the population code rather than any code that uses it. Another limitation of queries that insert to table variables is that they cannot have a parallel plan so that could explain why the population query might run faster.

share|improve this answer

MartinC is correct, plus you could also apply indexes to temp tables. This couldn't be done with a table variable.

share|improve this answer
It is possible to add indexes to table variables via constraints both Primary Key and Unique but these still have the same assumptions i.e. 1 row. – MartinC Jan 19 '12 at 21:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.