>>I will try and get the figures (BTW select was 10 times faster, not 100 as above). Our tests were on multiple tables of over five million rows in each.
>
>
>Wow! That is definitely worth hearing about.
>
>Peter

I've spoken to one of the others involved in this test and it transpires that there was an index on DELETED() on these tables (actually, on every table in the building!). This slows things down. Off the top of our heads we recalled that this was slowing things down. Select was taking 16 seconds to return a data set, seek and scan was taking about 1.5 seconds to do same. Set deleted off returned the set in 0.2 seconds, moving clauses between WHERE and HAVING improved slightly again.

I will try to get the figures for you. It seems like, on large tables, some indexes are transferred to your local drive BEFORE the select is done - I think this is an optimization feature.