1

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 ?
Community
  • 1
  • 1
Jeevan Bhatt
  • 5,881
  • 18
  • 54
  • 82

2 Answers2

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

anishMarokey
  • 11,279
  • 2
  • 34
  • 47
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...

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • 1
    RE: The first line of this answer try `declare @t table (n int) insert into @t values(1),(2) select sys.fn_PhysLocFormatter(%%physloc%%) FROM @t` You will see that the table variable rows have a page and slot location in `tempdb`. – Martin Smith Mar 06 '11 at 11:21