does it drop temp table (created in transaction) if rollback transaction in sql server?

Question

In the transaction, I created some temp tables and inserted some data. if everything goes ok, the temp tables will be dropped and then the transaction will be committed. if there is exception, the connection will be close.

My question is: Do I have to rollback the transaction before close the connection? or, as long as the connection is closed, it is ok?

If I rollback, I assume the temp tables created in transaction will be dropped (rollback) too, correct?

thanks

Answer 1

A temp table will be dropped when it's out of scope.

However losing a connection while having a transaction open creates an orphaned transaction, and any locks it holds will remain.

Using SET XACT_ABORT should rollback any transaction as soon as a run-time error is encountered.

http://msdn.microsoft.com/en-us/library/ms188792.aspx