How to create a unique index on a NULL column?

Question

I am using SQL Server 2005. I want to constrain the values in a column to be unique while allowing NULLS.

My current solution involves a unique index on a view like so:

CREATE VIEW vw_unq WITH SCHEMABINDING AS

    SELECT Column1

      FROM MyTable

     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

Any better ideas?

Answer 1

If you are using SQL Server 2008, then create a filtered index.

 For detailed information refer to this: http://msdn.microsoft.com/en-us/library/cc280372.aspx. 

Another solution is to use a trigger to check uniqueness, but this may affect the performance.