1

In one SP1 I used 7 @Table variables and in another SP2 I used 7 #Temp tables. Both the SPs Using same tables. But SP1 Is Taking more time to execute as compare to SP2. Can any one will give clear explanation.

Jalandhar
  • 23
  • 5
  • Table variable is not suggested when dealing with large number of rows. SQL Server always expect that table variable has only 1 row, which when the table has a large number of rows, will mess up the generated execution plan. [**This**](http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server) might help. – Felix Pamittan May 25 '16 at 07:05

1 Answers1

0

Table variable is designed to hold only a small amount of data (Statistics are not applied on them)

Temp table can hold much bigger and faster (But not suitable for very very large dataset, as it uses IO operations on disk)

if you are using these for paging, use fetch next rows method or group by (generate row number) then filtering the row number

Arun Prasad E S
  • 9,489
  • 8
  • 74
  • 87