How is memory and the temp db used in SQL?

Though this blogpost does not totally answer your question, it does present lots of information about temp tables and table variables.

In this FAQ (question 4) about SQL Server 2000, it's said that both temp tables and table variables are stored in memory if there's enough memory.

This MSDN Social discussion gives a somewhat clearer picture to what's meant by the answer to Q4 in the above FAQ.

And so on. There are tons of discussions about how #temp tables are created and stored. There's no consencus about where #temp tables are stored, but the majority of I-know-SQL-Server-people seems to argue that they are stored in memory only if possible.

Finally, consider this when you use #temp tables and @table variables.

create table #t (id int identity(1,1) primary key, f1 varchar(100))
declare @t table (id int identity(1,1) primary key, f1 varchar(100))
insert into @t select 'hej'
insert into #t select 'hej'
begin tran
insert into @t select 'hå'
insert into #t select 'hå'
rollback
select * from @t
select * from #t

tempdb works just like any other database with regards to caching and writing to disk. If the table is small enough to fit into cache it will.

To find ou how the table is created you need to start playing around with trace flags and DBCC commands.

Whilst the topic isn't directly related to memory usage and tempdb, this blog post by Paul Randal dives into table structures in tempdb: Misconceptions around TF 1118

You can modify his test code to show how allocations occur for a temp table of your design:

USE tempdb
GO

-- Create a nice temp table
CREATE TABLE #MarksTempTable
    (c1 int IDENTITY,
     c2 int NOT NULL,
     c3 varchar(255)) ;
GO

-- Find out the allocation information for the new temp table
-- Note that after initial creation, there has only been a meta data allocation
-- (Page numbers are all set to 0:0)
-- If you try reading the meta data using the DBCC commands IND and PAGE you 
-- won't get any results.
EXEC sp_AllocationMetadata '#MarksTempTable'
GO

-- Now insert some data
INSERT INTO #MarksTempTable
        (c2, c3)
VALUES  (0,'')
GO

-- Re-Run the meta data retrieval and there are now entries in the meta data
EXEC sp_AllocationMetadata '#MarksTempTable'
GO

-- Now we can take a look at the information using DBCC
-- take the information from the column "First Page" of the meta data retireval

-- Redirect DBCC PAGE results to your screen instead of log file
DBCC TRACEON (3604); 
GO

-- Outputs a lot of information about the first page of your table
DBCC PAGE ('tempdb', <Number before colon>, <Number After Colon>, 3);     

-- Outputs information about pages in an index
DBCC IND ('tempdb','#MarksTempTable',-1) 
GO 

-- Turn the DBCC output redirection back off
DBCC TRACEOFF (3604)
GO

Its a common mis-consumption that table variables always reside on memory. If you have plenty with cache then, temp tables or variable tables will reside in cache only. If cache is low then tempDB will expand to disk. Consider optimizing the tempDB by isolating tempdb files to separate disk, adequately size your tempdb, consider using multiple files for tempdb.