SQL SERVER – Introduction to Function SIGN
- by pinaldave
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(@NumVal2) 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)
Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
