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:
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;
GLOBAL TEMPORARY TABLES:
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:
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.
Hi,
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...