Bitwise NOT example on sql server

user_16469114 0
Viewed 160

in SQL Server books online, there is an example for explaining bitwise not operator which made me confused:

The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. Performing the bitwise NOT operation on this value produces the binary result 1111 1111 0101 0101, which is decimal -171.

As an integer is 4 bytes in SQL Server shouldn't it be:

170: 0000 0000 0000 0000 0000 0000 1010 1010 (0x000000AA)

and

~170 = -171 : 1111 1111 1111 1111 1111 1111 0101 0101 (0xFFFFFF55)

Example says the bitwise not operator gives the result: 1111 1111 0101 0101 which is 0xFF55 and if we convert it to int its totally a different integer:

Select cast(0xFF55 as int)
--> 65365

if it was smallint then the example would be true:

Select cast(0xFF55 as smallint)
--> -171

Does the example missed first 2 bytes of 1's or am I misunderstanding something?

Thanks,

sql-server sql
2 Answers

Indeed, if you carry out a cast using a bitwise AND (&) given that max INT in SQL value, start with 1 and move up, note max INT is the top value.

Reference (&) https://docs.microsoft.com/en-us/sql/t-sql/language-elements/bitwise-and-transact-sql?view=sql-server-ver15

So, as you see it depends on the type of the input stored size (see the note on that OP referenced page), here we can see what it would be (I added spaces to make it visually easier to see) Note 2147483647 is max INT

BinaryStringNot170
---------------------------------------
1111 1111 1111 1111 1111 1111 0101 0101

BinaryString170
---------------------------------------
1000 0000 0000 0000 0000 0000 1010 1010

And the queries to produce those:

SELECT 
     cast( cast((~ 170) & 2147483647 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 1073741824 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 536870912 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 268435456 as bit) as CHAR(1)) + ' '
    +cast( cast((~ 170) & 134217728 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 67108864 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 33554432 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 16777216 as bit) as CHAR(1)) + ' '
    +cast( cast((~ 170) & 8388608 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 4194304 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 2097152 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 1048576 as bit) as CHAR(1)) + ' '
    +cast( cast((~ 170) & 524288 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 262144 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 131072 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 65536 as bit) as CHAR(1)) + ' '
    +cast( cast((~ 170) & 32768 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 16384 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 8192 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 4096 as bit) as CHAR(1)) + ' '
    +cast( cast((~ 170) & 2048 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 1024 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 512 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 256 as bit) as CHAR(1)) + ' '
    +cast( cast((~ 170) & 128 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 64 as bit) as CHAR(1)) 
    +cast( cast((~ 170) & 32 as bit) as CHAR(1))
    +cast( cast((~ 170) & 16 as bit)  as CHAR(1)) + ' '
    +cast( cast((~ 170) & 8 as bit)  as CHAR(1))
    +cast( cast((~ 170) & 4 as bit)  as CHAR(1))
    +cast( cast((~ 170) & 2 as bit)   as CHAR(1))
    +cast( cast((~ 170) & 1 as bit)  as CHAR(1))
 AS BinaryStringNot170


 SELECT 
     cast( cast(( 170) & 2147483647 as bit) as CHAR(1))  
    +cast( cast(( 170) & 1073741824 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 536870912 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 268435456 as bit) as CHAR(1)) + ' '
    +cast( cast(( 170) & 134217728 as bit) as CHAR(1))  
    +cast( cast(( 170) & 67108864 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 33554432 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 16777216 as bit) as CHAR(1))  + ' '
    +cast( cast(( 170) & 8388608 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 4194304 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 2097152 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 1048576 as bit) as CHAR(1))  + ' '
    +cast( cast(( 170) & 524288 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 262144 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 131072 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 65536 as bit) as CHAR(1)) + ' '
    +cast( cast(( 170) & 32768 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 16384 as bit) as CHAR(1))  
    +cast( cast(( 170) & 8192 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 4096 as bit) as CHAR(1)) + ' '
    +cast( cast(( 170) & 2048 as bit) as CHAR(1))  
    +cast( cast(( 170) & 1024 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 512 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 256 as bit) as CHAR(1))  + ' '
    +cast( cast(( 170) & 128 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 64 as bit) as CHAR(1)) 
    +cast( cast(( 170) & 32 as bit) as CHAR(1))
    +cast( cast(( 170) & 16 as bit)  as CHAR(1)) + ' '
    +cast( cast(( 170) & 8 as bit)  as CHAR(1))
    +cast( cast(( 170) & 4 as bit)  as CHAR(1))
    +cast( cast(( 170) & 2 as bit)   as CHAR(1))
    +cast( cast(( 170) & 1 as bit)  as CHAR(1))
 AS BinaryString170

Just to be more complete here note this returns MAXINT value stored in sql

SELECT CONVERT(INT, CONVERT(VARBINARY, '0x7FFFFFFF', 1))

This returns -1

SELECT CONVERT(INT, CONVERT(VARBINARY, '0xFFFFFFFF', 1))
user_1259810

You are correct.

That article has now been updated to say

The binary representation of 170 (a_int_value or A) is 0000 0000 0000 0000 0000 0000 1010 1010. Performing the bitwise NOT operation on this value produces the binary result 1111 1111 1111 1111 1111 1111 0101 0101, which is decimal -171.

user_116830
Related