Introduction

SQL Server 2022 introduced the LEFT_SHIFT and RIGHT_SHIFT functions, which allow you to manipulate numbers by shifting their binary digits either to the left or right. These functions are part of bitwise operations, which directly operate on the binary representation of numbers.

Although these functions are new to SQL Server, they are widely used in other databases like MySQL, and their importance in performance optimization and data manipulation cannot be overstated. In this article, we’ll walk through how these functions work, how to use them, and provide simple examples that illustrate practical applications of these functions.

What are Bitwise Shifts?

In computing, numbers are represented as sequences of 1s and 0s, known as binary numbers. A bitwise shift involves moving these 1s and 0s either to the left or to the right. This operation effectively changes the value of the number. Shifting a number left or right is similar to multiplying or dividing it by powers of 2.

• Left Shift: Shifting a number to the left is like multiplying it by 2 for each position shifted.
• Right Shift: Shifting a number to the right is like dividing it by 2 for each position shifted.

Bitwise shifts are often used in performance-sensitive applications like data compression, encryption, or flag handling, where you may need to manipulate individual bits efficiently.

The LEFT_SHIFT Function Explained

The LEFT_SHIFT function shifts the bits of a number to the left by a specified number of positions. Each left shift multiplies the number by 2 for every position shifted.

LEFT_SHIFT(value, shift_count)

The parameters are:

• value: The number whose bits you want to shift.
• shift_count: The number of positions to shift the bits to the left.

Let’s take the number 14 and shift its bits 3 positions to the left.

First, we convert 14 to binary: 1110. Shifting 1110 3 positions to the left gives 1110000. Converting 1110000 back to decimal results in 112. Here is the code in T-SQL:

SELECT LEFT_SHIFT(14, 3) AS ShiftedValue;

We see the result in SSMS:

RIGHT_SHIFT Function Explained

The RIGHT_SHIFT function shifts the bits of a number to the right by a specified number of positions. Each right shift divides the number by 2 for each position shifted.

RIGHT_SHIFT(value, shift_count)

The parameters are:

• value: The number whose bits you want to shift.
• shift_count: The number of positions to shift the bits to the right.

Now, let’s take the number 1478 and shift its bits 4 positions to the right. First, we convert 1478 to binary: 10111000110. Shifting 10111000110 4 positions to the right gives 00001011100. Converting 00001011100 back to decimal results in 92. Here is the code in T-SQL:

SELECT RIGHT_SHIFT(1478, 4) AS ShiftedValue;

Here is the result:

Hexadecimal Example of LEFT_SHIFT and RIGHT_SHIFT

Let’s now look at how the LEFT_SHIFT and RIGHT_SHIFT functions work with hexadecimal values, which are commonly used in computing for compact representation of large binary numbers.

Let’s take a hexadecimal value 0xA (which is 1010 in binary, and 10 in decimal) and shift it 2 positions to the left.  The original Hexadecimal Value: 0xA (decimal 10, binary 1010). Shifting 1010 2 positions to the left gives 101000 (binary). Converting 101000 back to hexadecimal results in 0x28.

Here is the example in T-SQL:

SELECT LEFT_SHIFT(0xA, 2) AS ShiftedValue;

We can see the result:

Now, let’s take the hexadecimal value 0xF0 (which is 11110000 in binary, and 240 in decimal) and shift it 3 positions to the right. The original Hexadecimal Value: 0xF0 (decimal 240, binary 11110000). Shifting 11110000 3 positions to the right gives 00011110 (binary). Converting 00011110 back to hexadecimal results in 0x1E.

Here is the T-SQL

SELECT RIGHT_SHIFT(0xF0, 3) AS ShiftedValue;

Here are the results.

Common Errors

You may encounter errors if you use invalid data types or provide incorrect arguments. Both the LEFT_SHIFT and RIGHT_SHIFT functions expect numeric values. If you pass a string instead of a number, SQL Server will throw an error.

Here is an example of an incorrect data type.This query will throw an error because the LEFT_SHIFT function expects a number, not a string.

SELECT LEFT_SHIFT('2223', 4) AS Value;

Similarly, both functions only accept two arguments. Providing more than two will also result in an error. For example:

SELECT LEFT_SHIFT(5, 4, 3) AS Value;

This will throw an error because the LEFT_SHIFT function can only handle two arguments: the number to shift and the number of positions to shift by.

To get around this, here is an example of an incorrect data type with error handling. This query will throw an error because the LEFT_SHIFT function expects a numeric value as its first argument, but '2223' is a string. SQL Server cannot perform a bitwise operation on a string, so it generates an error. The TRY...CATCH block is used to capture the error message, which will be returned by the ERROR_MESSAGE() function.

BEGIN TRY
    SELECT LEFT_SHIFT('2223', 4) AS Value;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Practical Use Cases

The LEFT_SHIFT and RIGHT_SHIFT functions are valuable tools in various real-world scenarios:

• Data Encryption: Bitwise shifts can be used to obscure data temporarily in low-security applications. For instance, before storing data, you might shift the bits of a number to make it less readable. Later, you can reverse the shift to recover the original value.
• Data Compression: Bitwise shifts are useful for reducing the size of numbers in datasets, allowing for faster storage and processing. Shifting bits to the right can reduce the size of a number, which is particularly helpful in large datasets.
• Flag Handling: Bitwise shifts allow efficient manipulation of flags. For example, in a system where each bit represents a permission (read, write, delete, etc.), you can use shifts to enable or disable flags efficiently.
• Performance Optimization: Shifting bits is an extremely fast operation compared to multiplication or division. Therefore, bitwise shifts can optimize performance in tasks like large-scale data analysis, image processing, and other computationally expensive applications.

Conclusion

The LEFT_SHIFT and RIGHT_SHIFT functions in SQL Server provide an efficient way to manipulate numbers at the binary level. These functions are indispensable in tasks like data encryption, compression, and flag manipulation, where performance is crucial.

By understanding and using these bitwise operations effectively, you can optimize your SQL queries, resulting in faster and more efficient applications, especially when working with large datasets or high-performance environments. Whether you’re working with decimal or hexadecimal values, bitwise shifts will provide you with the flexibility to solve complex problems more efficiently.