While waiting for an index rebuild process to complete a client of mine Comprehensive Database Performance Health Check recently asked me if I have any puzzles to discuss. Of course, I have one where you have to guess the answer for a simple select statement. Let us see if you can guess it correctly or not.
Before running the following select statement in SSMS, guess the answer.
SELECT ~ 1
Please note that right before 1 sign there is Tilde sign and not a minus sign.
Guess the Answer
Now, run the same select statement in any version of SQL Server Management Studio.
The answer is -2, now my question is that why the query above gives us an answer as -2 and not any other values. Please leave your answer in the comments section. I am very curious to know if you can find the answer to the same. I will publish answer to this puzzle sometime next week.
Technology Online
I hope you find these Learning paths helpful. If you have a Pluralsight subscription, you can watch it for free. If you do not have a Pluralsight subscription, you can still watch the course for FREE by signing up for a trial account. Please note that you do not need any credit card.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
124 Comments. Leave new
~(Bitwise NOT)
~1 is -2
in U2
dec 0001
NOT 1110
U1 1110
(-2^3)+2^2+2^1 = -8+4+2 = -2
The binary of 1 is 01 and its negation is 10 which is 2
~ is a bitwise not operator for select ~ 1, its values would be -2
for 1 = 00000000000000000000000000000001 (32 bit )
for ~1 = 11111111111111111111111111111110
so here most significant bit is 1 so number would be negative
so lets calculate ~1 – we have to do a 2s compliment of 1
00000000000000000000000000000001
+ 1
—————————————————-
00000000000000000000000000000010
which represents 2 , hence ~1 is -2
Because ~ makes a bitwise logical not operation with the representation of the number.
1 on binary = 0000 0000 0000 0001
performing the bitwise not operation produces
1111 1111 1111 1110
wich in decimal from signed 2’s complement is equal to -2
The tilde is a bitwise NOT operator. The sign bit would turn it negative. So 001 would then be 110 which is negative 2. The size defaults to what is needed to store 1, 1 bit. But it is also defaulting to to signed. Two’s complement of 1 as one signed bit means that negative zero is -2.
Bitwise NOT Operator
This state performs a bitwise logical NOT operation on an integer value
~ = Tilde character is the bitwise not; so decimal 1 or binary 0000 0001 bitwise not 1111 1110 that is -2 decimal as two complements
The Tilde sign ia a operator that perform a bitwise logical NOT operation on the number. The number 1 is rapresent as binary signed: 0000000000000001
The bitwise NOT operation change ones to zeros and zeros to ones so the result is the binary signed number: 1111111111111110 and this number rapresent the decimal number -2
Bitwise logical NOT
Bitwise NOT operator – reverses bits (so from 1 to -2): from 0000000000000000000000000001 to 11111111111111111111111111111110
Hi got given below answer when we run “select ~1 ” output “18446744073709551614”
I am guessing it is based on register size of the hose machine ?
Why? Because it is a size constrained operation. smallint, 16 bits, produces the same results as not specifying data size. Same for int.
SELECT ~ 1 gives -2
SELECT ~ 2 gives -3
SELECT ~ 3 gives -4
SELECT ~ cast(1 as tinyint) gives 254 a positive number instead of zero
SELECT ~ cast(1 as bit) gives 0 which is expected. there is only one bit to flip.
SELECT ~ cast(1 as smallint) gives -2 same for int and big int. Optimizer knows all 64 or 128 bits will not be used.
Bitwise Not
The ~ performs a bitwise NOT operation on the 1 (binary 0001) converting it into binary 254 (1111 1111 1111 1110) or a signed binary of -2.
Its working on logic of -(X+1)
According to some error messages, int is the default data size or 64 bits for what I am running on older i7 pro.
Which begs the question – why the other bits are not flipped ? Why does it act like an older 16 bit processor ?
From Sybase / SQL origins on os/2 using the new 16 bit processors ?
32 bit processors were out in 85 and common by 1989
SELECT ~ returns the inverse value minus 1
I guess 1 as a signed integer is 001 and the ~ provides the negative of that which is 110 which is -2 if the first digit is the sign.
Hmmm I guess -2 is the next negative number not greater than -1