Temporary tables are used to hold temporary data for the connection that creates the table. These tables get created in the tempdb system database.
There is one more interesting difference from a permanent – a foreign key cannot be defined on a temporary table.
The temp table exists only for the connection that creates it. No other connection can see the same temp table.
Difference between temporary tables and table variables:
1. Table variables are transaction neutral, i.e., transaction logs are not maintained for changes to temporary variables
2. Table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible to inner stored procedures and exec (string) statements. Also, they cannot be used in an insert/exec statement.
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
s
---------------
old value #
s
---------------
new value @
