This question already has an answer here:
这个问题在这里已有答案：
- How to create a unique index on a NULL column? 4 answers
- 如何在NULL列上创建唯一索引？ 4个答案
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….”
我们有一个表在列上具有唯一索引，可以接受空值。问题是我们发现这个结构只能接受一行具有NULL值。如果我们尝试添加NULL值的第二行，我们会得到一个错误。 “无法在对象中插入重复的键行...”。
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?
我们可以做些什么来保持此列上的索引以及将NULL值添加到多行的能力？
1 个解决方案
#1
28
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.
这将允许您具有NULL值的重复项。如果您需要更多详细信息，请参阅此深入文章。
http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/
#1
28
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.
这将允许您具有NULL值的重复项。如果您需要更多详细信息，请参阅此深入文章。
http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/