Recreating cluster index to another filegroup with DROP_EXISTING = ON

Question

Hi all,
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

Answer 1

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?

When you rebuild the clustered index with DROP_EXISTING=ON and the key definition is the same, the non-clustered indexes are not touched because the row locator (clustered index key plus uniqueifier when needed) does not change. The behavior is the same with or without a different filegroup.