SQL SERVER – How to Flip Value of Bit Field in SQL Server? – Part 2

You can flip value of bit field data in SQL Server without a CASE statement, and five expressions do it. I ran Vinod Kumar’s four methods and the readers’ answers on SQL Server 2025. Here is what each one returns for 0, 1 and NULL.

The Original Puzzle

Here are a few different methods to flip value of a bit field suggested by my very good friend Vinod Kumar. All the methods are also very simple and flips the value of the bit field in SQL Server.

The challenge was to find an interesting way to flip the value that does not use a CASE statement. Here is the script from my friend Vinod Kumar.

DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT @field1 Field1, @field2 Field2
-- Method one
SELECT @field1 = 1^@field1, @field2 = 1^@field2
SELECT @field1 Field1, @field2 Field2
-- Method 2
SELECT @field1 = 1-@field1, @field2 = 1-@field2
SELECT @field1 Field1, @field2 Field2
-- Method 3
SELECT @field1 = (1+@field1)%2, @field2 = (1+@field2)%2
SELECT @field1 Field1, @field2 Field2
-- Method 4
SELECT @field1 = ABS(@field1-1), @field2 = ABS(@field2-1)
SELECT @field1 Field1, @field2 Field2

Each method flips the result of the one before. The two variables start at 0 and 1. After method one they hold 1 and 0. After method two they are back to 0 and 1. Methods three and four repeat the same two steps.

What Readers Added

A reader named sandeepmittal11 used the tilde, the bitwise NOT operator. Jamie sent the same idea. Here is the first version.

DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT ~@field1 Field1, ~@field2 Field2

Vivek Kumar suggested adding one to the first value and subtracting one from the second.

DECLARE @field1 BIT
DECLARE @field2 BIT
SET @field1 = 0
SET @field2 = 1
SELECT @field1 Field1, @field2 Field2
SELECT @field1+1 Field1, @field2-1 Field2

Jitendra asked how the same logic works for integer values. The last section answers that.

The Answer, Tested

A script that flips two variables does not show how the methods treat a table. So I made a small temp table with three rows: 0, 1 and NULL. Then I ran all the expressions side by side.

CREATE TABLE #Switch (Id int IDENTITY(1,1) PRIMARY KEY, IsOn bit NULL);
INSERT INTO #Switch (IsOn) VALUES (0), (1), (NULL);
SELECT Id, IsOn, ~IsOn AS Tilde, IsOn ^ 1 AS Xor1, 1 - IsOn AS Minus, (IsOn + 1) % 2 AS Modulo, ABS(IsOn - 1) AS AbsVal FROM #Switch;

All five expressions agree. They turn 0 into 1, 1 into 0 and leave NULL as NULL. That is the correct behavior, because NULL means unknown, and the opposite of unknown is unknown.

The data type differs. I asked SQL Server for the type of each result.

SELECT SQL_VARIANT_PROPERTY(~IsOn, 'BaseType') AS TildeType, SQL_VARIANT_PROPERTY(IsOn ^ 1, 'BaseType') AS XorType, SQL_VARIANT_PROPERTY(1 - IsOn, 'BaseType') AS MinusType FROM #Switch WHERE Id = 1;

The tilde keeps the bit type. The other four return an int, which converts back to bit when you store it. A bit column holds one bit, so the tilde flips that single bit and nothing else.

Now the idea from Vivek Kumar and the IIF function, which has a CASE inside.

SELECT Id, IsOn, IIF(IsOn = 1, 0, 1) AS IifResult, IsOn + 1 AS PlusOne, IsOn - 1 AS MinusOne FROM #Switch;

Adding one works only when the value is 0, and subtracting one works only when it is 1. Used on the whole column, the first gives 2 and the second gives -1. You must already know the current value, so neither is a general method. IIF flips 0 and 1 but turns NULL into 1. The test IsOn = 1 is not true for NULL.

To flip a whole table, use the tilde in an UPDATE.

UPDATE #Switch SET IsOn = ~IsOn;
SELECT Id, IsOn FROM #Switch;

What About Integer Columns?

Jitendra asked about integers. The tilde on an int flips all 32 bits, not one. The result is not a clean 0 and 1 flip. I tried the three most likely candidates on the values 0, 1 and 5.

SELECT v.n, ~ v.n AS Tilde, v.n ^ 1 AS Xor1, ~ CAST(v.n AS bit) AS ViaBit FROM (VALUES (0), (1), (5)) AS v(n);

The tilde gives -1 and -2, which is wrong for a flip. The XOR with 1 works when the column holds only 0 and 1. For 5 it returns 4. If any value other than 0 means true, cast to bit first. The cast turns 5 into 1, and the tilde then returns 0.

Is CASE Really Worse?

You could say CASE is easier to read. Fair point. A reader who sees ~IsOn must know the operator, and a comment helps. But CASE needs a branch for NULL, and IIF showed what happens when you forget it.

The tilde is short, keeps the bit type and handles NULL the right way. In my own code I use it on bit columns and write a short comment above it.

A Simple Rule

To flip value of bit field data, use ~IsOn on a bit column. For an integer that holds only 0 and 1, use 1 – n. For an integer where nonzero means true, use ~CAST(n AS bit). Skip the idea of adding one or subtracting one. Test the NULL row every time.

When you finish testing, remove the temp table.

DROP TABLE #Switch;

Flipping a bit is not a branch to write, it is one operator to remember.

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