none
Maximum Capicity of Table Variable RRS feed

  • Question

  • Hi,

    I have created one SP in which i am storing data in table varible. Right now, records are 100 . Each records size is 1000 varchar. Now, what is the maximum capacity of table variable to contail the records of this size ?

     

    Thanks,

    Thursday, May 6, 2010 5:33 AM

Answers

  • Hallo Anand,

    Like for every "normal" table it's limited by database size (Express Edition) and the freespace on you storage.

    A table variable is like a temporary #table stored in the TempDB and handled like a common table, so take care about you tempdb in size and performance (good storage system). And don't forget, you can't index a table variable, so with large data you may get poor performance.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, May 6, 2010 6:31 AM
    Moderator

All replies

  • Hallo Anand,

    Like for every "normal" table it's limited by database size (Express Edition) and the freespace on you storage.

    A table variable is like a temporary #table stored in the TempDB and handled like a common table, so take care about you tempdb in size and performance (good storage system). And don't forget, you can't index a table variable, so with large data you may get poor performance.


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, May 6, 2010 6:31 AM
    Moderator
  • Thank You Very Much Olaf. This is really helpful to me.
    Thursday, May 6, 2010 7:37 AM
  • Yes, you can index a table variable...

    If you create it as 'Type'

    And Type has an index... its an indexed table variable...

    • Proposed as answer by DhruvSingh Thursday, October 27, 2016 6:44 AM
    Friday, February 5, 2016 4:09 PM