SQL: SET NOCOUNT ON in SQL Server Triggers

I’ve done a lot of Microsoft certification exams over the years. Most of those have been SQL Server exams. The quality of the questions in those exams though, varies enormously.

One of the exams that really drove me crazy a while back was one of the intro-level ones. The problem was with the number of errors in the exam. When the question-writer gives you five substantial blocks of T-SQL, and asks which one is correct, yet they are all actually wrong in some way, that makes it a very, very tough question to answer. I find these types of exams very tiring because I’m endlessly trying to second guess what the question writer actually meant to ask. Some exams have a large number of these.

A more subtle problem though, is when there are questions that aren’t really wrong, but it’s immediately obvious that the question-writer just didn’t “get” something. Here’s an example:

One of the very first things that we teach people about writing DML triggers (ie: INSERT, DELETE, UPDATE triggers) is about the use of SET NOCOUNT ON. Yet on T-SQL exams that involve these triggers, I’ve lost count of how many triggers I’ve seen that don’t include this.

What that tells me is that the question-writer has read about triggers and has a theoretical understanding of them, but hasn’t really spent time implementing them.

If the question-writer was used to writing triggers, they’d put this in almost every time, without even thinking. Let me explain why.

We’ll start with a simple table:

I’ll then update one row:

Note that it shows 1 row affected as expected. If I execute that UPDATE statement from an application, and I expect it to update one row, the code will often check that one row was affected.

But now let’s add an auditing table and a trigger:

After doing this, note what happens with our previous UPDATE:

This will not be what the application is expecting. The two sets of affected row counts might well be a problem. Worse, imagine if the second one had two rows affected in the background. The app might total the rows affected, or it might use the last value. Either way it’ll be wrong.

Now let’s alter the trigger to add SET NOCOUNT ON:

When we then do the update, we’re back to what we expected:

So, while there are exceptions to any rule, in general when writing DML triggers, you should have SET NOCOUNT ON in the code.

2018-10-22