Sounds very much like 80% 20% story. 80% that was easy to do is done and now 20% that is complex and progress is slow is left to be done. Sounds very familiar from the comment in plan cache invalidation :)
I've pretty much finished the project I got a bee in my bonnet about last week, which is to teach SELECT DISTINCT how to (optionally) use hashing for grouping in the same way that GROUP BY has been able to do for awhile.
There are still two places in the system that hard-wire the use of sorting for duplicate elimination:
* Set operations (UNION/INTERSECT/EXCEPT)
* Aggregate functions with DISTINCT
I'm thinking of trying to fix set operations before I leave this topic, but I'm not sure it's worth the trouble to change DISTINCT aggregates. They'd be a lot more work (since there's no executor infrastructure in place that could be used) and the return on investment seems low.
