Seeder question: I've been told that table variables are stored in memory, but temporary tables are written out disk in the tempdb. I've also been told that I should always use table variables because they'll be faster because they're only in memory. Are these statements true and if not, why not?
The above article is a good reference, but I thought it would be worth mentioning a couple of key points here:
1) Table variables lack statistics. This means that query plans often treat the table variable as if it had 1 row. This can cause some severely sub-optimal plans when getting to larger numbers of rows.
2) Indexing on table variables is limited. For example, if you want to create a non-unique clustered index on a table variable, you are out of luck.
And, just for completeness, I'll mention here that both table variables and temporary tables (and, for that matter, data passed via a table type) can exist either in memory, or on disk. The query engine will make the appropriate choices as to where and how to store the objects.
Here is another blog post from the SQL Server Storage Engine Team http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
Check this link below out...
There is a "Myth" section
http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
As pointed out by sp_lock, the article at http://www.sqlservercentral.com/articles/Temporary+Tables/66720/ by Wayne Sheffield does a good job of debunking this and expanding on it greatly.
I find that code written with table variables tends to be easier to read and has less worries about the entity remaining when the script is concluded. Thus, I tend to use table variables unless there is a compelling reason to use temp tables.
If you create a table variable and then query tempdb.sys.objects, you will see an entry there. This definitely suggests that a table variable is a proper object, which is not stored in memory.
But in addition to this, consider the scenario where you put a large amount of data into a table variable. You see the transaction log of tempdb growing as well.
Table variables are definitely stored on disk, although being in tempdb, I would hardly suggest that they 'persist'... they are only scoped to the batch.
