Two identical numbers can produce different answers when the operator changes. Understanding bitwise XOR alongside AND and OR explains why two sevens return either seven or zero.
Compare Bitwise XOR With AND and OR
The puzzle begins with small integers rather than a complicated query. Run the expressions separately labeled so their outcomes are easy to compare. The symbols work on individual bits within the integer representation. They do not add the numbers, multiply them, or evaluate a single true-or-false condition for the entire value.
SELECT 7 & 7 AS AndResult,
7 | 7 AS OrResult,
7 ^ 7 AS XorResult;AND keeps a bit when both corresponding input bits are one. OR keeps it when either corresponding bit is one. XOR keeps it when the two corresponding bits differ. Seven has the same bits on both sides, so AND and OR retain seven while XOR clears every matching bit.
Those answers follow directly from the operators' definitions. They are expected arithmetic results, rather than measured database performance. I start with identical operands because that comparison makes the difference visible before introducing flags or variables. A symbol that looks small still deserves a name in a code review.
Write the Small Numbers in Binary
Consider only the lowest four positions for this demonstration. Seven is 0111, five is 0101, and two is 0010. The positions represent eight, four, two, and one from left to right. A one contributes its position's value; a zero contributes nothing. Leading zeroes let the examples line up neatly.
SELECT 7 & 5 AS AndResult,
7 | 5 AS OrResult,
7 ^ 5 AS XorResult,
CAST(7 AS binary(1)) AS SevenAsOneByte;Comparing 0111 and 0101 gives 0101 for AND, 0111 for OR, and 0010 for XOR. These correspond to five, seven, and two. The binary cast displays a byte representation, typically rendered as hexadecimal in the results grid. It does not automatically print a string of zeroes and ones.
Use nonnegative integers while learning the pattern. Negative integers use a signed representation, and the sign bit becomes part of the calculation. Also keep operand types deliberate. Mixing widths introduces conversion rules that make a beginner's diagram harder to follow. There is no reward for hiding the type boundary inside the puzzle.
Test a Flag With a Mask
A flag mask gives each independent option a power-of-two value. For example, one represents email, two represents text notifications, and four represents weekly summaries. Combining those values produces a stored integer whose individual bits record the selected options. The application and database must share the same definitions.
DECLARE @Flags int = 5;
DECLARE @SummaryMask int = 4;
SELECT CASE WHEN (@Flags & @SummaryMask) = @SummaryMask
THEN 1 ELSE 0 END AS SummaryEnabled;The equality test confirms that every bit in the requested mask is present. For a single-bit mask, comparing the AND result with zero also distinguishes enabled from disabled. For a multi-bit mask, nonzero means at least one requested bit exists, while equality means all requested bits exist. That difference matters for combined requirements.
Document each assigned position and reserve unused positions intentionally. A reused flag value can change the interpretation of old data without changing a single stored integer. Keep the meanings stable across releases, and test application readers when adding a new option to the contract.
Set a Flag With a Mask
OR enables a chosen bit without disturbing other enabled positions. Applying the same mask repeatedly leaves the same result. That makes it appropriate for a request such as enable weekly summaries, where retries should preserve the requested state rather than reverse it. Use a named mask instead of an unexplained integer.
DECLARE @Flags int = 1;
SET @Flags = @Flags | 4;
SELECT @Flags AS AfterSetting;
SET @Flags = @Flags | 4;
SELECT @Flags AS AfterSettingAgain;For a stored column, perform the calculation in the UPDATE statement so a read-modify-write cycle in application code does not overwrite another session's change. Scope the UPDATE to the intended row and retain normal concurrency controls. A bitwise expression does not remove the need for a reliable key or transaction policy.
Toggle a Flag With Bitwise XOR
Use bitwise XOR when the requirement really means invert this option. An enabled position becomes disabled, and a disabled position becomes enabled. Repeating the operation twice returns the original state. That behavior is useful for a deliberate toggle and troublesome for a retried command that was supposed to enable an option.
DECLARE @Flags int = 5;
SET @Flags = @Flags ^ 4;
SELECT @Flags AS AfterFirstToggle;
SET @Flags = @Flags ^ 4;
SELECT @Flags AS AfterSecondToggle;
SELECT @Flags & ~4 AS WithSummaryCleared;The complement operator in the final expression provides a clear operation: AND with the inverted mask removes the selected bit. Parenthesize larger expressions and make the intended state explicit. Ask whether the caller requested a state or requested a transition before choosing the operator.
A NULL flag value produces a NULL expression, which is different from a known collection with every option disabled. Decide whether the column permits unknown state. A NOT NULL integer with a documented zero default is simpler when every row must have a complete flag interpretation. Reject unsupported bits at the application boundary or through an appropriate constraint. Flags also become awkward when options need their own dates, owners, or other attributes; a related table provides a clearer model for that richer information.
Understand the Bitwise XOR Swap Trick
The XOR swap works because a value XOR itself becomes zero and a value XOR zero remains unchanged. Three assignments exchange two independent integer variables without a temporary variable. Each assignment depends on the previous result, so the sequence is part of the demonstration.
DECLARE @A int = 7, @B int = 5;
SET @A = @A ^ @B;
SET @B = @A ^ @B;
SET @A = @A ^ @B;
SELECT @A AS FirstValue, @B AS SecondValue;I use this as an explanation of the algebra, not a recommendation for everyday database code. A temporary variable is clearer, handles broader types, and avoids cleverness around shared storage. Keep the demonstration variables distinct. Do not transplant the trick into an UPDATE and assume assignment evaluation behaves like these separate statements.
Use the Newer Functions Where They Help
SQL Server 2022 adds bit functions that make some intentions easier to read. GET_BIT reads a position, SET_BIT sets a position, and BIT_COUNT counts enabled bits. Positions start at zero. Their use still requires a documented mask contract and a deliberate integer width.
SELECT GET_BIT(CAST(5 AS int), 2) AS BitAtPositionTwo,
SET_BIT(CAST(1 AS int), 2, 1) AS SummaryEnabled,
BIT_COUNT(CAST(7 AS int)) AS EnabledBitCount;Which version must the script support? Keep the classic operators for older supported servers and use the newer functions where the deployment supports them. The most useful result of understanding bitwise XOR is choosing readable code with the right retry behavior, rather than collecting another surprising expression.
Related reading on this blog: Bitwise Puzzle: SQL in Sixty Seconds 160 and Using Bitwise And (&) Instead of a Junction Table: Notes from the Field #053.
A bitwise operator is not ordinary arithmetic, it is a rule applied to each matching bit position.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
