2

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

Community
  • 1
  • 1
Suj
  • 416
  • 1
  • 5
  • 11

2 Answers2

0

What is going on is that the developer is demonstrating the use of a temporary table (which for most intents is the same as a regular table) and a variable that is a table. When a rollback occurs any changes made to the temporary table is undone (the table is in the same state as before transaction started) but the variable is changed - its not affected by the rollback.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
0

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.

sqluser
  • 5,502
  • 7
  • 36
  • 50
  • Thank you sqluser for giving an example. Any other example will be highly appreciated. – Suj Mar 30 '15 at 15:44