I recently had to mass-migrate all the indexes from a database to a new file group since we’d added some additional storage to our database server. I found this article at SQL Server Central (unfortunately, registration required, so I’ve included a copy of the original script in the download at the end). While it worked okay, there were some things I didn’t like about it:
- Assumed 90% index fill rate
- “Moved” indexes were all created as non-unique, regardless of original
- Fail during index creation left you without an index (drop and then create, with no rollback)
- Table was un-indexed during the move (index dropped and then created)
- Script re-created indexes without any “Included” columns, even if original index had them
To address these limitations, I rebuilt the process using that script as a starting point. The new script:
- Uses 90% fill rate by default, but if the original index had a different rate specified, it will use that
- Re-creates indexes as unique if the source index was unique
- Rollback problem resolved – new index is created with different name, old index is dropped, and then new index is renamed, all in a TRY-CATCH block
- Since the new index is created and then the old one dropped, table indexing remains “online” during the move
- Migrates “Included” columns in index
- Updated the script to use SYS views (breaks compatibility with SQL 2000, since SYS is 2005/2008/beyond only)
Download .SQL scripts (contains both Original and Modified scripts)