Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!


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.

6 votes
Sign in
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 24

<=-=Jan 4 2011 4:40PM=-=>

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

<=-=Jan 4 2011 5:15PM=-=>

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,

<=-=Apr 5 2011 8:07AM=-=>

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 doesn’t work. Please fix that.

1 comment

Sign in
Sign in with: Microsoft
Signed in as (Sign out)

Feedback and Knowledge Base