just reading about a new “FOR XML PATH” operator in sql2005 that apparently gives us a better way to do row concatenation than what we’ve been doing. instead of something like:
DECLARE @categories varchar(200)
SET @categories = NULL
SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
SELECT @categories
you write this (you’ll need to strip out leading ‘,’):
SELECT ',' + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH('')
one author saw up to a 10x speed improvement, but this will depend on the specifics of your query, so double-check the execution times if you use it. articles are here:
http://www.sqlmag.com/Article/ArticleID/96784/sql_server_96784.html
