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.

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

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

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

About these ads

19 thoughts on “SQL SERVER – Introduction to Function SIGN

  1. 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

    Like

  2. 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!

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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.

    Like

  6. 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?

    Like

  7. 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!

    Like

  8. 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

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

  10. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s