Explain User-defined Aggregates ?

Question

Hi .NET worker Explain how and when to create User-defined Aggregates and how to deploy with Visual Studio and finally how to use in SQL Query and in last how to use from Client Code? Come on developers you are Champions.

Raj Kumar · Answer

A user-defined aggregate (UDA ) function returns a scalar result that is the result of a calculation on values in a set of rows. Examples of such functions include built-in SQL Server aggregate functions such as SUM AVG MIN and MAX. A CLR UDA function is implemented as a structure or class in a .NET Framework assembly. A CLR UDA function can be invoked in T-SQL statements with the same rules that apply to system aggregate functions. The following example creates registers and executes a UDA function that returns the sum of a SqlMoney column in a table using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [SqlUserDefinedAggregate(Format.Native)] public struct SumMoney { private SqlMoney sum; public void Init( ) { sum = 0; } public void Accumulate(SqlMoney Value) { sum += Value; } public void Merge(SumMoney Group) { sum += Group.sum; } public SqlMoney Terminate( ) { return sum; } } In SQL Server Management Studio register the assembly and create the UDA function by executing the following statement: USE AdventureWorks GO CREATE ASSEMBLY SumMoney FROM 'C:\PSS2005\Uda\Uda\bin\Debug\Uda.dll' GO CREATE AGGREGATE SumMoneyUda ( @Value money ) RETURNS money EXTERNAL NAME SumMoney.SumMoney Execute the aggregate function on the Sales.SalesOrderHeader table in Aventure-Works: SELECT dbo.SumMoneyUda(SubTotal) dbo.SumMoneyUda(TaxAmt) dbo.SumMoneyUda(Freight) dbo.SumMoneyUda(TotalDue) FROM Sales.SalesOrderHeader