Ken Lee-263418 (1/4/2012)
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?
Clean up of the existing data would have been easy enough. For each ID with multiple actives, keep the one with the latest data (there were easy ways to determine that based on audit trails), and set the rest to 0. Then add the filtered unique index to prevent future issues.
Would cause whatever flawed code was creating the duplicates in the first place to start throwing errors. But that's a good thing long-term, because they had no faintest clue what code was causing the violations, so errors in production were the only way they'd ever find it.
I don't know if they implemented my recommendations or not, since I left that place before anything was done on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon