# SQL Puzzle – SELECT ~ 1 : Guess the Answer

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

• Fernando Carvalho
April 13, 2021 1:45 pm

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 :)

• Ashutosh Sharma
April 13, 2021 1:46 pm

~is NOT operator in most language and in here is does inverse of binary 01 to 10 which comes 2

• Ali
April 13, 2021 2:02 pm

The ~ operator evaluates the given value to bitwise NOT. And for plus one the complement is in fact minus two

• mctDBA
April 13, 2021 2:21 pm

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.

• Matthias Brehm
April 13, 2021 2:27 pm

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

• KK
April 13, 2021 3:40 pm

~ is bitwise not operator, ~1 becomes 11111….11110 which equals to -2 in two’s complement

• Garima Shrivastava
April 13, 2021 5:33 pm

~ applies -1- before the number passed and resulting number is displayed in result set.

• SQL Pirate
April 13, 2021 5:52 pm

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.

• Alex
April 13, 2021 6:34 pm

It’s because SELECT ~ 2 returns 1, naturally. :-) ~ is bitwise NOT.

• Mike
April 13, 2021 7:14 pm

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

• Gabriele
April 13, 2021 7:28 pm

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

• DashGandhi
April 13, 2021 8:28 pm

~ 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.

• Abhijat
April 13, 2021 8:49 pm

I can validate the result is correct, but not sure why. Its beyond my SQL knowledge. Please share yours to us.

• Sukhen Dass
April 13, 2021 9:55 pm

Hi,
~ means bitwise not means convert value to negation then add -1(for ~)
so ~-1 =0
Regards,
Sukhen Dass

• Santanu Bera
April 13, 2021 10:43 pm

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 :)

• Mehmet YAVUZ
April 14, 2021 12:45 am

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)

• Piyusha Mohanty
April 14, 2021 1:49 am

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.

• Ajay Gangwar
April 14, 2021 10:38 am

Waiting for the solution

• Avaneesh Bajoria
April 14, 2021 11:53 am

“~” 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.

• 