Search code examples
sqlsql-servertemp-tablestable-variable

Can someone give me a real time example with the below temp table and tablevariable example that I found in stackexchange

Difference between temp table and table variable as stated:

Operations on @table_variables are carried out as system transactions, independent of any outer user transaction, whereas the equivalent #temp table operations would be carried out as part of the user transaction itself. For this reason a ROLLBACKcommand will affect a #temp table but leave the @table_variable untouched.

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Can anyone tell me when will this above mentioned application/scenario will be used in real time? Can anyone give a real time example. Thanks

P.S. - Referred from this link: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Solution

  • In a real example, just consider you have a transaction and for somehow your transaction rollbacks but you still want to log and see why the transaction is failed and try to keep the log until you execute the transaction without any rollbacks.

    In this example, you can capture all your logs information into a table variable.