Temporary Table and Temporary Variable

Temporary tables are also table like user objects, It has structure, we can create indexes, we can perform Insert, Update, Delete commands, supported in transaction management and it supports everything like an ordinary user object. Only difference it is is not stored inside the database, It will store on the TempDB database. The life of temporary table is till the user disconnects the session from SQL Server, temporary table will be removed from tempdb database. There are two types of temporary tables Local Table, Global Table, only difference between those two tables is scope. Local temporary table is specific to session, if you disconnect the session then it will erased from tempdb, global temporay table the name itself indicates it is globally available to other users also. The life of global temporary variable is till the table owner disconnects the instance.

Examples for global and local temporary tables.

Local Temporary Table
Use Kalyandb
go
create table #local_table (id int, name varchar(20))
Insert into #local_table values (1, 'kalyan')
Insert into #local_table values (2, 'kumar')

Eventhough you create a table in kalyandb the object will create in tempdb because it is a temporary table.
We can verify by using the below command
select * from tempdb..sysobjects where xtype='U' * from tempdb..sysobjects where xtype='U'


Global Temporary Table

Use Kalyandb
go
create table ##global_table (id int, name varchar(20))
Insert into ##global_table values (1, 'kalyan')
Insert into ##global_table values (2, 'kumar')

Try to access the global temporary table using other session, we can able to view the table data.


Table Variable -- Like all other variables, this is also one type of variable which can be declared in the while performing T-SQL programming, the scope of table variable is only specific to that batch. The difference between temp variable and temp table is we cann't create index or statistics on temp variable, and this cannot be used in transaction management, you can't use DDL statement like alter on temp variable whereas it is possible in temp table.

Examples

declare @temp_table table (id int, name varchar(20))
insert into @temp_table values(1, 'kalyan')
insert into @temp_table values(2, 'kumar')
select * from @temp_table