Clustered Index fragmentation vs Index with Included columns fragmentation
Your questions:
Doesn't an Index with included columns have the exact same problem?
Yes.
Is a table with included columns not just the same as a &quot;shadow table&quot; with the same fragmentation problems?
Yes
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
I would, yes.
How to prevent fragmentation?
There are a couple of different types of fragmentation to consider. One is when you only have part of your pages being used because you’ve had page splits. If you have a lot of inserts, this will happen. Don’t stress too much. The other is when you have pages where the subsequent page is in a different extent. Again, I wouldn’t worry too much. If your data is mostly in the buffer cache, it doesn’t really matter if it moves across extents.
So... don’t worry about it too much. But don’t bother having a complete copy of the data in a way that you won’t actually be querying it.