Flipping a Sql Server Bit value

In a language like C# you can use ! for a bool to make it become true if it was false and vice versa, for example:

bool a = true;
a = !a;
Console.WriteLine(a.ToString());
a = !a;
Console.WriteLine(a.ToString());
Console.ReadLine();

The output will be of the above statement will be:

False
True

How can you do this in SQL Server? It is pretty easy and there are several ways. Lets take a look...

Method 1 (most efficient & recommended) - Bitwise NOT Operator:

SELECT ~CONVERT(bit,0)
SELECT ~CONVERT(bit,1)

The ~ symbol is the Bitwise NOT operator, here is what books on line has to say about the Bitwise NOT operator:

The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.

Method 2 - Brute Force method:

There is the brute force method. I like this method primarily because it is somewhat self-documenting and there is absolutely no question what the coder intends to do here.

USE Northwind
UPDATE products SET discontinued = CASE WHEN discontinued = 1 THEN 0 ELSE 1 END

Method 3 - Bitwise XOR Operator:

This option is to use the bitwise XOR operator with either a ’0′ or a ’1′. What does this do? The XOR operator returns ’0′ if the two arguments match and ’1′ if they don’t.

USE Northwind
UPDATE products SET discontinued = discontined ^ 1

Method 4 - Use MOD:

Finally, one may use MOD:

USE Northwind
UPDATE products SET discontinued = ((discontinued + 1) % 2)