SQL Temp Table, Table Variable and Global Table Variable

There are a few options when creating a table as part of a SQL operation. 

Notes: 

• The highlighted symbol is the indicator of the type of table.
• Temp table and table variable declaration/creation syntax is different

1.) Temp Table - This temp table is only active during the execution of the query and does not persist

DECLARE @tmp TABLE

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO @tmp (ID, Description)

VALUES (1, 'test temp table')

2.)  Table Variable - This table will need DROPped as it will persist

CREATE TABLE #tmp 

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO #tmp (ID, Description)

VALUES (1, 'test table variable')

DROP TABLE #tmp

3.)  Global Table Variable - This table will be accessible across query windows.  This table will need DROPped as it will persist.

CREATE TABLE ##globalTmp 

(

Id INT,

Description VARCHAR(32)

)

INSERT INTO ##globalTmp (ID, Description)

VALUES (1, 'test global table variable')

DROP TABLE ##globalTmp