Temporary and Global Temporary Table in SQL Server 2008

Temporary Table in SQL Server

Temporary tables are tables that are available only to the session that created them.

These tables are automatically destroyed at the termination of the procedure or session that created them.

Use of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in the current session.

Temporary tables are created using the same syntax as a CREATE TABLE except the table name starts with a '#' sign. When the table consists of a single '#' sign, it is defined as a local temporary table and its scope is limited to the session it is created in.

Global Temporary Table in SQL Server

Global Temporary tables are visible to or available across all sessions. And all users.

Global Temporary tables are created using the same syntax as a CREATE TABLE except the table name starts with "##" (two '#' signs). When the table is only "##", it is defined as a local global temporary table and it's scope is not limited to the session it is created in.

A Global Temporary table is dropped automatically when the last session using the temporary table has completed.

Both the local temporary tables and global temporary tables are physical.

Uses of Temporary Tables

A Temporary Table variable can be very useful when used with stored procedures to pass input/output parameters or to store the result of a table valued function.

Now to create the Temporary table the query will be:

CREATE TABLE #TEMPTABLE
(
Id INT,
Name VARCHAR(30),
Date DATETIME DEFAULT GETDATE()
)

Run the query. After that the "TEMPTABLE" will be created.

Now we are going to insert some values into the TempTable.

INSERT INTO #TEMPTABLE(Id, Name) VALUES(1,'shirsendu');
INSERT INTO #TEMPTABLE(Id, Name) VALUES(2,'Sarnali');
INSERT INTO #TEMPTABLE(Id, Name) VALUES(3,'Mrinal');

Execute the Query

Now to see the values inserted into the temp table execute the following query:

select * from #TEMPTABLE

The result will look like

Now to create the Global Temporary Table.

For creating the table we already know to use "##" before the table name.

CREATE TABLE ##GLOBALTEMPTABLE
(
Id INT,
Address VARCHAR(30),
Date DATETIME DEFAULT GETDATE()
)

Run the query. After that the "GLOBALTEMPTABLE" is created.

Now we are going to insert some values into the GLOBALTEMPTABLE.

INSERT INTO ##GLOBALTEMPTABLE(Id, Address) VALUES(1,'Bangalore');
INSERT INTO ##GLOBALTEMPTABLE(Id, Address) VALUES(2,'Bangkok');
INSERT INTO ##GLOBALTEMPTABLE(Id, Address) VALUES(3,'CAlcutta');

Now to see the values inserted into the GLOBALTEMPTABLE execute the following query.

select * from ##GLOBALTEMPTABLE

The result will look like:

Now you can cross join between the Temporary table and the GlobalTemporaryTable as in:

select * from ##GLOBALTEMPTABLE,#TEMPTABLE

The result will look like:

Conclusion

So in this article, we have seen how to create a temporary table and a global temporary table and how the data can be fetched.

1 Comment

Join the conversation! Your thoughts help the community grow.

Lalit Raghuvanshi11y ago
There is another good detailed example on:Sql server Temporary tables, their types, use with examples and important points http://www.webcodeexpert.com/2015/01/example-to-create-and-use-temporary_31.html