0

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?

John Woo
  • 258,903
  • 69
  • 498
  • 492
smwikipedia
  • 61,609
  • 92
  • 309
  • 482
  • What happened when you tried it? – Dan Bracuk Feb 18 '13 at 03:11
  • here, [>>Click here for DEMO<<](http://www.sqlfiddle.com/#!3/87e2b) *uncomment* the last line and build the schema again. see what happens. – 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 Answers1

9

If you're on 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 a 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.

Pang
  • 9,564
  • 146
  • 81
  • 122
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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