Difference between SIGN(@Value) * @Value vs. ABS(@Value) function

Question

Hi All,

I was recently reviewing some code, and I came across this construct:

case when sign(@value) * @value  > 100 then null else sign(@value) * @value end   

I'm wondering, is this more efficient than

case when ABS(@value)  > 100 then null else ABS(@value) end   

Is there any difference? Performance? Mathematically?

Just curious!

Thanks,
--Dan

Answer 1

Hi,

As much as mathematically speaking SIGN(@value) * @value will return the same as ABS(@value)

Regarding performance, I am not sure that you can notice difference in the client side even if there was any different algorithm behind he scenes.

---

By the way, Theoretically using the function SIGN on DECIMAL is totally different from using on INTEGER.

(Level 550 internals discussion) Behind the scene SQL Server stores negative DECIMAL in a ones' complement system while negative INTEGER are stored on Two's complement. In short this means that the binary value of negative DECIMAL is exactly like the positive value except one bit, while negative value of INTEGER looks totally different.

For example: The value which is stored for the number one when using data type SMALLINT is 0000000000000001, but the value for minus ONE is 1111111111111111

SELECT CONVERT(BINARY(2), CONVERT(SMALLINT,1))
--   1 :   0x0001   :    0000000000000001
SELECT CONVERT(BINARY(2), CONVERT(SMALLINT,-1))
--  -1 :   0xFFFF   :    1111111111111111

You can read more about what Two's complement means here: https://en.wikipedia.org/wiki/Two's_complement

And the value which is stored for the number one when using data type DECIMAL:

SELECT CONVERT(BINARY(7), CONVERT(DECIMAL(5,2),1))
--   1 :   0x05020001640000
SELECT CONVERT(BINARY(7), CONVERT(DECIMAL(5,2),-1))
--  -1 :   0x05020000640000

-->> First byte on the left is the precision (5) and the second byte is the scale (2)
-- These two bytes are not store in a table!
-- They are only used when using the value on the fly.
-- The number value starts from byte 3

-->> SQL Server stores the decimal number (the integer part and the fraction part) without seperating of the decimal separator. For example the number 1.23 will be stored exactly like the number 123
-- Using DECIMAL(5,2) the number 1 is the same as 1.00 which means that SQL Server stores 100

--> 100 in binary is 64 in Hex
--> The number 1 before the "64" in 0x05020001640000 indicate that this is positive number

Therefore,

In both cases (INT and DECIMAL) we can find the sign of the number by reading a single bit but changing the SIGN of DECIMAL is done simply by changing one bit which indicate that this value is negative while changing the SIGN on INTEGER means that we need to calculate the entire BIT value. Using ABS(@Value) we can have one calculation but using sign(@value) * @value we need to find the SIGN and calculate the change in sign.

Answer 2

Zhou and Pituach,

Thank you for these very detailed explanations. Way more detailed than I expected!

The reason I asked was because the fragment of code I referenced executes very frequently.

The use of sign(@value) * @value seems obscure to me. My rule of thumb, especially if there is no significant performance penalty, is to opt for code readability over clever SQL tricks.

The use of ABS(@value) seems more logical in this case than sign(@value) * @value.

Since there seems no good reason to use SIGN in this case, I would have opted for ABS.

Thanks again,

--Dan

Answer 3

Hi，@Abrams, Dan
Welcome to Microsoft T-SQL Q&A Forum!

First, the sign() function is a sign used in SQL to return numbers, this function will return one of the following:

1. If number > 0, return 1
2. If number = 0, return 0
3. If number < 0, return -1
   Look at your case statement, it is judging the variable with our sign to determine the positive and negative. For the use of this function, you can refer to the example code given by the official documentation: DECLARE @value REAL
   SET @value = -1
   WHILE @value < 2
   BEGIN
   SELECT SIGN(@value)
   SET NOCOUNT ON
   SELECT @value = @value + 1
   SET NOCOUNT OFF
   END
   SET NOCOUNT OFF
   GO

In this document, the value of @value is judged in turn, and the three cases mentioned above are used, which should be easy for you to understand.

Second, the ABS() function returns the absolute value of a number, characteristic :

1. This function is used to find the absolute value of the specified number.
2. This function accepts a single parameter.
3. The accepted argument is a numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.
4. The data type of the return value will be the same as the numeric data type of the specified parameter.
   Please see this example: DECLARE @Parameter_Value float;
   SET @Parameter_Value = -34.87;
   SELECT ABS(@Parameter_Value);

The output is the absolute value of -34.87, while retaining the same numeric data type floating point 34.869999999999997.

When we understand the use of these two functions, let's analyze the code.
If our value is a negative number, sign(@value) becomes a positive number at this time, because it is a negative number.
-->According to the characteristics of sign, the multiplication of two negative numbers is a positive number, which is equivalent to the absolute value of the returned value below. had the same effect.
-->At the same time, regardless of whether the value is positive or negative or 0, the effect achieved is the same, so to sum up, these two structures achieve the same effect, and I personally prefer the first one, because the readability of the function is still Easy to understand is better than absolute value.
If your code is very short and easy to understand, the second one is also a good choice, I hope it can help you.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.