SQL Server: Differences between Temp Tables and Table Variables

by

I had been thinking of collating the differences between temp tables and table variables and posting it, but Martin Smith has already written a great summary over at DBA StackExchange:

What’s the difference between a temp table and table variable in SQL Server?

It’s broken up into the following categories:

  • Storage Location
  • Logical Location
  • Visibility to different scopes
  • Lifetime
  • Transactions
  • Logging
  • Object Metadata
  • Cardinality
  • Column statistics
  • Recompiles
  • Locking
  • Indexes
  • Parallelism
  • Other Functional Differences
  • Memory Only?