SQL Server Performance

Table Variable Limitation

Discussion in 'SQL Server 2008 General Developer Questions' started by Jayakumar.s, Feb 18, 2010.

  1. Jayakumar.s New Member

    Hi
    I am planning to use the table variable in my transactional database. Upto what extent i can use the table variables? Any storage limitation is there for table variable?
    Thanks
    Jayakumar
  2. Adriaan New Member

    You don't need to worry about amounts of data. However, at some point SQL will make a guess as to the amount of data that will go into the table variable, and there is a threshold at which it will decide to implement the table variable as a temp table.
    Another restriction is that the only type of index you can have with a table variable is a primary key. If you need indexes on non-key columns, you'll need to create a temp table.
  3. Jayakumar.s New Member

    As I don't have the permission to create #table , i am going for table variables.If i know the limitations it would be better to consider this option.
  4. FrankKalis Moderator

    Hold on! Why do you think you have no permission to create a temp table? This permission is granted to virtually anyone and does not require any special permissions.
    Also, for a good comparison between temp tables & table variables see these:
    http://support.microsoft.com/kb/305977
    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
  5. Subhash_Chandra New Member

    Hello Jaykumar,
    In documents there is no size or record count limit for table variable but in practice table variable are good only if record count is not more than few hundrads.
    Regards,
    Subhash
    Founder: http://SQLReality.com/blog/
  6. satya Moderator

    I would like to refer http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx in this case, also it is better to avoid using the same method continuosly.If your application frequently creates temporary tables, consider using the table variable or a permanent table. You can use the table data type to store a row set in memory. Many requests to create temporary tables may cause contention in both the tempdb database and in the system tables. Very large temporary tables are also problematic. If you find that you are creating many large temporary tables, you may want to consider a permanent table that can be truncated between uses.
    Table variables use the tempdb database in a manner that is similar to how table variables use temporary tables, so avoid large table variables. Also, table variables are not considered by the optimizer when the optimizer generates execution plans and parallel queries. Therefore, table variables may cause decreased performance. Finally, table variables cannot be indexed as flexibly as temporary tables.
    You have to test temporary table and table variable usage for performance. Test with many users for scalability to determine the approach that is best for each situation. Also, be aware that there may be concurrency issues when there are many temporary tables and variables that are requesting resources in the tempdb database.

Share This Page