2 Tempdb usage: Temporary table and table variable

We can create temp table, table variable or event regular table in tempdb.

However, the table variable wont be able to take the advantages for caching . ( see detail here ).

In the example below, you can also see the table variable shows the wrong statistic in the exaction plan.

There are 3 DMV to help us monitor the temp db usage.

• sys.dm_db_file_space_usage
• sys.dm_db_session_space_usage
• sys.dm_db_task_space_usage

 

Temporary table: Global ( ##) or session (#)

 

create table #sunny (c1 int, c2 char(7000) not null)

declare @x int

set @x = 0

while (@x < 100)

begin

insert into #sunny values (@x, 'sunny')

set @x = @x + 1

end

go

select count(*) from #sunny;

Table variable

 

declare @summer table ( c1 int,c2 char(7000) not null);

declare @x int;

set @x = 0;

while (@x < 100)

begin

insert into @summer values (@x,'summer');

set @x = @x + 1;

end;

select COUNT(*) from @summer;

 

Check the tempdb usage

 

select * from sys.dm_db_file_space_usage;

select * from sys.dm_db_session_space_usage;