I have a table with many records. Column X is nullable and a lot of records have NULL in this column. Can I create a UNIQUE non-clustered index on this column? Aren't those null values violating the unique constraint?
Asked
Active
Viewed 5,122 times
0
-
What happened when you tried it? – Dan Bracuk Feb 18 '13 at 03:11
-
here, [>>Click here for DEMO< – John Woo Feb 18 '13 at 03:15
-
[How to create a unique index on a NULL column?](http://stackoverflow.com/questions/191421/how-to-create-a-unique-index-on-a-null-column?rq=1) – peterm Feb 18 '13 at 03:16
-
Check this for workaround - http://www.databasejournal.com/features/mssql/article.php/3711501/UNIQUE-Column-with-multiple-NULL-values.htm – rs. Feb 18 '13 at 03:20
1 Answers
9
If you're no SQL Server 2008 or newer - you can set up a filtered, unique index to exclude / ignore all the NULL values.
CREATE UNIQUE NONCLUSTERED INDEX uixYourIndexName
ON dbo.YourTableName(YourColumnName)
WHERE YourColumnName IS NOT NULL
That would essentially "filter out" all NULL values - not including them into the index at all. Any query that uses the same WHERE clause can use this filtered index to find those rows.
Without the filtered index, you could not create an UNIQUE INDEX on your column since with a UNIQUE index, you're only allowed to have a single row that has NULL in that column.
marc_s
- 704,970
- 168
- 1,303
- 1,425
-
Does this exclude rows with a null value, or exclude null values when creating the index? IE with rows `1, 2, null` and `1, 2, null`, does this violate the constraint for third column `IS NOT NULL` or not? – Captain Prinny Mar 09 '21 at 21:29
-
@CaptainPrinny: the `WHERE` clause just skips any rows that match the condition - so here, any row with `NULL` in the `YourColumnName` will **not** be added to the index and cannot be found by using that index – marc_s Mar 09 '21 at 21:31