title: Most efficient way to split string into rows
body: <p>I am using the following function to split a string into rows. It is much faster than the previous function that I was using, however I need to somehow churn through this data quicker (its an ETL job):</p>
  
  <pre><code>ALTER FUNCTION [dbo].[ArrayToTable]
  (
       @InputString VARCHAR(MAX) = ''
       , @Delimitter VARCHAR(1) = ',' 
  ) 
  RETURNS @RESULT TABLE([Position] INT IDENTITY, [Value] VARCHAR(MAX)) 
  AS 
  BEGIN 
      DECLARE @XML XML 
      SELECT @XML = CONVERT(XML, SQL_TEXT) 
      FROM ( 
          SELECT '&lt;root&gt;&lt;item&gt;' 
              + REPLACE(@InputString, @Delimitter, '&lt;/item&gt;&lt;item&gt;') 
              + '&lt;/item&gt;&lt;/root&gt;' AS SQL_TEXT 
          ) dt 
  
      INSERT INTO @RESULT([Value]) 
      SELECT t.col.query('.').value('.', 'VARCHAR(1000)') AS [Value] 
      FROM @XML.nodes('root/item') t(col) 
      RETURN 
  END 
  </code></pre>
  
  <p>Can anyone think of a better/quicker way to turn a delimited string into rows? I am using a <code>cross apply</code> on my query to join to these results. </p>
  
  <p>Can anyone think of a more efficient method to turn delimited strings into rows? </p>