2

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.

1 Answer 1

3

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."

2
  • I guess the efficiency argument applies to geometry too, since you could use a SqlGeometryBuilder in .NET. Still surprised (wrt geometry) it's never even suggested as a quick and dirty option though. Thank you.
    – andyb
    Jan 26, 2012 at 13:32
  • 1
    @andyb - The string concatenation approach gained a lot of ground before SQL Server 2005 came along I suppose. Sometimes this would be useful to do things with one pass of the data if it could be relied upon. Also would be useful if they actually implemented the IsInvariantToOrder property on the CLR aggregates Jan 26, 2012 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.