This pops up every so often, as it does Here.  The post title is the DB2 syntax for achieving a unique index that allows for nulls.  I always felt that since Nulls are not equal to anything, not even themselves, then how could you get a dup key violation.   To be fair, even in DB2 you can not have a primary key that allows more than 1 Null.  But you can create a unique index in lieu of the the PK that will allow nulls.  This allows the ability to build non-identifying relationships, something I believe is lacking in SQL Server. 

In any event, there are all kinds of work arounds to do this, as David shows here.  Also, with my exposure to SQL Server, it is the general concensus to avoid nulls at all cost.  I was raised differently.  After reading Bonnie Bakers article, “Much Ado about Nulls” I am convinved more now than ever (well, ok, that was about 5 years ago).

Anyway, here's a method using a trigger where you can simulate this.

Update:  I forgot to add code to handle the update...duh.  The additional code is added in Purple for anyone who has already downloaded the code.  Or you can just cut and paste it all over.

Note:  With the new code added for the update, it's kind of a hack, since I'm using the identity column.  What I should really do, instead of using the surrogate, is to perform a logical update to the natural keys(Col1 and Col2) by performing a DELETE and a new INSERT operation.  This eliminates the dependancy on the surrogate.  But as an example, it serves it's purpose here.  Thanks for reading.

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (
 [id] int IDENTITY (1, 1) NOT NULL ,
 Col1 varchar (15) NULL ,
 Col2 varchar (14) NULL ,
)

CREATE INDEX myTable99_Col1 ON myTable99(Col1)
CREATE INDEX myTable99_Col2 ON myTable99(Col2)
GO

CREATE TRIGGER myTrigger99 ON myTable99
INSTEAD OF INSERT, UPDATE
AS
  BEGIN
 SET NOCOUNT ON
 IF EXISTS ( SELECT *
        FROM myTable99 t
  INNER JOIN inserted i
          ON t.Col1 = i.Col1
         AND t.Col2 = i.Col2
       WHERE i.Col1 IS NOT NULL
         AND i.Col2 IS NOT NULL)
 OR EXISTS ( SELECT *
        FROM myTable99 t
  INNER JOIN inserted i
          ON t.Col1 = i.Col1
       WHERE i.Col1 IS NOT NULL
         AND i.Col2 IS NULL AND t.Col2 IS NULL)
 OR EXISTS ( SELECT *
        FROM myTable99 t
  INNER JOIN inserted i
   ON t.Col2 = i.Col2
       WHERE i.Col1 IS NULL AND t.Col1 IS NULL
         AND i.Col2 IS NOT NULL)
   BEGIN
  RAISERROR ('Violation of Logical PRIMARY KEY constraint. Cannot insert duplicate key in object myTest99', 16, 1)
     ROLLBACK TRANSACTION
   END
 ELSE IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
  INSERT INTO myTable99(Col1, Col2) SELECT Col1, Col2 FROM inserted
 ELSE
  UPDATE t
     SET Col1=i.Col1
       , Col2=i.Col2
    FROM myTable99 t INNER JOIN inserted i
      ON t.[id] = i.[id]

 SET NOCOUNT OFF
  END
GO

INSERT INTO myTable99(Col1, Col2)
SELECT '1' , Null UNION ALL
SELECT '1' , '1'  UNION ALL
SELECT Null, '1'  UNION ALL
SELECT '2' , '2'  UNION ALL
SELECT '2' , Null UNION ALL
SELECT Null, '2'  UNION ALL
SELECT Null, '3'  UNION ALL
SELECT '3' , Null UNION ALL
SELECT '3' , '3'
GO

SELECT * FROM myTable99
GO

UPDATE myTable99 SET Col1 = 4 WHERE id = 9

UPDATE myTable99 SET Col2 = 4 WHERE id = 9

SELECT * FROM myTable99
GO

--Dup Keys

INSERT INTO myTable99(Col1, Col2)
SELECT '1', Null
GO

INSERT INTO myTable99(Col1, Col2)
SELECT '1', '1'
GO

INSERT INTO myTable99(Col1, Col2)
SELECT Null, '1'
GO

UPDATE myTable99 SET Col2 = 4 WHERE id = 9
GO

SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO