Types of Temp Table:

DBTempTable

You can run the follow script to see the differences between Global temp table, local temp table and table variables:

CREATE TABLE ##TGlobal (s VARCHAR(128))
CREATE TABLE #T (s VARCHAR(128))
DECLARE @T TABLE (s VARCHAR(128))

INSERT INTO ##TGlobal SELECT ‘old value ##’
INSERT INTO #T SELECT ‘old value #’
INSERT INTO @T SELECT ‘old value @’

BEGIN TRANSACTION
UPDATE ##TGlobal SET s=’new value ##’
UPDATE #T SET s=’new value #’
UPDATE @T SET s=’new value @’
ROLLBACK TRANSACTION

SELECT * FROM ##TGlobal
SELECT * FROM #T
SELECT * FROM @T

Advertisements