Search code examples
sql-serverstored-proceduresrollback

SQL Server rollback behaviour after incrementing a counter

If I have a stored procedure with an UPDATE that includes:

SET attendeeCount = attendeeCount + @incrementBy

and later in the transaction I rollback, is attendeecount decremented from it's current value (which may have changed), or is it just reset to it's original value before the transaction?

Thanks!

Solution

  • If the attendeeCount is an ordinary column in a table (be it normal or temporary table), its value will be reverted back to what it was before transaction has started. The only situations when the behaviour is different are:

    • column with the identity attribute. The value will be rolled back, but internal identity counter will not. The same goes for sequences.
    • columns in table variables. These are unaffected by rollbacks.