I will be taking a break from my series on “Underappreciated features of Microsoft SQL Server” to address a problem that I see frequently during the code reviews that I conduct in my capacity as a technical lead in the project I work for.

SQL Server 2008 introduced a great new feature – support for compound operators. This feature makes T-SQL programming a breeze for anyone who is a typical “hybrid” database developer – the kind that switches often between let’s say C# programming with a little bit of T-SQL here and there.

Compound Operators

As a quick introduction, compound operators execute some operation and set an original value to the result of the operation. This is what we commonly know see as “i++” to run a loop in code – the “++” operator takes the original value of the variable “i” and increments it by 1. The examples for compound operators provided on Books-On-Line provide great learning material – you can get these from here.

The most common error

I have often seen a very common mistake with respect to compound operators during code reviews – the sequence! Sequence is important here as well like most things in life, and the slightest typo error will cause SQL Server to behave differently. Let’s see this a practical example.

DECLARE @iTest INT
SET @iTest = 1

--Wrong! This works like an assignment.
--Effectively, we are assigning a value of +2 to @iTest
SELECT @iTest =+ 2
SELECT @iTest

--Right! This will use Compound Operators
SELECT @iTest += 2
SELECT @iTest

--For those who are curious, this works! (performs simple addition)
SELECT @iTest ++ 2

-- But, none of the following works
SELECT @iTest ++= 2
SELECT @iTest =++ 2

Simply run the above code in SSMS against your instance of SQL Server 2008 to see the difference. The first set of statements will cause a normal garden-variety assignment operation – the second will perform the compound operation, which we originally intended it to do. Do note that SQL Server will NOT throw an error message, because there is nothing wrong with the statement! It’s a simple, logical error, which might take days to troubleshoot if not looked at carefully.

As you would probably have guessed by now, this is only applicable to the compound operators += and –= (all others will cause SQL Server to error out). These are some of the most common operations that we use in code, and our habit of typing fast causes this error to occur very frequently. So the next time you use compound operators, be extra careful. For code reviewers, update your checklists to explicitly check for these errors.

Until we meet next time,

Be courteous. Drive responsibly.