By deviprasadPosted on

Transactional Differences

Table variables are independent of Transactions. Like other SQL Server variables data is not recorded in transaction logs for them. Thus Rollback or Commit transactions do not affect the data in Table variables.

Temporary tables are dependent of Transactions. They behave like ordinary tables when they are placed in a transaction. Rollback and Commit transactions affect the data in Table variables.

[sql]
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb.dbo.#TempTable1’) IS NOT NULL
DROP TABLE #TempTable1

DECLARE @TableVariable1 TABLE (VALUE VARCHAR(50))
CREATE TABLE #TempTable1 (VALUE VARCHAR(50))

INSERT INTO @TableVariable1(VALUE)
SELECT ‘INSERTED AFTER CREATION’

INSERT INTO #TempTable1(VALUE)
SELECT ‘INSERTED AFTER CREATION’

BEGIN TRAN

INSERT INTO @TableVariable1(VALUE)
SELECT ‘INSERTED IN TRANSACTION’

INSERT INTO #TempTable1(VALUE)
SELECT ‘INSERTED IN TRANSACTION’

DECLARE @TableVariable2 TABLE (VALUE VARCHAR(50))

CREATE TABLE #TempTable2 (VALUE VARCHAR(50))

INSERT INTO @TableVariable2(VALUE)
SELECT ‘INSERTED AFTER CREATION’

INSERT INTO #TempTable2(VALUE)
SELECT ‘INSERTED AFTER CREATION’

DROP TABLE #TempTable1

ROLLBACK TRAN

SELECT ‘@TableVariable1’ AS TableName,* FROM @TableVariable1
SELECT ‘#TempTable1’ AS TableName,* FROM #TempTable1
SELECT ‘@TableVariable2’ AS TableName,* FROM @TableVariable2
SELECT ‘#TempTable2’ AS TableName,* FROM #TempTable2
[/sql]

Output:

TableName       VALUE
--------------- --------------------------------------------------
@TableVariable1 INSERTED AFTER CREATION
@TableVariable1 INSERTED IN TRANSACTION

TableName   VALUE
----------- --------------------------------------------------
#TempTable1 INSERTED AFTER CREATION

TableName       VALUE
--------------- --------------------------------------------------
@TableVariable2 INSERTED AFTER CREATION


Msg 208, Level 16, State 0, Line 37
Invalid object name '#TempTable2'.

From the above output we can come up with the following notes:

  • Data in the temporary tables is directly bound to transactions
    i.e. If the transaction is rolledback then the data inserted/Modified in the transaction is lost
  • Data in the table variables is not bound to transactions.
    i.e. If the transaction is rolledback then the data inserted/Modified in the transaction is not lost.
  • Creation/Deletion of temp tables is also bound to transactions.
    i.e. Temporary tables created in a transaction does not exist further if the transaction is rolled back, Temporary tables dropped in a transaction exist further if the transaction is rolled back.
  • If a Table variable is created in a transaction it exists further even if the transaction is rolled back.

Usage Differences

Temporary Tables cannot be created within a function where as table variables can be created within a function.
[sql]
CREATE FUNCTION dbo.TestFn( @i int)
RETURNS INT
AS
BEGIN
DECLARE @A TABLE ( i INT)

RETURN 1
END
[/sql]
Executes succesfully.

[sql]
CREATE FUNCTION dbo.TestFn( @i int)
RETURNS INT
AS
BEGIN
CREATE TABLE #A( I INT)

RETURN 1
END
[/sql]
Fails with an error:

Msg 2772, Level 16, State 1, Procedure TestFn, Line 5
Cannot access temporary tables from within a function.

Table variables should be referenced with an alias when used in joins etc where as alias is not mandatory for Table Variables

[sql]
CREATE TABLE #Values1 (ID INT,VALUE VARCHAR(100))
CREATE TABLE #Values2 (ID INT,VALUE VARCHAR(100))

INSERT INTO #Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

INSERT INTO #Values2 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 3,’C’

SELECT * FROM #Values1 JOIN #Values2
ON #Values1.ID = #Values2.ID
[/sql]
Executes Successfully!!

[sql]
DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))
DECLARE @Values2 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

INSERT INTO @Values2 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 3,’C’

SELECT * FROM @Values1 JOIN @Values2
ON @Values1.ID = @Values2.ID
[/sql]
Fails with an error:

Msg 137, Level 16, State 1, Line 15
Must declare the scalar variable “@Values1”.
Msg 137, Level 16, State 1, Line 15
Must declare the scalar variable “@Values2”.

Temporary tables can be dropped explicitly where as dropping table variables is not possible
[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
DROP TABLE @TableVaraiabe
[/sql]
Fails with error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘@TableVaraiabe’.

[sql]
CREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100))
DROP TABLE #TableVaraiabe
[/sql]
Executes Successfully!!

DDL Statements can be applied on temporary tables to add/modify and delete the columns but table variables cannot be altered after they are declaration.
[sql]
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb.dbo.#TempTable1’) IS NOT NULL
DROP TABLE #TempTable1

CREATE TABLE #TempTable1 (VALUE VARCHAR(50))
ALTER TABLE #TempTable1 ADD VALUE1 VARCHAR(50)
[/sql]
Executes Successfully!!

[sql]
DECLARE @TableVariable1 TABLE (VALUE VARCHAR(50))
ALTER TABLE @TableVariable1 ADD VALUE1 VARCHAR(50)
[/sql]
Gives Error!!

Temporary Tables can be truncated where as table variables cannot.

Scope or Lifespan Differences

Scope of the temporary table is the session in which it is created.
Table variables scope is the batch of execution similar to other variables.
[sql]
CREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100));
SELECT * FROM #TableVaraiabe
GO
SELECT * FROM #TableVaraiabe
[/sql]
Executes Successfully!!

[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100));
SELECT * FROM @TableVaraiabe
GO
SELECT * FROM @TableVaraiabe
[/sql]
The statement after go fails as the table variables scope ends with the first batch.
Error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@TableVaraiabe”.

In nested procedures temporary tables are visible to the procedures called where as table variables are not.
[sql]
CREATE TABLE #Values1 (ID INT,VALUE VARCHAR(100))

INSERT INTO #Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

EXEC sp_executesql @statement = N’SELECT * FROM #Values1′
[/sql]
Executes Successfully!!
[sql]
DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

EXEC sp_executesql @statement = N’SELECT * FROM @Values1′
[/sql]
Fails as the table variable @Values1 is not visible to sp_executesql Procedure.
Error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@Values1”.

Indexing and Statistics

Indexes and Statistics can be created on temporary table, where as its not possible on table variables.

Pre-Compiling Procedures

Procedures that use table variables can be pre-compliled where are those that use Temporary tables cannot be pre-compiled.

Creation Differences:

Table variables are created in the same way similar to creating other variables.
[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
[/sql]

Temporary tables are created in the same way similar to creating permanent tables.
[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
[/sql]

Temporary tables can be created using SELECT * INTO clause where as its not possible for Table variables.
[sql]
SELECT * INTO #TempTable FROM (
SELECT 1 AS I) AS A
[/sql]
Executes Successfully!!

[sql]
SELECT * INTO @TempTable FROM (
SELECT 1 AS I) AS A
[/sql]
Fails with the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘@TempTable’.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.

Categories: SQLServerPedia Syndication, Table Variables, Temporary Tables
Tags:

Leave a Reply

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