When should I use a table variable vs temporary table in sql server

Question

I m learning more details in table variable  It says that temp tables are always on disk  and table variables are in memory  that is to say  the performance of table variable is better than temp table because table variable uses less IO operations than temp table   But sometimes  if there are too many records in a table variable that can not be contained in memory  the table variable will be put on disk like the temp table   But I don t know what the  too many records  is  100 000 records  or 1000 000 records  How can I know if a table variable I m using is in memory or is on disk  Is there any function or tool in SQL Server 2005 to measure the scale of the table variable or letting me know when the table variable is put on disk from memory

User · Answer

Use a table variable if for a very small quantity of data  thousands of bytes   Use a temporary table for a lot of data  Another way to think about it  if you think you might benefit from an index  automated statistics  or any SQL optimizer goodness  then your data set is probably too large for a table variable   In my example  I just wanted to put about 20 rows into a format and modify them as a group  before using them to UPDATE   INSERT a permanent table  So a table variable is perfect   But I am also running SQL to back-fill thousands of rows at a time  and I can definitely say that the temporary tables perform much better than table variables   This is not unlike how CTE s are a concern for a similar size reason - if the data in the CTE is very small  I find a CTE performs as good as or better than what the optimizer comes up with  but if it is quite large then it hurts you bad   My understanding is mostly based on http   www developerfusion com article 84397 table-variables-v-temporary-tables-in-sql-server   which has a lot more detail

User · Answer

Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables   I have written quite an extensive answer on the DBA site looking at the differences between the two object types  This also addresses your question about disk vs memory  I didn t see any significant difference in behaviour between the two    Regarding the question in the title though as to when to use a table variable vs a local temporary table you don t always have a choice  In functions  for example  it is only possible to use a table variable and if you need to write to the table in a child scope then only a  temp table will do   table-valued parameters allow readonly access    Where you do have a choice some suggestions are below  though the most reliable method is to simply test both with your specific workload     If you need an index that cannot be created on a table variable then you will of course need a  temporary table  The details of this are version dependant however  For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a UNIQUE or PRIMARY KEY constraint  SQL Server 2014 introduced inline index syntax for a subset of the options available in CREATE INDEX  This has been extended since to allow filtered index conditions  Indexes with INCLUDE-d columns or columnstore indexes are still not possible to create on table variables however  If you will be repeatedly adding and deleting large numbers of rows from the table then use a  temporary table  That supports TRUNCATE  which is more efficient than DELETE for large tables  and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here  If you will be deleting or updating a large number of rows then the temp table may well perform much better than a table variable - if it is able to use rowset sharing  see  Effects of rowset sharing  below for an example   If the optimal plan using the table will vary dependent on data then use a  temporary table  That supports creation of statistics which allows the plan to be dynamically recompiled according to the data  though for cached temporary tables in stored procedures the recompilation behaviour needs to be understood separately   If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles  would possibly require hints to fix the plan you want   If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan  If you need the data in the table to survive a rollback of an outer user transaction then use a table variable  A possible use case for this might be logging the progress of different steps in a long SQL batch  When using a  temp table within a user transaction locks can be held longer than for table variables  potentially until the end of transaction vs end of statement dependent on the type of lock and isolation level  and also it can prevent truncation of the tempdb transaction log until the user transaction ends  So this might favour the use of table variables  Within stored routines  both table variables and temporary tables can be cached  The metadata maintenance for cached table variables is less than that for  temporary tables  Bob Ward points out in his tempdb presentation that this can cause additional contention on system tables under conditions of high concurrency  Additionally  when dealing with small quantities of data this can make a measurable difference to performance    Effects of rowset sharing  DECLARE  T TABLE id INT PRIMARY KEY  Flag BIT    CREATE TABLE  T  id INT PRIMARY KEY  Flag BIT    INSERT INTO  T  output inserted   into  T SELECT TOP 1000000 ROW NUMBER   OVER  ORDER BY   SPID   0 FROM master  spt values v1  master  spt values v2  SET STATISTICS TIME ON    CPU time   7016 ms   elapsed time   7860 ms    UPDATE  T SET Flag 1     CPU time   6234 ms   elapsed time   7236 ms    DELETE FROM  T     CPU time   828 ms   elapsed time   1120 ms    UPDATE  T SET Flag 1     CPU time   672 ms   elapsed time   980 ms    DELETE FROM  T  DROP TABLE  T

User · Answer

writing data in tables declared declare  tb and after joining with other tables  I realized that the response time compared to temporary tables tempdb      tb is much higher   When I join them with  tb the time is much longer to return the result  unlike  tm  the return is almost instantaneous   I did tests with a 10 000 rows join and join with 5 other tables

User · Answer

Microsoft says here      Table variables does not have distribution statistics  they will not trigger recompiles  Therefore  in many cases  the optimizer will build a query plan on the assumption that the table variable has no rows  For this reason  you should be cautious about using a table variable if you expect a larger number of rows  greater than 100   Temp tables may be a better solution in this case

User · Answer

Variable table is available only to the current session  for example  if you need to EXEC another stored procedure within the current one you will have to pass the table as Table Valued Parameter and of course this will affect the performance  with temporary tables you can do this with only passing the temporary table name   To test a Temporary table     Open management studio query editor  Create a temporary table  Open another query editor window  Select from this table  Available     To test a Variable table     Open management studio query editor  Create a Variable table  Open another query editor window  Select from this table  Not Available     something else I have experienced is  If your schema doesn t have GRANT privilege to create tables then use variable tables

User · Answer

I totally agree with Abacus  sorry - don t have enough points to comment    Also  keep in mind it doesn t necessarily come down to how many records you have  but the size of your records   For instance  have you considered the performance difference between 1 000 records with 50 columns each vs 100 000 records with only 5 columns each   Lastly  maybe you re querying storing more data than you need  Here s a good read on SQL optimization strategies  Limit the amount of data you re pulling  especially if you re not using it all  some SQL programmers do get lazy and just select everything even though they only use a tiny subset   Don t forget the SQL query analyzer may also become your best friend

[sql-server] When should I use a table variable vs temporary table in sql server?

Examples related to sql-server

Examples related to temp-tables

Examples related to table-variable