Say you have a column in a SQL Server table that allows NULL values. You want this column to be unique when it has a value other than NULL. What’s the best way to program this behavior?

SQL Server has no built-in mechanism to prohibit duplicates exceptNULLs, so you need to implement a custom CHECK constraint to enforce this restriction. For example, the following code snippet enforces exactly this kind the kind of integrity:

 USE tempdbCREATE table t1 (c1 int NULL, c2 char(5) NULL)CREATE trigger mytrigger on t1 for insert, update asBEGIN       IF (select max(cnt) from (select count(i.c1)as cnt from t1, inserted i where t1.c1=i.c1 groupby i.c1) x) > 1       ROLLBACK TRANEND