Recently someone pointed out that SQL Server allows duplicate indexes and they weren't sure why this was the case. The need, or benefits/disadvantages of duplicate indexes has been debated, but I haven't really seen a case or reason why duplicate indexes are allowed.
There's a Connect item on this open, and the feedback from Microsoft says there are no advantages and potential disadvantages, but it's in there for backwards compatibility. Personally I'm not sure why you need this for compatability. If you removed duplicates, wouldn't the remaining index be used? Could you just ignore some index hint if the index was gone?
In any case, perhaps there is some reason that I haven't heard yet, so this Friday's poll:
Do We Need Duplicate Indexes?
Is there some case where duplicates make sense? I'd argue that non-clustered and clustered indexes with the same keys are not duplicates and there could be cases where you'd want that. If you had one index with ASC and one with DESC, are they the same? I'd argue no.
I've heard the the new filtered indexes might make do with the same columns and a different filter, but wouldn't those indexes be different?
I'm stumped. Can anyone come up with a reason to have duplicate indexes?
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 13.3MB WMV
- iPod Video Podcast - 12.3MB MP4
- MP3 Audio Podcast - 2.6MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.