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
The tilde character (~) is used for the bitwise NOT operator. But I am not quite sure why it is returning -2. Waiting to know the answer
~ inverts the bits of your number.
Assume 8 bits for simplicity:
1d = 00000001
~1 = 11111110 = -2d
A bitwise NOT operation on a two’s complement number will have this effect, IIRC (and some Googling seems to bear that memory out).
~ in math is and approximation 1 is approximately 2, 1 ~ 2, Kind of like close counts in horseshoes.
Had to research this myself and learned that the tilde (~) is a unary operator. It flips the bits of its operand.
Therefore,
1 = 0x00000001
~1 = 0xFFFFFFFE
The 0xFFFFFFFE reads as -2 in base 10.
select top 100
[number]
,BitwiseNOTNumber = ~[number]
,VarbinaryNumber = CAST([number] as varbinary)
,VarbinaryBitwiseNOTNumber = CAST((~[number]) as varbinary)
from master.dbo.spt_values
where [type] = ‘P’
Also, noticed what you did there in the first paragraph, play on the word “not.”
:)
SELECT ~ 1
~ = Bit wise NOT
0000 0001 = 1
1111 1110 = Bit wise NOT of 1
0000 0001 = 1 Binary of value of 1
0000 0000 = 0 Binary of value of 0
1111 1111 = -1 Binary of value of -1
1111 1110 = -2 Binary of value of -2
The ~ is a bitwise NOT operator so it flips (1s become 0s and 0s become 1s) the bits. The 1 is 0000 0001 in binary and ~1 becomes 1111 1110 which is -2. I am just using a byte size integer, larger sizes would just have more 0 and 1s but still end up as -2 in this case.
~ (tilde) is bitwise NOT operator
The binary value for 1 is manipulated with ~ which shows binary value 2
Tilde is the bitwise NOT operator in SQL Server. The representation of -2 for NOT 1 is the binary inverse of 1.
The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.
select ~ 0 = -1
Select ~ 1 = -2
~ is a binary negation, so all bits of right hand side operand are flipped. So for example, using datatype tinyint (any integer works):
1: 0000 0001
~1: 1111 1110
This negated number will always be -2, because how ints work in 2s complement. First bit is sign (0-pos, 1-neg), the rest is actual number.
Both positive and negative numbers are arranged in order, which means the highest negative whole number (-1) has all bits set, and the second highest negative number has all but the rightmost bit set. So a negated 1 is always -2.
Seems to be a shorthand for adding 1 to the number then reverses the sign
e.g. SELECT ~ -555 returns 554
e.g. select ~ 555 returns -556
Two’s complement :)
You made me remember… the times of two’s complement
thank you for your witty mind, Pinal
~ is bitwise logical NOT so 1 in binary is 01 , applying NOT will give us 10 which is 2 in decimal
Honestly, I had not used this operator (~) before, so I looked it up in the official help, and I see that it is an operator that “Performs a bitwise logical NOT operation on an integer value.”, Which can be used in various data types, I did tests with INT and BIT, but I am still the same, not knowing why it is doing this:
SELECT 0, ~ CAST( 0 AS BIT ) UNION
SELECT 1, ~ CAST( 1 AS BIT )
SELECT 1, ~ 1, ‘0001’, ‘0010’ UNION
SELECT 2, ~ 2, ‘0010’, ‘0011’ UNION
SELECT 3, ~ 3, ‘0011’, ‘0100’ UNION
SELECT 4, ~ 4, ‘0100’, ‘0101’
Further research and found that it’s a bitwise NOT, reversing 0’s to 1’s and 1’s to 0’s.
The tilde symbol performs a bitwise logical NOT operation on an integer value. So for 1 if we do a bitwise logical NOT operation it returns -2 which we are seeing in the output. This can be done with programmer calculator (or) we have convert the decimal to binary -> then do bitwise not operation -> convert that into decimal.
~ is a Bitwise Not operator in SQL. So for 1 the equivalent binary value is 0000000000000001 and exact bitwise not is 1111111111111110 which is -2 in decimal