Search

Friday, January 21, 2011

Temp Table VS Table Variable in SQL Server

Here are some differences between Temp Table and Table Variable in SQL Server
Temp Table
Table 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,etc
It is not possible to alter a table variable
It is possible to truncate a temp table
It 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 data
For small set of data, table variables can be useful

No comments:

Post a Comment