Temporary tables and table variables in SQL Server

This article outlines and describes the available temporary storage tables in SQL server, which are Local temporary tables, Global Temporary tables and Table Variables. Each type is useful in different scenario.

In some cases during query processing you may need to temporarily store data in tables, either for a current session or
for the current batch. In those cases instead of using permanent tables you can store data into temporary tables. SQL
Server supports three types of temporary tables: local temporary tables, global temporary tables and table variables.
All this temporary tables are created in tempdb database.

Local temporary tables are visible only to the session that created it and only in creating level and all inner levels.
For example, if a stored procedure SP1 calls a procedure SP2, and SP2 calls SP3; if SP2 creates a temporary table #T
before calling SP3 then #T will be visible to SP2 and SP3 and not to SP1; and #T will be destroyed automatically when SP2
ends executing. If the temporary table is created in the outermost ad-hoc batch then it will be visible to all next
batches; and will be destroyed only when session ends.

Local temporary tables name are prefixed with #, for example #TestTempLocal.
Sample queries with local temporary table:

CREATE Table #TestLocalTempTable
col1 int,
col2 int

INSERT INTO #TestLocalTempTable (col1,col2) Values (1,2);

Select col1,col2 from #TestLocalTempTable;

They are useful when you want to share temporary data with all other sessions. They are destroyed
when creating session ends and there is no current session referring to it. Global temporary tables name are prefixed with
##, for example ##TestTempGlobal.
Sample queries with global temporary table:

CREATE Table ##TestTempGlobal
col1 int,
col2 int

INSERT INTO ##TestTempGlobal(col1,col2) VALUES(12, 13);

Select col1,col2 from ##TestTempGlobal;

Data in this table can be modified and retrieved from any active session.
You can explicitly destroy the global temporary table from any session using following query:
DROP Table ##TestTempGlobal;

Table variables are visible only to current session and only in current batch. They are not visible to inner batches in
the call stacks.
Sample queries:

DECLARE @TestTableVariable TABLE
col1 int,
col2 int

INSERT INTO @TestTableVariable (col1, col2) values(12,13);

Select col1,col2 from @TestTableVariable;

Due to optimization difference, we should use table variables with very small volumes of data (few rows) and should use
local temporary tables otherwise.


Author: naveensanagasetti24 Jul 2014 Member Level: Gold   Points : 3


Nice and simple Explanation, but you should give some detailed explanation out of this like which one is best and why..? With sample screenshots then it will be easy to understood to everyone.

As of my knowledge TableVarriables are best option.

Why because it will destroy once the execution part has been completed. But That's not possible in Temporary variables we need to destroy that object through manually...

Author: praveen28 Jul 2014 Member Level: Silver   Points : 0

A Temporary table is also destroyed automatically when session that created temporary table ends.

Author: naveensanagasetti29 Jul 2014 Member Level: Gold   Points : 3

Yah, but assume that In my procedure I want to return 1,00,00,000 records. I just store those all records into one temporary table, until I destroy this temporary table object will hold some data, once we allocate data to that temp table. Until and unless that is waste of memory. Once Procedure ends that object will destroy but up to that it holds that data, this will definitely causes performance issue.

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: