The XML construct within that function is sub-optimal, 23 operators in the execution plan and there of 3 XML Reader Table valued functions.

😎

This can be significantly improved by changing the XML construct, here is a modified version which reduces the construct to a single constant scan operator and the execution plan down to 12 operators.

CREATE FUNCTION dbo.SplitStrings_XML_EE

(

@List NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

(

SELECT

X.DATA.value('(./text())[1]', 'nvarchar(4000)') AS Item

FROM

(

SELECT

CONVERT(XML,X.TXML,1) AS XXML

FROM (

SELECT

'<i><![CDATA['

+ REPLACE(@List, @Delimiter, ']]></i><i><![CDATA[')

+ ']]></i>'

) AS X(TXML)

) XOUT

CROSS APPLY XOUT.XXML.nodes('/i') X(DATA)

)

;

Edit: Testing showed that the modified version was three times slower regardless of the execution plan simplification.

😎