SQL Bitwise not (~)

Question

Can someone please explain why

select ~1

gives the result

-2

Perhaps there is a lot for me to learn about the actual bits of number types. What resource would you recommend?

Answer 1

If you want to do binary (yes/no) bitwise not; then first cast the data as a BIT and then apply bitwise not.

SELECT ~CAST( 1 AS BIT)

gives the result

0

Since BIT only has three values: 0, 1 and NULL it will always inverse from 0 to 1 or 1 to 0. ~NULL will always end up as NULL.

Answer 2

Q: Can someone please explain why select ~1 gives the result -2?

A: For the same reason ~0 would give -1 :)

Here's a good article on "twos complement" arithmetic:

• http://en.wikipedia.org/wiki/Twos_complement

Most CPU architectures are two's complement (vs. one's complement). From the same article:

Two's complement is the easiest to implement in hardware, which may be the ultimate reason for its widespread popularity[citation needed]. Processors on the early mainframes often consisted of thousands of transistors – eliminating a significant number of transistors was a significant cost savings. The architects of the early integrated circuit-based CPUs (Intel 8080, etc.) chose to use two's complement math. As IC technology advanced, virtually all adopted two's complement technology. Intel, AMD, and Power Architecture chips are all two's complement.

Answer 3

You should read about Two's Complement (http://en.wikipedia.org/wiki/Two's_complement)

+1 in binary is represented as 00000001, the inverse of which is 11111110, which is -2 in Two's Complement