Design for implicit and explicit transactions

May include but is not limited to: nested transactions, savepoints, TRY/CATCH

A transaction is an atomic, consistent, isolated, durable operation – this is best known as the acronym ACID. There are two kinds of transactions: explicit – this is what you refer as a transaction. It starts with the BEGIN TRANSACTION clause, and ends with a COMMIT / ROLL BACK – what you prefer. There are also implicit transactions. These are enabled when you connect SQL Server with implicit transactions enabled. By this, some clauses automatically start transactions (CRUD operations, DDL statements, etc.) which you can commit or roll back immediately.

To enable implicit transactions, you call the SET IMPLICIT_TRANSCATIONS ON statement. Remember – a transaction either succeeds or fails, so this mode holds some surprises to you.

Nothing can hold you back from nesting transactions. There are some rules, although. The first rule is that the outmost transaction always wins. So if you commit every nested transaction, but roll back the outmost one, every inner transaction is rolled back. The reverse is true.

Continue reading


Implement error handling

May include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @ERROR.

Unfortunately, we cannot write bullet-proof code in any language, T-SQL included. Fortunately, most languages provide ways to deal with errors within your applications, T-SQL included (at least, Microsoft’s implementation).

The easiest but most limited way to deal with errors is to query the @@ERROR global variable. If any error occurs, @@ERRROR returns its number. If not, it returns 0. The severe limitation is that it resets its value after each successful statement. And a simple SELECT @@ERROR counts as a successfully executed statement, so you won’t be able to pass the error information any further.

But I went too far. Let’s see what builds up an error in SQL Server:

  • Error number: a unique number which identifies the current error message. SQL Server reserves the error numbers 0-50000, so you can build custom errors from number 50001.
  • Severity level: from 0 to 25, indicates the severity of what has happened.
  • Error message: a textual representation of the error.

Continue reading