SQL SERVER – Introduction to Function SIGN

Yesterday I received an email from a friend asking how do SIGN function works. Well SIGN Function is very fundamental function. It will return the value 1, -1 or 0. If your value is negative it will return you negative -1 and if it is positive it will return you positive +1. Let us start with a simple small example.

DECLARE @IntVal1 INT, @IntVal2 INT,@IntVal3 INT
DECLARE
@NumVal1 DECIMAL(4,2), @NumVal2 DECIMAL(4,2),@NumVal3 DECIMAL(4,2)
SET @IntVal1 = 9; SET @IntVal2 = -9; SET @IntVal3 = 0;
SET @NumVal1 = 9.0; SET @NumVal2 = -9.0; SET @NumVal3 = 0.0;
SELECT SIGN(@IntVal1) IntVal1,SIGN(@IntVal2) IntVal2,SIGN(@IntVal3) IntVal3
SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal3) NumVal3  

The above function will give us following result set.

SQL SERVER - Introduction to Function SIGN signfunction1

You will 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 you will 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 datatype is compatible with this function.  Here is the quick look up of the return types.

  • bigint -> bigint
  • int/smallint/tinyint -> int
  • money/smallmoney -> money
  • numeric/decimal -> numeric/decimal
  • everybody else -> float
What will be the best example of the usage of this function that you will not have to use the CASE Statement. Here is example of CASE Statement usage and the same replaced with SIGN function.

USE tempdb
GO
CREATE TABLE TestTable (Date1 SMALLDATETIME, Date2 SMALLDATETIME)
INSERT INTO TestTable (Date1, Date2)
SELECT '2012-06-22 16:15', '2012-06-20 16:15'
UNION ALL
SELECT '2012-06-24 16:15', '2012-06-22 16:15'
UNION ALL
SELECT '2012-06-22 16:15', '2012-06-22 16: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
-- Using SIGN Function
SELECT SIGN(DATEDIFF(d,Date1,Date2)) AS Col
FROM TestTable
GO
DROP TABLE TestTable
GO

SQL SERVER - Introduction to Function SIGN signfunction2

This was interesting blog post for me to write. Let me know your opinion.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Follow up – Usage of $rowguid and $IDENTITY
Next Post
SQL SERVER – Download SQL Server Product Documentation

Related Posts

18 Comments. Leave new

  • Excellent post. It’s impressive how SIGN allows one to write much less code to get the same output.

    Reply
  • Sign() can also be used it calculate an equivalent alternative to the ABS() function. Note that the last two columns have the same values.:

    USE tempdb
    CREATE TABLE T ( N INT )
    INSERT INTO T VALUES ( -42), (-11), (0), (NULL), (17), (2)
    SELECT N,
    N * SIGN(N) AS NxSignN,
    ABS(N) AS AbsN
    FROM T
    DROP TABLE T
    GO

    Reply
  • Michael Emerick
    June 24, 2012 10:11 pm

    Think you might have a small typo, though the results you have portrayed are correct.

    This:
    SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal2) NumVal3

    Should be:
    SELECT SIGN(@NumVal1) NumVal1,SIGN(@NumVal2) NumVal2,SIGN(@NumVal3) NumVal3

    As always thanks so much for all of your posts, they have helped me come a long way!

    Reply
  • Thanks for writing about this function. I have learnt a new function. But seems you have a typo in the second SELECT statement:
    SIGN(@NumVal2) NumVal3 => SIGN(@NumVal3) NumVal3

    Reply
  • Sign can also be use in
    i)When we have to find if table contain any rowcount or not.
    Select sign(@@rowcount)
    Select sign(count(*) from emp
    ii) IsNullOrEmpty(equivalent function of c#)

    CREATE FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
    RETURNS BIT
    AS
    BEGIN

    return SIGN(LEN(isnull(@text,”)))

    END

    GO

    Reply
  • Result for NumVal3 should be 0.00 in the screenshot :)

    Reply
  • My I suggest another correction: NumVal3 value in the screenshot should be 0.00 :)

    Reply
  • There is a kind of “inverse” function of the SIGN function? For example, if the value is a positive number, it returns -1, and if it is a negative number, it returns 1.

    Reply
  • Brilliant.

    I have to run a report where all values must be positive. Now, instead of:

    UPDATE MyTable
    SET Amount =
    CASE
    WHEN Amount < 0 THEN
    (Amount * -1) * Currency
    ELSE
    Amount * Currency
    END

    I can use:

    UPDATE MyTable
    SET Amount = (Amount * SIGN(Amount)) * Currency

    Seems to run quicker too. Any comment on that?

    Reply
  • Hi! This is SQL statement found in SQL Profiler..

    SELECT SUM(amount_book * -SIGN(cleared_type -0.5)) FROM cminpdtl WHERE date_applied <= 734760 AND cash_acct_code = '15080202ADMNHQIP002'

    May I know what is the use of -SIGN(fieldname -0.5)?
    Why must use -0.5 and not just the fieldname will do? cleared_type is just a value either 0 or 1.

    Thank you very much in advance!

    Reply
  • SUM( DECODE( SIGN(DAYDIFF(TransactionReceivableDetail.Receivable.AssignedPayment.CreatedOn,TempAttributesRescission.RescissionPaymentAsofDate)),1,0,TransactionReceivableDetail.Receivable.AssignedPayment.PaymentAmount))

    this query is returning a null value can u explain

    Reply
  • Hi Pinal,

    Thanks but how can we use (‘^’) sign in sql server like I want to calculate below formula in sql.

    Cost price x (1- Dep Rate%) ^ (Date of WDV Calculation – Date of Purchase + 1)/365

    In SQL 2008 I couldn’t use this formula directly.

    Can you please help me how can we use above formula in SQL 2008 ?

    Thanks,
    Sagar

    Reply

Leave a Reply

Menu