Unique Column That Allows Multiple Nulls

Sometimes it may be necessary for a column to hold unique values, but still allow multiple nulls. When this scenario occures then a Unique Index will not suffice as all values within the column MUST be unique, meaning it will only allow a single null value.

One solution is to include an INSERT, UPDATE trigger on the table that checks the value being inserted/updated to ensure it is unique, but will ignore null values. The following trigger does the trick:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TG_TRIGGER_NAME] ON [dbo].[TABLE_NAME]
  FOR INSERT, UPDATE
AS
  BEGIN
    SET NOCOUNT ON ;
    IF EXISTS ( select  1
                from    inserted AS i
                        inner join dbo.TABLE_NAME AS t on i.[COLUMN_NAME] = t.[COLUMN_NAME]
                group by t.[COLUMN_NAME]
                having  count(t.[COLUMN_NAME]) > 1 ) 
      BEGIN
        ROLLBACK
        RAISERROR ('[COLUMN_NAME] must be unique', 16, 1)
      END
  END

Replace the values [TG_TRIGGER_NAME], [TABLE_NAME], [COLUMN_NAME] to match the names used within your database.