Maximum Number of Records a table variable can have in SQL Server

Question

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?

Answer 1

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:

• Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
• When should I use a table variable vs temporary table in sql server?
• TempDB:: Table variable vs local temporary table

Answer 2

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 :-)