July 29, 2010

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

7 Responses to "Temp Table VS Table Variable in SQL Server"
  1. Tripy said...
    July 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

  2. Matt Cherwin said...
    July 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

  3. Anonymous said...
    September 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

  4. DRG said...
    September 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

  5. Max Trinidad said...
    June 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.

  6. Matt said...
    June 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?

  7. Max Trinidad said...
    June 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.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions