Difference between Temporary Table and Table Variable

Difference between Temporary Table and Table Variable?

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

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.

Sql Server support two types of Temporary Tables.

• Local Temporary Table
• Global Temporary Table

Local Temporary Table

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 (#).

>System Database >tempdb > Temporary Tables > dbo.#[Local Temporary Table]

Example:

-- 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

Screenshot:

Global Temporary Table

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 (##).

>System Database >tempdb > Temporary Tables > dbo.## [Global Temporary Table]

Example:

-- 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

Screenshot:

Table Variable

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 complete

Table 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.

Example:

-- 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

Screenshot:

devdiscuss

Search This Blog