пришлось сегодня столкнуться:

"I created a unique constraint on a column called Col1 in table Test1 to enforce uniqueness with the exception of NULL. The entry for "Unique Constraint" in SQL Server Books Online (BOL) specifically says that you can create a unique constraint on columns that allow NULL. However, as soon as I add data and try to enter a second record with NULL in Col1, I receive a message that says I've violated the unique constraint. As I understand unique constraints (as opposed to unique indexes), they allow duplicate NULLS. Am I correct?

BOL can be misleading on this topic. The correct answer is no: The unique constraint doesn't let you add multiple records that contain a value of NULL.

Here's why I believe the reference in BOL is misleading. In the "Unique Constraint" entry, BOL says, "UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values." In addition, the ANSI SQL-92 standard says that NULL isn't equal to NULL—in other words, the expression (1=1) evaluates to true, whereas (NULL = NULL) evaluates to false—so it seems reasonable to assume that the unique constraint would allow multiple values of NULL. However, keep in mind that you use a standard unique index to physically enforce a unique constraint. The "Create Index" section of BOL says, "Multiple NULL values are considered duplicates when a UNIQUE index is created."

In general, a value of NULL isn't equal to NULL, but two different NULL values are treated as equal for the purpose of enforcing a unique index. Alternatively, you could easily use a set of UPDATE and INSERT triggers to create an index that would allow just one NULL value in the set of composite keys for the index."

какая теперь разница, что пишет BOL
я просто наткнулся на это в базе, когда понадобилось создать UNIQUE constraint
SQL не дался..а таблице было много NULLs
завтра жду ответ от разработчиков.....

триггер проблематично
во всей базе нет ни одного триггира, так он разработана...
т.е идеалогия такая - без триггеров
мы можем внести лишь небольшие изменения
скорее всего будем через sp_

какая теперь разница что в BOL написано?
заказчику же не BOL продаем ;-)