Temporary tables can be used as helper tables. They are stored in tempdb database.
Global temporary tables start with ## and are visible in all sessions.
Local temporary tables start with # and are visible only in current session.
Table variables are similar to temporary tables, but they are not physically stored and should be used only for small amount of data and easy queries. They are visible in current batch only.
Temporary tables play normally with transactions and table variables not. Table variables are not rollbacked when transaction is rollbacked. It can be usefull to reproducing errors.
CREATE TABLE #MyLocalTempTable -- table visible only in current session ( id INT NOT NULL PRIMARY KEY, name NVARCHAR(100) NOT NULL ); INSERT INTO #MyLocalTempTable VALUES(1, 'Bob'); CREATE TABLE ##MyGlobalTempTable -- table visible in all sessions ( id INT NOT NULL PRIMARY KEY, name NVARCHAR(100) NOT NULL ); INSERT INTO ##MyGlobalTempTable VALUES(1, 'Bob'); DECLARE @MyTableVariable AS TABLE -- variable visible only in current batch ( id INT NOT NULL PRIMARY KEY, name NVARCHAR(100) NOT NULL ); INSERT INTO @MyTableVariable VALUES(1, 'Bob');
Listing temporary objects:
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';