Peter Larsson Blog

Patron Saint of Lost Yaks

SQLCLR aggregate function

Phew! Now it's finally done.
I haven't coded outside SQL Server since 2007 and that was with VB version 6.0.

Well, I felt the need to start code again (at least for SQLCLR), since there are many tasks that will be easier to do with a SQLCLR routine. To start easy, I coded a "least square regression" routine and you can download it from this location

Sample.sql (good linear regression)
Sample2.sql (better linear regression)
Sample3.sql (polynomial regression)

* I have created a homepage for this SQLCLR function

For the Sample2 above, the result for c: drive on Server1 look like this

<dws bestfit="linear" r2="0.99" type="least square regression">
 <linear A="15417024.67" b="-388.60" r2="0.99" y="A+b*x" x="(y-A)/b" />
 <exponential A="1.9E+102" b="-0.00"r2="0.98" y="A*e^(b*x)" x="ln(y/A)/b" />
 <logarithmic A="162488444.70" b="-15345855.35" r2="0.99" y="A+b*ln(x)" x="e^((y-A)/b)" />
 <power A="INF" b="-224.35" r2="0.98" y="A*x^b" x="e^(ln(y/A)/b)" />

For Sample3 above, the result can look like

<dws a="-4.00"b="6.64"c="1.64"r2="0.98"y="a + b*x + c*x^2 + d*x^3" />

With these constants, you can easy calcuate what freespace there will be on a certain date, or on which date the freespace is x megabytes. 

It has an ugly hack, because it seems VS2008 doesn't accept multiple parameters for Accumulate routine. I have to wait until VS2010 has fixed the strong name bug before doing this the right way.
I also have begun to code routines for Polynomial Regression too, along with Circle, Ellipse and parabolic regression. My idea is that this library of statistical and mathematical functions will continue to extend.

Nevertheless, the ugly hack works, and I would love some feedback.

There are plenty of uses for this routine. For example, if you store disk space amount in a history table, apply the BestFit formula on top of the data, and you can predict when space is out, or at least when space will be at a certain level in the future.

If you can find more uses, please let me know. And thanks to Mladen Prajdic and Florian Reischl for the last pieces of the xml return puzzle.


Legacy Comments

re: SQLCLR aggregate function
where is the C# code ?

re: SQLCLR aggregate function
You want the source code?

re: SQLCLR aggregate function
why Not?
Unless you have ulterior profitable motives :-)

re: SQLCLR aggregate function
I don't have C# code, I have VB.Net code.

Also, yes the DLL will be available for order later on but for now it's free, as long as the ugly hack is there.

Simon Sabin
re: SQLCLR aggregate function
Don't use VS to deploy your aseembly and you will be fine. Do it by hand.

Or use Niels Berglunds SQLCLR project