Best way to explain this is to use an example, a very simple example.
CREATE TABLE Agenda (
AgendaId [int] IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50),
LastName VARCHAR(50),
ExternalId INT,
CONSTRAINT [PK_Agenda] PRIMARY KEY CLUSTERED ([AgendaId] ASC))
AgendaId column is the primary key of the table and ExternalId is a column that link to an external system. For some of the records, ExternalId field is NULL (synchronization not made with the external system). Our job is to make sure that values from our columns are unique ignoring of course NULL values. Let's insert some data into our table.
INSERT INTO Agenda
SELECT 'John', 'Doe', 100
UNION
SELECT 'Cameron', 'Smith', NULL
UNION
SELECT 'Ken', 'McGregor', NULL
UNION
SELECT 'Judy', 'Knight', 200
UNION
SELECT 'Jan', 'McQueen', 300
Let's try to create the UNIQUE index on ExternalId column:
CREATE UNIQUE NONCLUSTERED INDEX [MyExternalId] ON [dbo].[Agenda]
([ExternalId] ASC)
-- Error message:
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Agenda' and the index name 'MyExternalId'. The duplicate key value is (NULL).
Next step is to create a calculated column (please note the use of our primary key), as follows:
ALTER TABLE Agenda ADD ExternalIdUnique AS
(CASE WHEN ExternalId IS NULL THEN AgendaId ELSE ExternalId END)
There is no problem to create the unique index on the calculated column:
CREATE UNIQUE NONCLUSTERED INDEX [MyExternalId] ON [dbo].[Agenda]
([ExternalIdUnique] ASC)
-- Test the index...
INSERT INTO Agenda VALUES ('test', 'failed', 100)
GO
INSERT INTO Agenda VALUES ('OK', 'PASSED', NULL)
GO
Basically, that's all, pretty simple.