There are a number of bitwise functions in sql server: AND; OR; Exclusive OR; NOT.
For this topic I will be giving a working example of using an “Exclusive OR”.
Firstly lets understand what a bitwise exclusive or operation does:
|LEFT Flag||Right Flag||Result|
So a normal ‘OR’ would return TRUE if: 1) Both Sides are true; 2) one side is true. The difference for XOR is that if both sides are true then the result is false. Another way to think is that it is an ‘OR’ minus an ‘AND’.
We can also look at the binary representation of this. Courtesy of MS Docs:
LEFT 0000 0000 1010 1010
Throughout my experience I have very rarely since this logic used, but as always there will be occasions where these functions are useful.
Take the following example. Suppose you want to compare two tables to see if something has changed:
So if you want to record what will be changed on Table2 into Updates. You could run the following:
This is fairly typical. Note As most experienced SQL devs know, NULLS are our bane of existence. If something can be NULL and it needs to be compared, then you need to protect against it. ie WHERE NULL = NULL is false and NULL != NULL is false, because by default NULLS cannot be compared. So you either SET ANSI_NULLS OFF (which is due to be depreciated), or you replace the NULLS on both sides, as done above.
Now imagine that you cannot replace the NULL with values on both sides. So in this case you are not allowed to use
ISNULL(T1.Col2,'') != ISNULL(T2.Col2,''). It would then not be possible to compare
T1.Col1 != T2.Col1 if either side is NULL.
T1.Col2 != T2.Col2 will capture any differences that don’t contain nulls, so we need to do something like
T1.Col2 != T2.Col2 OR (T1.Col2 IS NULL XOR T2.Col2 IS NULL). The important addition to this is
T1.Col2 IS NULL XOR T2.Col2 IS NULL. What this means is. If either T1.Col2 is NULL Or T2.Col2 IS NULL, but BOTH are not NULL.
|NOT NULL||NOT NULL||N/A|
N/A The NOT NULL AND NOT NULL logic would never occur as that would be captured by the
T1.Col1 != T2.Col1 side of
T1.Col2 != T2.Col2 OR (T1.Col2 IS NULL XOR T2.Col2 IS NULL)
Before we look at fixing the where clause we will replicate the table above:
DECLARE @T1_COL2 VARCHAR(10), @T2_COL2 VARCHAR(10);
So we can see that the sql code
CASE WHEN @T1_COL2 IS NULL THEN 1 ELSE 0 END ^ CASE WHEN @T2_COL2 IS NULL THEN 1 ELSE 0 END will equal ‘1’ if either T1.Col2 is NULL or T2.Col2 is NULL but NOT both.
Therefore the original piece of SQL can become: