How does index fragmentation happen over time, and how do you address it?

As data is inserted, updated, and deleted, an index's logical order can become increasingly out of sync with its physical storage order, called fragmentation, which can slow down queries that rely on scanning the index, and is typically addressed by periodically reorganizing lightly fragmented indexes or fully rebuilding heavily fragmented ones.

-- Reorganize for lighter fragmentation
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;

-- Rebuild for heavier fragmentation
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;