Temporary Tables (# tables) , table variables and CTEs are commonly used for storing data temporary.
Many people believe that Table Variable will be in the memory and Temp tables will be stored in the Tempdb database.
Let us see this. Following script will declare table variable and query the sys.objects in tempdb database.
So, declaring table variable it self will create a # table in tempdb throwing away the above myth.
Another myth is, table variable will be moved once the data load is high on the table variable. However, Following is a disk usage report taken for temp table and table variable.
Above report shows, that even for 5 rows, temp tables and table variable both have same in sizes which breaks the myth.
I did another test with #tables , table variables and CTE. Bellow graph is write transactions/sec for each scenario. So CTE does not use tempdb at all.
Following is from a KB article (http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k) but this applies to SQL server 2000. Couldn’t find any thing applies to 2008 or 2005.
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).