SQLServerCentral / Discuss Content Posted by David Poole / Article Discussions / Article Discussions by Author / A genuine use for a SQL CLR Aggregate / Latest Posts

RE: A genuine use for a SQL CLR Aggregate

Solomon Rutzky — Tue, 04 Jan 2011 10:26:09 GMT

[quote][b]Adam Machanic (1/4/2011)[/b][hr]Garbage in, garbage out. If you pass in nonsensical data that lacks a key, you'll get back a nonsense answer. Break the ties and everything works fine.[/quote]D'oh! ;-) I just retested with unique SalesOrderId values and the Celko method did work correctly.I still do like the simplicity of the UDA, though.

RE: A genuine use for a SQL CLR Aggregate

Adam Machanic — Tue, 04 Jan 2011 10:05:57 GMT

[quote][b]Mark-101232 (1/4/2011)[/b][hr]I don't think the CELKO method works correctly in all circumstances, the problem occurs when there are duplicates and the ASC and DESC ROW_NUMBERs don't match up. [/quote]Garbage in, garbage out. If you pass in nonsensical data that lacks a key, you'll get back a nonsense answer. Break the ties and everything works fine.

RE: A genuine use for a SQL CLR Aggregate

Solomon Rutzky — Tue, 04 Jan 2011 09:55:19 GMT

[quote][b]Mark-101232 (1/4/2011)[/b][hr]I don't think the CELKO method works correctly in all circumstances, the problem occurs when there are duplicates and the ASC and DESC ROW_NUMBERs don't match up. The data below shows the problem, the result should be 15, the Ben-Gan is correct here.INSERT INTO SalesOrderHeader(CustomerId, TotalDue, SalesOrderId)SELECT 100,10,10 UNION ALLSELECT 100,10,10 UNION ALLSELECT 100,20,10 UNION ALLSELECT 100,30,10[/quote]Great catch, Mark!! I tested again with your data and my code (posted above), this time into a new table of just this data into a new DB as well:[code="sql"]USE [Test]GOCREATE TABLE dbo.SalesOrderHeader (CustomerId INT, TotalDue INT, SalesOrderId INT)INSERT INTO dbo.SalesOrderHeader(CustomerId, TotalDue, SalesOrderId)SELECT 100,10,10 UNION ALLSELECT 100,10,10 UNION ALLSELECT 100,20,10 UNION ALLSELECT 100,30,10 [/code]And then of course change the "Sales" Schema references to "dbo" in the previously noted sample code.It should be noted that the CLR (via SQL#) Median Aggregate (UDA) also worked correctly. And this is one reason why I definitely like CLR for things like this since it encapsulates complex logic. The harder code is to read the more likely it will get messed up by someone either in the beginning or when making changes. The CLR Aggregate gives a very simple and accurate way of doing an otherwise complex task that leaves the SQL developer to worry about the rest of the query without the complicating factor of what was needed to get the Median working. So while it is slightly less efficient, I do see definite benefits to it :-).

RE: A genuine use for a SQL CLR Aggregate

Mark-101232 — Tue, 04 Jan 2011 04:58:56 GMT

[quote][b]Solomon Rutzky (12/29/2010)[/b][hr][quote][b]Adam Machanic (12/29/2010)[/b][hr]Have you compared the performance of doing a median calculation w/compression against performing the same calculation using Joe Celko's ROW_NUMBER technique?[url]http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx[/url]Compression is far from cheap, and I suspect that the ROW_NUMBER solution will scale much better. But that's just a suspicion and I don't have time to test myself at the moment.[/quote]Hi Adam. Thanks for mentioning that article as I had not seen it. I did just test this against the AdventureWorks2008 DB using SQL Server 2008 SP2 using the Celko method, Ben-Gan method, and my CLR UDA. The CLR method has the same number of reads as the Celko method but has slightly higher CPU and Elapsed times as compared to both Celko and Ben-Gan methods.So yes, the pure T-SQL methods do appear to be slightly more efficient. However, for the record I did not say that compression was cheap. In fact, I did say that it would be slightly less efficient to do that but that it might prove to be a worthwhile trade-off depending on the circumstances.My testing is as follows (the first two queries are copied directly from your blog that you noted above):[code="sql"]------------------------------------------------------------------------DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ONSELECT CustomerId, AVG(TotalDue)FROM( SELECT CustomerId, TotalDue, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc FROM Sales.SalesOrderHeader SOH) xWHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)GROUP BY CustomerIdORDER BY CustomerId;SET STATISTICS IO OFFSET STATISTICS TIME OFF-- logical reads 686 -- CPU time = 47 ms, elapsed time = 1087 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)-- CPU time = 62 ms, elapsed time = 235 ms. (when cached)------------------------------------------------------------------------DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ONSELECT CustomerId, AVG(TotalDue)FROM( SELECT CustomerId, TotalDue, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue) AS RowNum, COUNT(*) OVER ( PARTITION BY CustomerId) AS RowCnt FROM Sales.SalesOrderHeader) xWHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)GROUP BY CustomerIdORDER BY CustomerId;SET STATISTICS IO OFFSET STATISTICS TIME OFF-- logical reads 686 + 139407 for Worktable = 140093 total-- CPU time = 344 ms, elapsed time = 1085 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)-- CPU time = 328 ms, elapsed time = 374 ms. (when cached)------------------------------------------------------------------------DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ON SELECT CustomerId, SQL#.SQL#.Agg_Median(TotalDue)FROM Sales.SalesOrderHeader SOHGROUP BY CustomerIdORDER BY CustomerId;SET STATISTICS IO OFFSET STATISTICS TIME OFF-- logical reads 686 -- CPU time = 1812 ms, elapsed time = 2707 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)-- CPU time = 1906 ms, elapsed time = 1948 ms. (when cached)------------------------------------------------------------------------[/code][/quote]I don't think the CELKO method works correctly in all circumstances, the problem occurs when there are duplicates and the ASC and DESC ROW_NUMBERs don't match up. The data below shows the problem, the result should be 15, the Ben-Gan is correct here.INSERT INTO SalesOrderHeader(CustomerId, TotalDue, SalesOrderId)SELECT 100,10,10 UNION ALLSELECT 100,10,10 UNION ALLSELECT 100,20,10 UNION ALLSELECT 100,30,10

RE: A genuine use for a SQL CLR Aggregate

DEK46656 — Mon, 03 Jan 2011 09:12:47 GMT

[quote][b]CELKO (12/29/2010)[/b][hr][quote][b]rdw-769613 (12/29/2010)[/b][hr]Another good example to use CLR is GMT/Local/Unix time handling. Trying this in T-SQL is near imposible and within CLR it's simple and faster.[/quote]I always did this by mimicking the ANSI/ISO Standards system tables for the WITH TIMEZONE option in the DDL. Then everything is done with VIEWs. No need for a CLR.[/quote]Joe, do you have an article on this that you could link, or is this in one of your books? I've had my own issues with this over time :-D and would like to see your implementation.

RE: A genuine use for a SQL CLR Aggregate

Bradley Deem — Wed, 29 Dec 2010 16:25:30 GMT

[quote][b]Solomon Rutzky (12/29/2010)[/b][hr][quote][b]Bradley Deem (12/29/2010)[/b][hr]I think part of this limitation is due to the lack of ability to perform an ORDER BY clause with an aggregate.Assuming I've implemented it correctly, this could produce output such as[code="other"]firstRowValue,secondRowValue,thirdRowValue[/code]or maybe parallelism kicks in (on a large set) and produces output where the order changes ie[code="other"]secondRowValue,firstRowValue,thirdRowValue[/code]Now, if we had we had the ability to perform the ORDER BY clause with the OVER clause ie[code="sql"]SELECT dbo.List(myStringColumn,',') [b]OVER(ORDER BY myStringColumn)[/b]FROM myTable[/code]Then we could guarantee the correct (expected) order of output.[/quote]Hello Bradley. I am not sure that I agree that this cannot be done in the Aggregate (hence requiring the ORDER BY). Maybe I am thinking of something that is not accurate, but I think this can be handled in the "Terminate()" method. The parallelism issue that you mention is handled via the "Merge()" method but the output still needs to go through the "Terminate()" method so it is there that you could properly order the string. Would that work?[/quote]You are correct, you could handle this in the terminate method, but who wants to determine the fastest CLR method to sort strings (or some other input) when we have SQL at our finger tips?

RE: A genuine use for a SQL CLR Aggregate

Solomon Rutzky — Wed, 29 Dec 2010 15:59:14 GMT

[quote][b]Bradley Deem (12/29/2010)[/b][hr]I think part of this limitation is due to the lack of ability to perform an ORDER BY clause with an aggregate.Assuming I've implemented it correctly, this could produce output such as[code="other"]firstRowValue,secondRowValue,thirdRowValue[/code]or maybe parallelism kicks in (on a large set) and produces output where the order changes ie[code="other"]secondRowValue,firstRowValue,thirdRowValue[/code]Now, if we had we had the ability to perform the ORDER BY clause with the OVER clause ie[code="sql"]SELECT dbo.List(myStringColumn,',') [b]OVER(ORDER BY myStringColumn)[/b]FROM myTable[/code]Then we could guarantee the correct (expected) order of output.[/quote]Hello Bradley. I am not sure that I agree that this cannot be done in the Aggregate (hence requiring the ORDER BY). Maybe I am thinking of something that is not accurate, but I think this can be handled in the "Terminate()" method. The parallelism issue that you mention is handled via the "Merge()" method but the output still needs to go through the "Terminate()" method so it is there that you could properly order the string. Would that work?

RE: A genuine use for a SQL CLR Aggregate

Solomon Rutzky — Wed, 29 Dec 2010 15:38:08 GMT

[quote][b]David.Poole (12/29/2010)[/b][hr]Thanks Solomon, what I was getting at with the Median is that to find the middle value you have to keep track of all the values and my worry (as yet unsubstantiated) is that you could run an aggregate on such a large recordset that you consume all the RAM on your system and destabilize your server.[/quote]Hello David. I am not sure this is a real issue since in SQL Server 2005 the max memory that the UDA can take up is 8k and if using the -1 MaxSize starting in SQL Server 2008 you still only get up to 2 GB. And yes, 2 GB is a lot but it is a constraint that is presumably less than the total amount of memory available to SQL Server. Also, with using the compression the memory usage will be less. Of course, as Adam pointed out it is possible to do this (Median) in straight T-SQL but I have also included "Geometric Average" and "Root Mean Square" Aggregates in my SQL# library and neither one needs to keep the entire set in memory so it doesn't matter how large the dataset is. And to your final point, I also think that Microsoft should include at least those two ("Geometric Average" and "Root Mean Square") if not all three as built-in Aggregates.

RE: A genuine use for a SQL CLR Aggregate

hsun — Wed, 29 Dec 2010 15:23:10 GMT

Have you tried to use sqlSingle instead of sqlDouble in the CLR?

RE: A genuine use for a SQL CLR Aggregate

David.Poole — Wed, 29 Dec 2010 15:22:42 GMT

[quote][b]Solomon Rutzky (12/29/2010)[/b][hr]The only issue I have is with your statement that "median" is not a good case for a CLR aggregate as it might "require a substantial portion of the set to be held in memory". I disagree because it is possible to reduce the size of data in memory by compressing it, as I have shown in this article:[url]http://www.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/3208/[/url][/quote]Thanks Solomon, what I was getting at with the Median is that to find the middle value you have to keep track of all the values and my worry (as yet unsubstantiated) is that you could run an aggregate on such a large recordset that you consume all the RAM on your system and destabilize your server.It may be that the point where this happens is beyond the bounds of most users.The other thing that jumps out at me is that most stats functions seem to rely on what I will call the primitive aggregates; the built in aggregates. Effectively the CLR aggregates provide a short-hand for data analysts so they can focus on what the data is telling them rather than the mechanics.I should like to see Microsoft consider adding more in-built statistical functions given the way that the BI world is heading. I should also like to see some focusing on integration with external stats packages such as SPSS and SAS.

RE: A genuine use for a SQL CLR Aggregate

thirst — Wed, 29 Dec 2010 12:30:34 GMT

I agree, i haven't been able to really leverage CLR functions successfully. With that said, I have had one victory using CLR fuctions. That was creating a CLR function to extend the functionality of the REPLACE() function within SQL. We ran into a situation where we needed to find and replace strings greater than the lengths allowed using the native REPLACE() function. Now with BIGREPLACE (creativity is not my strongsuit) we're able to pass the function 3 nvarchar(max) arguments. I guess CLR functions also give us t-sql fellas an excuse to poke around in some c# :p

RE: A genuine use for a SQL CLR Aggregate

Solomon Rutzky — Wed, 29 Dec 2010 12:03:01 GMT

[quote][b]Adam Machanic (12/29/2010)[/b][hr]Have you compared the performance of doing a median calculation w/compression against performing the same calculation using Joe Celko's ROW_NUMBER technique?[url]http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx[/url]Compression is far from cheap, and I suspect that the ROW_NUMBER solution will scale much better. But that's just a suspicion and I don't have time to test myself at the moment.[/quote]Hi Adam. Thanks for mentioning that article as I had not seen it. I did just test this against the AdventureWorks2008 DB using SQL Server 2008 SP2 using the Celko method, Ben-Gan method, and my CLR UDA. The CLR method has the same number of reads as the Celko method but has slightly higher CPU and Elapsed times as compared to both Celko and Ben-Gan methods.So yes, the pure T-SQL methods do appear to be slightly more efficient. However, for the record I did not say that compression was cheap. In fact, I did say that it would be slightly less efficient to do that but that it might prove to be a worthwhile trade-off depending on the circumstances.My testing is as follows (the first two queries are copied directly from your blog that you noted above):[code="sql"]------------------------------------------------------------------------DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ONSELECT CustomerId, AVG(TotalDue)FROM( SELECT CustomerId, TotalDue, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc FROM Sales.SalesOrderHeader SOH) xWHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)GROUP BY CustomerIdORDER BY CustomerId;SET STATISTICS IO OFFSET STATISTICS TIME OFF-- logical reads 686 -- CPU time = 47 ms, elapsed time = 1087 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)-- CPU time = 62 ms, elapsed time = 235 ms. (when cached)------------------------------------------------------------------------DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ONSELECT CustomerId, AVG(TotalDue)FROM( SELECT CustomerId, TotalDue, ROW_NUMBER() OVER ( PARTITION BY CustomerId ORDER BY TotalDue) AS RowNum, COUNT(*) OVER ( PARTITION BY CustomerId) AS RowCnt FROM Sales.SalesOrderHeader) xWHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)GROUP BY CustomerIdORDER BY CustomerId;SET STATISTICS IO OFFSET STATISTICS TIME OFF-- logical reads 686 + 139407 for Worktable = 140093 total-- CPU time = 344 ms, elapsed time = 1085 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)-- CPU time = 328 ms, elapsed time = 374 ms. (when cached)------------------------------------------------------------------------DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHESET STATISTICS IO ONSET STATISTICS TIME ON SELECT CustomerId, SQL#.SQL#.Agg_Median(TotalDue)FROM Sales.SalesOrderHeader SOHGROUP BY CustomerIdORDER BY CustomerId;SET STATISTICS IO OFFSET STATISTICS TIME OFF-- logical reads 686 -- CPU time = 1812 ms, elapsed time = 2707 ms. (when DROPCLEANBUFFERS and FREEPROCCACHE are run)-- CPU time = 1906 ms, elapsed time = 1948 ms. (when cached)------------------------------------------------------------------------[/code]

RE: A genuine use for a SQL CLR Aggregate

timothyawiseman — Wed, 29 Dec 2010 11:57:58 GMT

A fantastic article. I think I would rather approach this particular problem in pure T-SQL (or bringing it into something more suited for thorough statistical analysis, depending on the project at hand), but this was still a well written intro to the topic.

RE: A genuine use for a SQL CLR Aggregate

CELKO — Wed, 29 Dec 2010 10:46:15 GMT

[quote][b]rdw-769613 (12/29/2010)[/b][hr]Another good example to use CLR is GMT/Local/Unix time handling. Trying this in T-SQL is near imposible and within CLR it's simple and faster.[/quote]I always did this by mimicking the ANSI/ISO Standards system tables for the WITH TIMEZONE option in the DDL. Then everything is done with VIEWs. No need for a CLR.

RE: A genuine use for a SQL CLR Aggregate

Steve Jones - SSC Editor — Wed, 29 Dec 2010 09:36:15 GMT

The images have been corrected. My apologies for the issues.

RE: A genuine use for a SQL CLR Aggregate

Adam Machanic — Wed, 29 Dec 2010 08:41:13 GMT

[quote][b]Solomon Rutzky (12/29/2010)[/b]And that article was done before SQL Server 2008 added the ability to go beyond the 8k memory limit (with a MaxSize set to -1) so adding that option to the mix solves all problems (generally) outside of the possible efficiency of the CPU it takes to compress/decompress the dataset. At that point I would argue that if your situation requires a Median calculation (and it appears to for quite a few people) then a little bit of extra processing time is worth the ability to do this in the first place.[/quote]Have you compared the performance of doing a median calculation w/compression against performing the same calculation using Joe Celko's ROW_NUMBER technique?[url]http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx[/url]Compression is far from cheap, and I suspect that the ROW_NUMBER solution will scale much better. But that's just a suspicion and I don't have time to test myself at the moment.

RE: A genuine use for a SQL CLR Aggregate

Solomon Rutzky — Wed, 29 Dec 2010 08:18:27 GMT

Hello David and thanks for a great article on yet another good use of SQL CLR.The only issue I have is with your statement that "median" is not a good case for a CLR aggregate as it might "require a substantial portion of the set to be held in memory". I disagree because it is possible to reduce the size of data in memory by compressing it, as I have shown in this article:[url]http://www.sqlservercentral.com/articles/SS2K5+-+CLR+Integration/3208/[/url]And that article was done before SQL Server 2008 added the ability to go beyond the 8k memory limit (with a MaxSize set to -1) so adding that option to the mix solves all problems (generally) outside of the possible efficiency of the CPU it takes to compress/decompress the dataset. At that point I would argue that if your situation requires a Median calculation (and it appears to for quite a few people) then a little bit of extra processing time is worth the ability to do this in the first place.

RE: A genuine use for a SQL CLR Aggregate

Bradley Deem — Wed, 29 Dec 2010 08:17:38 GMT

Thanks for sharing David. I agree most CLR aggregates have not had much success beyond academics. I think part of this limitation is due to the lack of ability to perform an ORDER BY clause with an aggregate.For example, lets say I create a CLR aggregate that concatenates strings together ie[code="sql"]SELECT dbo.List(myStringColumn,',') -- Where ',' means comma delimitedFROM myTable[/code]Assuming I've implemented it correctly, this could produce output such as[code="other"]firstRowValue,secondRowValue,thirdRowValue[/code]or maybe parallelism kicks in (on a large set) and produces output where the order changes ie[code="other"]secondRowValue,firstRowValue,thirdRowValue[/code]Now, if we had we had the ability to perform the ORDER BY clause with the OVER clause ie[code="sql"]SELECT dbo.List(myStringColumn,',') [b]OVER(ORDER BY myStringColumn)[/b]FROM myTable[/code]Then we could guarantee the correct (expected) order of output.If you believe this would be a useful feature, and I do, please vote for it on connect.[url]https://connect.microsoft.com/SQLServer/feedback/details/254387/over-clause-enhancement-request-order-by-for-aggregates[/url]In regards to the attribute IsInvariantToOrder, it's not supported, see the following link.[url]http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqluserdefinedaggregateattribute.isinvarianttoorder.aspx[/url]Apparently, it will be implemented in the "future" which after 5 years hasn't been far enough into the future.

RE: A genuine use for a SQL CLR Aggregate

bbalok — Wed, 29 Dec 2010 07:21:02 GMT

We have developed a Project Planning and Management system and I have used CLR routines in two main places. The user wanted cost escalation at user defined periods of the project in the planned budget calculation. These calculated amounts had to be used by UI screens and reports and had to be consistent. Each work unit is inspected to see if it falls within one to many escalation periods that occur during the life of the project and how much of the work falls within a period. A planned budget amount is calculated and aggregated based on the cost, amount of work and escalation percent. There are also status codes that are used to filter the work units involved in the calculation, multiple tables of data and many loops through the data so it was easier for me to use a CLR and C# to gather the data I needed and loop through the work units to provide an escalated work unit planned budget. Did I mention different contract types that use these escalated amounts in different ways to create a budget? I could also encapsulate this logic in this routine so I could return a planned budget amount for every contract type in the system. We allow budgeting at the lowest work breakdown level but have to provide aggregated budget amounts at every parent level so another loop through the data to perform this aggregation to the parent.Since the CLR works as a Table Valued Function we can write queries against it for UI and reporting. Every UI or report will have the same planned budget value because the math and rounding and contract types are encapsulated in this routine. It seems that if I do division of two numbers in T-Sql , C#, Crystal Reports I get a different number of decimals and value of decimals. I am not sure I could have done this without the use of a CLR routine to perform the looping and provide consistent rounded amounts. The other place I use it is to provide a cross tab type output but I will save that for another day as I have spouted enough.

RE: A genuine use for a SQL CLR Aggregate

ianemery-640190 — Wed, 29 Dec 2010 06:49:01 GMT

see what happens when you add a materialized view... this will be much quicker..create VIEW vx_agg WITH SCHEMABINDINGasSELECT --( COUNT_big(*) * SUM(col1*col2) - ( SUM(col1) * SUM(col2)))/( COUNT_big(*)* SUM(col1*col1)-( SUM(col1)* SUM(col1))) AGGCOUNT_big(*) cnt,SUM(col1*col2) col1col2,SUM(col1) col1,SUM(col1*col1) col1sq,SUM(col2) col2FROM dbo.test_tableGOCREATE UNIQUE clustered INDEX IX_AGG ON vx_agg (cnt)SELECT ( COUNT(*) * SUM(col1*col2) - ( SUM(col1) * SUM(col2)))/( COUNT(*)* SUM(col1*col1)-( SUM(col1)* SUM(col1)))FROM dbo.test_table

RE: A genuine use for a SQL CLR Aggregate

rdw-769613 — Wed, 29 Dec 2010 05:44:19 GMT

Another good example to use CLR is GMT/Local/Unix time handling. Trying this in T-SQL is near imposible and within CLR it's simple and faster.

RE: A genuine use for a SQL CLR Aggregate

Robert Frasca — Wed, 29 Dec 2010 05:41:25 GMT

Thanks for putting this together. It was a fascinating exercise that I really enjoyed. Like many others I have struggled to find a genuine use case for this feature. I agree that SQL Server 2008 made the feature more usable but I'm still not sure the tradeoffs make it worthwhile. Great article.Bob

RE: A genuine use for a SQL CLR Aggregate

David.Poole — Wed, 29 Dec 2010 04:37:52 GMT

On the subject of the expensive sort in the execution plan I had a note back from Bob Beauchamp saying that there is an internal stream aggregator operation that causes the sort and unfortunately there is no way around it other than to put the clustered index on the column(s) used in the GROUP BY statement.:crying:

RE: A genuine use for a SQL CLR Aggregate

tcm_mail — Wed, 29 Dec 2010 01:02:16 GMT

Please, can you repair the broken links of the pictures:http://www.sqlservercentral.com/articles/SQL+CLR/71942/m.gifhttp://www.sqlservercentral.com/articles/SQL+CLR/71942/regression.gifhttp://www.sqlservercentral.com/articles/SQL+CLR/71942/Regressiongraph.gifhttp://www.sqlservercentral.com/articles/SQL+CLR/71942/CLRAggregateExecutionPlan.JPGhttp://www.sqlservercentral.com/articles/SQL+CLR/71942/CLRAggWithGroupExecutionPlan.JPG

A genuine use for a SQL CLR Aggregate

David.Poole — Tue, 28 Dec 2010 21:09:57 GMT

Comments posted to this topic are about the item [B]A genuine use for a SQL CLR Aggregate[/B]