Table Variable Vs Temp Table Vs Global Temp Table

Comparative Analysis of Temporary Table and Table Variable based on different aspects.

1. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions, whereas Table Variables can be used in User Defined Functions.

Demo on Table Variable : User defined Functions allow Table Variables in it.

Demo on Temporary Table : User defined Functions doesn’t allow Temporary Table in it.

2. INDEXES
Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are added as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
On the other hand 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.

Demo on Temporary Table :

Demo on Table Variable :

3. SCOPE
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.
Whereas, 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.

Demo on Table Variable :

--Try to Access the Table Variable outside the batch
--but within the same session scope

Demo on Local Temporary Table :

Another demo on Stored Procedures : This example demonstrates that a Local Temporary Table created within a stored procedure can’t be accessed outside the stored procedure scope.

Try to Access the Temporary Table created during the SP

Below are some more examples where I am also sharing code to understand difference : 

Demo 1 : This example demonstrates that a Table Variable created within a stored procedure can’t be accessed outside the stored procedure scope.

CREATE PROCEDURE dbo.SomeSPForTableVariableDemo
AS
BEGIN
 --Declare Table Variable
 DECLARE @TableVariableScopeSPDemo TABLE(Name VARCHAR(50))
 --Insert a Record in the Temporary Table
 INSERT INTO @TableVariableScopeSPDemo
 VALUES ('Mitesh Gupta')
 --Try to Access Table Variable
 SELECT * FROM @TableVariableScopeSPDemo
END
GO

Try to execute the above stored procedure which creates a Table Variable and a statement accessing the Table Variable immediately after the Stored Procedure within the same batch as shown in the below script. This results in a compilation error.

--Execute the Stored Procedure
EXEC dbo.SomeSPForTableVariableDemo
--Try to Access the Table Variable created during the SP
--SomeSPForTableVariableDemo execution by previous statement
SELECT * FROM @TableVariableScopeSPDemo
GO

Try to execute the above stored procedure which creates a Table Variable and a statement accessing the Table Variable created in the SP in separate batches one after another but in the same session as shown below. In such scenario the SP execution succeeds, but the statement trying to access the Table variable outside SP in the next batch errors-out.

--Execute the Stored Procedure
EXEC dbo.SomeSPForTableVariableDemo
GO
--Try to Access the Table Variable created during the SP
--SomeSPForTempTableDemo execution by previous statement
 SELECT * FROM @TableVariableScopeSPDemo
GO

Demo 2: This example demonstrates that a Local Temporary Table created in the Parent Stored Procedure can be accessed by it’s child stored procedures.

-------------Create ParentSP----------------
CREATE PROCEDURE dbo.ParentSPForTempTableDemo
AS
BEGIN
 --Create Temporary Table
 CREATE TABLE #TemporaryTableScopeSPDemo(Name VARCHAR(50))
 --Insert a Record in the Temporary Table
 INSERT INTO #TemporaryTableScopeSPDemo
 VALUES ('Mitesh Gupta')
 --CALL Child SP
 EXEC dbo.ChildSPForTempTableDemo
END
GO
-------------Create ChildSP----------------
CREATE PROCEDURE dbo.ChildSPForTempTableDemo
AS
BEGIN
 --Try to Access Temp Table created in the Parent SP
 SELECT * FROM #TemporaryTableScopeSPDemo
END
GO
----------Execute the ParentSP----------
EXEC dbo.ParentSPForTempTableDemo

Demo 3 : This example demonstrates that a Table Variable Created in the Parent SP is not accessible by it’s child stored procedures. Basically, Sql Server doesn’t allow us to Create a Child Stored Procedure accessing the Table Variable Created in the Parent SP.

-------------Create ParentSP----------------
CREATE PROCEDURE dbo.ParentSPForTableVariableDemo
AS
BEGIN
 --Create Temporary Table
 CREATE TABLE @TableVariableScopeSPDemo(Name VARCHAR(50))
 --Insert a Record in the Temporary Table
 INSERT INTO @TableVariableScopeSPDemo
 VALUES ('Mitesh Gupta')
 --CALL Child SP
 EXEC dbo.ChildSPForTableVariableDemo
END
GO
-------------Create ChildSP----------------
CREATE PROCEDURE dbo.ChildSPForTableVariableDemo
AS
BEGIN
 --Try to Access the Table variable Created in Parent SP
 SELECT * FROM @TableVariableScopeSPDemo
END
GO


Thank you for reading!