Most definitely.

Most common situation is a version-upgrade of the application where the schema is changing and data needs to be migrated (especially when a normalization-change is involved).

The standard pattern for this is to add one or more additional (physical or computed) columns to the old tables that provide some new key used to obtain info handy for the migration. Create an index on those new columns if they are going to be used to filter the old data for migration purposes. Then build the new tables from the old (here the indexes could seriously speed up the migration process).

BTW, all this could be part of the multi-step release patter described in Solomon's article "Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!" (http://www.sqlservercentral.com/articles/Data+Modeling/72814/). Where those additional columns/indexes are created in the pre-release (as a separate new table).

Another case is to investigate some bug and I want to analyse the data in ways never foreseen in original development. Some additional indexes can help playing with the data-query iteratively in a timely fashion (having to wait 10s of seconds for a result can be very annoying when you want to play with the query after seeing the results). I normally do this in a database copied to my local machine, since this sort of querying could seriously hamper the production tables (being locked for long periods).

Marco