I'm weighing in on this discussion later on because I really do believe that dynamic index creation is a necessity in some circumstances.

In my case I am processing reasonably large amounts of historical data (3.5mm rows) for a virtual data warehouse and I use an SP to handle repeated update statements.

The problem is that every time an update statement is executed in such a batch, it performs an "index update" on all indexes that are impacted by set clause of the statement. That is a big performance hit. And so I found the process runs a lot faster if all indexes are dropped as of the start. Then I create the ones I need only when they are needed.

The design schema is fairly good and so there are no other visible areas for improvement that I can see other than indexes-on-the-fly.

Just wanted to put in my 2 cents about it, because it seems like the whole concept of dynamic indexing is getting short shrift in the SQL forums in general.