0

I have done a wholesale transfer of tables from Microsoft Access to SQL Server, and I am attempting to apply a unique index to a table field that, while it has some null values, has zero duplications among the values that are populated. Access handles this all the time without complaint, but SQL Server is setting up a conundrum, where the index is defined as allowing nulls, but returns an error message, saying that it can't build the index because it has a duplicate value of "Null". I have included an image of the creation of the index and its accompanying error message. Please help me resolve this issue. Thank you.

Error when creating image that includes nulls

PeterF
  • 37
  • 6

1 Answers1

1

Yeah, that is an issue where SQL Server is not following standard SQL practices. There is a solution, though. Use separate indices. You can FILTER indices.

  • A UNIQE index where the field is NOT NULL
  • A non unique index where the field IS NULL

Done.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • I thought I was creating a unique index where the field is not null. Could you please show an example of filtering an index? Thanks. – PeterF Jun 12 '20 at 17:47
  • Your solution calls for nothing short of a COWABUNGA HOORAH! The realization that, in order to make an index unique, I could FILTER it first came with ecstatic revelation. Knowing beforehand that all the populated fields have unique values, writing "(WebID is not Null)" in the Index Filter made the success of creating a unique index a foregone conclusion. The possibilities for what can be done with filtered indexes are staggering. To anyone else finding this post, I commend you to search on "Unique, Non-clustered, Filtered" and ground yourself in the technique. TomTom: my sincere thanks. – PeterF Jun 13 '20 at 05:07
  • To TomTom's point, seeing "Allow Nulls: Yes" led me to believe that the index itself handled Nulls, which is how it's done in Access. Allowing the consumer to dictate the terms of the filtered index is so much better! The filter can encompass any values the consumer, during precalculation, has designated as pre-eminent. To me, that is the gold standard of leveragable discernment. – PeterF Jun 13 '20 at 05:14
  • Yes, it is - welcome to your first step in REAL sql. There are a TON of gems like that hidden that most people never get to know and use. – TomTom Jun 13 '20 at 06:32