Compound Operators in SQL Server 2008

Introduction

SQL Server 2008 has introduced the new feature compound operator. Compound operators are available in other programming languages like C# etc. Compound Assignment Operators are operators where variables are operated upon and assigned on the same line.

The following Operators are supported as compound operators:


Examples

+= Operator

DECLARE @addvalue int = 53;
SET @addvalue += 20 ;
PRINT 'Add value :' + CAST(@addvalue AS VARCHAR);
--Result : Add value :73 

DECLARE @concString VARCHAR(50) = 'Jignesh';
SET @concString += ' Trivedi' ;
PRINT 'Output :' + @concString;
--Result : Output :Jignesh Trivedi

-= Operator

DECLARE @subValue int = 99;
SET @subValue -= 2 ;
PRINT 'subtract value :' + CAST(@subValue AS VARCHAR);
--Result : subtract value :97

*= Operator

DECLARE @mulValue int = 75;
SET @mulValue *= 20 ;
PRINT 'Multiplication :' + CAST(@mulValue AS VARCHAR);
--Result : Multiplication :1500

/= Operator

DECLARE @divValue NUMERIC(8,2) = 27;
SET @divValue /= 2.5 ;
PRINT 'Division :' + CAST(@divValue AS VARCHAR);
--Result : Division :10.80

%= Operator

DECLARE @modulo int = 25;
SET @modulo %= 5 ;
PRINT 'Modulo :' + CAST(@modulo AS VARCHAR);
--Result : Modulo :1

&= Operator

DECLARE @bitAnd int = 90;
SET @bitAnd &= 13 ;
PRINT 'Bitwise AND Operation:' + CAST(@bitAnd AS VARCHAR);
--Result : Bitwise AND Operation:8

^=Operator

DECLARE @bitExOr int = 244;
SET @bitExOr ^= 20 ;
PRINT 'Bitwise Exclusive OR Operation:' + CAST(@bitExOr AS VARCHAR);
--Result : Bitwise Exclusive OR Operation:224

|= Operator

DECLARE @bitOR int = 270;
SET @bitOR |= 25 ;
PRINT 'Bitwise OR Operation:' + CAST(@bitOR AS VARCHAR);
--Result : Bitwise OR Operation:287

Conclusion

The Compound Operators feature is enhanced in SQL Server 2008. They are like compound operators in languages like C, C++, and C #. Compound operators are a combination of operator with another operator.