7

Is there any constraints to limit the number of records we can have in a table variable? If yes, what will be the maximum number records a table variable can hold? I have to write a stored procedure to process around 1000 records. Do I need to go with table variable or temporary table?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bmsqldev
  • 2,627
  • 10
  • 31
  • 65
  • Possible duplicate of [Table variable row limitation?](http://stackoverflow.com/questions/6945749/table-variable-row-limitation) – MusicLovingIndianGirl Nov 06 '15 at 05:01
  • 3
    **Be aware** : no matter how many rows you have in a table variable, the query optimizer (for lack of better information) will always assume there's only **1 single row** in your table variable, which can lead to horribly bad execution plans if you have lots of rows in your table variable. Use it with caution - if you intend to have thousands of rows, I'd much rather use a temporary table – marc_s Nov 06 '15 at 06:03
  • but, usage of temp tables leads to add load in tempdb. I cant try cte too, since it need to be referred multiple places in the code – bmsqldev Nov 06 '15 at 11:56

2 Answers2

11

As such the official MSDN site where the Maximum Capacity Specifications for SQL Server there is no such upper limit defined for table variables because it depends on the database size and the free memory available for the storage. You can also refer the MSDN forum discussing the same; Maximum Capicity of Table Variable

Do I need to go with Table Variable or Temporary Table?

You can use any of them as there is no such golden rule as to when you should use Table variable and when to use Temporary variables. There are some references which can be helpful to understand it more:

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
1

Do you mean a table row? Or do you want a variable in some T-SQL that is a table? I am guessing you must mean a variable of type table and then the answer is 'no'. The limit of what a table can hold should depend only on the size of your disk. If you only want to put a certain number of rows in to the table then perhaps use the TOP key word on the query that populates the table? If you provide a bit more detail in the question you will get a better answer :-)

Paul Coldrey
  • 1,389
  • 11
  • 20
  • I normally query data from multiple tables and put it into table variable or temp table depending on the size of the data after query filtering. Sometimes, I confuse to choose between table variable and temporary table. – bmsqldev Nov 06 '15 at 05:11