Temp Table VS Table Variable in SQL Server

Here are some differences between Temp Table and Table Variable in SQL Server

Temp TableTable Variable
Temp table is valid for a session.

For eg: when you run the following code

create table #temp(i int)
insert into #temp select 345
Go
create table #temp(i int)
insert into #temp select 345
Go

you will get an error		Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost

For eg: when you run the following code

declare @t table(i int)
insert into @t select 45
GO
declare @t table(i int)
insert into @t select 45
GO

you will not get an error
It is possible to alter the temp table to add columns, idexes,etcIt is not possible to alter a table variable
It is possible to truncate a temp tableIt is not possible to truncate a table variable
SELECT INTO method can be used for temp table


SELECT * INTO #temp from your_table
SELECT INTO method cannot be used for table variable. You get error for the following

SELECT * INTO @t from your_table
Temp table can be useful when you have a large amount of dataFor small set of data, table variables can be useful
at

7 comments:

  1. tripyJuly 29, 2010 at 4:00 AM

    And more important:

    temp table datas are stored on disk, in the tempdb database.

    Table variable are stored in memory

    ReplyDelete
  2. Matt CherwinJuly 29, 2010 at 6:14 AM

    Tripy,

    I'm afraid that's not accurate. Both table variables and temp tables are instantiated in tempdb. If there's enough memory to hold the entire construct, then both a temp table and a table variable will live entirely in memory. If there isn't, both will be written to disk in tempdb.

    For more information, see: http://support.microsoft.com/kb/305977/en-us

    ReplyDelete
  3. AnonymousSeptember 2, 2010 at 8:31 AM

    Helpful articl.

    one missing point: Do temp tables perform differently temp variables? i.e. will you r application run faster if you move from "temp tables" to "temp variables"

    thanks

    ReplyDelete
  4. DRGSeptember 2, 2010 at 11:18 PM

    A really good article with examples and performance is found here..
    http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx
    I also experienced the same, the procedure with a table variable I created for 2000 works slower now in 2005.
    We the velepers have to use judiciously as to when and were to use which one.

    DRG

    ReplyDelete
  5. Max TrinidadJune 8, 2011 at 5:48 AM

    You can use table variables with inserts along your T-SQL lines. It does work.

    You just point out one catch... not to use the "GO" if you want to keep using your table variable.

    Here's a sample code that works:
    declare @t table(i int)
    insert into @t(i) select 45
    insert into @t(i) select 35
    Select * from @t
    go

    -- this block will fail
    insert into @t(i) select 25
    insert into @t(i) select 15
    Select * from @t
    go

    declare @t table(i int)
    insert into @t(i) select 25
    insert into @t(i) select 15
    Select * from @t
    go

    So, terminating your T-SQL block with "GO" will dispose of your table variable.

    Thx.

    ReplyDelete
  6. MattJune 9, 2011 at 5:21 AM

    that's a good example of table variable with inserts. So basically GO keeps it into a separate Batch right?

    ReplyDelete
  7. Max TrinidadJune 9, 2011 at 6:38 AM

    Hi Matt -Yes! You're right. I've been using Table Variable in combination with Temporary table to update large amount of data and it save some processing time. But, this article show me that the "GO" clears only the Table Variable.

    ReplyDelete