More T-SQL Error Functionality in Denali / SQL 11


In my previous post I wrote about the new THROW keyword in Denali / SQL 11. Having played around a bit more with Denali, I wanted to write some additional things about THROW and it's relation to RAISERROR.

RAISERROR

First some background / overview of RAISERROR:

As I mentioned in my previous post, RAISERROR has been around since forever - and it works fairly well. One of the major drawbacks with RAISERROR - as I also wrote in my previous post; is that it cannot be used to re-throw an error we might have trapped in a structured error handling block. Or rather, this may not be that much a RAISERROR issue, as an issue that SQL Server has not previously supported the notion of re-throwing an error. Be as it may with that, there are other drawbacks with RAISERROR which I will mention later in this post.

THROW

In Denali / SQL 11 Microsoft introduces the THROW keyword, which allows us to re-throw an exception caught in an exception handling block. Some characteristics of THROW:

THROW 50000, 'Ooops', 1;

So, THROW looks fairly cool, but what are the drawbacks with RAISERROR I mentioned above? Well, for one - beginning with Denali / SQL 11 RAISERROR is being deprecated, i.e. it will eventually be removed from SQL Server. Another reason has to do with transactions and error handling.

UPDATE: According to Aaron Bertrand, in his post, it is only some very old RAISERROR syntax that is being deprecated.

XACT_ABORT

As every T-SQL programmer worth his (or her) salt should know, an exception does not roll back a transaction by default (ok, ok, it does depend on severity level to an extent - but a "normal" exception does not roll back a tran). I.e. the following code would cause two rows to be inserted in the table t1:

--first create a test table which we will use throughout the code samples
CREATE TABLE t1 (id int primary key, col1 nvarchar(15));
--now onto the 'meat'
BEGIN TRAN
INSERT INTO t1 VALUES(1, 'row1');
--emulate some error, this will indeed cause an exception to happen,
--but the processing will continue
SELECT 1 / 0
INSERT INTO t1 VALUES(2, 'row2')
COMMIT

We can indicate to SQL Server that we want "automatic" rollback of transactions when an exception happens by setting XACT_ABORT. This will cause a rollback to happen if a system exception happens. So based on the example above, no rows will be inserted when the code below executes:

SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO t1 VALUES(3, 'row3');
SELECT 1 / 0
INSERT INTO T1 VALUES(4, 'row4')
COMMIT

However, what happens if the user throws an exception using RAISERROR? In that case no rollback happens, i.e. RAISERROR does not honor the XACT_ABORT setting:

SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO t1 VALUES(5, 'row5');
--the user raises an error, but the tx will not roll back
RAISERROR('Oooops', 16, 1)
INSERT INTO t1 VALUES(6, 'row6')
COMMIT

This can catch developers out and is in my opinion a fairly severe drawback. So with the introduction of Denali / SQL 11 and the THROW keyword, Microsoft has tried to fix this by making THROW honor XACT_ABORT:

SET XACT_ABORT ON
BEGIN TRAN
INSERT INTO t1 VALUES(7, 'row7');
--the user raises an error, and the tx will roll back
THROW 50000, 'Ooops', 1
INSERT INTO t1 VALUES(8, 'row8')
COMMIT

When you run the code above, you will see that the transaction is indeed rolled back and no rows are inserted.

So developers, "go forth" and THROW exceptions in SQL Server Denali / SQL 11.

Share this Post:

Twitter | Google+ | LinkedIn

Blog Feed:

To automatically receive more posts like this, please subscribe to my RSS/Atom feed in your feed reader!