I would guess that about 50-75% of our SQL use is to fill Datasets on the .NET side of things. We once had a talented DBA who suggested we use temporary indexes on a number of our SQL queries used to fill these Datasets in an effort in increase performance. We gave it a try...

What we found was that on queries that fill large Datasets, we did see some improvement, but on smaller (or some medium) fills, the temporary indexes were actually slowing performance. We were able to increase performance there by making a number of these indexes permanent so we avoided the create-on-the-fly element involved.

What is interesting about all this is that in "our world" we have to do a bit of experimenting each time we get into these situations. For example, sometimes its better to take just a plain old 'give me the data' Dataset fill, and then play with code on the .NET side to get whatever result we might be after - where other times (especially in reporting) we find its better to work on the SQL side and tweak around with temporary indexes and in some cases temporary tables/cursors to maximize performance.

I think temporary indexes have their place, but like most 'get the data' situations in software development, they are not the be-all-end-all answer to performance. They can be in individual cases, but it really depends on what the end goal is, and that takes some experimenting to determine.