Differences Between Table Variables And Temporary Tables
Posted on : 19-01-2012 | By : Devi Prasad | In : SQLServerPedia Syndication, Table Variables, Temporary Tables
0

Transactional Differences
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.
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
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
CREATE FUNCTION dbo.TestFn( @i int) RETURNS INT AS BEGIN DECLARE @A TABLE ( i INT) RETURN 1 END
Executes succesfully.
CREATE FUNCTION dbo.TestFn( @i int) RETURNS INT AS BEGIN CREATE TABLE #A( I INT) RETURN 1 END
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
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
Executes Successfully!!
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
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
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100)) DROP TABLE @TableVaraiabe
Fails with error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘@TableVaraiabe’.
CREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100)) DROP TABLE #TableVaraiabe
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.
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)
Executes Successfully!!
DECLARE @TableVariable1 TABLE (VALUE VARCHAR(50)) ALTER TABLE @TableVariable1 ADD VALUE1 VARCHAR(50)
Gives Error!!
Temporary Tables can be truncated where as table variables cannot.
Scope or Lifespan Differences
Table variables scope is the batch of execution similar to other variables.
CREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100)); SELECT * FROM #TableVaraiabe GO SELECT * FROM #TableVaraiabe
Executes Successfully!!
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100)); SELECT * FROM @TableVaraiabe GO SELECT * FROM @TableVaraiabe
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.
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'
Executes Successfully!!
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'
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
Pre-Compiling Procedures
Creation Differences:
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
Temporary tables are created in the same way similar to creating permanent tables.
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
Temporary tables can be created using SELECT * INTO clause where as its not possible for Table variables.
SELECT * INTO #TempTable FROM ( SELECT 1 AS I) AS A
Executes Successfully!!
SELECT * INTO @TempTable FROM ( SELECT 1 AS I) AS A
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’.
Reference : Devi Prasad (sqlserverlearner.com)
Do you like my blog?
If you liked reading this blog, please help spread the word by sharing this blog with your friends.
Need Help On SQL Server?
Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.

Tags: can temporary table be used in a function, Cannot access temporary tables from within a function, Creation Differences Table Variables And Temporary Tables, DDL Statements on table variables, DDL Statements on temporary tables, Differences Between Table Variable And Temp Table, Differences Between Table Variables And Temp Tables, dropping table variables, Invalid object name temporary table, Msg 102 Level 15 State 1 Line 2 Incorrect syntax near, Msg 102 Level 15 State 1 Line Incorrect syntax near, Msg 1087 Level 15 State 2 Line Must declare the table variable, Msg 137 Level 16 State 1 Line, Msg 137 Level 16 State 1 Line Must declare the scalar variable, Msg 137 Must declare the scalar variable, Msg 156 Level 15 State 1 Line Incorrect syntax near the keyword 'AS'., Msg 208 Level 16 State 0 Line 37, Msg 2772 Level 16 State 1 Procedure Line, performance of temp table over table variable, Scope or Lifespan Differences between temporary table and table variable, should a procedure have table variable or temp table, should a procedure have table variable or temporary table, table variables not bound to transactions, Table variables should be referenced with an alias when used in joins etc, Table variables temporary tables Transactions, Table variables temporary tables usage, temp table vs table variable, temporary table vs table variable, Temporary Tables can be truncated where as table variables cannot, temporary tables directly bound to transactions, Usage Differences temp table table variable, use table variable in a function, what to choose temporary table or table variable