Wednesday, October 20, 2010

Compare Temp Table & Table Variable in SQL Server

Temp Table
--------------
1) 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
2) It is possible to alter the temp table to add columns, idexes,etc
3) It is possible to truncate a temp table
4) SELECT INTO method can be used for temp table
SELECT * INTO #temp from tablename
5) Temp table can be useful when you have a large amount of data
Table Variable
--------------

1) 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
2) It is not possible to alter a table variable
3) It is not possible to truncate a table variable
4) SELECT INTO method cannot be used for table variable. You get error for the following
SELECT * INTO @t from your_table
5) For small set of data, table variables can be useful

No comments:

Post a Comment