23 December 2010

Temporary tables vs Table Variables

Temporary tables

  • They are like regular tables but stored in the tempdb database.
  • These tables get dropped after they have been used.
  • Temporary tables are of two types,
  • Local Temporary table - defined using a pound sign (#) and accessible only within the session that created it
  • Global Temporary table - defined using double pound signs (##) and visible to all users.

Table Variables

  • It is a data type that looks similar to a table but has a fixed lifetime scope (i.e within a function, stored procedure or batch that it is declared in)
Here are some differences between Temporary Table and Table Variables
Temporary Tables
Table Variables
Created and stored in tempdb database Created in Memory (although it can write to tempdb)
Transaction logs are recorded for temporary tables so they are bound to transactions. Transaction logs are not recorded for the table variables so they are not bound to any transactions. So no effect of transaction rollbacks.
Can participate in parallel operations Cannot participate in parallel operations
The log activity remains till it is manually cleared or the server restarts Table variable log activity is truncated immediately
SQL Server creates statistics for temporary tables SQL Server does not create statistics for table variables
Stored procedure containing temporary tables cannot be pre-compiled Stored procedures containing table variables can be pre-compiled
You can drop a Temporary Table You cannot manually drop a table variable
You can create indexes on them You cannot ‘explicitly’ create Indexes on them (exception is the index created while creating a Primary Key)

To be more clear about the Transaction mechanism just consider the following example
create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #' 
insert into @T select 'old value @' 
begin transaction 
     update #T set s='new value #' 
     update @T set s='new value @' 
rollback transaction 
select * from #T 
select * from @T 

The o/p of will be
old value #
new value @

No comments:

Post a Comment

Your comments, Feedbacks and Suggestions are very much valuable to me :)

Things are upgraded

My Dear readers, I am really thankful for being supportive all these years. This site was the first blog site I ever created in my life...