Boolean 'NOT' in T-SQL not working on 'bit' datatype?

Working with bitwise negation

Working with bit datatype and need negation? ~ operator is your new best friend:

DECLARE @MyBit bit = 1; -- Let there be light
SELECT ~@MyBit; -- And darkness embraced everything. Returns 0 (FALSE)


The advantage of '~'

While you can play with subtraction:

SELECT 1 - @MyBoolean FROM YourTable; -- mathematician mode on


Going with the ~ operator is like negation on steroids — straightforward, concise, and clear:

SELECT ~@MyBoolean FROM YourTable; -- just flip it!


Tips and traps

Dealing with NULL

Beware, the beast NULL is still NULL after bitwise NOT:

DECLARE @MyBit bit = NULL; 
SELECT ~@MyBit; -- NULL to the power of NULL. Still returns NULL


Check your 'NOT's

In conditions, use NOT wisely with explicit checks:

-- Insert coin to play
SELECT * FROM YourTable WHERE NOT YourBitColumn; -- Player 1 Defeated!

-- Continue? 10, 9, 8...
SELECT * FROM YourTable WHERE NOT (YourBitColumn = 1); -- Player 1 Ready! 


Advanced Operations

Pairing with CASE statement

Negation in a conditional play:

SELECT CASE WHEN SomeCondition THEN ~YourBitColumn ELSE YourBitColumn END FROM YourTable; -- Just in case...


Handy in JOINs

The bitwise NOT for enhancing joins:

SELECT a.*, b.*
FROM TableA a
JOIN TableB b ON a.ID = b.ID AND ~a.YourBitColumn = b.YourBitColumn; -- Join me, and together we can rule the Query!


Indexes & negation

Fight-off performance hits. Avoid using negation in indexed columns.