For some obscure reason Sql Server does not allow multiple NULL values on columns with UNIQUE constraints. This is in conflict with ANSI standards and also very contra-intuitive.
If you think this is poorly designed, you’re not alone: http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values
However, there is a way to create a constraint that allows multiple NULL values rather easily (this uses “filtered constraints”, introduced in Sql Server 2008):
CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull ON YourTable(yourcolumn) WHERE yourcolumn IS NOT NULL;