Microsoft
MicrosoftHi Jeff_Moden , AlexOnData - Very happy to see the discussion here, wanted to add my 2cents...
As per Azure Synapse - I get requests from time to time about that, unfortunately this is not specifically suitable for Synapse, it has some different strategy which is not in the scope of this script.
As per Rebuilding vs Reorganizing vs preventing fragmentation - no discussion about the best option is to prevent, however some other scenarios would perfectly fit for rebuild or reorganize, I saw Jeff_Moden's approach, it's very good approach as long as the schema supports that, if not (and for many customers it's not) they still benefit from reorganizing their indexes.
on top of that like AlexOnData rightly mentioned that the main focus today should be in statistics maintenance, while the impact of fragmented indexes is not as critical as it was in the past or specifically with on-prem magnetic disks.
It's not a bad thing to keep the indexes de-fragmented, however, when they do get fragmented it does not necessarily impact the business application.
Thank you for being active and sharing your insights here.
Yochanan.