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 value of 1 as int16 in binary = 0000 0000 0000 0001
The value of ~1 = 1111 1111 1111 1110 =-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.
+1 in binary is represented as 00000001. Using “~” inverts the the bits which would give you 11111110 (i.e. -2 in binary).
This is because SQL server converts 1 to a tinyint and perform bitwise NOT operation resulting 11111110 that is -2.
~ is the bitwise not operator. So NOT(1) bitwise is -2.
The bitwise operator is flipping the binary values. If you enter ~170 you will get back -171. It is taking the binary for 170 of 0000 0000 1010 1010 and flipping each 1 to a 0 and 0 to a 1: 1111 1111 0101 0101
Tilde being a bitwise Not operator will give you a binary opposite value. So 1 will be -2 and ~0 will be -1.
the tilde “~” is the Bitwise NOT operator – it reverses the bits. If the bit in 1 are reversed it becomes
0x0000000000000000000000000000000000000000000000000000FFFFFFFE
in INT, that’s – 2
It’s performing a Bitwise NOT operation. It changes zeroes to ones.
It’s performing a bitwise operation on the binary representation of 1 and returning the decimal representation of that result.
-2 is the binary counterpart of +1 (1111 1110 versus 0000 0001)
Performs a bitwise logical NOT operation on an integer value. 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.
Hah! Tricky one… ;-)
Well, the binary value of 1 = 0000000000000001. With the tilde (~) you signal that you want the inverted version, so that’s 1111111111111110. Binary value 1111111111111110 back to an integer gives -2.
I understand how it works and even why it works… But I cannot imagine when I would ever use this one!
Cheers,
Andre
Tilde character (~) is the Bitwise “NOT” Operator in T-SQL . The binary representation of 1 is 0000 0000 0000 0001 and bitwise NOT is 1111 1111 1111 1110 which is in decimal -2
This is a bitwise NOT operation and as I understand it since it looks like SQL Server is using a signed integer here you will get this (x = -x – 1) That is what I was able to determine based on my research.
Hello.
The tilde inverts the binary value of 1 (0001) to -2 (1110). The tilde is a logical “NOT” for the select statement.
The ~ (tilde) character is the bitwise not operator, so it takes the binary equivalent of 1 and then inverts the bits, so it goes from “00000000000000000000000000000001” to “11111111111111111111111111111110” and when you convert the result back to integer, it equals -2.
Well Pinal,
Do you know without checking up on it how much is “select ~-0” ?
select ~2, ~1, ~0, ~-0, ~-1, ~-2
Easy enough to check what ~ do on a serie.
Nice ;)
Bitwise not operator : ~ 0000 0001 = 1111 1110
-2