Add a unique constraint to a nullable column

Enforcing uniqueness is a simple matter of adding a UNIQUE constraint with the following code:

ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT UNQ__TableName__ColumnName UNIQUE ([ColumnName]);

But if your column includes null values, then multiple nulls are considered non-unique and they are not allowed.
SQL Server 2008 offers something called 'filtered indexes' which are able to enforce uniqueness on all values except nulls.

CREATE UNIQUE INDEX indexName
ON tableName(columnName) WHERE columnName IS NOT NULL;

Basically, a filtered index creates an index on a filtered subset of rows, improving query performance and reducing index storage costs, as well as providing a nifty solution to the problem of enforcing uniqueness on a non-primary key field.

Note that it is possible to add a unique constraint to an existing field which may already contain non-unique values. Use the WITH NO CHECK command to prevent the constraint being applied to existing fields. Check out MSDN for more information.