Problem
I have a column in one of the tables in a SQL Server database where the values must be unique. This column can also contain null values. Now, if I add a unique index on this column, it ensures that all values must be unique in the column (and indexes them too), but, that includes null values too. This means that the column can contain only one null value (at most) i.e. duplicate nulls are not allowed! The problem is how to allow multiple null values in a column with unique index?
Solution
The solution is to use Filtered Index on the column with the filter to allow null values. Filtered Indexes are good for performance as they use a filter predicate to index only a subset of rows in the table. And in this scenario they are also helpful in ignoring null values and indexing rest of the rows.
How to create a Filtered Index to allow multiple Null Values?
CREATE NONCLUSTERED INDEX {Index Name}
ON {Table} ({column})
WHERE {column} IS NOT NULL ;
Subscribe