Table variable vs temp table in SQL Server 2008

Question

Possible Duplicate:
What's the difference between a temp table and table variable in SQL Server?

What is the difference between table variable and temp table, actually I have two question on it.

• how to decide when to use what?
• which one is better in performance and WHY ?

Answer 1

A table variable is allocated in memory, only when the table gets to large, will it be assigned to the tempdb.

On a temp table you can create indexes as per normal tables, as these are created in the tempdb by definition.

So I would recomend that you make this decision on the number rows to be stored...

Answer 2

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #' 
insert into @T select 'old value @' 
begin transaction 
     update #T set s='new value #' 
     update @T set s='new value @' 
rollback transaction 
select * from #T 
select * from @T 

Read more : http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx