I was having a good discussion with a fellow developer around SQL Server Temporary Tables and Table Variables. Here are some points from the discussion:
Temporary tables are like regular tables but stored in the tempdb database. These tables get dropped after they have been used. Temporary tables are of two types,
Local Temporary table - defined using a pound sign (#) and accessible only within the session that created it
Global Temporary table - defined using double pound signs (##) and visible to all users.
Table Variables is a data type that looks similar to a table but has a tighter lifetime scope (i.e within a function, stored procedure or batch that it is declared in) and should be used with small datasets.
Here are some differences between Temporary Table and Table Variables
|Created and stored in tempdb database||Created in Memory (although it can write to tempdb)|
|Transaction logs are recorded for temporary tables||Transaction logs are not recorded for the table variables|
|They are bound to transactions.||They are not bound to any transactions. So no effect of transaction rollbacks|
|Can participate in parallel operations||Cannot participate in parallel operations|
|The log activity remains till it is manually cleared or the server restarts||Table variable log activity is truncated immediately|
|SQL Server creates statistics for temporary tables||SQL Server does not create statistics for table variables|
|Stored procedure containing temporary tables cannot be pre-compiled||Stored procedures containing table variables can be pre-compiled|
|You can drop a Temporary Table||You cannot manually drop a table variable|
|You can create indexes on them||You cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)|
For further reading, http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html and Table Variables