No one wants to use more keystrokes than they have to when they write a chunk of T-SQL code. To help with minimizing the number of characters a T-SQL developer needs to type the Microsoft team introduced three new shortcuts operators when they release SQL Server 2008. These shorts cuts are the String Concatenation, Add EQUALS, and the Subtract EQUALS operators. In this article I will be describe the functionality of these operators and showing a number of examples of how they work.

Syntax for these new operators

The syntax for the String Concatenation, Add EQUALS, and the Subtract EQUALS operators follow similar syntax found in other languages, like C, and C++. The String Concatenation and the Add EQUAL operators both have the same syntax which can be found in Figure 1.

Expression += Expression

Figure 1: Syntax for the String Concatenation and the Add EQUALS

By reviewing the syntax found in Figure 1 you can see the String Concatenation and Add EQUALS and consist of two different expressions with the “+=” operator between them. This is where the similarity of these two operators ends.

The String Concatenation operator on the other hand is used to concatenate string values together. The expressions in the String Concatenation operator can be any valid character data type. The resulting concatenated value returned by the String Concatenation operation will contain the data type of the expression with the highest precedence. Just like the Add EQUALS the first expression (left hand) must be variable, and is the variable that will contain the final result of the concatenation operation.

The Add EQUALS operator requires each expression to be of a numeric data type, other than BIT. This operator takes the value of each expression and adds them together, returning a numeric value from the addition operation where the data type of the result has a data type the same as the expression with the highest precedence. The expression on the left of the += operator must be a variable. The variable on the left is set to the results of the Add EQUALS operation.

The Subtract EQUALS operation syntax can be found in Figure 2.

Expression -= Expression

Figure 2: Syntax for the Subtract EQUALS operator.

Just like the Add Equals and the String Concatenate operators the Subtract EQUALS operation has two expressions. Both expressions need to be a numeric type other than BIT. Like the Add EQUALS operator, the Subtract EQUALS operator takes the value of each expression and subtracts the second one from the first one and then returns a numeric value where the data type has a data type with the highest precedence. Just like the prior two operators the Subtract EQUALS operator requires a variable for the expression on the left side of the Subtract EQUALS operator, and it is this variable that get the resulting value of the Subtract EQUALS operation.

Examples of using the += operator to concatenate strings

Most of us know how to use the + operator to concatenate strings. Well now you can use the += (String Concatenation) operator shortcut to concatenate two strings. For a simple example of how this new concatenation shortcut works, review the code in Listing 1.

-- Concatenate with Shortcut
DECLARE @String varchar(100);
SET @String = 'Greg';
SET @String += ' Larsen';
SELECT @String;
GO
-- Concatenate without Shortcut
DECLARE @String varchar(100);
SET @String = 'Greg';
SET @String = @String + ' Larsen'
SELECT @String;
GO

Listing 1: Simple use of the String Concatenate Operator

The code in Listing 1 has two different examples of how to concatenate two strings together. The first batch of statements uses the String Concatenation operator shortcut, whereas the second batch uses the “+” operator to concatenate two strings. As you can see, in the first batch I have only specified the @String variable once. It was specified only on the left hand-side of the String Concatenate (+=) operator. Whereas in the second batch I specified the @String variable on both sides of the equal (=) operator. These two batches perform the exact same concatenation operation. By using the String Concatenate operator you can see there was less keystrokes needed to write the code that concatenate my first name with my last name.

The String Concatenate operator requires that a variable be specified on the left side of the operator. Because of this, the code in Listing 2 is invalid.

SELECT 'Greg' += ' Larsen';

Listing 2: In appropriate use of the String Concatenate operator

When I run the code in Listing 2 I get a syntax error.

Examples of use += and -= to increase or decrease a numeric value

Adding and subtracting to a variable is one of those common things you need to do when you are writing code. With the addition of the Add EQUALS and the Subtract EQUALS operators it just became easier to implement. Suppose you need to loop through some code 10 times. A common practice for doing this is to write a WHILE loop that is controlled with an integer variable, where the integer value 1 is added to or subtracted from the WHILE loop control variable each time through the loop. By using the Add EQUALS or Subtract EQUALS operator you can now code your setting of a loop variable with fewer keystrokes. Let’s see how to use the Add Equals to change the value of a variable each time through a WHILE loop by reviewing the code in Listing 3.

DECLARE @I tinyint = 1;
WHILE @I < 10
BEGIN
SELECT 'The value of @I is ' + CAST(@I as char(1));
SET @I += 1;
END

Listing 3: Setting the value of a variable controlling the WHILE loop with the Add EQUALS operator

By looking at the code in Listing 3 your can see that I incremented the value of the variable @I by 1 each time through the WHILE loop by using the Add EQUALS operator. The code in Listing 4 is equivalent to the code in Listing 3 which doesn’t use the Add EQUALS operator to increment the variable.

DECLARE @I tinyint = 1;
WHILE @I < 10
BEGIN
SELECT 'The value of @I is ' + CAST(@I as char(1));
SET @I = @I + 1;
END

Listing 4: Another way to increment the @I variable without using the Add EQUALS operator.

By comparing the code in Listing 3 with the code in Listing 4 you can see I used fewer keystroked in Listing 3 to increment the variable @I. This is what shortcuts are all about, to save you keystrokes and time when you are writing your code.

The Subtract EQUALS operator can be used just like the Add EQUALS operator. Listing 5 contains some similar code to Listing 3 but decrements the WHILE loop control variable by using the Subtract EQUALS operator.

DECLARE @I tinyint = 10;
WHILE @I > 0
BEGIN
SELECT 'The value of @I is ' + CAST(@I as char(2));
SET @I -= 1;
END

Listing 5: Example of using the Subtract EQUALS operator

In all of my examples so far I’ve used a constant with Add EQUALS and Subtract EQUALS operators to increase or decrease the variable being set. You can also use variables or formulas on the right side of these two operators, as I have done in Listing 6.

DECLARE @I int = 0;
DECLARE @X int = 10;
DECLARE @Y int = 2;
SET @I += @X * @Y
SELECT @I

Listing 6: Example of using a formula with the Add EQUALS operator

In Listing 6 I multiplied the variables @X and @Y together and then added that to the variable @I. When I run this code the SELECT statement returns the value 20.

Now that we have the Add EQUALS and the Subtract EQUALS available in SQL Server you can minimize the number of key strokes needed to increment, or decrement a numeric data type.

Summary

Having shortcuts provides a single or double keystroke option to accomplishing a task. If you are running SQL Server version 2008 and above then you should consider using the String Concatenation, Add EQUALS and/or the Subtract EQUALS operator to minimize the number of characters you need to write your T-SQL code. Next time you need to concatenate two strings, increment or decrement a variable you should consider using the += and -+ operators.

Question and Answer

In this section you can review how well you have understood using the += and -= operators by answering the following questions.

Question 1:

The += operator is useful for which of the following operations (select all that apply)?

• Incrementing a string variable by a value
• Incrementing a numeric variable by a value
• Concatenating two character data type values
• Concatenating two numeric data type values

Question 2:

The Subtract EQUALS operator performs what functions (select all that apply)?

• Decrements a character variable by a value
• Decrements a numeric variable by a value
• Decrements a character variable by the results of a formula
• Decrements a numeric variable by the results of a formula

Question 3:

What is the outcome of running the following code?

DECLARE @String1 varchar(100) = 'Test ';
DECLARE @String2 varchar(100) = 'Questions';
SELECT @String1 += @String2;
SELECT @String1;

• Test
• Questions
• Test Questions
• Syntax error

Answers:

Question 1:

The correct answer is b and c. The += can be used to increment numeric values and concatenate string values.

Question 2:

The correct answer is b and d. The Subtract EQUAL (-=) allows you to decrement a numeric variable by a number or to decrement a numeric variable by a formula that produces a numeric value.

Question 3:

The correct answer is c. The += (String Concatenation) operator is to concatenate two string variables. Therefore, a and b are incorrect because those are not concatenated values. d is incorrect because this code does not produce an error.