Check for Duplicates Help

I have a table that has an ssn column that is nullable. I want to allow duplicate null values but if someone tries to insert or update th column with a value that is not null I need to check to see if the value already exists and if so generate an error.

Can anyone tell or better yet provide an example of the best way to do this. I'm guessing a trigger but I'm pretty green when it comes to writing efficient triggers.

Thanx in advance!!!You can not use unique index but trigger will be good for your case:

drop table test
go
create table test(ssn varchar(7))
go
alter trigger no_duplicates on test
for insert,update
as
if exists(select ssn from test
where ssn in(select ssn from inserted)
and ssn is not null
group by ssn having count(*)>1)begin
raiserror('duplicates!!!',0,1)
rollback tran
end|||Thanks a bunch snail!!!|||No kidding?

DB2 has something SQL Server doesn't?

UNIQUE WHERE NOT NULL

Nothing like that here?

I'm shocked!|||that's an extension for fat ibm dba's