table variables created and held in memory or in tempdb?

Question

Are table variables created in memory or in tempdb? Same for short temp tables?

Answer 1

A temp table will be created in tempdb and you can easily check for it by querying the sysobjects table in tempdb

example

create table #test (Item char(1),  TimeSold varchar(20))

select * from tempdb.sys.sysobjects
where name like '#test%'

you should see something with a name like #test_______000000000905 but then with more underscores

If you need to check if a temp table exists then see also How Do You Check If A Temporary Table Exists In SQL Server

The structure of Table variable is also created in tempdb To see the table variable you could do something like this but there is not guarantee that someone didn't sneak in before you when creating his/her table variable. The table variable name will be something like #7BB1235D

    declare @v table(id int) 
select top 1 * from tempdb.sys.sysobjects
where name like '#%'
and name not like '%[_]%'
order by crdate desc
select * from @v

For more info see here: http://support.microsoft.com/kb/305977