Home > DeveloperSection > Articles > SIGN function in SQL SERVER

SIGN function in SQL SERVER


Database Database 
Ratings:
0 Comment(s)
 2519  View(s)
Rate this:

SIGN function in SQL SERVER

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


Don't want to miss updates? Please click the below button!

Follow MindStick