CLR - Pro SQL Server Internals - page 265

from dbo.Numbers
where Num between @MinNum and @MaxNum

set @RowCount = 0
open curWork
fetch next from curWork into @Num
while @@fetch_status = 0
begin
set @RowCount += 1
fetch next from curWork into @Num
end
close curWork
deallocate curWork

As you see in Table 13-5 , row-by-row processing using SqlDataReader is much more efficient than using the
T-SQL cursor.

Table 13-5. Data access performance (SQLReader vs. Cursor): Execution time

T-SQL Stored Procedure CLR Stored Procedure

556 ms 116 ms

Finally, let's look at the performance of CLR aggregates. We will use the aggregate that concatenates the values
into a comma-separated string. The code for doing this is shown in Listing 13-16.

Listing 13-16. CLR Aggregate
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls=true,
IsInvariantToDuplicates=false,
IsInvariantToOrder=false,
MaxByteSize=-1
)]
public class Concatenate : IBinarySerialize
{
// The buffer for the intermediate results
private StringBuilder intermediateResult;

// Initializes the buffer
public void Init()
{
this.intermediateResult = new StringBuilder();
}