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


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

7 comments:

Tripy said...

And more important:

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

Table variable are stored in memory

Matt Cherwin said...

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

Anonymous said...

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

DRG said...

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

Max Trinidad said...

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.

Matt said...

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

Max Trinidad said...

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.