SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.MyTable (MyColumn varchar(100) NULL,
                          INDEX UQ_MyColumn UNIQUE (MyColumn) WHERE MyColumn IS NOT NULL);
INSERT INTO dbo.MyTable (MyColumn)
VALUES('a'),(NULL),(NULL),('b'); --Works
4 rows affected

INSERT INTO dbo.MyTable (MyColumn)
VALUES('a'); --Fails
Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'UQ_MyColumn'. The duplicate key value is (a).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

DROP TABLE dbo.MyTable;