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?

Youmans

dba.stackexchange.com/questions/16385/… – Essive

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

Eckert

Answer 2

It's been my understanding that, at a minimum, the structure of a table variable is always created in TempDB. Then, as pointed out by SQLMenace, the data may or may not spill over.

Per this Microsoft Knowledge Base Article:

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).

Spigot

The data is always held on data pages belonging to tempdb. These may or may not be written to disc and if written to disc the pages may or may not still be in the buffer pool. Some relevant demonstrations at the very end of my answer here – Cavanaugh

Answer 3

In MS SQL 2014 was introduced special type of table variables "Memory-Optimized Table Variables". And they don't use tempdb.

See https://msdn.microsoft.com/en-us/library/dn535766.aspx

Narial