Nice, as always, Jeff. I notice you said you didn't do any testing on 2005. If you do more testing on 2005, be sure to try out the XML data type with FOR XML for concatenating strings. Here's some timings for the sample below (run on a 1.9 GHz box with 2 GB RAM, SQL 2005):

* Test with PK/CI on RowNum and NCI on SomeID ran in 9 seconds for 1,000,000 rows

* An alternate version using the data() node test as a column name and the REPLACE function instead of SUBSTRING ran in 11 seconds on 1,000,000 rows

* Changing the NCI to a covering index on (SomeID, SomeCode) the code executed in 4 seconds on 1,000,000 rows. I left RowNum out of the covering index create index statement since it's the CI already, so automatically added to the NCI.

Who knows Jeff, maybe XML will find a place in your heart after all? 🙂

--Statement to create covering index:

--CREATE INDEX IX_TestData_SomeInt ON dbo.TestData(SomeID, SomeCode)

-- Jeff's table creation code

SELECT TOP 1000000

RowNum = IDENTITY(INT,1,1),

SomeID = ABS(CHECKSUM(NEWID()))%2500+1,

SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

INTO dbo.TestData

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 ;

ALTER TABLE dbo.TestData

ADD PRIMARY KEY CLUSTERED (RowNum);

CREATE INDEX IX_TestData_SomeInt ON dbo.TestData(SomeID);

-- End table creation code

GO

-- Begin function creation

CREATE FUNCTION dbo.fnConcatTest (@SomeID INT)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @x XML;

DECLARE @Return VARCHAR(8000);

SET @x = (SELECT ',' AS '*',

SomeCode AS '*'

FROM dbo.TestData

WHERE SomeID = @SomeID

FOR XML PATH (''), TYPE);

SET @RETURN = SUBSTRING(CAST(@x AS VARCHAR(8000)), 2, 8000);

RETURN @Return;

END

GO

--Speed test

SELECT SomeID, dbo.fnConcatTest(SomeID) AS CSVString

FROM dbo.TestData

GROUP BY SomeID;