



Difference between Temporary Table and Table Variable?

Temporary Table

Local Temporary Table

-- Create Temporary Table

CREATE TABLE #student (Id INT Primary Key, Name VARCHAR(50))

--Insert records into #student (temp) table

INSERT INTO #Student VALUES(1,'Ravi')

INSERT INTO #Student VALUES(2,'Shankar')

--Get all student list

SELECT * FROM #student

Global Temporary Table

-- Create Temporary Table

CREATE TABLE ##student (Id INT Primary Key, Name VARCHAR(50))

--Insert records into #student (temp) table

INSERT INTO ##Student VALUES(1,'Ravi')

INSERT INTO ##Student VALUES(2,'Shankar')

--Get all student list

SELECT * FROM ##student

Table Variable

-- Create Table Variable

DECLARE @Student TABLE

(

SNo INT IDENTITY(1,1),

StudentName NVARCHAR(20),

RollNo INT

)

--Insert Record into @Student (Table Variable)

INSERT INTO @Student(StudentName,RollNo)

VALUES('Ravi', 1) ,('Shankar',2)



--Select @Student (Table Variable) Data

Select * from @Student

Similarities in Temporary Table and Table Variable

Both stored into tempdb Database

Clustered indexes support into Temporary Table and Table Variable

Keywords -





Difference between Temporary Table and Table Variable

Temporary Table vs Table Variable

Local Temporary Table Vs Global Temporary Table.

Sql Server provide different ways to store data like Temporary Table, Table Variable, CTE, Local Temporary Table and Global Temporary Table. Today we showing difference between Temporary Table and Table Variables.Temporary table behave like a real tables but created on run time. Its work similar to real table. We can do almost every operations which possible into real tables. We can use DDL Statements like ALTER, CREATE, DROP on Temporary Tables.Any changes in structure of Temporary table is possible after creation. Temporary Table stored into “tempdb” Database of system Databases.Temporary Table participate in transactions, logging or locking. Due to that reason it’s slower than Table Variable.Temporary Tables are not allowed in User Defined Functions.Local Temporary Table only available to the current session and connections only. If you use other session then Local Temporary Tables will not available. Local Temporary Tables automatically deleted when the session end.You can create temporary table start with single hash ("#") sign. You can see Local Temporary Table into tempdb database in System Databases starting with single hash (#).Global Temp tables are available to all sessions or connections. Table created by any session available to other session, its share the temporary table till all session not closed. You can create Global temporary table started with double hash ("##") sign.You can see Global Temp Table into tempdb database in System Databases with starting with double hash (##).It’s Variable but work like a table. It’s also created into Tempdb Database not in the memory. Table Variable only available in the batch or stored Procedure scope. You no need to drop Table Variable , It’s automatically dropped when batch and store Procedure execution process completeTable variable support primary Key, identity on creation time. But it not support non-clustered index. After declaration primary key, identity you can’t modify them.Table variables don't participate in transactions, logging or locking. Transactions, logging and locking not effect to Table Variables.Table Variable is faster than Temporary Table due to it’s not participate in transactions, logging and locking.You can use Table Variable in User Defined Functions.