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.

SQL Puzzle - SELECT ~ 1 : Guess the Answer tilde-800x245

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.

SQL Puzzle - SELECT ~ 1 : Guess the Answer selecttilde1

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)

Previous Post
Solve Puzzle about Data type – SQL in Sixty Seconds #108
Next Post
SQL Puzzle – Unsolved CASE Expression

Related Posts

123 Comments. Leave new

  • 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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Reply
  • Hetalkumar Kachhadiya
    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’

    Please improve the answer if needed.

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

    Reply
  • 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)

    Reply
  • Because tilde character (~) is the bitwise NOT operator in T-SQL.

    Reply
  • minus 1 from the negative version of the number entered, can see a use for this though?

    Reply
  • I found that tilde (~) means NOT. So I think that the result is two’s complement.
    1 represents 0001 and then its two’s complement 1110 represents -2.

    Reply

Leave a Reply

Menu