Make SQL Server CLR aggregate similar to native aggregates

Question

I'm comparing my custom CLR aggregate vs AVG (SQL Server 2017). My queries are:

  SELECT groupId, Helpers.CustomCLR(value)
  FROM table
 group by groupId

  SELECT groupId, AVG(value)
  FROM table
 group by groupId

And CLR is

    [Serializable]
    [SqlUserDefinedAggregate(
        Format.Native, //use clr serialization to serialize the intermediate result  
        IsInvariantToNulls = true, //optimizer property  
        IsInvariantToDuplicates = false, //optimizer property  
        IsInvariantToOrder = true)
]
    [StructLayout(LayoutKind.Sequential)]
    public class CustomCLR
    {
        float a = 2;
        public void Init()
        {
        }

        public void Accumulate(SqlSingle value)
        {
        }

        public void Merge(CustomCLR other)
        {
        }

        public double? Terminate()
        {
            return a;
        }
    }

Execution plans are quite different, though. CLR query does row mode sorting and AVG query does batch mode hash match. How to make CLR aggregate behave like AVG one?

Answer 1

There are definitely some unfortunate differences between built-in and SQLCLR User-Defined Aggregate functions (UDA). One of them should be that SQLCLR cannot do batch mode. I will see if I can find an authoritative reference for this.

Another difference is that SQLCLR UDAs do not support the HashAggregate operator, resulting in:

CLR Aggregate performs an expensive sort

I just tested again in SQL Server 2017 CU 12 and SQL Server 2019 CTP 2.2 and it is still an issue. Please see Bob Beauchemin's post (in that linked forum thread) on Thursday, December 9, 2010 for some suggested work-arounds.

Please also support Bob's enhancement request to allow SQLCLR UDAs to use OPTION(HASH GROUP):

Allow OPTION(HASH GROUP) with SQLCLR UDAs