How To Create Unique Index In Ms Sql That Will Allow Null

How to create unique index in MS SQL that will allow NULL

Hello everybody,

today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null. 

How to achieve it? Following T-SQL can do this:

CREATE UNIQUE INDEX idx_studentcardid_notnull ON dbo.Students(studentcardid) WHERE studentcardid IS NOT NULL;


in this example MS SQL will create Unique index, but that index will be applied only to non null values, while null values will be ignored by WHERE condition.

Comments are closed