Thursday, May 8, 2008

table vartable Vs temp table

  • If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
  • If you need to create indexes on it then you must use a temporary table.
  • When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.