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

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)

## SQL Authority News – Training SQL Server Query Optimization And Performance Tuning

• Integer data types in SQL are signed datatypes, except tinyint. These data types lets us represent positive and negative numbers.

In a binary notation, the bit furthest to the left is called the most significant bit (MSB), it gives the sign of the number (sign bit) , 0 for positive and 1 for negative.

In binary, if we want to represent a negative number we use the twos’ complement value. To convert from positive to negative or the opposite way, a bitwise NOT or tilde ~ can be applied to a number. As result 1 (0000 0001) is converted to -2 (1111 1110).

128 64 32 16 8 4 2 1
1 1 1 1 1 1 1 0
-128 + 64 + 32 + 16 + 8 + 4 + 2 + 0 = -2

To change the sign of a number we just invert the bits and add 1. In the above example we just need to add 1 to become -1.
e.g. select ~70 + 1 returns -70 as result.

Decimal Two’s-complement
value representation
0 0000 0000
1 0000 0001
2 0000 0010
126 0111 1110
127 0111 1111
−128 1000 0000
−127 1000 0001
−126 1000 0010
−2 1111 1110
−1 1111 1111

• The ~ is the one’s complement of a number in SQL.

• this is doing a bitwise not operation on decimal number 1,
which is stored as 0000000000000001 in binary.
The result is 1111111111111110, which is -2 in decimal

• Tilde (~) is a bitwise Not Operator.
It works in Signed binary format and whenever the we use this operator it inverts all digit in binary.
best part number changes from positive to negative and negative to positive. It is because of MSB(Most significant bit) or may called left most digit which is 0 for positive and 1 for negative.

• ~ is the bitwise not operator. This operators flips all bits, from 0 to 1 and 1 to 0. 1 binary representation is 00000000000000000000000000000001, the bitwise not of this value is 11111111111111111111111111111110, which is -2 in IEEE 754 standard for integers.

• Tilde will return the Two’s Complement of the binary version of the integer so “Select ~8” 8 being 0000000000000000000000000000000000000000000000000000000000001000 will return 1111111111111111111111111111111111111111111111111111111111110111 which represents -9.

The twos complement will increase the number by 1 and invert it’s sign.

• Mahesh Ravipati
April 13, 2021 4:11 am

~ is BITWISE NOT operator –> it will perform logical NOT operation on bit values of the integer

Binary value for 1 = 0000 0001; ~1 will produce the value as 1111 1110 which is decimal equivalent to -2

• Arnošt Kobylka
April 13, 2021 4:41 am

~ is bitwise NOT operator. The binary representation of int is 0x00000001. The ~0x00000001 = 0xFFFFFFFE = -2.

Generally because x + ~x = 0xFFFFFFFF = -1 (the missing 1 bits from x are in the ~x) –> ~x = -1-x for all signed int types (smallint, bigint and int).

The tinyint is unsigned so there is rule: x+~x = 0xFF = 255 (= maxtinyint) –> ~x = 255 – x

• The ~ operator does a bitwise not on integers, so 1 as a set of bits is 0000 0000 0000 0001 now a bitwise not is simply flipping 0’s to 1’s and 1’s to 0’s so we get 1111 1111 1111 1110 which is the number -2

• ~ is the bitwise NOT operator.

1 is interpreted as an integer by default and such has the HEX representation of 0x00000001.
Flipping this with the not operator we get 0xFFFFFFFE.

As a check, If you first cast 1 as a tinyint you get 0x01 (0000 0001) as the HEX value, flipping this would Give 0xFE (1111 1110) and return 254.

• Binary Value of +1 = 01
Has tilde is used as not operator above binary Value will be converted to 10 which is 2 and positive sign converts to negative.
So -2

Not sure under the woods this is logic in sql server management.

Thanks for the quiz

• Tilde will return the Two’s Complement of the binary version of the integer so “Select
~8” with 8 being 0000000000000000000000000000000000000000000000000000000000001000
will return 1111111111111111111111111111111111111111111111111111111111110111 which represents -9.

The twos complement will increase the number by 1 and invert it’s sign.

• The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. The binary representation of 1 is 0001. Performing the bitwise NOT operation on this value produces the binary result 0010, which is decimal -2.

• It’s working on logic of -(X+1)

April 13, 2021 12:04 pm

Tilde character (~) is the Bitwise NOT Operator in T-SQL. 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.

The binary expression value of ‘1’ is ‘0000 0000 0000 0001’. When Bitwise NOT Operator is applied on this binary value the new value becomes ‘1111 1111 1111 1110’. And this new value represents the decimal ‘-2’

• Somendra Kanaujia
April 13, 2021 12:25 pm

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.

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

The following example creates a table using the int data type to store the values and inserts the two values into one row.

CREATE TABLE bitwise (
a_int_value INT NOT NULL,
b_int_value INT NOT NULL);
GO
INSERT bitwise VALUES (170, 75);
GO

The following query performs the bitwise NOT on the a_int_value and b_int_value columns.
SELECT ~ a_int_value, ~ b_int_value
FROM bitwise;

Here is the result set:
-171 -76

(1 row(s) affected)