Right Shift And Left Shift Operator in SQL Server

Question

In SQL Server, shift operators are not present as per knowledge.

If I have to achieve right shift and left shift, what will be the efficient way of doing it?

1. With mathematical expressions which will give me the same output as shift operator must have given.

OR

2. Will call CLR function to calculate the right shift and left shift because shift operators are available in C# which will give me the output what I am expecting.

Please suggest which one will be the more efficient way of doing it.

Answer 1

From SQL Server 2022 you can simply use

• LEFT_SHIFT ( expression_value, shift_amount ) (or <<)
• RIGHT_SHIFT ( expression_value, shift_amount ) (or >>)

Answer 2

Option 1 of course.

with mathematical expressions which will give me the same output as shift operator

A single left shift is just a multiplication by 2. repeat as often as necessary to emulate a << n. Accordingly, right shift is integer division by 2.

There is also a nice hack involving varbinary, described in this related answer.

Even with arithmetic overflow prevention added, either will outperform an unmanaged/managed transition in SQL server's CLR hosting environment by a magnitude.