I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

T-SQL vs CLR: SQL Server 2005 group multiplication

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;

    public void Init()
    {
        product = 1;
    }

    public void Accumulate(SqlDecimal Value)
    {
        product = product * (decimal)Value;
    }

    public void Merge(Prod Group)
    {
        Accumulate(Group.Terminate());
    }

    public SqlDecimal Terminate()
    {
        return product;
    }

    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r)
    {
        product = r.ReadDecimal();
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(product);
    }

    #endregion
}

 

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
    
    SELECT @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.

 

 

Print | posted on Monday, February 12, 2007 12:22 PM | Filed Under [ .Net SQL Server ]

Feedback

Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

Cool!
Is there a resolution difference between SQL float and CLR decimal to accouht for the inaccuracyy?

rockmoose
2/12/2007 11:54 PM | rockmoose
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

this explains the diff:
http://msdn2.microsoft.com/en-us/library/system.data.sqltypes.sqldecimal.aspx

the diff comes from that in sql you log an int to a decimal. sum that to get another decimal and then you do e^x.
neither Log or e^x are are precise definite numbers so here goes your accuracy.
2/13/2007 11:32 AM | Mladen
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

All "high school math knowledge" here is that:
log(A * B) = log(A) + log(B)
2/14/2007 2:29 AM | Anatoly Lubarsky
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

LOL!
thanx Anatoly.

i thought that this didn't need an explenation :))
2/14/2007 10:41 AM | Mladen
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

And using the decimal rather than the e functions reduces the errors.

SELECT power(10.000, (sum(log(c1)))) as MultiplicationResult FROM test
5/29/2007 5:08 PM | Bob chief of the sheep
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

Hi!

Thank you for great code example. I added reference to this blog entry in my company's blog too. Great example for everyone who is going to find out what CLR has offer to us.
6/15/2007 1:49 PM | Gunnar
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

great!
6/15/2007 1:50 PM | Mladen
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

Will this work??????

declare @temp table(i int)
declare @p numeric(18,2)

insert into @temp values(NULL)
--insert into @temp values(-1)
--insert into @temp values(0)
insert into @temp values(1)
insert into @temp values(2)
insert into @temp values(3)
insert into @temp values(4)
insert into @temp values(5)
insert into @temp values(6)
insert into @temp values(7)


-- select @p=coalesce((@p*(1+(i/100.00)),(1+i/100)) from @temp

select @p=coalesce(@p*i,i) from @temp
select @p
select 1*2*3*4*5*6*7
8/1/2008 8:45 PM | Jayan
Gravatar

# re: T-SQL vs CLR: SQL Server 2005 group multiplication

This is fantasic work. Thanks! This actually saved my butt putting together a Rolled Throughput Universe in BusinessObjects and I desperately needed a PROD/MULT function in SQL Server.

I am however having a problem with the return value. I am only getting 1 or 0 back as a result. Does this have something to do wit the fact that the Write finction is using a BinaryWriter? How do I get this function to actually return the decimal value? Any help would be greatly appreciated. Almost there! :)

~ Gene
7/31/2009 9:28 PM | Gene
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET