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:

1. Local Temp Tables
2. 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.