|Business Application Development with: SQL Server, C#, VB, VB.Net, ASP, ASP.Net, and XML|
|News Links Schedule Site Map Contact|
Creating a User Defined Aggregate with SQL Server 2005
by Andrew Novick
is a comment on the Product aggregate by Joe Celko and more
SQL Server doesn't have a Product aggregate function. A recent blog post by Karen Watterson pointed out that Microsoft Knowledge Base article Q89656: Simulating a PRODUCT() Aggregate Function discusses how to achieve the Product aggregate functionality using the POWER function. We e-mailed about it and she challenged me to create a Product aggregate in SQL Server 2005. I decided to accept the challenge.
SQL Server 2005 allows the creation of User Defined Aggregates in VB.Net or C#. I started with a new Visual Basic/Database type project and I selected SQL Server Project. That created the project without any classes. The next step is to add a User Defined Aggregate Class to the project and code it. So here it is, my first attempt at a udagg:
A user defined aggregate must implement four methods:
There are a couple of choices that have to be made in the design of the Product aggregate:
I chose to use SQLDouble as the data type for the aggregate. For starters, There's only a limited choice of types that can be when using the Format.Native attribute. SQLDouble is one of them. Without Format.Native, the aggregate must implement the IBinarySerializable interface and I preferred not to do that for a first cut at a udagg.
The artribute IsNullIfEmpty:=True covers the case where there are no values to aggregate by returning NULL. But what's the Product of only NULL values? I decided it was NULL. I suppose that an argument could be made for 1. After all, one raised to the zero power is one. And you might even argue for zero. In the end, I decided to mimic the behavior of the SUM aggregate which returns NULL when given only NULL values. However, the point of udagg's is that these decisions are now in our hands.
Visual Studio 2005 takes care of deploying the assembly to SQL Server 2005 (f.k.a Yukon). If the assembly is added to SQL Server manually, a CREATE AGGREGATE statement is required. Here's a sample of what it looks like with an appropriate GRANT statement:
After using the Build -> Deploy menu command you're ready to try out Product. My first instinct was to run my tests in the the new SQL Server Management Studio, which takes over the function of Query Analyzer, but I decided to stick to Visual Studio. My understanding is that they're actually the same program, just configured differently. The Visual Studio 2005 Solution Explorer has a node for TestScripts. All you have to do is pick Add Test Script from its context menu. I added a script called Test.sql. Here it is:
-- Simple case with 3 integer sample numbers.
I set Test.sql as the "Default Debug Script" using its context menu and pressed F5, equivalent to Debug->Start. Here are the results:
There seem to be a few more system messages than in SQL Server 2005 than in 2000 but everything seems in order. Integers were converted to float by SQL Server. NULLs were handled the way that I intended. It worked.
Okay. It didn't really happen so easily. I spent a fair amount of time going back and forth between the documentation and the book A First Look at SQL Server 2000 for Developers and my sample code. All-in-all it wasn't that difficult.
I'll be writing more about programming with the .Net CLR in SQL Server 2005 in coming months. CLR programming will be the theme for my Coding-in-SQL Newsletter in September and October so you might want to sign up. Also in September, I'm giving a presentations on the topic at Microsoft's Code Camp IV (Sept 24-25). If you're in the Boston area you might want to attend.