SQL Server Performance Forum – Threads Archive

Temp tables vs Variable tables

Hi All, I am wondering what the difference is in using temp tables vs variable tables in code? The only thing i can see is that temp tables require the use of the tempdb while the variable tables are created in the actual db the code is executing in. Anything else and what performance considerations are involved? thanks in advance

As a general rule of thumb, you should use table variables any time that your temporary data is of reasonable size and is only used a few times. As your temporary data grows in size, complexity, and reuse, a temporary table will be more appropriate. Of course, you don’t have to make this decision blindly. With execution plans, SQL Profiler, and performance counters available to monitor what’s going on inside of your stored procedures, you can code the alternatives and test them when there’s any doubt. Table variables are often faster than temp tables, but test with a realistic amount of data.

Thanks for the info. I also read that when using temp tables inside stored procs that causes them to be recompiled each time. But with table variables the SP doesnt get recompiled each time.

Refer this also
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=7929
Madhivanan Failing to plan is Planning to fail

Table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used. Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources. One advantage of using variables of type table is that you no longer drop the table, like you do with temporary tables. Also, while performance should be better with variables of type table, if there is the potential for a lot of data a temporary table should probably be used. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I tried to batch convert create table # to declare @tblname Table, and SQL Server would complain about syntax error or @tblname must be declared. select @tblname.fieldname1
from @tblname
where @tblname.fieldname1 = 2 must be converted to: select t.fieldname1
from @tblname t
where t.fieldname1 = 2 anyway around this? there would be a lot of changes..

I realize that this request is quite old now, and I’ve no idea if you are even still out there but I wanted to share under this post what I had discovered in using both. The biggest advantage I’ve found is that the population of each seems to take identical time when measuring the query plan and in monitoring the resulting duration in Profiler. However, there is a huge performance gain when it comes to rejoining the data from the table variable to another table instead of joining the temp table. When I join the temp table the query plan shows me that it does a table scan of the "real" table and then does a merge of the two. However, when I use the table variable it does an index seek for just the rows that match (join fields) just as it would if I had written the query and said where id = value.

I haven’t found a way around the DECLARE option if I need to reuse the table variable multiple times. However, if I only needed the table variable say 1 time then I was able to simply do something like the following: select table1.field(s), variable_table
from table1, (select stuff from another table) as variable_table
where table1 conditions
and table1.id_field = variable_table.id_field Where the (select stuff from another table) represents whatever select clause I used. In maintaining a database from a third party vendor, I’ve found that many times they did create temp tables that were in fact only used 1 time such as above, and was able to do what I showed above. In other cases I did need to do a declare ahead of time so that I could reuse the table many times, based on my previous response the more times I end up joining to the table the better the performance gains I saw: declare @Variable_table table (
[id] [uniqueidentifier] NOT NULL ,
[account_id] varchar(12) not null etc ) insert into @Variable_table
select enc_id, account_id
from patient_encounter e Hope this helps someone in the future who searches for this topic,
Dalton

]]>