I've been reading Part 2 of the Ben-Gan SQL 2005 books

http://www.amazon.co.uk/Inside-SQL-Server-2005-Programming/dp/0735621977/sr=8-2/qid=1160398841/ref=sr_1_2/026-8787779-9446068?ie=UTF8&s=books

and in one of the chapters he discussed temporary tables

vs. table variables

http://support.microsoft.com/kb/305977

Ever since table variables were introduced in SQL 2000 I have used them by default rather than temporary tables. This decision was made after team members at the company I was working with at the time had experienced blocking when using temporary tables within transactions in SQL 7.0. Their solution was to use "permanent"  temp tables with the connections SPID as part of the primary key. When they migrated to SQL 2000, they couldn't replace permanent temp tables with table variables as the data was required by more than one procedure in the same transaction (the stored procedures were very granular to avoid using syntax such as "WHERE @ip_Paramter1 IS NULL OR Field1 = @ip_Parameter1). However, for vast majority of the work I have done subsequently I have used table variables.

In his book, Ben-Gan made the interesting point that statistics are not kept for table variables so the execution plan may be less than optimal. I thought I would test this for the longest running stored procedure in the application I am currently working on.

If I had been using SQL 2000 client tools, it would've been more difficult to compare result, however, SQL 2005 makes it very easy -

Notes:

• Inserts, deletes and updates are shown as zero as NOCOUNT was on
• The temporary table holds around 500 rows and is updated 5 times
• I am running against a SQL 2000 server
• The first four tests are for the table variable and the last four for temporary tables

So, for my test, table variables are faster than temporary tables, but the book taught me that you must constantly challenge your preconceptions with technology advancing as quickly as it does.