ScottPletcher (7/21/2016)
But, to me, there are exceptions. Such as when: (1) the only filtering is by active flag, and (2) there are a very high number of inactive rows, and (3) all (or nearly all) columns are being SELECTed -- as in this case (with my condition of #2 being required to cause the index change). Then the flag itself is as selective as it gets, and thus I would cluster on it, to seek to only the active rows. The active-row INSERT only happens once, as does the update to inactive, but row gets read 100Ks+ times. I guess we'll disagree on this one.
Heh... As always, I'll agree that "it depends". 🙂 The bottom line would be that a test needs to be done to ensure rapid fire inserts aren't going to be crushed.
On the other hand, it would be much more fun to have a pork chop dinner with the designer of such a table. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.