Ensure Uniqueness of Nullable Columns

alter-table.sql

ALTER TABLE dbo.Employee
    DROP    Constraint UQ_Employee_SSN
GO


ALTER TABLE dbo.Employee
  ADD SSN_CMPTD AS
    CASE
      WHEN SSN IS NOT NULL
        THEN SSN
      ELSE
        CAST( Id AS Char(10) )
    END
GO

ALTER TABLE dbo.Employee
  ADD CONSTRAINT  UQ_Employee_SSN_CMPTD UNIQUE(SSN_CMPTD)
GO

employee.sql

CREATE  TABLE   dbo.Employee(
      Id          BIGINT          NOT NULL    IDENTITY
          CONSTRAINT PK_Employee_Id Primary KEY
  ,   FirstName   nVarChar(50)    NOT NULL
  ,   LastName    nVarChar(100)   NOT NULL
  ,   SSN         Char(10)            NULL
          CONSTRAINT UQ_Employee_SSN UNIQUE
)
ON [PRIMARY]
GO

error.bash

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_Employee_SSN'. Cannot insert duplicate key in object 'dbo.Employee'. The duplicate key value is (<NULL>).

filtered-index.sql

ALTER TABLE dbo.Employee
    DROP CONSTRAINT UQ_Employee_SSN_CMPTD
GO

ALTER TABLE dbo.Employee
    DROP   COLUMN SSN_CMPTD
GO


CREATE UNIQUE NONCLUSTERED INDEX Idx_Employee_SSN
    ON  dbo.Employee( SSN )
WHERE   SSN IS NOT NULL
GO

insert.sql

INSERT INTO dbo.Employee( FirstName , LastName , SSN )
    VALUES( 'Jane' , 'Doe' , NULL )

INSERT INTO dbo.Employee( FirstName , LastName , SSN )
    VALUES( 'John' , 'Doe' , NULL )