SIGN is an in-build function of Transact-SQL. This is a mathematical function that returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Syntax

SIGN ( numeric_expression )

Example
DECLARE @value int

SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)AS 'RETURN VALUE'
      SELECT @value = @value + 1
   END

 Screen Shot

SIGN function in SQL SERVER

If data type is decimal

-- DATA TYPE DECIMAL

DECLARE @value DECIMAL(4,2)
SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)AS 'RETURN VALUE'
      SELECT @value = @value + 1
   END
 Screen Shot

SIGN function in SQL SERVER

Here we notice that when there is positive value the function gives positive values and if the values are negative it will return you negative values. Also we notice that if the data type is INT the return value is INT and when the value passed to the function is Numeric the result also matches it. Not every data type is compatible with this function.  Here is the quick look up of the return types.

1.       bigint -> bigint

2.       int/smallint/tinyint -> int

3.       money/smallmoney -> money

4.       numeric/decimal -> numeric/decimal

5.       everybody else -> float

  Modified On Nov-29-2017 09:31:27 AM

Leave Comment