Temporary Tables VS Table Variables

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

Temporary Tables

Table Variables

Created and stored in tempdb databaseCreated in Memory (although it can write to tempdb)
Transaction logs are recorded for temporary tablesTransaction 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 operationsCannot participate in parallel operations
The log activity remains till it is manually cleared or the server restartsTable variable log activity is truncated immediately
SQL Server creates statistics for temporary tablesSQL Server does not create statistics for table variables
Stored procedure containing temporary tables cannot be pre-compiledStored procedures containing table variables can be pre-compiled
You can drop a Temporary TableYou cannot manually drop a table variable
You can create indexes on themYou 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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook


Anonymous said...

Very Precise
Good Job

Tom said...

There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment- It may turn out that you may have to abandon the use of both of them in favour of a defined table used for intermediate data.