Search This Blog

Monday, December 21, 2009

difference between Temp tables and Table variables in Sql Server

            Difference between Temp tables and Table variables in Sql Server

1)      Transaction logs are not recorded for the table variables. They are variables and thus aren't bound to a transaction.
      Temp tables behave same as normal tables and are bound by transactions.
2)      Any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
3)      Table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec (string) statements. Also, they cannot be used in an insert/exec statement.
4)      As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables.
5)      If we use Temporary Table in a stored procedure, we should drop it at the end. It is not necessary in the case of Table variable.

A simple example shows this difference quite nicely:

GO
 BEGIN TRAN
        declare @var table (id int, data varchar(20) )
        create table #temp (id int, data varchar(20) )
       
        insert into @var
        select 1, 'data 1' union all
        select 2, 'data 2' union all
        select 3, 'data 3'
       
        insert into #temp
        select 1, 'data 1' union all
        select 2, 'data 2' union all
        select 3, 'data 3'
       
        select * from #temp
        select * from @var

ROLLBACK

select * from @var
if object_id('tempdb..#temp') is null
    select '#temp does not exist outside the transaction'

GO

We see that the table variable still exists and has all it's data unlike the temporary table that doesn't exists when the transaction rollbacked.

Post a Comment

Popular Posts