Temporary tables vs table variables

Query performance can be affected hugely by choice of temporary tables or table variables. Both have upsides and downsides. Faced with either type of table SQL Server will try to keep operations in memory, and both use the tempdb database.
The main differences are: temporary tables are written to the transaction log (so operations can be rolled back), you can create indexes on temporary tables and as such they often perform better when there are many records in the temp table and the query involves joins. Temporary tables however need more careful cleanup – the scope isn’t restricted to the procedure as with table variables. Finally, procedures with temporary tables may need to be recompiled frequently whereas procs with table variables can be pre-compiled.

If the recordsets for the tables are small, and the proc is executed very frequently then table variables might be the way to go. Try both flavours – certainly don’t assume that one is better than the other.

Advertisements

One Response to Temporary tables vs table variables

  1. scott says:

    Thanks for the good info.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: