Allow OPTION(HASH GROUP) with SQLCLR UDAs by Bob Beauchemin

Currently, it appears that when SQLCLR UDA are used with a GROUP BY clause, a StreamAggregate operation must always be used. Attempting to specify OPTION(HASH GROUP) on a query like this:

SELECT dbo.SomeUDA(col1) FROM someTable GROUP BY someColumn OPTION(HASH GROUP)

produces an "Msg 8622, Query processor could not produce a query plan..."

I only have a single test case, but because there is no special considerations in the grouping column (someColumn) in this case, I can only guess its because you'd need to allocate N UDA instances where N is the number of groups.

This should be permitted (indeed a HashAggregate should be considered by the query optimizer), because, unless the appropriate index exists, an expensive sort operator will always be introduced, decreasing the performance of the UDA.