Make sure all non-empty (non-null) values are unique

Q: A column of my SQL Server table allows null values. I want the column to be unique when its value is not NULL. How can you implement this behavior programmatically? If you set a unique constraint on the column, I can only include a record with a null value. I'm using triggers to implement this constraint, can you recommend a simpler way to guarantee that all non-null values are unique?

A: SQL Server does not have an internal mechanism to implement non-null value uniqueness, so you need to implement this mechanism through custom check constraints. For example, the following section of the encoding implements the functionality you need:

Use tempdb
CREATE table T1 (c1 int NULL, C2 char (5) NULL)
CREATE trigger Mytrigger on T1 for INSERT, update as
BEGIN
IF (select Max (CNT) from (select count (I.C1)
As CNT from T1, inserted I where T1.C1=I.C1 group
by I.C1) x > 1
ROLLBACK TRAN
End

In SQL Server 2000, you can also use the instead OF triggers to implement this functionality. For more information about instead OF triggers, see the following articles. To access these articles, visit the SQL Server Magazine Web site, enter the InstantDoc number in the InstantDoc box, and then click Go. Related articles are as follows:

Tricks with INSTEAD of triggers; InstantDoc number 15828
INSTEAD of triggers on views; InstantDoc number 15791
INSTEAD of triggers; InstantDoc number 15524

-sql Server MVPs