SqlUserDefinedAggregate.IsNullIfEmpty: Does it work?

No. At least for me. I haven’t made this one work at all and I always have to resort to a dirty workaround. According to MSDN documentation, setting this property to true should direct your aggregate to return SQL-null when applied to an empty table which is exactly what you wanted. However, this is not the case. Let’s take a look at this naive example of a UAG which simply concatenates the string value of a column:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined, MaxByteSize=8000, IsNullIfEmpty=true)]
public struct ConcatStr : IBinarySerialize 
{
   SqlString _concatenatedStrs;

   public void Init()
   {
       _concatenatedStrs = null;
   }

   public void Accumulate(SqlString value)
   {
       if (value == SqlString.Null)
           _concatenatedStrs += value;
   }

   public void Merge(ConcatStr group)
   {
      _concatenatedStrs += group.Terminate();
   }

   public SqlString Terminate()
   {
       return _concatenatedStrs;
   }
   #region IBinarySerialize Members

   void IBinarySerialize.Read(System.IO.BinaryReader r)
   {
       _concatenatedStrs = new SqlString(r.ReadString());
   }

   void IBinarySerialize.Write(System.IO.BinaryWriter w)
   {
       if (_concatenatedStrs != SqlString.Null)
           w.Write(_concatenatedStrs.Value);
   }

   #endregion
}

There’s nothing fancy about the codes and its implementation is pretty much straightforward. The quirk is, you don’t get a “NULL” if you apply this to an empty table as you see here

The workaround is simple. You need a flag that you can check if the Accumulate() method has been called or not. Accumulate() is called for every row in a table so no-call on this means the table is empty. Here’s the modified codes for our string aggregator

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined
   ,MaxByteSize=8000, IsNullIfEmpty=true)]
public struct ConcatStr : IBinarySerialize
{
   SqlString _concatenatedStrs;
   bool _isEmpty;

   public void Init()
   {
       _concatenatedStrs = null;
       _isEmpty = true;
   }

   public void Accumulate(SqlString value)
   {
       if (value != SqlString.Null)
       {
           _concatenatedStrs += value;
          if (_isEmpty==true)
               _isEmpty = false;
       }
   }

   public void Merge(ConcatStr group)
   {
      _concatenatedStrs += group.Terminate();
   }

   public SqlString Terminate()
   {
       if (!_isEmpty)
           return _concatenatedStrs;
       else 
           return SqlString.Null;
   }
   #region IBinarySerialize Members

   void IBinarySerialize.Read(System.IO.BinaryReader r)
   {
      _isEmpty = r.ReadBoolean();
       if (!_isEmpty)
           _concatenatedStrs = new SqlString(r.ReadString());
   }

   void IBinarySerialize.Write(System.IO.BinaryWriter w)
   {
      w.Write(_isEmpty);
       if (!_isEmpty)
       {
           if (_concatenatedStrs != SqlString.Null)
               w.Write(_concatenatedStrs.Value);
      }
   }

   #endregion
}

You shouldn’t forget to serialize the flag. Failure to do so wouldn’t preserve its value and it would always have the value in the initialization. If you put a breakpoint, in the terminate event, you could clearly say this one works.

And the result is what you expected:

The same workaround can be applied to any data type of your return value, be it a value-type or UDT. For example, if you don’t apply a workaround like the one below to an integer UAG, you get zero instead of null for an empty table!

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native
   ,IsNullIfEmpty=true)]
public struct SumInts
{
   int _accumulator;
   bool _isEmpty;
   public void Init()
   {
       _isEmpty = true;
   }

   public void Accumulate(SqlInt32 value)
   {
       if (value != SqlInt32.Null)
       {
           _accumulator += value.Value;
           if (_isEmpty)
               _isEmpty = false;
       }
   }

   public void Merge(SumInts group)
   {
       _accumulator += group.Terminate().Value;     
   }

   public SqlInt32 Terminate()
   {
       if (!_isEmpty)
           return new SqlInt32(_accumulator);
       else
           return SqlInt32.Null;
   }
}

I still hope I would be proven wrong with my findings. But until then, I just have to content with this dirty codes. What’s important is that it works.