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.
You can not use unique index but trigger will be good for your case:
drop table test
create table test(ssn varchar(7))
alter trigger no_duplicates on test
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