Temporary Tables in SQL Server

Introduction

Temporary tables are tables which are created at run time. Can perform all kind of operations on temp tables like normal table but scope is limited. These are stored in temporary tables.

Temporary tables are two types:

Local Temp Tables
Global Temp Tables

Local Temp Tables:

These are accessed with current connection only. These will delete when user disconnected with instance and prefixed with '#' sign.

Global Variables:

These are accessed with any connection. These will delete when all connections are closed and prefixed with '##' sign.

Example for Temp Tables:

CREATE TABLE #MYTABLE
(DID INT,
DNAME VARCHAR(10),
DLOC VARCHAR(10)
)

Operations on Temp Table:

INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME) VALUES (14,'CSE','1ST FLOOR')
SELECT * FROM #MYTABLE
UPDATE #MTTABLE SET DNAME = 'ECE', DLOC = '4TH FLOOR' WHERE DID = 14
DELETE FROM #MYTABLE WHERE DID = 14

Example for Temp Variables:

These are also like temp tables, can be created and can perform all kind of operations like normal tables.

BEGIN
DECLARE @TEMPTABLEVARIABLE TABLE (DID INT,DNAME VARCHAR(10),DLOC VARCHAR(100))
INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME,DLOC) VALUES (14,'CSE','3RD FLOOR')
INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME,DLOC) VALUES (15,'IT','1ST FLOOR')
SELECT * FROM @TEMPTABLEVARIABLE
UPDATE @TEMPTABLEVARIABLE SET DLOC = '5TH FLOOR' WHERE DID = 14
SELECT * FROM @TEMPTABLEVARIABLE
DELETE FROM @TEMPTABLEVARIABLE WHERE DID = 14
SELECT * FROM @TEMPTABLEVARIABLE
END

Difference between Temp Table and Temp Variable:

Let us observe below example once:

BEGIN
DECLARE @VAR1 INT
SET @VAR1 = 1
SELECT @VAR1 BEFORETRANSACTION
BEGIN TRAN
SET @VAR1 = 2
ROLLBACK
SELECT @VAR1 AFTERTRANSACTION
END

If you observe care fully local variable does not affected / unafftected by roll back statement. If temp table variable is really a variable, can have same behavior. See below once.

BEGIN
IF OBJECT_ID('TEMPDB.DBO.#MYTABLE') IS NOT NULL DROP TABLE #MYTABLE
CREATE TABLE #MYTABLE (DID INT,DNAME VARCHAR(10))
INSERT INTO #MYTABLE(DID,DNAME) VALUES (12,'IT')
DECLARE @TEMPTABLEVARIABLE TABLE (DID INT,DNAME VARCHAR(10))
INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME) VALUES (14,'CSE')
SELECT DID,DNAME FROM #MYTABLE
SELECT DID,DNAME FROM @TEMPTABLEVARIABLE
BEGIN TRAN
INSERT INTO #MYTABLE(DID,DNAME) VALUES (15,'ECE')
INSERT INTO @TEMPTABLEVARIABLE (DID,DNAME) VALUES (16,'EEE')
ROLLBACK
SELECT * FROM #MYTABLE
SELECT * FROM @TEMPTABLEVARIABLE
END

Conclusion

Here need to understand Temp Table variable are not affected by rollback whereas Temp Table is affected by rollback. So Temp Table Variable is variable which can stores multiple records, with number of columns.