SQL Server - Unique index to allow multiple NULL

UNIQUE Constraint/Index :

Both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness.

Unlike PRIMARY KEY constraints, UNIQUE constraints allow the value NULL,however only one null value is allowed per column. 

--Example for unique index to allow multiple NULL

CREATE TABLE Unique_Duplicate (Col1 INT,Col2 VARCHAR(100))
GO
CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 IS NOT NULL
GO
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
GO
SELECT * FROM Unique_Duplicate
GO
---------------------------------------------------------
--Example Unique index to allow one particular duplicate value


DROP INDEX Unq_Nci_idx ON Unique_Duplicate
GO
CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 <> 1
GO
INSERT INTO Unique_Duplicate VALUES (1,'Unique index to allow one particular duplicate value')
INSERT INTO Unique_Duplicate VALUES (1,'Unique index to allow one particular duplicate value')
GO
SELECT * FROM Unique_Duplicate
GO
--DROP TABLE Unique_Duplicate

No comments: