Unique Index, but with nulls - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique Index, but with nulls

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-09-17 : 18:56:06

I'm have a feeling the answer to this no, but there is a lot I don't know about SQL Server, so who knows.

I would like to put a unique index on a table for column a and b, unless they are both null. I realize that calling it a 'unique index' when you can have multiple identical rows with nulls is inaccurate, but still, is there a way to do it?

Greg

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-17 : 19:18:44

Not in 2005, in 2008 you could do a filtered index.

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 19:33:48

you can do a workaround by means of check constraint though!

something like

ALTER TABLE tablename ADD CONSTRAINT Chk_UniqueData CHECK (dbo.CheckCount(a,b)<=1)

and make UDF like


CREATE FUNCTION CheckCount
(
@A int,
@B int
)
RETURNS int
AS
BEGIN
DECLARE @Cnt int

SELECT @Cnt= COALESCE(Cnt,0)
FROM (SELECT COUNT(1) AS Cnt
      FROM Table
      WHERE a=@A
      AND b=@B
     )t

RETURN(@Cnt)
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-18 : 00:04:10

You can accomplish this with a unique index on a view that excludes rows where A and B are both null:

CREATE TABLE tbl(a int null, b int null, otherCol varchar(10))
GO
create view dbo.tbl_unq with schemabinding as
select a,b,otherCol FROM dbo.tbl
WHERE not (a is null AND b is null)
GO
create unique clustered index unq_tbl_unq
on dbo.tbl_unq(a,b)
go

insert tbl values(1,1,'a')   --yes
insert tbl values(1,2,'a')   --yes
insert tbl values(1,1,'a')   --dupe
insert tbl values(null,1,'a')--yes
insert tbl values(null,1,'a')--dupe
insert tbl values(null,null,'a')--yes
insert tbl values(null,null,'a')--yes

This avoids the overhead of the check constraint, however if the view is dropped you lose the constraint.

Is there any specific business reason why you need those columns to be null, and both at the same time? As you mentioned it's a fundamental conflict with being unique, so which facet is more important?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-18 : 01:41:11

The above suggestion of Filtered Index is good. Something like:
CREATE NONCLUSTERED INDEX myIndex
ON Production.Invoices (a,b)
WHERE a IS NOT NULL and b IS NOT NULL

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

GregDDDD
Posting Yak Master

120 Posts

Posted - 2012-09-18 : 11:55:44

Thank you. All good suggestions.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources