Try, Catch… Finally. Some .NET, some SQL, and whatever else

Migrate database indexes to a new file group

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)