Microsoft SQL Server 2008 R2 Unleashed- P207:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1854 CHAPTER 46 SQLCLR Developing SQL Server Objects in .NET IsNullIfEmpty Tells SQL Server that the UDA will return null if its aggregated value is empty that is if its value is 0 or the empty string and so on . Name Tells the deployment routine what to call the UDA when it is created in the database. MaxByteSize Tells SQL Server not to allow more than the specified number of bytes to be held in an instance of the UDA. You must specify this when using . For this example you implement a very simple UDA that sums values in an integer column but only if they are prime. Listing shows the code to do this. LISTING A UDA That Sums Prime Numbers using System using using using using Serializable IsInvariantToDuplicates false IsInvariantToNulls true IsInvariantToOrder true IsNullIfEmpty true public struct SumPrime SqlInt64 Sum private bool IsPrime SqlInt64 Number for int i 2 i Number i if Number i 0 return false return true public void Init Sum 0 Developing Custom Managed Database Objects 1855 public void Accumulate SqlInt64 Value if IsPrime Value Value 1 Sum Value public void Merge SumPrime Prime Sum public SqlInt64 Terminate return Sum In this code SQL Server first calls Init initializing the private Sum data field to 0. For each column value passed to the aggregate the Accumulate method is called wherein Sum is increased by the value of the column if it is prime. When multiple threads converge Merge is called adding the values stored in each instance as the Prime parameter to Sum. When the rowset has been completely parsed SQL Server calls Terminate wherein the accumulated value Sum is returned. Following are the results of testing SumPrime on an existing AdventureWorks2008 table SELECT TOP 10 AS PrimeSum FROM p .
