Multiple NULL values in column with UNIQUE index

Question

We have a table that has a unique index on a column that can accept null values. Problem is that we found out this structure can only accept one row with NULL value. If we try to add second row with NULL value we get an error like. “can not insert duplicate key row in object….”

Is there anything we can do to keep the index on this column and the ability to add NULL values to more than one row?

Index's should never be null, why do you need it to be null? — KevinA, Mar 04 '13 at 16:19
@KevinA: What do you mean, if I have a column that is nullable, I should not have indexes on it? Why not? — ypercubeᵀᴹ, Mar 04 '13 at 16:22

score 29 · Answer 1 · answered Mar 04 '13 at 16:19

Yes, you can use filtered index to support this. Just drop your existing index and create new index like this

CREATE UNIQUE INDEX Index_Name ON TableName(ColumnName)
WHERE ColumnName IS NOT NULL

This will allow you to have duplicates of NULL values. Here is an in depth article if you need more details on this.

http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/

Multiple NULL values in column with UNIQUE index

1 Answers1

Linked

Related