Overhead with 2 unique indexes

[timscronin]

I have a 3rd party app which has a clustered unique index on 2 fields, and then another unique index on the same 2 fields are 3 others. I have been having some errors coming back from my monitoring software with this table. With 2 unique indexes aren't I asking for a lot of extra overhead with inserts/updates

 

[ptheriault]

That doesn't make sense having two unique indexes on the same fields?? Can you drop the non-clustered one? I would also think that would confuse the query optimizer. Which index would it know to use. Obviouly the clustered index would be better but could it use the non-clustered? If not, why bother wasting the space.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[timscronin]

Should have elaborated, my issues are with inserts. To me this is extra overhead on a multi million row table

 

[ptheriault]

To add a few more thoughts....the physical data is stored in the clustered index. The non-clustered index is just pointers to the clustered index. So yes, this is added overhead that is not buying you anything.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

will the vendor complain if you drop it?


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[timscronin]

Maybe, I refer to it as enlightment

 

[ptheriault]

Maybe they don't have to know... : )


- Paul
- If at first you don't succeed, find out if the loser gets anything.