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

Status : 


Sign in
to vote
ID 629920 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 12/8/2010 4:35:40 PM
Access Restriction Public


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. 
Sign in to post a comment.
Posted by Ivan.Zlatanov on 4/5/2011 at 8:07 AM
I can confirm that this is happening to us as well. We needed a custom aggregate function, and we were very surprise to find out that there was an unnecessary sort happening that was hogging the query. It's very unfortunate that the OPTION( HASH GROUP ) doesn't work. Please fix that.
Posted by Bob Beauchemin on 1/4/2011 at 5:15 PM
Sure Eric, there are a few that I can think of. This actually came about because of the following forum question: I realized that the sort was required because of choice of stream aggregate. And he can't put on every index possible to get rid of the sort.

Second is that the spatial aggregates in Denali would need this funtionality. Related to that is that there's a vendor product that consists of a library of UDAs, Fuzzy Logix ( that could benefit from this flexibility as well.

Hope this helps,
Cheers, and thanks,
Posted by Microsoft on 1/4/2011 at 4:40 PM
Hi Bob,

Thanks for the feedback. We'll consider fixing this in a future release. Can you tell me more about the scenario? What UDAgg were you implementing and why? Feel free to contact me by email if you want.

Best regards,
Eric Hanson
Program Manager, SQL Server Query Processing