Ever missed a Prod(columnName) function that works like a sum but it multiplies the column values?

If you have then you probably know that there's a workaround using a bit of high school math knowledge about base 10 logarithms.

It goes like this:

SELECT exp(sum(log(c1))) as MultiplicationResult FROM test

However this little helper doesn't yield correct results for a large enough set, because it goes from integer to decimal.

So there's a possibilty of an error.

This is also a perfect example of a user defined aggregate that can be implemented in CLR. And becuase there's no int to deciaml transition

it yields correct results.

This is a simmple C# mulitplication aggregate:

Project Name = SqlProduct

Class name = Prod.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 300, IsNullIfEmpty=true)]
public struct Prod : IBinarySerialize
{
private decimal product;
<span class="kwrd">public</span> <span class="kwrd">void</span> Init()
{
    product = 1;
}

<span class="kwrd">public</span> <span class="kwrd">void</span> Accumulate(SqlDecimal Value)
{
    product = product * (<span class="kwrd">decimal</span>)Value;
}

<span class="kwrd">public</span> <span class="kwrd">void</span> Merge(Prod Group)
{
    Accumulate(Group.Terminate());
}

<span class="kwrd">public</span> SqlDecimal Terminate()
{
    <span class="kwrd">return</span> product;
}

<span class="preproc">#region</span> IBinarySerialize Members

<span class="kwrd">public</span> <span class="kwrd">void</span> Read(System.IO.BinaryReader r)
{
    product = r.ReadDecimal();
}

<span class="kwrd">public</span> <span class="kwrd">void</span> Write(System.IO.BinaryWriter w)
{
    w.Write(product);
}

<span class="preproc">#endregion</span>

}

And this is SQL code to test the new CLR multiplication and the T-SQL Exp-Sum-Log multiplication:

CREATE ASSEMBLY SqlProduct 
FROM 'D:\Test\SqlProduct.dll'
WITH PERMISSION_SET = SAFE 
CREATE AGGREGATE Prod(@input DECIMAL(38,10))
RETURNS DECIMAL(38,10)
EXTERNAL NAME SqlProduct.Prod;
GO
CREATE TABLE test (c1 int)
GO
SET NOCOUNT ON
DECLARE @i INT
SELECT @i = 0
WHILE @i < 20
BEGIN
INSERT INTO test
SELECT RAND()*10+1
<span class="kwrd">SELECT</span> @i = @i + 1 

END
SET NOCOUNT OFF
SELECT  dbo.Prod(c1) AS MultiplicationResult
FROM    test
SELECT  exp(sum(log(c1))) AS MultiplicationResult
FROM    test
DROP TABLE test

T-SQL version is about 3 times faster but the CLR one is of course 100% accurate.