title: Substring is slow with while loop in SQL Server
body: <p>One of my table column stores ~650,000 characters (each value of the column contains entire table). I know its bad design however, Client will not be able to change it.</p>
  <p>I am tasked to convert the column into multiple columns.
  I chose to use dbo.DelimitedSplit8K <a href="https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function" rel="nofollow noreferrer">function</a></p>
  <p>Unfortunately, it can only handle 8k characters at max.</p>
  <p>So I decided to split the column into 81 8k batches using while loop and store the same in a variable table (temp or normal table made no improvement)</p>
  <pre><code>    DECLARE @tab1 table   ( serialnumber int, etext nvarchar(1000))
  declare @scriptquan int = (select MAX(len (errortext)/8000) from mytable)
  
  
  DECLARE @Counter INT 
  DECLARE @A bigint = 1
  DECLARE @B bigint = 8000
  SET @Counter=1
  WHILE ( @Counter &lt;= @scriptquan + 1)
  BEGIN
      insert into @tab1 select ItemNumber, Item from dbo.mytable cross apply dbo.DelimitedSplit8K(substring(errortext, @A, @B), CHAR(13)+CHAR(10)) 
  
      SET @A = @A + 8000
      SET @B = @B + 8000
      
      SET @Counter  = @Counter  + 1
  END
  </code></pre>
  <p>This followed by using below code</p>
  <pre><code>declare @tab2 table (Item nvarchar(max),itemnumber int, Colseq varchar(10)) -- declare table variable
  
  ;with cte as (
  select [etext] ,ItemNumber, Item from @tab1 -- insert table name
  cross apply dbo.DelimitedSplit8K(etext,'    ')) -- insert table columns name that contains text
  
  insert into @tab2 Select Item,itemnumber, 'a'+ cast  (ItemNumber as varchar) colseq
  from cte  -- insert values to table variable
  
  ;WITH Tbl(item,  colseq) AS(
      select item, colseq from @tab2
  ),
  CteRn AS(
      SELECT item, colseq,
          Rn = ROW_NUMBER() OVER(PARTITION BY colseq ORDER BY colseq)
      FROM Tbl
  )
  SELECT
     a1 Time,a2 Number,a3 Type,a4 Remarks
  FROM CteRn r
  PIVOT(
      MAX(item)
      FOR colseq IN(a1,a2,a3,a4)
  )p
      
      where a3 = 'error'
  </code></pre>
  <p>gives the desired output. However, just the loop takes 15 minutes to complete and overall query completes by 27 minutes. Is there any way I can make it faster? Total row count in my table is 2. So I don't think Index can help.</p>
  <p>Client uses Azure SQL Database so I can't choose PowerShell or Python to accomplish this either.</p>
  <p>Please let me know if more information is needed. I tried my best to mention everything I could.</p>