Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope in Sql Server

Differences, Sql Server, , , , , , ,

This is the third article in the series of articles on Difference Between Temporary Table and Table Variable. This article provides the Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope with extensive list of examples.

Below is the complete list of articles in this series:

Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope

1. USER DEFINED FUNCTION

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

Table Variable

DEMO 1: User defined Functions allow Table Variables in it.

--Create a function which is using a Table Variable
CREATE FUNCTION TableVariableWithinAFunction()
RETURNS INT
AS
BEGIN
	--Declare Table Variable
	DECLARE @TableVariable TABLE(Name VARCHAR(50)) 
	IF EXISTS(SELECT 1 FROM @TableVariable)
		RETURN 1

	RETURN 0
END
GO
--Call the function
PRINT dbo.TableVariableWithinAFunction()
GO

RESULT:
FUNCTION Table Variable

Temporary Table

DEMO 1: User defined Functions doesn’t allow Temporary Table in it.

CREATE FUNCTION TemporaryTableWithinAFunction()
RETURNS INT
AS
BEGIN
	CREATE TABLE #TempTable (Name VARCHAR(50)) 
	IF EXISTS(SELECT 1 FROM #TempTable)
		RETURN 1

	RETURN 0
END

RESULT:
Msg 2772, Level 16, State 1, Procedure TemporaryTableWithinAFunction, Line 5
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure TemporaryTableWithinAFunction, Line 6
Cannot access temporary tables from within a function.

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.

Temporary Table

Demo 1: You can add Indexes to the Temporary Table both explicitly and implicitly. Below script creates a Temporary Table #Employee which has Primary Key with Clustered option, it means it will implicitly create a Clustered Index on the Id column. After the creation of the Temporary Table, explicitly adds a Non-Clustered Index IX_#Employee_FirstName on the FirstName column.

--Create Temporary Table
CREATE TABLE #Employee
(
  Id INT PRIMARY KEY CLUSTERED,
  FirstName NVARCHAR(50),
  LastName NVARCHAR(50)
)
--Add Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_#Employee_FirstName 
      ON #Employee(FirstName)
GO

Table Variable

You can’t add Indexes explicitly to the Table Variable. But as Table variables support Primary and Unique Key constraints, so specifying these constraints during Table variable declaration internally creates Indexes on the Table Variable columns. But as we know both Primary and Unique Key doesn’t allow duplicate values in it (i.e. enforces unique constraint), so they provide means to create only Implicit Clustered or Non-Clustered Unique indexes.  It implies Tables Variables doesn’t support adding a  Non-Unique Non-Clustered indexes either implicitly or explicitly.

Demo 1:  Below script creates a Table variable @Employee which has Primary Key with NonClustered option, this means it will implicitly create a Unique Non-Clustered Index on the Id column. And it also has Unique Clustered Constraint on the Name column, so it will create an Unique Clustered Index on the Name column.

DECLARE @Employee TABLE
(
  Id INT PRIMARY KEY NONCLUSTERED,
  Name NVARCHAR(50) UNIQUE CLUSTERED(Name)
)

3. SCOPE

Table Variable

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.

Temporary Table

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.

Below DEMOS explain the above explained scoping aspect of the Table Variable and Temporary Tables with extensive list of examples.

DEMO 1: This example demonstrates that a Table variables Scope is the Batch in which it is created, they are not accessible in the same session’s next batch of statement.

--Declare Table Variable 
DECLARE @TableVariableScopeBatchDemo TABLE (Name VARCHAR(50)) 
--Insert a Record in the Table Variable
INSERT INTO  @TableVariableScopeBatchDemo
VALUES ('Basavaraj Biradar')
--Try to Access Table Variable in the batch scope
SELECT * FROM @TableVariableScopeBatchDemo
GO -- GO Statement Signals the END of the BATCH
--Try to Access the Table Variable outside the batch 
--but within the same session scope
SELECT * FROM @TableVariableScopeBatchDemo
GO

RESULT:
Scope Of Table Variables Is The Batch1

DEMO 2: This example demonstrates that a Local Temporary Table scope is the session in which it is created. Here a local Temporary Table created within the first batch is accessible in the same session’s next batch of statements.

--Create Temporary Table 
CREATE TABLE #TemporaryTableScopeBatchDemo (Name VARCHAR(50)) 
--Insert a Record in the Temporary Table
INSERT INTO  #TemporaryTableScopeBatchDemo 
VALUES ('Basavaraj Biradar')
--Try to Access Temporary Table in the batch scope
SELECT * FROM #TemporaryTableScopeBatchDemo
GO -- GO Statement Signals the END of the BATCH
--Try to Access the Temporary Table outside the batch 
--but within the same session scope
SELECT * FROM #TemporaryTableScopeBatchDemo
GO

Scope Of Temporary Table Is The Session

DEMO 3: This example demonstrates that a Local Temporary Table created within a stored procedure can’t be accessed outside the stored procedure scope.

CREATE PROCEDURE dbo.SomeSPForTempTableDemo
AS
BEGIN
 --Create Temporary Table 
 CREATE TABLE #TemporaryTableScopeSPDemo(Name VARCHAR(50)) 
 --Insert a Record in the Temporary Table
 INSERT INTO #TemporaryTableScopeSPDemo
 VALUES ('Basavaraj Biradar')
 --Try to Access Temporary Table
 SELECT * FROM #TemporaryTableScopeSPDemo
END
GO
--Execute the Stored Procedure
EXEC dbo.SomeSPForTempTableDemo
--Try to Access the Temporary Table created during the SP 
--SomeSPForTempTableDemo execution by previous statement
SELECT * FROM #TemporaryTableScopeSPDemo
GO

Scope of Temporary Table Created within Stored Procedure

DEMO 4: 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 ('Basavaraj Biradar')
 --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

Scope of Table Variable Created within Stored Procedure1

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

Scope of Table Variable Created within Stored Procedure2

DEMO 5: 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 ('Basavaraj Biradar')
 --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

RESULT:
Scope of Temporary Table in Nested Stored Procedure

DEMO 6: 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 ('Basavaraj Biradar')
 --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

RESULT:
Scope of Table Variable in Nested Stored Procedure

You may like to read the other articles in this Series of articles on Difference Between Temporary Table and Table Variable:

2 thoughts on “Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope in Sql Server”

  1. Pingback: Difference Between Temporary Table and Table Variable in Sql Server | SqlHints.com
  2. Pingback: Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions in Sql Server | SqlHints.com

Leave a Reply

Your email address will not be published. Required fields are marked *