SQL Server TSQL Temporary Table

Temp tables in SQL Server: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases. Temp tables can be created in all versions of SQL Server including SQL Server Express Edition.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

It is most common using temporary tables is from within a stored procedure.

It is common to use Temp table is to store the results of a SELECT INTO statement and then Insert into… Select statement.

Different Types of Temporary Tables SQL Server provides two types of temp tables based scope of the table. They are:

1.Local Temp Table
2.Global Temp Table

1.Local Temp Table

Local temp tables are only available to the current connection of the user. They are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash “#” sign.
Global Temp Table

Global Temp Table can be created by any 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.Global Temporary tables name starts with a double hash “##”

Creating Temporary Table in SQL Server

Local Temporary Table

 

CREATE TABLE #LocalTempTable
(ID int,
Name varchar(30),
Address varchar(300))

It Can be used like below


insert into #LocalTempTable values ( 1, 'Vamshi','India');



CREATE TABLE ##NewGlobalTempTable
(ID int,
Name varchar(30),
Address varchar(300))


It Can be used like below

insert into #NewGlobalTempTablevalues ( 1, 'Vamshi','India');


All temporary tables can be seen under Tempdb in SQL server under tables while they are in use and are stored in TempDB.