SQL SERVER 2008 has introduced a new concept of Compound Assignment Operators. Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators are operated where variables are operated upon and assigned in the same line. Compound-assignment operators provide a shorter syntax for assigning the result of an arithmetic or bitwise operator. They perform the operation on the two operands before assigning the result to the first operand.
Let us see the following operation without using Compound Assignment Operators.
DECLARE @myVar INT SET @myVar = 10 SET @myVar = @myVar * 5 SELECT @myVar AS MyResult GO
Above operation can be done using Compound Assignment Operators as demonstrated in following script.
DECLARE @myVar INT SET @myVar = 10 SET @myVar *= 5 SELECT @myVar AS MyResult GO
Here is the table of all the compound operators in SQL Server.
| Operator | Action |
|---|---|
| += | Adds some amount to the original value and sets the original value to the result. |
| -= | Subtracts some amount from the original value and sets the original value to the result. |
| *= | Multiplies by an amount and sets the original value to the result. |
| /= | Divides by an amount and sets the original value to the result. |
| %= | Divides by an amount and sets the original value to the modulo. |
| Performs a bitwise AND and sets the original value to the result. | |
| ^= | Performs a bitwise exclusive OR and sets the original value to the result. |
| |= | Performs a bitwise OR and sets the original value to the result. |
Reference: Pinal Dave (https://blog.sqlauthority.com)
