Error Handling in T-SQL « SQL Fool

Error handling is one of those things in SQL Server that just doesn’t get the attention it deserves. Even a properly constructed stored procedure can still result in error, such as primary key or unique constraint errors.

Why should you care? Consider this real-world example:

You’re a DBA monitoring a well-performing environment. You deploy a new application to production. Suddenly, performance degrades but you do not know why. You look in your error log and see a whole mess of primary key errors. Digging into your newly deployed application, you find that you are now making an extra (and unnecessary) insert to the database, which is resulting in error and causing your performance issues.

This is just one example of many. Fortunately, SQL 2005 has really simplified the error handling process with features such as the Try/Catch block.

TSQL Error Messages

TSQL Error Codes – 0 to 199

snaps & snippets

Optimistic concurrency (with essential corrections)

Optimistic concurrency rests on somewhat wobbly foundations – the presumption that no user will ever be blocked by another user. Which is usually true for single-user solutions while multi-user solutions require a slight correction to this logic. Put another way: with optimistic concurrency we assume that a particular row will only be modified by one user at any given time, and being fully optimistic we do nothing to prevent different users from trying to modify the same row.

(I must admit that the subject of this post has been discussed pretty much everywhere up to the point of almost being disgusting, but I’ve had this sample laying around for a while and now I’ve decided on moving it somewhere else rather than permanently removing it from my disk.)

Of course not doing anything to prevent what seems quite probable is not optimism – it’s stupidity. On the other hand doing too much (like introducing a sophisticated row-locking mechanism) is also hardly a good idea, especially since elementary cuncurrency management mechanisms already are part of the platform. Basically, what the client application really needs to know before attempting a data modification is: