{"id":119,"date":"2012-01-19T11:42:37","date_gmt":"2012-01-19T11:42:37","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/01\/19\/differences-between-table-variables-and-temporary-tables\/"},"modified":"2012-01-19T11:42:37","modified_gmt":"2012-01-19T11:42:37","slug":"differences-between-table-variables-and-temporary-tables","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/01\/19\/differences-between-table-variables-and-temporary-tables\/","title":{"rendered":"Differences Between Table Variables And Temporary Tables"},"content":{"rendered":"

Transactional Differences<\/strong><\/p>\n

\nTable 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.<\/p>\n

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.<\/em><\/p>\n

[sql]
\nSET NOCOUNT ON
\nIF OBJECT_ID(‘tempdb.dbo.#TempTable1’) IS NOT NULL
\nDROP TABLE #TempTable1<\/p>\n

DECLARE @TableVariable1 TABLE (VALUE VARCHAR(50))
\nCREATE TABLE #TempTable1 (VALUE VARCHAR(50))<\/p>\n

INSERT INTO @TableVariable1(VALUE)
\nSELECT ‘INSERTED AFTER CREATION’<\/p>\n

INSERT INTO #TempTable1(VALUE)
\nSELECT ‘INSERTED AFTER CREATION’<\/p>\n

BEGIN TRAN<\/p>\n

INSERT INTO @TableVariable1(VALUE)
\nSELECT ‘INSERTED IN TRANSACTION’<\/p>\n

INSERT INTO #TempTable1(VALUE)
\nSELECT ‘INSERTED IN TRANSACTION’<\/p>\n

DECLARE @TableVariable2 TABLE (VALUE VARCHAR(50))<\/p>\n

CREATE TABLE #TempTable2 (VALUE VARCHAR(50))<\/p>\n

INSERT INTO @TableVariable2(VALUE)
\nSELECT ‘INSERTED AFTER CREATION’<\/p>\n

INSERT INTO #TempTable2(VALUE)
\nSELECT ‘INSERTED AFTER CREATION’<\/p>\n

DROP TABLE #TempTable1<\/p>\n

ROLLBACK TRAN<\/p>\n

SELECT ‘@TableVariable1’ AS TableName,* FROM @TableVariable1
\nSELECT ‘#TempTable1’ AS TableName,* FROM #TempTable1
\nSELECT ‘@TableVariable2’ AS TableName,* FROM @TableVariable2
\nSELECT ‘#TempTable2’ AS TableName,* FROM #TempTable2
\n[\/sql]<\/p>\n

Output:<\/strong><\/p>\n

\nTableName       VALUE\n--------------- --------------------------------------------------\n@TableVariable1 INSERTED AFTER CREATION\n@TableVariable1 INSERTED IN TRANSACTION\n\nTableName   VALUE\n----------- --------------------------------------------------\n#TempTable1 INSERTED AFTER CREATION\n\nTableName       VALUE\n--------------- --------------------------------------------------\n@TableVariable2 INSERTED AFTER CREATION\n\n\nMsg 208, Level 16, State 0, Line 37\nInvalid object name '#TempTable2'.\n<\/font>\n<\/pre>\n
From the above output we can come up with the following notes:<\/strong><\/p>\n
    \n
  • Data in the temporary tables is directly bound to transactions
    \n   i.e. If the transaction is rolledback then the data inserted\/Modified in the transaction is lost<\/li>\n
  • Data in the table variables is not bound to transactions.
    \n   i.e. If the transaction is rolledback then the data inserted\/Modified in the transaction is not lost.<\/li>\n
  • Creation\/Deletion of temp tables is also bound to transactions.
    \n   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.<\/li>\n
  • If a Table variable is created in a transaction it exists further even if the transaction is rolled back.<\/li>\n<\/dd>\n
    Usage Differences<\/strong><\/p>\n
    \nTemporary Tables cannot be created within a function where as table variables can be created within a function.<\/em>
    \n[sql]
    \nCREATE FUNCTION dbo.TestFn( @i int)
    \nRETURNS INT
    \nAS
    \nBEGIN
    \nDECLARE @A TABLE ( i INT)<\/p>\n
    RETURN 1
    \nEND
    \n[\/sql]
    \nExecutes succesfully.<\/p>\n
    [sql]
    \nCREATE FUNCTION dbo.TestFn( @i int)
    \nRETURNS INT
    \nAS
    \nBEGIN
    \nCREATE TABLE #A( I INT)<\/p>\n
    RETURN 1
    \nEND
    \n[\/sql]
    \nFails with an error:
    \n
    \nMsg 2772, Level 16, State 1, Procedure TestFn, Line 5
    \nCannot access temporary tables from within a function.
    \n<\/font><\/p>\n
    Table variables should be referenced with an alias when used in joins etc where as alias is not mandatory for Table Variables<\/em><\/p>\n
    [sql]
    \nCREATE TABLE #Values1  (ID INT,VALUE VARCHAR(100))
    \nCREATE TABLE #Values2  (ID INT,VALUE VARCHAR(100))<\/p>\n
    INSERT INTO #Values1 (ID, VALUE)
    \nSELECT 1,’A’
    \nUNION
    \nSELECT 2,’B’<\/p>\n
    INSERT INTO #Values2 (ID, VALUE)
    \nSELECT 1,’A’
    \nUNION
    \nSELECT 3,’C’<\/p>\n
    SELECT * FROM #Values1 JOIN #Values2
    \nON #Values1.ID = #Values2.ID
    \n[\/sql]
    \nExecutes Successfully!!<\/p>\n
    [sql]
    \nDECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))
    \nDECLARE @Values2 TABLE (ID INT,VALUE VARCHAR(100))<\/p>\n
    INSERT INTO @Values1 (ID, VALUE)
    \nSELECT 1,’A’
    \nUNION
    \nSELECT 2,’B’<\/p>\n
    INSERT INTO @Values2 (ID, VALUE)
    \nSELECT 1,’A’
    \nUNION
    \nSELECT 3,’C’<\/p>\n
    SELECT * FROM @Values1 JOIN @Values2
    \nON @Values1.ID = @Values2.ID
    \n[\/sql]
    \nFails with an error:
    \n
    \nMsg 137, Level 16, State 1, Line 15
    \nMust declare the scalar variable “@Values1”.
    \nMsg 137, Level 16, State 1, Line 15
    \nMust declare the scalar variable “@Values2”.
    \n<\/font><\/p>\n
    Temporary tables can be dropped explicitly where as dropping table variables is not possible<\/em>
    \n[sql]
    \nDECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
    \nDROP TABLE @TableVaraiabe
    \n[\/sql]
    \nFails with error:
    \n
    \nMsg 102, Level 15, State 1, Line 2
    \nIncorrect syntax near ‘@TableVaraiabe’.
    \n<\/font>
    \n[sql]
    \nCREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100))
    \nDROP TABLE #TableVaraiabe
    \n[\/sql]
    \nExecutes Successfully!!<\/p>\n
    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.<\/em>
    \n[sql]
    \nSET NOCOUNT ON
    \nIF OBJECT_ID(‘tempdb.dbo.#TempTable1’) IS NOT NULL
    \nDROP TABLE #TempTable1<\/p>\n
    CREATE TABLE #TempTable1 (VALUE VARCHAR(50))
    \nALTER TABLE #TempTable1 ADD VALUE1 VARCHAR(50)
    \n[\/sql]
    \nExecutes Successfully!!<\/p>\n
    [sql]
    \nDECLARE @TableVariable1 TABLE (VALUE VARCHAR(50))
    \nALTER TABLE @TableVariable1 ADD VALUE1 VARCHAR(50)
    \n[\/sql]
    \nGives Error!!<\/p>\n
    Temporary Tables can be truncated where as table variables cannot.<\/em><\/p>\n<\/dd>\n
    Scope or Lifespan Differences<\/strong><\/p>\n
    \nScope of the temporary table is the session in which it is created.
    \nTable variables scope is the batch of execution similar to other variables.<\/em>
    \n[sql]
    \nCREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100));
    \nSELECT * FROM #TableVaraiabe
    \nGO
    \nSELECT * FROM #TableVaraiabe
    \n[\/sql]
    \nExecutes Successfully!!<\/p>\n
    [sql]
    \nDECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100));
    \nSELECT * FROM @TableVaraiabe
    \nGO
    \nSELECT * FROM @TableVaraiabe
    \n[\/sql]
    \nThe statement after go fails as the table variables scope ends with the first batch.
    \nError:
    \n
    \nMsg 1087, Level 15, State 2, Line 1
    \nMust declare the table variable “@TableVaraiabe”.
    \n<\/font>
    \nIn nested procedures temporary tables are visible to the procedures called where as table variables are not.<\/em>
    \n[sql]
    \nCREATE TABLE #Values1  (ID INT,VALUE VARCHAR(100))<\/p>\n
    INSERT INTO #Values1 (ID, VALUE)
    \nSELECT 1,’A’
    \nUNION
    \nSELECT 2,’B’<\/p>\n
    EXEC sp_executesql @statement = N’SELECT * FROM #Values1′
    \n[\/sql]
    \nExecutes Successfully!!
    \n[sql]
    \nDECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))<\/p>\n
    INSERT INTO @Values1 (ID, VALUE)
    \nSELECT 1,’A’
    \nUNION
    \nSELECT 2,’B’<\/p>\n
    EXEC sp_executesql @statement = N’SELECT * FROM @Values1′
    \n[\/sql]
    \nFails as the table variable @Values1 is not visible to sp_executesql Procedure.
    \nError:
    \n
    \nMsg 1087, Level 15, State 2, Line 1
    \nMust declare the table variable “@Values1”.
    \n<\/font>\n<\/dd>\n
    Indexing and Statistics<\/strong><\/p>\n
    \nIndexes and Statistics can be created on temporary table, where as its not possible on table variables.<\/em>\n<\/dd>\n
    Pre-Compiling Procedures<\/strong><\/p>\n
    \nProcedures that use table variables can be pre-compliled where are those that use Temporary tables cannot be pre-compiled.<\/em>\n<\/dd>\n
    Creation Differences:<\/strong><\/p>\n
    \nTable variables are created in the same way similar to creating other variables.
    \n[sql]
    \nDECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
    \n[\/sql]<\/p>\n
    Temporary tables are created in the same way similar to creating permanent tables.
    \n[sql]
    \nDECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
    \n[\/sql]<\/p>\n
    Temporary tables can be created using SELECT * INTO clause where as its not possible for Table variables.<\/em>
    \n[sql]
    \nSELECT * INTO #TempTable FROM (
    \n      SELECT 1 AS I) AS A
    \n[\/sql]
    \nExecutes Successfully!!<\/p>\n
    [sql]
    \nSELECT * INTO @TempTable FROM (
    \n      SELECT 1 AS I) AS A
    \n[\/sql]
    \nFails with the error:
    \n
    \nMsg 102, Level 15, State 1, Line 1
    \nIncorrect syntax near ‘@TempTable’.
    \nMsg 156, Level 15, State 1, Line 2
    \nIncorrect syntax near the keyword ‘AS’.
    \n<\/font>\n<\/dd>\n","protected":false},"excerpt":{"rendered":"
    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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,758,759],"tags":[760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792],"class_list":["post-119","post","type-post","status-publish","format-standard","hentry","category-sqlserverpedia-syndication","category-table-variables","category-temporary-tables","tag-can-temporary-table-be-used-in-a-function","tag-cannot-access-temporary-tables-from-within-a-function","tag-creation-differences-table-variables-and-temporary-tables","tag-ddl-statements-on-table-variables","tag-ddl-statements-on-temporary-tables","tag-differences-between-table-variable-and-temp-table","tag-differences-between-table-variables-and-temp-tables","tag-dropping-table-variables","tag-invalid-object-name-temporary-table","tag-msg-102-level-15-state-1-line-2-incorrect-syntax-near","tag-msg-102-level-15-state-1-line-incorrect-syntax-near","tag-msg-1087-level-15-state-2-line-must-declare-the-table-variable","tag-msg-137-level-16-state-1-line","tag-msg-137-level-16-state-1-line-must-declare-the-scalar-variable","tag-msg-137-must-declare-the-scalar-variable","tag-msg-156-level-15-state-1-line-incorrect-syntax-near-the-keyword-as","tag-msg-208-level-16-state-0-line-37","tag-msg-2772-level-16-state-1-procedure-line","tag-performance-of-temp-table-over-table-variable","tag-scope-or-lifespan-differences-between-temporary-table-and-table-variable","tag-should-a-procedure-have-table-variable-or-temp-table","tag-should-a-procedure-have-table-variable-or-temporary-table","tag-table-variables-not-bound-to-transactions","tag-table-variables-should-be-referenced-with-an-alias-when-used-in-joins-etc","tag-table-variables-temporary-tables-transactions","tag-table-variables-temporary-tables-usage","tag-temp-table-vs-table-variable","tag-temporary-table-vs-table-variable","tag-temporary-tables-can-be-truncated-where-as-table-variables-cannot","tag-temporary-tables-directly-bound-to-transactions","tag-usage-differences-temp-table-table-variable","tag-use-table-variable-in-a-function","tag-what-to-choose-temporary-table-or-table-variable"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/119","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=119"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/119\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=119"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=119"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=119"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}