Jun 17 2011
In part 1 of this 2 part series on rollback gotchas we looked at the physical operations. In part 2 we are going to look at an example of when the rollback statement does not appear to work at all and take a look into the reason why this is.
One of the big debates that you find on any SQL Server forum since SQL Server 2000 was released is whether to use temporary tables (#Tables) or table variables. There are many views on which is better which in turn has led to many myths being circulated, I will not comment on these myths just yet. For now, lets take a look at how the rollback statement affects both of these types of objects:
CREATE TABLE #temptable ( id INT );
DECLARE @tableVariable TABLE ( id INT PRIMARY KEY );
INSERT INTO #temptable
INSERT INTO @tableVariable
DELETE FROM #temptable where id = 1;
DELETE FROM @tableVariable where id = 1;
SELECT * FROM #temptable;
SELECT * FROM @tableVariable;
DROP TABLE #temptable;
If you ran the code above you will have seen that the data in the table variable was not rolled back, however the data in the temp table was. At this point my psychic powers predict 1 of 3 responses from you:
- What the ****?!?
- Oh crap, I need to revisit some code.
- I knew that and see where he’s going with this.
If you fell into group 3 congratulations, if not don’t despair. The extract below has been taken from the following link from BOL http://msdn.microsoft.com/en-us/library/ms175010.aspx
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
The key words to take away from this quote are “duration of an update” and “logging resources”. If BOL states a transaction will only last the duration of an update on the object, it stands to reason that rollbacks will not be possible. Operations on table variables are logged significantly less than temporary and permanent tables, you may find various resources on the internet stating that table variables are not logged at all, this is simply not true!
Earlier I mentioned that the debate between table variables and temporary tables had lef to the rise of many myths. Gail Shaw has written an excellent blog dispelling several myths regarding table variables which is well worth a read -http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/. It’s so good in fact that I have dropped the example I had planned to include in this blog entry on the varying levels of logging between table variables, temporary tables and permanent tables!
Is the fact table variables do not rollback an issue? The answer has to be “it depends”, used in the correct way this can be a fantastic benefit as you can use table variables to sit outside transactions and write this data away should the transaction roll back for such things as custom error messages. The other side of this is that you could potentially suffer logical data integrity issues should table variables have been used in the wrong way.
As always, plan your solutions carefully and test, test, test!