How to create a UNIQUE Key on a Column which is having multiple NULL values?

Question

In SQL Server, Unique Key Constraint will allow only one NULL value. But there are situations that are more than one null value in the column, but still have to maintain Uniqueness by ignoring all null values.

Answer 1

Method 1
Only works on SQL Server 2008 and above
Using Filter Index. Filtered index is used to Index a portion of rows in a table. While creating an index, we can specify conditional statements. The below SQL Query will create a Unique Index on the rows having non null values:

CREATE UNIQUE INDEX IX_ClientMaster_ClientCode ON ClientMaster(ClientCode)
WHERE ClientCode IS NOT NULL

Method 2
Create a view having the unique fields and create a Unique Clustered Index on it:

Create View vClientMaster_forIndex
With SchemaBinding
As
Select ClientCode From dbo.ClientMaster Where ClientCode IS NOT NULL;
Go

CREATE Unique Clustered Index UK_vClientMaster_ForIndex
on vClientMaster_forIndex(ClientCode)

Method 3
Create a Computed Column like the following and create a UNIQUE KEY on that:

[CMP_ClientCode]  AS (case when [ClientCode] IS NULL _
then CONVERT([varchar](10),[ClientID],0) else [ClientCode] end)