| 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 tableSELECT * INTO #temp from your_table | SELECT INTO method cannot be used for table variable. You get error for the followingSELECT * 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 |
About The Author
7 comments:
And more important:
temp table datas are stored on disk, in the tempdb database.
Table variable are stored in memory
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
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
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
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.
that's a good example of table variable with inserts. So basically GO keeps it into a separate Batch right?
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.
Post a Comment