I find the below faster on 2005.

[font="Courier New"]

CREATE FUNCTION [Strings].[fnStringSplit_2005_Distinct_CHAR]

(

@SourceString VARCHAR(MAX)

)

/*======================================================================================

'P System : Multiple

'P Subsystem : Common Functions

'P Script : fnSMS_StringSplit_2005

'P Creation date : 15/10/2010

'P

'P Description : Splits a Comma Delimited String Into a Table. Join as a Table

'P

'P SELECT * FROM Strings.fnStringSplit_2005_Distinct_CHAR('9,8,7,6,5,4,3,2,1,')

'P

'P Parameters----------------------------------------------------------------------

'P Inputs : @SourceString - Comma delimited string

'P Outputs : table variable

'P====================================================================================*/

RETURNS @Values TABLE

(

--POSITION INT IDENTITY,

VALUE VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS

PRIMARY KEY ([VALUE])

)

AS

BEGIN

DECLARE @vchString VARCHAR(MAX)

DECLARE @xmlString XML

BEGIN

SET @vchString = @SourceString

IF RIGHT(@vchString,1) = ','

SET @vchString = LEFT(@vchString, LEN(@vchString)-1)

/*------------------------------------------

| Convert the string to xml

'-----------------------------------------*/

SET @xmlString = CAST('<i>' + REPLACE(@vchString, ',', '</i><i>') + '</i>' AS XML)

/*------------------------------------------

| Read xml into a table variable

'-----------------------------------------*/

INSERT INTO @Values(VALUE)

SELECT DISTINCT x.i.value('.', 'VARCHAR(255)') AS Item

FROM @xmlString.nodes('//i') x(i)

END

RETURN

END

/*---------------------------------------------------------------------------------------

|--------------------------- End of common function -----------------------------|

|--------------------------------------------------------------------------------------*/[/font]