Thanks, a blindingly obvious solution, only you need to know about it. I didn't know you could filter the index because the last time I REALLY looked at an index, you couldn't and didn't encounter one using a where clause since then.

Since their cleanup process was obviously flawed, did they implement the suggestion and did they run into problems with the process if they did? Since the data is not unique to begin with how did you handle creating the index? Create a new table with the index and copy into it, then replace the table. Determine the unique rows, set everything else to 0, when it fails to create, try again?

Looked up something: The Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns.

It can't be clustered on a filtered index can it?