Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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 '12 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 –  Martin Smith Jan 26 '12 at 13:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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