Temp Table Vs Table Variable In Sql


This article lists out
  • Difference between Temporary Table and Table Variable.
  • Global Temporary Table Declaration
  • Indexing  Temporary Table and Table Variable.
 
Temp Table Table Variable
Below is the sample example of Creating a Temporary Table, Inserting data, Selecting Data, Droping table.
-- Create Temporary Table
CREATE TABLE #Sample
(
Id INT,
StdName VARCHAR(50)
)
GO
--Inserting Records
INSERT INTO #Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
GO
--Retrieve the records
SELECT * FROM #Sample
GO
--DROP Temporary Table
DROP TABLE #Sample
Below is the sample example of Declaring a Table Variable, Inserting records into it and selecting the rows from it.
-- Create Table Variable
DECLARE @Sample TABLE
(
Id INT,
StdName VARCHAR(50)
)
--Inserting Records
INSERT INTO @Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
--Retrieve the records
SELECT * FROM @Sample
Possible to applay DDL statements ALTER, CREATE, DROP, After table declaration
-- Create Temporary Table
CREATE TABLE #Sample
(
Id INT,
StdName VARCHAR(50)
)
GO
--Inserting Records
INSERT INTO #Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
GO
--Add New Column
ALTER TABLE #Sample
ADD Age INT
GO
--Retrieve the records
SELECT * FROM #Sample
GO
--DROP Temporary Table
DROP TABLE #Sample
Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP after its declaration.
Possible to create index explicitly,after table declaration
-- Create Temporary Table
CREATE TABLE #Sample
(
Id INT,
StdName VARCHAR(50)
)
GO
--Inserting Records
INSERT INTO #Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
GO
--Creating NONCLUSTERED index
CREATE NONCLUSTERED INDEX IndX_Name ON #Sample([StdName]);
GO
--Retrieve the records
SELECT * FROM #Sample
GO
--DROP Temporary Table
DROP TABLE #Sample
Not possible to create Index explicitly after table declaration. only possible indexing during declaration

--Create Table Variable with primary key
DECLARE @Sample TABLE
(
Id INT PRIMARY KEY,
StdName VARCHAR(50),
INDEX IndX_Name NONCLUSTERED (StdName)
)
--Inserting Records
INSERT INTO @Sample
SELECT 1,'Sanoop'
UNION ALL
SELECT 2,'Vasanth'
--Retrieve the records
SELECT * FROM @Sample




Temporary Tables are not allowed in User Defined Functions Table Variables can be used in User Defined Functions.
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. Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. Or you can access Global Temporary Table out side the procedure

/*Create and dropping Global temporary table*/

CREATE TABLE ##Sample
(
Id INT,
StdName VARCHAR(50)
)
DROP TABLE ##Sample
Scope of the Table variable is the Batch or Stored Procedure in which it is declared

No comments:

Post a comment