SQL Server Performance Forum – Threads Archive
table variables vs temp tables
I am using a stored procedure which retuns a resultset. Currently I am using a table variable and then running some query and populating this variable. Towards the end I just return a ‘select * from my_table_variable’. Any ideas if a temp table will be better or shall I try to eliminate the table variable/temp table altogether? Thanxtemporary table takes advantage over table variable
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=9392
Madhivanan Failing to plan is Planning to fail
table variables were designed specifically to guard against stored procedure recompiles DURING execution. So if you have a temporary tables and insert/update/delete a fair amount of data in it, then you may benefit from having a table variable. While you can’t create indexes on a table variable, you can create unique constraints and a primary key, which will in effect index the table. BUT a table variable is assumed to be of a certain size when it comes to execution plans (I think it was something like 10 pages) which means that the optimiser could get the plans wrong if the data is wildly more than that Cheers
Twan
If the result set is not long enough then I would stick to table variable, otherwise temp table has its advantages. 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.
In dynamic SQL queries you cant use table variable but can temp. table.
In situation where you had to fire select into goes only with temp tables and not table variable. Prefer table variable and avoid temp tables as it locks the tempdb too and costs query recompilations.
So it highly depends on your situation.
When the data in table variable grows above 3MB, its moved to tempdb. So, if you are not sure about the size, go for table variable. Happy Querying!
]]>