Inside Microsoft® SQL Server™ 2005

Conclusion

Exception handling has come a long way since SQL Server 2000. It’s now structured, elegant, and traps all errors except for the most severe ones. It also provides a lot of useful information via the exception-related functions. You can now have three different transaction states: no transaction open, open and committable, open and uncommittable (failed). Remember that the failed state, which is new in SQL Server 2005, allows you to keep a transaction open, holding all locks, while you’re investigating data. You then must roll back the transaction before making any change.