#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions.
Common properties of #TempTable and @TempVariable
They are instantiated in tempdb.
They are backed by physical disk.
Changes to them are logged in the transaction log1. However, since tempdb always uses the simple recovery model, those transaction log records only last until the next tempdb checkpoint, at which time the tempdb log is truncated.
Discussion of #TempTable and @TempVariable relations with each other.
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch it is declared in. Within its scope, a table variable can be used like a regular table. Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined. Table variables require fewer locking and logging resources. If a temporary tables are used in stored procedure, it will create separate copy of the temporary table for each user in system who makes use of that stored procedure. SQL Server identifies the different temporary tables by internally adding a numerical suffix to the name.
Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used. If the temporary tables referred to in a stored procedure are created outside the procedure, that will cause recompilation. Recompilation occurs when DECLARE CURSOR statements whose SELECT statements reference a temporary table, or DROP TABLE statements comes before other statements referencing a temporary table. In each of these cases, changing to a table variable rather than a temporary table will avoid the repeated recompilation.
Temp Tables supports non-clustered indexes and creates statistics on the query executed. This helps some query which needs stats and indexes to run faster. Temp Tables supports input or output parameters. Also they can be copied to another temp tables. Temp tables supports SELECT INTO or INSERT EXEC but Temp variables does not. Temp Table definition can be changed after it is created. Temp table can be explicitly dropped as well.
My Recommendation: Which one to use?
Simple : Choose a TempVariables as Default. If any of the following circumstances arises use TempTables.
1) Transactions needs to rollback
2) If query optimizer needs stats to run complex query
3) If result sets of one tables needed for another stored procedure like SELECT INTO or INSERT EXEC
4) Complex logic of dynamic SQL, which are not supported by TableVariables like creating Index or Constraints
5) Results set is very large (greater than 100K rows)
The bottom line is that both temporary tables are table variables are invaluable tools in your SQL Server toolbox, and you really should become familiar with both.
Reference : Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
I found the link.
You have stated that both temp table and table variable instantiated in tempdb. But I remember reading that table variables are stored in memory. Could you please confirm.
Very good site listing
I agree with you on all but one comment about using temp tables.
Use temp tables if:
Results set is very large (greater than 100K rows)
I think it is efficient to use staging physical tables if result set is very large. because of sp recompilation, locks on tempdb and better use of parallelism
Hi,
I have a table with columns
distributor_id ,
brand_id,
year,
month,
total_sales
distributor_id ,
brand_id,
year,
month are my primary keys.
I have records for last 3 years od data.
I have distinct records till last year,
Some are till last month (Current month – 1)
some are till feb 2007…….
some are till jan 2007..
Now i want to insert records to all distinct
distributor_id ,
brand_id,
year,
month
Zero for sales
where i have to take max month + 1 and hardcode as zero till current month + 10 months.
i appreciate if you get me a insert script without cursors.
Dave,
We have a procedure that is ran evry 2 minutes in a windows service. The procedure uses a table variable. It appears tha after a couple hours of execution the server that this service is running on starts to take a performance hit. We have also seen a tremendous increase in timeouts since the implementation of this stored procedure. Is ti possible that the table variable is not being released from memeory?
Hi Readers,
Please consider the following situation ,
Here part (1) will render the result but not part (2) and (4)
Can any one suggest an answer, why the temp table and table variable canot be used in string level execution.
In part (2) it will execute, but while fetching the value (select * from #tmp_date) it will generate error,
but part(3) will render the result(select * from ##tmp_date).
(1) Table
select convert(varchar,getdate(),101) as ToDay into dat_date
select * from dat_date
drop table dat_date
(2)Temporary table
declare @var varchar(1000)
set @var=’select convert(varchar,getdate(),101) as ToDay into #tmp_date’
execute (@var)
select * from #tmp_date
drop table #tmp_date
(3)Temporary table
declare @var varchar(1000)
set @var=’select convert(varchar,getdate(),101) as ToDay into ##tmp_date’
execute (@var)
select * from ##tmp_date
drop table ##tmp_date
(4)Table variable
declare @var_ varchar(1000)
declare @var_table table(To_day datetime)
set @var_=’insert into @var_table select convert(varchar,getdate(),101) as ToDay ‘
execute (@var_)
select * from @var_table
drop table @var_table
Any guesses?? please post…!
And the following will be working………..!!
declare @var_ varchar(1000)
declare @var_table table(To_day datetime)
set @var_=’select convert(varchar,getdate(),101) as ToDay ‘
insert into @var_table
execute (@var_)
select * from @var_table
Hi Pinal Dave,
I’m ur big fan. Thanks for all your articles.
1.Please tell me what are the issues in using temp tables.
2.For handling huge records which one will be better. Temp tables or physical tables with unique id to identify the session. If physical tables please justify?
Thanks,
Dineshbabu
Hi Dave,
Could u plz explain or give me the link where I can find the answer of the below question:
” why Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used?”