In our Datawarehouse context, we have to update some record in fact tables inside our ETL.
One thing we did is to create nonclustered indexes just before the heaviest queries, and drop them afterwards. This lead us to much less time spent scanning tables in queries, and the time spent in building indexes has very low impact (1-2 minutes).
Is this a bad practice?
Note: We cannot partition tables right now.