sql temp tables @tmp vs #tmp

Question

whats the difference between the two types of temp tables @tmp vs #tmp in SQL 2005? and are their other types i am unaware of?

thanks

Answer 1

#tmp is a temp table and acts like a real table mostly. It can have indexes, can have statistics, participates in transactions, optimiser will work out correct row estimates

@tmp is a table variable. No indexes, no statistics, not transaction aware, optimiser always assumes exactly 1 row

Otherwise, they are both scoped (slightly different), in memory/cache but context is tempdb though, will spill to disk if too big etc

Edit:

About keys on table variables. They make no difference. There are no stats and one row is assumed. It will change a table scan to a clustered index scan which is the same. Check any query plan and estimated rows.

Also, just read this Read What a difference a temp table makes over a table variable

The first thing that I did was put a primary key on the @ComputersToProcess table variable. That turned the table scan into a Clustered Index Scan, but didn’t do anything for performance.