I’ve heard many times that table variables are in memory on the SQL Server and thus very fast. However, I’ve also read on a few blogs (one of which I read frequently and has a lot of good content: http://blog.sqlauthority.com/) that both of these options are stored in the tempdb. I finally found some documentation on Microsoft’s site in a support article that indicates that the tempdb will be used if there is too much data to store in memory, but if memory suffices it will stay there. Specifically, here is what was written (it’s for SQL Server 2000 but I assume not much has changed in how these operate):
Question: 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?
Answer: 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).
The key phrase here is “If memory is available, both table variables and temporary tables are created and processed while in memory (data cahce)”
The article lists advantages / disadvantages to each method because there are a few more. In general, table variables are what I use in most cases but the article lists where each is advantageous: http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k