Bitwise Flags: Test, Add and Toggle Individual Bits

I read bitwise flags one mask at a time. AND tests the selected bit, OR includes it, and XOR toggles it. Those operations answer different questions even when some results happen to match.

State what the mask represents

The example uses mask four, which selects one bit in the integer. FlagValue remains visible beside every derived column. Zero, one, four and five supply cases where the selected bit is absent or present.

I’d name the flag’s business meaning in real code. A bare four is readable in a small demonstration, but it doesn’t explain an application’s policy. Keep the mapping from masks to meanings in one documented place rather than repeating unexplained numbers across queries.

WITH Flags AS
(
 SELECT CaseId, CAST(FlagValue AS int) AS FlagValue
 FROM (VALUES (1,0),(2,1),(3,4),(4,5),
              (5,CAST(NULL AS int))) v(CaseId,FlagValue)
)
SELECT CaseId, FlagValue, FlagValue & 4 AS MaskedValue,
       CAST(CASE WHEN FlagValue IS NULL THEN NULL
                 WHEN (FlagValue & 4)=4 THEN 1 ELSE 0 END AS bit) AS HasFlag4,
       FlagValue | 4 AS WithFlag4,
       FlagValue ^ 4 AS ToggledFlag4
FROM Flags
ORDER BY CaseId;

Test with AND

MaskedValue keeps only the selected bit. For inputs four and five, that result is four. For zero and one, it’s zero. Comparing the masked result with the mask gives the HasFlag4 output.

The comparison is important because the bitwise result is an integer, not automatically a Boolean column. I cast the final test to bit deliberately. If the mask later selects several bits, equality to the full mask tests whether all of them are present.

Include a bit with OR

WithFlag4 includes the selected bit without removing the other bits. One becomes five, while five stays five. Four also stays four because adding an already present bit doesn’t toggle it away.

That makes OR useful for a set-bit expression. I’d still review whether unrelated bits are allowed in the value. A correct bitwise calculation doesn’t validate the entire flag domain or prove that each combination has a sensible business meaning.

Toggle a bit with XOR

ToggledFlag4 reverses the selected bit’s state. Five becomes one, and four becomes zero. Inputs without that bit gain it, so zero becomes four and one becomes five.

A toggle isn’t idempotent. Applying it twice returns the original selected-bit state, which matters when a request can be retried. I’d use a deliberate set or clear operation when the caller means a final state. A toggle isn’t a retry-safe enable request.

Keep missing values visible

The last row supplies SQL NULL and preserves NULL in all derived outputs. The CASE expression avoids calling a missing input false. A missing flag value has a different meaning from a known value without mask four.

I’d decide how the application handles that missing value separately. Defaulting it to zero can be legitimate, but the default belongs in the contract. Otherwise a report can make unknown permissions or settings look like confirmed absences.

Review the type and the complete value

All supplied numbers and masks here are positive int values or NULL. This example doesn’t generalize signed high-bit behavior to every numeric type.

The query changes no stored data. It reports five complete cases so the three operations can be compared directly. Before using an update, I’d verify the allowed masks and retry behavior. The surrounding business rule also needs a clear owner.

Write down what each mask means once, and your future self will say thanks.

A flag mask is not a business policy, it is a bit operation whose meaning must be documented.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.