Gus, RE the isolation level, it's the default read committed, with snapshot off.
L' Eomot Inversé (1/2/2013)
I think that the source of confusion here is teh difference between allocating space for some data on the disc and actually writing the data to the disc.Every page of table data, whether permanent table, temp table, or table variable, has to have space allocated for it on disc; however, it's possible that the data is never written to the space allocated for it. Most of the time, data is never written to the space allocated for table variables or for temp tables - that's what Gus is telling you; but all the time, space is allocated on disc so that the data could be written to disc if that became neccessary - that's what some of the references are telling you, but apparently it is so badly phrased as to appear to contradict what Gus told you.
The consensus by everyone so far in this thread definitely seems to be that table variables and temp tables work in RAM when available, and only spill to disk when RAM is not available. But just to recap for anyone who doesn't want to read this whole thread: I still haven't seen any recent articles or examples of this happening. Here are two examples that show the contrary, and claim that RAM-only table variables/temp tables are a myth:
The first link shows disk space being allocated to tempdb when both temp tables and table variables are used:
One posted said that although that shows space being allocated, data is never written to that space until it becomes necessary. Yet Gus's article that he linked to shows that that isn't necessarily true either - this article steps through in detail and shows the data being written to the disk (not just space being allocated) when a table variable is used and there is more than enough RAM.
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
I'll admit, I'm the rookie who's not yet believing what several experts tell me - based on the responses I've received so far, if this is a myth, it's a very persistent one! Maybe it takes a rookie to question what's always been told? The only examples I've seen show that table variables and temp tables are written (not just allocated space) to disk - even when RAM is available. That doesn't mean that they aren't also cached in memory to be read quickly, so far I believe that both are true. I'm really only curious now about writing data to temp tables/variables, and whether tempdb storage would be the bottleneck concern for writing to temp tables even when the system has an abundance of RAM.
Can anyone explain Gail's article that shows table variables being written to disk even when RAM is available (other than just linking to another article that simply states otherwise, without any proof or evidence)? And that's not meant to be confrontational, I'm really confused and looking for this answer because everyone's opinion seems to be contrary to the only actual examples I can find.
One of the previous comments suggested that Gail's example shows one table variable writing to disk, but doesn't mean that all table variables write to disk. Logically that's true, but impossible to test against all possible cases. So I would question if a 20MB table is immediately persisted to disk when there's 2GB of free RAM (as her example shows), then it sounds like writing temp data to disk may at least be so common that you can build a system to assume it will be the norm?
Sorry for the ramble, hard to respond to several posts in one!