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
In binary 1 is 00000001
So not 1 is 11111110
This is the two’s complement representation of -2 which is how computers store negative integers.
The tilde operator reverses the operand’s bits (i.e. it’s a bitwise NOT). Hence, for an operand of decimal 1 as a two-byte value, 00000001, the result is 11111110, or decimal -2.
It convert int value to hex, resulting in 0x00000001. next it applies bitwise “not” to it – resulting in 0xFFFFFFFE. next it converts it back to int – resulting in -2 (as 0xFFFFFFFF is -1 and so on)
It is Bitwise NOT and hence it results into -2
The binary representation of 1 is 0000 0000 0000 0001. Performing the bitwise NOT operation on this value produces the binary result 1111 1111 1111 1110, which is decimal -2.
“SELECT ~ 1” returns -2, why?
The tilde operator in SQL Server performs a bitwise “NOT” operation. In effect, it takes the binary representation of the operand and changes each 0 to a 1 and each 1 to a zero. The operation doesn’t care about the effect this operation has on the interpretation of the resulting set of bits.
Since there is no cast or convert operation specified, SQL Server will treat the 1 in this example as an integer, not a bit. In a Windows environment, a 4-byte integer value 1 is represented as 00000000 00000000 00000000 00000001. Under the covers, negative integers are stored in twos-complement notation.
The simplest way to understand twos-complement notation is to see how to change the sign of an integer value. For simplicity, I’m going to use a 1-byte integer but exactly the same method works with any byte size of integer. Let’s start with an integer value 1. In binary, that would be 00000001. The “rule” for switching the sign of an integer is to ‘flip’ the bits and add 1. To ‘flip’ the bits, change the 0s to 1s and the 1s to 0s. That gives us 11111110. Now add 1 (binary 00000001) and we get 11111111 which is the binary representation of -1. Note that this works the other way as well. Start with the binary representation of -1 (11111111), flip the bits (00000000) and add 1 to get positive 1 (00000001).
“SELECT ~ 1” flips the bits but does not add one so the result is and always will be -2.
~ is the bitwise NOT operator. It reverses the bits of the value. If you add the value and its ~ version you get a binary value that is all 1’s. Thatr is also the value for the integer -1. So for any value x, x + ~x = -1. Simple algebra gives that ~x = -x -1. If x is 1 then ~x is -1 -1 which is -2.
To see this more fully, look art this code which show the values of 1 and ~1 in hex and then in binary (as converted to character). I used smallint to keep the size manageable. BTW, since tinyint is not a signed value, you get 254 as the value for ~1.
declare @i smallint
set @i = 1
select cast (@i as varbinary(2)), cast (~@i as varbinary(2)),
CASE @i & 32768 WHEN 32768 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 16384 WHEN 16384 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 8192 WHEN 8192 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 4096 WHEN 4096 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 2048 WHEN 2048 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 1024 WHEN 1024 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 512 WHEN 512 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 256 WHEN 256 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 128 WHEN 128 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 64 WHEN 64 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 32 WHEN 32 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 16 WHEN 16 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 8 WHEN 8 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 4 WHEN 4 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 2 WHEN 2 THEN ‘1’ ELSE ‘0’ END
+CASE @i & 1 WHEN 1 THEN ‘1’ ELSE ‘0’ END
,
CASE ~@i & 32768 WHEN 32768 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 16384 WHEN 16384 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 8192 WHEN 8192 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 4096 WHEN 4096 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 2048 WHEN 2048 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 1024 WHEN 1024 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 512 WHEN 512 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 256 WHEN 256 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 128 WHEN 128 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 64 WHEN 64 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 32 WHEN 32 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 16 WHEN 16 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 8 WHEN 8 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 4 WHEN 4 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 2 WHEN 2 THEN ‘1’ ELSE ‘0’ END
+CASE ~@i & 1 WHEN 1 THEN ‘1’ ELSE ‘0’ END
~ is Bitwise operatior.
Its a bitwise NOT operation.
Binary of 1 is “0000000000000000000000000000000000000000000000000000000000000001”, so when apply NOT operation it becomes “1111111111111111111111111111111111111111111111111111111111111110” and it is -2 in decimal.
Operation support integer (i.e. 4 byte), hence value range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).
Select ~-2 will give you 1.
It gives previous number. i.e Select ~10 the result is -11 or Select ~100 result is -101
If you do Select ~~2 it gives 2.
~ is the bitwise NOT operator. ~1 is 111…[lots of]….10, which is -2 as a signed integer.
Didnt know that :) Nice click bait ;)
first shot:
~ (Bitwise NOT)
Which mean it made from 1 which is “01” in binary to “10” in binary which is 2 in decimal ;)
That’s a bitwise NOT operator
No idea and can’t find this anywhere when I search, good one Pinal!
The tilde complements all of the bits, so 0000 0000 0000 0001 >> 1111 1111 1111 1110, which would be 65,534, but it’s -2 for an integer. It’s easier to see when converting the 1 before the tilde:
SELECT ~ convert(bit, 1) >> answer is 0
SELECT ~ convert(tinyint, 1) >> answer is 254 (1111 1110)
SELECT ~ convert(int, 1) >> answer is -2
SELECT ~ convert(bigint, 1) >> answer is -2
I think it’s becous thats a Bitwise NOT operator since binary representation of 1 is 0001 that operator invert that representation to 1110 that’s -2
bitwise operation, adds 1 and logical not
That is because the two’s complement interpretation of the bitwise negation of the integer 1 is -2 (0xFFFFFFFE). This is not a special SQL Server behavior but applies to any system that uses two’s complement for representation of negative integers.
Because (int) 1 is “0000 0000 0000 0001” and apply xor operator you get “1111 1111 1111 1101” and it equal (int) -2
~ is the bitwise NOT operator. Represent 1 as binary, then flip all the bits. The resultant binary in decimal is -2.