Multiple NULL values in column with UNIQUE index [duplicate]

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
– KevinA, Commented Mar 4, 2013 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ᵀᴹ
– ypercubeᵀᴹ, Commented Mar 4, 2013 at 16:22

Djordje Kujundzic · Accepted Answer · 2013-03-04 16:19:19Z

30

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/

answered Mar 4, 2013 at 16:19

Djordje Kujundzic

1,0088 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple NULL values in column with UNIQUE index [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related