According to ANSI standards a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In SQL Server a single NULL is allowed but multiple NULL values are not.
In SQL Server 2008, it is possible to define a unique filtered index based on a predicate that excludes NULLS:
CREATE UNIQUE NONCLUSTERED INDEX ix_Mycolumn_notnull
WHERE Mycolumn IS NOT NULL;