CODEWITHSUNDEEP
sqlsql-serverbit
AAsk
AAsk

Reputation: 1491

TSQL BITWISE NOT

The binary representation of integer 170 is 1 0 1 0 1 0 1 0 Reversing the bits gives 0 1 0 1 0 1 0 1 That converted to decimal is integer 85.

Why does SELECT ~170 return -171?

I am struggling to understand what I am missing here.

Upvotes: 2

Views: 602

Answers (1)

Dave Cousineau
Dave Cousineau

Reputation: 13168

It's not a byte (8-bits), it's probably a 32-bit number.

Whatever the length (16, 32, or 64), it will have leading 0s. In 16-bit this looks like:

0000 0000 1010 1010 becomes 1111 1111 0101 0101 which is -171.

You can do this yourself with windows calculator with programmer mode, decimal, and anything higher than byte selected. Enter 170 and hit NOT and you get -171, and it will show you the bit representations of each.

Another thing to note would be that 1010 1010 is not actually 170, if it is a signed byte. As a signed byte it would be -86. Inverting it in byte mode yields 85, as you expected.

Note also the difference between signed and unsigned bit patterns. The bit length matters, because for signed integers, a number that starts with a 1 is negative, and you get the absolute value of that negative number by inverting the bits and adding one. (see: Two's Complement)

This is why 1010 1010 (as a byte) gives a positive number if you invert it, and why 0000 0000 1010 1010 gives a negative number if you invert it.

For reference, the integral types in SQL Server are:

  • tinyint: byte (1 byte) (8 bits) (unsigned)
  • smallint: word (2 bytes) (16 bits) (signed)
  • int: dword (4 bytes) (32 bits) (signed)
  • bigint: qword (8 bytes) (64 bits) (signed)

Note that while I described signed bytes, it looks like SQL Server does not have signed bytes at all, and does not have larger unsigned integer types at all. So if you use tinyint it will always be unsigned, and if you use anything larger, it will always be signed.

Upvotes: 3

Related Questions