Post reply

Maximum size of temporary tablke variable

  • November 18, 2008 at 2:48 am

    #383412

    Hi,

    Can anybody tell me what is the maximum size of a temporary table variable?

    I have read that using temporary table variable instead of temporary table is better for a small amount of result set. But what is the size threshhold of table variable?

    Where can I replace a temporary table with a table variable? Means in which situation?

    Because in OLTP system, everyday data size is changing, rather increasing.

    Thanks in advance.

  • November 18, 2008 at 3:18 am

    #899746

    Hello,

    May be take a look at these articles:-

    http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • November 18, 2008 at 5:14 am

    #899805

    Hi John,

    Thanks 4ur reply. I have gone through it. But my question is, there is a limitation of table variable reg. size. I want to know that threshhold size.

    thanks again.

  • November 18, 2008 at 5:31 am

    #899820

    arup_kc (11/18/2008)

    Hi John,

    Thanks 4ur reply. I have gone through it. But my question is, there is a limitation of table variable reg. size. I want to know that threshhold size.

    thanks again.

    Table Variables are out of scope of the transaction mechanism.

    If you make changes to a Temp Table inside a Transaction and

    Rollback the Transaction those changes will be lost.

    Changes made to Table Variable inside a Transaction will

    remain even if you Rollback the Transaction

    Any procedure with a temporary table cannot be pre-compiled

    An execution plan of procedures with table variables can be

    statically compiled in advance

    Table Variables exist only in the same scope as variables.

    They are not visible in inner stored procedures and in

    exec(string) statements

    Table variables are in-memory structures that may work from

    2-100 times faster than temp tables.

    Access to table variables gets slower as the volume of data

    they contain grows.

    At some point, table variables will overflow the available

    memory and that kills the performance.

    Use table variables only when their data content is

    guaranteed not to grow unpredictably; the breaking size is

    around several thousand records.

    For larger data volumes, use temp tables with clustered

    indexes.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • November 18, 2008 at 6:12 am

    #899857

    Hello again,

    As far as I am aware, the size of a Table Variables is only restricted by the amount of available storage, but you would never want to create one that large.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • November 18, 2008 at 6:20 am

    #899861

    Hi kshitij and John,

    I have heard that 2GB is the size limitation of table variable, later it crushes.

    Is it true?

    Thanks in advance.

  • November 18, 2008 at 6:50 am

    #899873

    arup_kc (11/18/2008)

    Hi kshitij and John,

    I have heard that 2GB is the size limitation of table variable, later it crushes.

    Is it true?

    Thanks in advance.

    AFAIK there is no such limit to a table variable. I know that there is a myth that table variable is stored in the memory, but in reality if there is not enough memory, it will be stored on the disk (in tempdb). The limitation of the table variable’s size is the limitation that you have on the tempdb size and the amount of free space that you have for tempDB. If your tempDB has more then 2GB free space, then your table variable can store more then 2GB of data. The question is why would you want to work with table variable that holds so much data? As mentioned before, table variable should be used with small amount of data.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 19, 2008 at 2:05 am

    #900379

    Hi Adi,

    Many thanks for ur reply. Actually its my mistake...instead of writing 2 MB , I have written 2 GB.

    Really 2 GB is a big data size. We should not put 2 GB data in table variable. But actually I want to know the max amount of data taht we can hold in table variable. I want to know the actual size.

    I m taking the imaginary situation that table variable is in memory only.

    Thanks in advance.

  • November 19, 2008 at 11:34 am

    #900657

    1) It is a common MISconception that table variables are memory-resident only. This is false. They will get written to disk in tempdb just like regular temp tables if the engine determines they are taking up too much RAM.

    2) There is not to my knowledge a limit on table variables other than tempdb storage.

    3) There are several reasons to not use table variables in many situations primarily related to the fact that there are no statistics on them and this causes problems with query optimization.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • November 19, 2008 at 11:48 pm

    #900896

    Thanks Guru...I got my answer

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply