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;

Shop online for Moncler fake moncler jackets Clothing, Jackets & Coats fake moncler men jackets for Men and Women . Free Shipping & Free Returns for Loyallists or most Orders Over ...
ReplyDeletefake moncler jackets Shop the Latest Men's fake moncler men jackets Collections from Moncler.
ReplyDelete