Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
Nice try on the article but you really need to do more research before you write about something like this... the statement above is dead wrong. Here's the URL to prove it... pay particular attention to Question and Answer Q4/A4... http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
Just in case someone doesn't actually want to make the trip, here's a copy of Q4/A4 from the URL above...
Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Also, I can't put my hands on the reference right now, but all this locking business about Temp tables is mostly a left over myth. In version 6.5, creating and using a temp table would cause all sorts of locking problems... they fixed all
that in version 7 and it hasn't been a problem for about 12 years.
Yes, mixing DDL and DML will still cause recompiles but most of the blocking done by temp tables is no longer true. Only time it's still true is when using SELECT/INTO and that's so short it just doesn't matter most of the time.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs