Does my temp table necessarily write out to tempdb on disk?
Say I create a small temp table (say 10,000 rows and 5 int columns for example): SELECT [blah] INTO #temp FROM [blah] WHERE [blah] Say I also have plenty of free memory. Is it possible that the table could spend it's life in memory and never need to be written out to disk? If there are some circumstances where temp tables don't need to be written to disk, and some circumstances where they do, how can I predict with useful accuracy, whether it (the writing to disk) is going to happen?
I think that there is no simple way to predict whether the data in a temporary table will ever actually be written to disk. If there is enough memory available, it will probably not ever get written to disk. As memory pressure grows, it is more likely that the temp table will end up on disk. SQL Server does try to avoid disk activity where it can since this can be relatively expensive. Yes most data does normally end up on disk if it is important enough but temporary tables contain temporary data - it may well be wasted effort writing it to disk.