Grant Fritchey (3/28/2013)
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.
But some root objects will change a lot, other root objects not at all. It's essentially impossible to handle that without fragmentation in the table. You could thus have extreme fragmentation in some areas of the table and virtually none or none in others. For example, newer rows would typically get updated much more frequently than very old rows.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.