ScottPletcher (3/28/2013)
There must be more fragmentation in a history table than a current-data-only table.For single row lookups, this is not really a performance hit.
But for table scans, even partial ones, it will be significant. Whether or not it will actually be noticeable depends on your environment: if you have extra capacity in your system to handle the added overhead, you may not notice the performance degradation. You could reduce fillfactor to reduce fragmentation, but that causes potential "dead space" issues, so it's not a panacea.
Some, but what we found was the if the clustered index was built right, then the storage was pretty consistent. The key structure had two values, the identity of the root object and the version of that object. That very evenly distributed the data in a known pattern. It really did work well and it's been going for about eight years now. I haven't seen it personally in the last two, but it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning