I read some article about the subject...
and specifically for the
CREATE .... WITH (DROP_EXISTING = ON)
This means the non clustered indexes only have to be updated once (to include the new key column).
Not twice - first to use the physical rid and then again to use the new CI key.
The DROP_EXISTING clause tells SQL Server that the existing clustered index is being dropped but that a new one will be added in its place, letting SQL Server defer updating the nonclustered index until the new clustered index is in place..
Additionally, SQL Server won't rebuild the nonclustered index at all if the clustered index key doesn't change and is defined as UNIQUE, which isn't an obvious performance benefit of defining a clustered index as UNIQUE
But moving it to another filegroup, there are always the same benefits or the rule only applies to the same filegroup.
So, applying the syntax that I have reported, is it correct then act on clustered and then to the non-clustered ones, since I will not physically delete them before recreating them?
Thanks all, Alen