Introduction to SIGN Function

SIGN Function is very basic function which is used to return value 1, -1 or 0. If value is negative it will return you negative -1 and if it is positive it will return you positive +1 and if value is 0 (zero) return 0(zero) value. Lets take the example as below

DECLARE @IntValue1 INT, @IntValue2 INT,@IntValue3 INT
DECLARE @DecValue1 DECIMAL(4,2), @DecValue2 DECIMAL(4,2),@DecValue3 DECIMAL(4,2)
SET @IntValue1 = 9; SET @IntValue2 = -9; SET @IntValue3 = 0;
SET @DecValue1 = 9.0; SET @DecValue2 = -9.0; SET @DecValue3 = 0.0;
SELECT SIGN(@IntValue1) IntValue1,SIGN(@IntValue2) IntValue2,SIGN(@IntValue3)IntValue3
SELECT SIGN(@DecValue1) DecValue1,SIGN(@DecValue2) DecValue2,SIGN(@DecValue3)DecValue3

The above function will give us following result set.

Output

IntValue1	IntValue2	IntValue3
1	-1	0

DecValue1	DecValue2	DecValue3
1	-1	0.00

You will notice that when there is positive value the function return positive values and if the values are negative it will return negative values. As the above result set you can also see that if the data type is INT the return value is INT and when the value passed to the function is Decimal the result also matches it. This is not true for every data type. This function is compatible with as below data type.

PassingDatatype --> ReturnDatatype

-------------------------------------------------------------------------------------

· bigint à bigint

· int/smallint/tinyint à int

· money/smallmoney à money

· numeric/decimal à numeric/decimal

· everything else à float

Now the question is that What will be the best example of the usage of this function. Answer is that you will not have to use the CASE Statement. Below is example of CASE Statement usage and the same replaced with SIGN function.

CREATE TABLE TestTable (Date1 SMALLDATETIME, Date2 SMALLDATETIME)
INSERT INTO TestTable (Date1, Date2)
SELECT '2012-06-23 17:15', '2012-06-20 17:15'
UNION ALL
SELECT '2012-06-24 17:15', '2012-06-23 17:15'
UNION ALL
SELECT '2012-06-23 17:15', '2012-06-23 17: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

The same output you will get from below query using Sign function

SELECT SIGN(DATEDIFF(d,Date1,Date2)) AS Col
FROM TestTable
GO
DROP TABLE TestTable
GO

This is a very small function, but useful in the above manner.

Cannot create index on view [ViewName]. It does not have a unique clustered index

If you are using View, you might have come across error message something like this:

Msg 1940, Level 16, State 1, Line 3

Cannot create index on view 'dbo.ViewName'. It does not have a unique clustered index.

This is a very common error while you are trying to create non clustered index on view. If you have view and you want to create Index on that view, that is fine but there is one prerequisite, you have to have one Unique Clustered Index in that view and then you will be able to create NonClustered Index on that view. You may face this error in any SQL Server version after SQL Server 2000.

Let us see it by example.

--create tbl_Employee table

create table tbl_Employee

(

EmployeeID int,

name varchar(50),

dept varchar(50),

company varchar(50)

)

GO

--create view on that table

create view dbo.vwEmployee

as

select name,dept,company from dbo.tbl_Employee

--check the view whether it is working

select * from vwEmployee

--create index on view

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (name,dept)

--as soon as you will try to create above index,

--you will get the below error.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'vwEmployee' because the view is not schema bound.

--reason of above error is, before creating index on view, View should be with schemabindings.

--let us drop view and recreate it.

drop view dbo.vwEmployee

go

create view dbo.vwEmployee

WITH SCHEMABINDING

as

select name,dept,company from dbo.tbl_Employee

GO

--now let us again create index.

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (name,dept)

go

--this time you will again getting different error (as below).

Msg 1940, Level 16, State 1, Line 2

Cannot create index on view 'dbo.vwEmployee'. It does not have a unique clustered index.

-- if you create unique clustered index first and then you will be able to --- create nonclustered index on view

CREATE unique CLUSTERED INDEX id_View_CLvwEmployee

ON dbo.vwEmployee (name,dept)

GO

--now create nonclustered index

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (company)

--select your view

Select * from vwEmployee