- 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.
- 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
|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
The o/p of will be
old value #
new value @