Multiple NULLs with Unique Constraints

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. (See also, "Unique Constraints with Multiple NULLs," March 2008).

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.