Introduction to SIGN Function

SIGN Function is very basic function which is used to return value 1, -1 or 0. If value is negative it will return you negative -1 and if it is positive it will return you positive +1 and if value is 0 (zero) return 0(zero) value. Lets take the example as below

DECLARE @IntValue1 INT, @IntValue2 INT,@IntValue3 INT
DECLARE @DecValue1 DECIMAL(4,2), @DecValue2 DECIMAL(4,2),@DecValue3 DECIMAL(4,2)
SET @IntValue1 = 9; SET @IntValue2 = -9; SET @IntValue3 = 0;
SET @DecValue1 = 9.0; SET @DecValue2 = -9.0; SET @DecValue3 = 0.0;
SELECT SIGN(@IntValue1) IntValue1,SIGN(@IntValue2) IntValue2,SIGN(@IntValue3)IntValue3
SELECT SIGN(@DecValue1) DecValue1,SIGN(@DecValue2) DecValue2,SIGN(@DecValue3)DecValue3

The above function will give us following result set.

Output

IntValue1	IntValue2	IntValue3
1	-1	0

DecValue1	DecValue2	DecValue3
1	-1	0.00

You will notice that when there is positive value the function return positive values and if the values are negative it will return negative values. As the above result set you can also see that if the data type is INT the return value is INT and when the value passed to the function is Decimal the result also matches it. This is not true for every data type. This function is compatible with as below data type.

PassingDatatype --> ReturnDatatype

-------------------------------------------------------------------------------------

· bigint à bigint

· int/smallint/tinyint à int

· money/smallmoney à money

· numeric/decimal à numeric/decimal

· everything else à float

Now the question is that What will be the best example of the usage of this function. Answer is that you will not have to use the CASE Statement. Below is example of CASE Statement usage and the same replaced with SIGN function.

CREATE TABLE TestTable (Date1 SMALLDATETIME, Date2 SMALLDATETIME)
INSERT INTO TestTable (Date1, Date2)
SELECT '2012-06-23 17:15', '2012-06-20 17:15'
UNION ALL
SELECT '2012-06-24 17:15', '2012-06-23 17:15'
UNION ALL
SELECT '2012-06-23 17:15', '2012-06-23 17:15'
GO
-- Using Case Statement
SELECT CASE
WHEN DATEDIFF(d,Date1,Date2) > 0 THEN 1
WHEN DATEDIFF(d,Date1,Date2) < 0 THEN -1
ELSE 0 END AS Col
FROM TestTable
GO

The same output you will get from below query using Sign function

SELECT SIGN(DATEDIFF(d,Date1,Date2)) AS Col
FROM TestTable
GO
DROP TABLE TestTable
GO

This is a very small function, but useful in the above manner.