Types for the temporary tables

SQL Server Temporary tables are a special type of tables that are written to the TempDB database and act like regular tables, providing a suitable workplace for intermediate data processing before saving the result to a regular table, as it can live only for the age of the database connection. Once session or database connection closed tables are automatically dropped.

There are four main types for the temporary tables

1.       Local Temporary Table

2.       Global Temporary Table

3.       Persistent Temporary table

4.       Table Variable  Table

Local temporary table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

See the example

create table #temp (ID Int identity(1,1), Name varchar(100) )

Temp table create successfully. Now I am inserting some records in the temp table.

We can use this table on this current connection. If we want to use other window (other connection) it will throw error like Invalid object name '#temp'. See

This table is created on the TempDB

We can create multiple local tables with same name on different connection.

Both table created on TempDB with same name (different numeric number). See in the database.

Global Temporary Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

See the example

create table ##Global_Temp_tbl (ID Int identity(1,1), Name varchar(100) )

Global temp table created successfully. I am inserting some records in this table.

Now I am trying to access this table in other window.

  

See in TempDB

We can’t create a multiple global table with same name like local temp table. Let’s see I am creating same table using other window.

Persistent Temporary table

A Persistent Temporary table which is named starting with a TempDB prefix such as TempDB.DBO.TempShipments

Table Variable Table

Table Variable that starts with an @ prefix (e.g. @ VarEmp) The SQL Server Database Engine can distinguish between the same temporary tables created while executing the same stored procedure many times simultaneously by appending a system-generated numeric suffix to the temporary table name. This is why the local temporary table name can’t exceed 116 characters.

Read: Differencebetween temp table and variable table

http://bageshkumarbagi-msbi.blogspot.in/2016/05/difference-between-temp-table-and.html

Example

DECLARE @VarEmp TABLE (  ID INT,  Name VARCHAR(50)  ) --Insert Two records INSERT INTO @VarEmp VALUES(1,'Bagesh Kumar Singh') INSERT INTO @VarEmp VALUES(2,'Rajesh kumar singh') --Retrieve the records SELECT * FROM @VarEmp

See the output