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)
123 Comments. Leave new
Tilde operator “~” performs a bitwise logical NOT operation.
Basically it’s inverting all 0 into 1 on the binary level, so, if integer 1 is binary “00000001” if inverted it would be “11111110”, which is the binary representation of -2.
Nice post :)
~is NOT operator in most language and in here is does inverse of binary 01 to 10 which comes 2
The ~ operator evaluates the given value to bitwise NOT. And for plus one the complement is in fact minus two
Tilde is the symbol for the bitwise NOT operator, which takes each bit and flips 1s to zeroes and 0s to ones. The expression, in this case, 1, is probably being interpreted as one of the INTs that can hold -ve numbers, as the positive\negative bit is being flipped to negative. What puzzles me is why the answer isn’t a much larger negative number :)
Great puzzle.
It’s because 1 is of INT data type by default. INT is signed (-2^31 to 2^31).
The ~ operator performs a bitwise NOT operation:
0000 … 0001 becomes 1111 … 1110 which represents a -2 in decimal.
In contrast, TINTINT is unsigned (0 to 255). Therefore the following statement returns 254:
SELECT ~ (CAST 1 as tinyint)
0000 0001 = 1 dec
1111 1110 = 254 dec
~ is bitwise not operator, ~1 becomes 11111….11110 which equals to -2 in two’s complement
~ applies -1- before the number passed and resulting number is displayed in result set.
The tilde is a bitwise NOT operator. This means that the binary value of decimal 1 (which is also 1) is inverted, resulting in a 64 bit binary value of all ones, except for the last bit, which denotes decimal -2.
It’s because SELECT ~ 2 returns 1, naturally. :-) ~ is bitwise NOT.
This is due to the signed bit of numbers. Example: if we were working in a signed 4-bit system the left most bit would be work -8. So, 1000 would = -8. If the left most bit is a 1 your final answer is negative. 1011 = -5. The ~ symbol (a NOT operator) swaps each bit. So, in the example of why ~1 = -2 we simply look at the bits: 1 = 0001 and ~1 = 1110 ==> -8 + 4 + 2 = -2
The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn.
-1 converted in binary bits is 0xFFFFFFFF = 11111111 11111111 11111111 11111111
Performing the bitwise NOT operation on this value produces the binary result 0xFFFFFFFE = 11111111 11111111 11111111 11111110, which is decimal -2
~ is a Bitwise Not operator, which reverses the bits of the integer operand. ie It gives 1’s compliment of the number. Because of the way SQL Server (and many other) stores negative numbers) ie Negation of a number is 2’s compliment of the number (which is nothing but adding 1 to the 1’s compliment)
Therefore, if A is the number.
Since ~A + 1 = -A, therefore ~A = -(A +1)
Hence ~1 = -(1+1) = -2.
I can validate the result is correct, but not sure why. Its beyond my SQL knowledge. Please share yours to us.
Hi,
~ means bitwise not means convert value to negation then add -1(for ~)
so ~-1 =0
Regards,
Sukhen Dass
This is due to the two’s complement.
if we convert 1 to two’s complement it becomes 0000 0001. With the tilde sign it becomes the inverse i.e. 1111 1110, which represents -2. Hope I explained this correctly :)
Hi, this is besause ~ is the “bitwise not” operator in T-SQL.If we get Two’s complement of 1 (001) it makes -2 (110)
Tilde(~) is a bitwise not operator. The binary representation of 1 is 01 and applying the bitwise not operator the value becomes 10 which represents -2.
Waiting for the solution
“~” or “Tilde” is the Bitwise NOT Operator in t-sql which reverses the bit value (0 or 1 to 1 or 0 respectively). simply putting, it replaces “0” with “1” and “1” with “0”. So,
SELECT ~ convert(bit,1)
would fetch “0”
AND
SELECT ~ convert(bit,0)
would fetch “1”
but since in the following query
SELECT ~ 1
“1” is an integer (decimal) which has base 10, and its binary (signed 2’s complement) equivalent value is “0000000000000001”. Now, due to “~”, “0” replaces “1” and “1” replaces “0” which leads to “1111111111111110” which is the Binary signed 2’s complement of
“-2”. Hence the answer.
~ stands for (-1)-