-- Temporary tables vs table variables

create table test_table (col1 int)
      declare @i int;
      set @i = 0;
      while (@i < 100000)
      begin
insert into test_table values(@i);
            set @i = @i + 1;
      end


create procedure table_variable_proc 
as 
begin
declare @table_variable table(col1 int, col2 varchar(128));
      declare @i int;
      set @i = 0;
      while (@i < 100000)
      begin
insert into @table_variable values(@i, convert(varchar(128), @i));
            set @i = @i + 1;
      end
select * from @table_variable tv join test_table on tv.col1 = test_table.col1;
end
go
 
exec table_variable_proc
go
 
-- Now let us rewrite the same example with temporary tables to compare and contrast the two approaches:
 
create procedure temp_table_proc 
as 
begin
create table #table_name(col1 int, col2 varchar(128));
      declare @i int;
      set @i = 0;
      while (@i < 100000)
      begin
insert into #table_name values(@i, convert(varchar(128), @i));
            set @i = @i + 1;
      end
select * from #table_name join test_table on #table_name.col1 = test_table.col1;
end
go
 
exec temp_table_proc
go
 
-- Now query the DMVs to get the query plan and average CPU time:
-- The temporary table outperforms the table variable query
 
select total_worker_time/execution_count as avg_cpu_time,
substring(st.text, (qs.statement_start_offset/2) + 1,
((case statement_end_offset 
when -1 
      then datalength(st.text) 
else 
      qs.statement_end_offset 
end 
- qs.statement_start_offset)/2) + 1) as statement_text
, cast(query_plan as xml)
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) 
order by total_worker_time/execution_count desc;
go


declare @@trancount int