SQL Server Performance

table variables vs temp tables

Discussion in 'T-SQL Performance Tuning for Developers' started by nattynatty, Jul 28, 2005.

  1. nattynatty New Member

    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?

    Thanx
  2. Madhivanan Moderator

  3. Twan New Member

    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
  4. satya Moderator

    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.
  5. ranjitjain New Member

    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.
  6. QueryAnalyzer New Member

    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!

Share This Page