0

I have a unique index on (id, name) columns. I have a date column that I want to add to the index since I want the uniqueness to be based on (id, name, date) columns. The date column contains a lot of null values. How would it affect the index?

Thom A
  • 88,727
  • 11
  • 45
  • 75
joemac12
  • 113
  • 1
  • 7
  • 2
    `NULL` would be treated as a value. So you couldn't have more than 1 row with the same value of `id` and `name` where the value of `date` was `NULL`. If you want `NULL` values to be ignored, you'd need to use a "filtered" unique index. – Thom A Oct 28 '21 at 14:11
  • 2
    Larnu has just written exactly what I was about to say, almost word for word, so I won't repeat it, but here's a fiddle that demonstrates it: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3d5362bd3e746656faa71b8c9345636c – GarethD Oct 28 '21 at 14:13
  • You can create an unique index that ignores null values, see the answer [here](https://stackoverflow.com/questions/31947263/duplicate-null-value-violation-on-unique-key-constraint-in-mssql/31949874#31949874). But in this case (more than one column in your index) it does means your combined uniqueness is not what you expect. The example of GarethD shows that perfect – GuidoG Oct 28 '21 at 14:16
  • Thanks for the replies! Very valuable information. Would changing the current index affect the performance of the index? – joemac12 Oct 28 '21 at 14:50

1 Answers1

0

If you are using SQL Server, so in SQL Server null values are not included in the index structure, But SQL Server has some new features, one of the filtering index. If a field has many null values so recommended creating an additional filtering index using where the field is null condition.

For more information about filtering index visit this link

Final result: You can do your add index operations comfortably, without problems, in many Databases null values don't affect performance.

Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8