Why use custom CLR aggregate functions for string concatenation and geometry unions in SQL Server?

Question

I often need to do string concatenation or geometry unions over a column in SQL Server 2008 and I'm aware that you can write custom aggregate functions in .NET and register them with SQL Server to do these things.

However, you can take a very simple approach to the problem using a local variable and a select e.g.

CREATE TABLE Test(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    geom geometry NOT NULL,
    attribute nvarchar(max) NOT NULL,
)
GO

INSERT INTO Test(geom, attribute)
VALUES ('POLYGON ((0 0, 1 0, 2 2, 0 0))', 'shape1'),
('POLYGON ((0 0, 0 1, 2 2, 0 0))', 'shape2'),
('POLYGON ((2 2, 3 2, 3 3, 2 3, 2 2))', 'shape3')
GO

-- string concatenation
DECLARE @mytext nvarchar(MAX) = '';

SELECT @mytext = @mytext + ' ' + attribute
FROM Test;

SELECT @mytext;

-- geometry union
DECLARE @mygeom geometry = 'POLYGON EMPTY';

SELECT @mygeom = @mygeom.STUnion(geom)
FROM Test;

SELECT @mygeom;

Nulls etc. aside, these seem to work fine. So I don't understand why these methods aren't suggested at all in most articles.

What is wrong with calculating aggregates values like this?

Thanks very much.

Answer 1

TSQL is very inefficient for string manipulation compared to .NET (where you can use a StringBuilder for example).

The concatenation approach will allocate a whole load of new strings. It might not be so bad if you could just create a big string up front and use STUFF to replace parts of it but I believe from trying this approach previously that this too just creates a new string behind the scenes.

Additionally the behaviour you are relying on for both cases is undocumented and not guaranteed. "The correct behavior for an aggregate concatenation query is undefined."