Some systems store several small status values through a single packed integer. For example, a device integration may require tracking operating mode, alert level, and zone information as one status code rather than separate values in different columns. That creates a practical reporting problem. The packed value may be convenient for an integration format, but a report still needs to know the device mode, alert level, or zone stored inside that value.

In this article, we'll use LEFT_SHIFT() to build a packed device status code and RIGHT_SHIFT() to decode the individual values again. This example requires SQL Server 2022 or later.

Challenge

Suppose a monitoring system uses one status code to carry three values for each device:

SQL Server receives these values separately, but an external integration format expects them to be combined into a single 16-bit status code. Later, SQL Server reports need to decode the stored integer and identify devices with a high alert level.

The challenge is to place each value in the correct bit range when the status code is created, then recover only the required bits when the value is read.

Approach

LEFT_SHIFT() can move a value into its assigned bit positions. Once the values have been shifted, the bitwise OR operator can combine them into one status code.To read the code, RIGHT_SHIFT() moves the required field back toward the lowest bit positions. A bitwise AND mask then removes any remaining bits that belong to other fields.

For this example, OperatingMode and AlertLevel each use four bits, while ZoneCode uses eight bits.

Creating the Sample Table

The following table represents the status log stored by the monitoring system. It keeps the device name and the packed status code.

CREATE TABLE dbo.DeviceStatusLog
(
StatusID INT IDENTITY(1,1) PRIMARY KEY,
DeviceName VARCHAR(50) NOT NULL,
PackedStatus INT NOT NULL
);
GO

The layout uses only 16 bits, but INT is used for the stored value. This avoids the signed range limitation of SMALLINT when a packed value uses the full range from 0 through 65535.

Packing the Status Code with LEFT_SHIFT()

The sample input contains an operating mode, alert level, and zone code for each device. These values are packed during the insert.

INSERT INTO dbo.DeviceStatusLog
(DeviceName, PackedStatus)
SELECT
DeviceName,
LEFT_SHIFT(ZoneCode, 8)
| LEFT_SHIFT(AlertLevel, 4)
| OperatingMode
FROM
(
VALUES
('Pump-01', 3, 5, 12),
('Valve-02', 1, 2, 7),
('Motor-03', 6, 8, 20),
('Pump-04', 2, 0, 12),
('Sensor-05', 4, 10, 3)
) AS s(DeviceName, OperatingMode, AlertLevel, ZoneCode);

SELECT
StatusID,
DeviceName,
PackedStatus
FROM dbo.DeviceStatusLog
ORDER BY StatusID;

ZoneCode is shifted left by eight positions because it occupies bits 8 through 15. AlertLevel is shifted by four positions, while OperatingMode already belongs in bits 0 through 3 and does not need to be shifted.

For Pump-01, ZoneCode 12 is moved to the upper eight bits, AlertLevel 5 is moved to bits 4 through 7, and OperatingMode 3 remains in the lowest four bits. The combined PackedStatus value is 3155.

The query should return packed values of 3155, 1825, 5254, 3074, and 932 for the five devices.

Decoding the Status Code with RIGHT_SHIFT()

The stored status code is useful to the integration, but a report usually needs the individual values. The following query decodes all three fields.

SELECT
StatusID,
DeviceName,
PackedStatus,
PackedStatus & 15 AS OperatingMode,
RIGHT_SHIFT(PackedStatus, 4) & 15 AS AlertLevel,
RIGHT_SHIFT(PackedStatus, 8) & 255 AS ZoneCode
FROM dbo.DeviceStatusLog
ORDER BY StatusID;

The mask 15 keeps the four bits used by OperatingMode or AlertLevel. The mask 255 keeps the eight bits used by ZoneCode.

For AlertLevel, RIGHT_SHIFT() first moves bits 4 through 7 down to bits 0 through 3. Applying & 15 then removes everything outside those four bits. ZoneCode follows the same pattern, except the value is shifted by eight positions and masked with 255.

For example, Pump-01 should decode back to OperatingMode = 3, AlertLevel = 5, and ZoneCode = 12. This confirms that the values packed with LEFT_SHIFT() can be recovered from the stored status code.

Using the Decoded Values in a Report

Once the values have been decoded, they can be used in normal reporting logic. Suppose the operations team wants to see only devices with an alert level of 5 or higher.

WITH DecodedStatus
AS (SELECT StatusID,
           DeviceName,
           PackedStatus,
           PackedStatus & 15 AS OperatingMode,
           RIGHT_SHIFT(PackedStatus, 4) & 15 AS AlertLevel,
           RIGHT_SHIFT(PackedStatus, 8) & 255 AS ZoneCode
    FROM dbo.DeviceStatusLog)
SELECT DeviceName,
       OperatingMode,
       AlertLevel,
       ZoneCode,
       PackedStatus
FROM DecodedStatus
WHERE AlertLevel >= 5
ORDER BY AlertLevel DESC,
         DeviceName;

Based on the sample data, Sensor-05 should appear with an alert level of 10, Motor-03 with 8, and Pump-01 with 5. Valve-02 and Pump-04 are excluded because their decoded alert levels are below the reporting threshold.

When This Pattern Is Useful

This pattern is useful when SQL Server must work with a packed numeric format that already exists, such as a device protocol, telemetry feed, legacy application, or another integration that assigns several small values to fixed bit ranges. The important requirement is that every value must fit within its assigned number of bits. If a value exceeds that range, its bits can overlap another field. Bits shifted outside the width of the underlying data type can also be lost, so input ranges should be validated before values are packed.

This approach should not be used simply to replace clear relational columns with a packed integer. It is most appropriate when a packed format is already required by a device protocol, external integration, or legacy system. If SQL Server controls the data model and the values are frequently searched, indexed, or updated independently, separate columns are usually easier to understand and maintain.

Final Thoughts

LEFT_SHIFT() and RIGHT_SHIFT() become more useful when they solve a specific packing and decoding problem rather than simply shifting numbers for demonstration. In this example, LEFT_SHIFT() placed device values into defined bit ranges, the bitwise OR operator combined them into a single status code, and RIGHT_SHIFT() together with bit masks recovered the original values for reporting.

For systems that must exchange or store predefined packed status codes, these functions provide a clear way to work with those values directly in SQL Server while keeping the bit layout visible in the query.