SQL Server 2022 introduced a bunch of new bit manipulation functions and operators. Amongst these are the right shift and left shift operators.
The right shift and left shift operators can be used to shift bits to the left or right. Given an integer or binary expression, these operators can shift bits by the amount that we specify.
The Left Shift Operator
Here’s an example that demonstrates how the left shift operator works:
SELECT 12345 << 1;Result:
24690
This example uses the left shift operator to shift the value one bit to the left. It’s the binary value that’s shifted – not the integer value. So to understand the above result, we need to look at what happened at the binary level.
The following table contains the above integer values and their equivalent binary values:
| Integer | Binary |
|---|---|
12345 | 0011 0000 0011 1001 |
24690 | 0110 0000 0111 0010 |
If we look at the two binary values, we can see that the second one is almost the same as the first one, except that the bits have been shifted by 1. Basically, a zero has been added to the right, and this has pushed all other bits to the left.
Removing the spaces can make this easier to see:
0011000000111001 0110000001110010
In the top value, the rightmost digit is a 1. In the bottom value, the 1 has been moved one step to the left (and the rightmost digit is now a zero). The same is true for all digits in the bottom value – they have all been moved one step to the left.
Here’s another way of looking at it:
0011000000111001 0011000000111001
We’ve effectively just moved the whole value to the left. The left shift operator does the same thing, but removes the leftmost digit and adds a zero to the right.
The resulting binary value is the equivalent of 24690, and so that’s what is returned in the above example.
The Right Shift Operator
The right shift operator works the same as the left shift operator, except that it shifts the bits to the right:
SELECT 12345 >> 1;Result:
6172
The integer values and their equivalent binary values are as follows:
| Integer | Binary |
|---|---|
12345 | 0011 0000 0011 1001 |
6172 | 0001 1000 0001 1100 |
So, the second row contains the result of the above example. The binary value is almost the same as the one on the first row, except that the bits have been shifted to the right by 1. A zero has been added to the left, and this has pushed all other bits to the right.
Here it is after removing the spaces:
0011000000111001 0001100000011100
And here’s another way to view the shift:
0011000000111001 0011000000111001
We’ve effectively just moved the whole bottom value to the right. The right shift operator does this too, but it removes the rightmost digit and adds a zero to the left.
The resulting binary value is the equivalent of 6172, and so that’s what is returned by the right shift operator.
The LEFT_SHIFT() and RIGHT_SHIFT() Functions
Also introduced in SQL Server 2022 were the LEFT_SHIFT() and RIGHT_SHIFT() functions. These functions provide an alternative way to perform the same operation that the left shift and right shift operators perform.