#temp tables and @table variables are NOT in-memory objects
#temp tables and @table variables are often used to optimise stored procedure performance by breaking down large queries (many joins) into smaller units of work or materialising resultsets to perform additional processing logic in isolation from other concurrent processes running in the database. These are effective & commonly used TSQL coding techniques but there's a dark side to over-using temp objects.
Developers have a tendency to think of temp objects, especially @table variables, as in-memory objects. This is a reasonable assumption given that most modern languages implement variables as in memory objects. However, TSQL is not one of those modern languages and the creation of / population of temp objects are operations which are always written to disk. Twice.
SQL Server uses similar mechanics to manage temp objects that it does for permanent objects. Any write operations against temp objects (inserts, updates & deletes) are first written to the tempdb's transaction log (the first disk hit) & then on a lazy basis to the tempdb's data volume (the second disk hit). SQL Server implements temp objects this way to guarantee that rowsets are fully written to temp objects or not at all in an environment where limited memory is available. This is natural of a RDBMS & shouldn't be that much of a surprise for DBAs who spend most of their time watching the inner RDBMS cogs ticking. Devs on the other hand often don't get down to the nitty-gritties of RDBMS internals and tend to miss this point, sometimes over-using temp objects as result of thinking they operate in-memory (like arrays, right?).
And who can blame devs for thinking this when Microsoft publish confusing articles such as KB305977 which states (mid way through):
Q4: 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).
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977
This article has been incrementally updated over the past few years & at least the current version indicates that temp objects aren't in-memory objects, but it's still a bit mis-leading in that it doesn't even mention hits against the TLOG. It also suggests at the end of the answer that temp tables are processed in memory where enough memory exists. This is quite mis-leading as all *updates* are actually processed on disk in the TLOG (you can check this yourself using a log reader or fn_dblog()). When it says that temporary tables are "created" in memory, it's just plain wrong. What *might* happen in memory is cache re-use if you're performing multiple reads against a single temp object & there's not much memory pressure on the tempdb.
I'm not against the use of #temp tables or @table variables - they're useful objects & I use them regularly. There are some great advantages - with @table variables, stored procs are recompiled less regularly, tempdb tlog records auto truncated & you can perform non-atomic operations when used within a user transaction. They have their place & are very much a useful tool for developing stored procs.
Be aware that temp objects are not in-memory objects & they can hit the tempdb's TLOG very hard if you use them a lot & your database is subject to high levels of concurrent usage. Sometimes it's better to move the rows you're thinking of processing in a temp object up to the data tier & work with them in arrays, datasets, enumerable objects etc as then you'll really be working with in-memory objects. There are other flow on benefits from doing this such as improved cache page life-expectancy on the database server (the tempdb isn't using the buffer pool as heavily) as well as greater scalability from use of unlimited (farmed) data tier memory rather than the limited buffer pool cache on the database server.