NJ-DBA (9/3/2010)
IF you drop and recreate a clustered index, nonclustered indexes are also recreated.
If you drop and recreate a clustered index, all nonclustered indexes are recreated twice. Once when the cluster is dropped, once when it's recreated
Typically, I do this: Script all indexes, drop all nonclustered indexes, drop clustered index, create clustered index, create nonclustered indexes....
Fine if you're doing batch loads or data and don't want the overhead of the indexes during the load process. Utterly silly if all you're trying to do is defragment indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability