Difference Between Temporary Table and Table Variable in Sql Server

Difference Between Temporary Table and Table Variable – Summary

Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.

Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.

1. SYNTAX
Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table. -- Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) --Insert Two records INSERT INTO #Customer VALUES(1,'Basavaraj') INSERT INTO #Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM #Customer --DROP Temporary Table DROP TABLE #Customer GO	Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. -- Create Table Variable DECLARE @Customer TABLE (  Id INT,  Name VARCHAR(50)   ) --Insert Two records INSERT INTO @Customer VALUES(1,'Basavaraj') INSERT INTO @Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM @Customer GO RESULT:
2. MODIFYING STRUCTURE
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP. Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped. --Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) GO --Add Address Column ALTER TABLE #Customer ADD Address VARCHAR(400) GO --DROP Temporary Table DROP TABLE #Customer GO	Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.
3. STORAGE LOCATION
One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.
4. TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.	Table variables doesn’t participate in the explicit transactions defined by the user.
5. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.	Table Variables can be used in User Defined Functions.
6. INDEXES
Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.	Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
7. SCOPE
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure.Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it.	Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes.

SQL Full outer join

The full outer join returns a result table with the matched data of two table then remaining rows of both lefttable and then the right table.

Full Outer Join Syntax is,

select column-name-list
from table-name1 
FULL OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

Example of Full outer join is,

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info on (class.id=class_info.id);

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null
null	null	7	NOIDA
null	null	8	PANIPAT

---

SQL Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table's columns.

Right Outer Join Syntax is,

select column-name-list
from table-name1 
RIGHT OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

Right outer Join Syntax for Oracle is,

select column-name-list
from table-name1, 
table-name2
on table-name1.column-name(+) = table-name2.column-name;

---

Example of Right Outer Join

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info on (class.id=class_info.id);

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
null	null	7	NOIDA
null	null	8	PANIPAT

SQL Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the lefttable and null for the right table's column.

Left Outer Join syntax is,

SELECT column-name-list
from table-name1 
LEFT OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

Left outer Join Syntax for Oracle is,

select column-name-list
from table-name1, 
table-name2
on table-name1.column-name = table-name2.column-name(+);

---

Example of Left Outer Join

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu
5	ashish

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI
7	NOIDA
8	PANIPAT

Left Outer Join query will be,

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id=class_info.id);

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI
4	anu	null	null
5	ashish	null	null

SQL Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural Join Syntax is,

SELECT *
from table-name1 
NATURAL JOIN 
table-name2;

---

Example of Natural JOIN

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info; 

The result table will look like,

ID	NAME	Address
1	abhi	DELHI
2	adam	MUMBAI
3	alex	CHENNAI

In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.

SQL INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query.

Inner Join Syntax is,

SELECT column-name-list
from table-name1 
INNER JOIN 
table-name2
WHERE table-name1.column-name = table-name2.column-name;

---

Example of Inner JOIN

The class table,

ID	NAME
1	abhi
2	adam
3	alex
4	anu

The class_info table,

ID	Address
1	DELHI
2	MUMBAI
3	CHENNAI

Inner JOIN query will be,

SELECT * from class, class_info where class.id = class_info.id;

The result table will look like,

ID	NAME	ID	Address
1	abhi	1	DELHI
2	adam	2	MUMBAI
3	alex	3	CHENNAI