There is some significant overlap between this post and yours, so I did some performance testing.
On the combination of both the test data you supply, and the 8000 replications of "123456" that Jeff uses in his article:
Declare @vTable Table(id int,val varchar(Max))
Insert into @vTable Values (1,'Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf sakjdf slakjdhf lskajhfd lksajdhflksajhdf lkjdsahflksajhdf salkdj flksajdhf lksajfd lksjd flksjadh flkjsahdf lkhfdlkjhdfs lksajdhf lksajdh flksjah flkasdjh flksjdh flkjs lkfjshlkdjfh s Atif Sheikh2 Sheikh123 skdjhfksdjhflkjdshflkjsahlkjfhalsjkhdflkjashldkfjhsalkjdhflksajdhflkjsadhflkjsahdlkfjhsadlkjfh sldkjflksajhdflkashdflkjahsdkfewiuyroiewuyoiuyoitureotiuoiutyoiuret,b,zcmnxbv,mzxnvb,mnxcvb,mnxzvb,mnxzcvb,mnxzvb,mnxbz,mvnbxz,mcnvb,mxzncvb,mxcznvb,mnxczbvmnlkhsdkljfhskljdhflkdjhkljshakdjlfhslkjhfkljsahdflkjsahd slkdjflskdjhflkjsahdfkjlsd lskdjhfslkjdhflkjs slkdjhflsakjdhflks skjd lksjhdfskjldhf ls slkdjhfslkjdhf lksajd fsaldkjfh slkjd fskldjhf lksjahdflksjahd flkjsadhflkjsadhflkjshadf slkjhfslakjhflkajshdf s s s s s s s sdlkfjdslkfj sldkfsldkfj;lskd')
Insert into @vTable Values (2,'Asif Sheikh1 Sheikh2 Sheikh3')
Insert into @vTable select 3, LEFT(REPLICATE(CAST('123456 ' AS VARCHAR(MAX)),8000),(8000*7)-1)
Your code gives timings on my box of:
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 564 ms.
Following ColdCoffee's suggestion from the above link, and putting Jeff's tally table method into a function:
CREATE FUNCTION dbo.fn_Split_TALLY
(@delimited nvarchar(max)
,@delimiter nchar(1)
)
RETURNS TABLE
as
RETURN
SELECT SUBSTRING(@delimiter+@delimited +@delimiter,N+1,CHARINDEX(@delimiter,@delimiter+@delimited+@delimiter,N+1)-N-1) as Val
FROM(
SELECT TOP (LEN(@delimited)+1) ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROMMaster.sys.All_Columns t1
CROSS JOINMaster.sys.All_Columns t2
) t
WHERE SUBSTRING(@delimiter+@delimited +@delimiter,N,1) = @delimiter --Notice how we find the comma
GO
and calling it like this against your sample table:
selectv.id, s.val
from@vTable v
cross apply dbo.fn_Split_TALLY(v.val,' ') s
gives timings of:
SQL Server Execution Times:
CPU time = 7937 ms, elapsed time = 7931 ms.
Slightly surprised by that performance, so I rewrote the script to use an existing indexed TALLY table, and the timings change to:
SQL Server Execution Times:
CPU time = 4719 ms, elapsed time = 4716 ms.
I also found some iterative looping code (just substrings in a WHILE loop):
CREATE FUNCTION fn_Split_LOOP
( @Stringnvarchar(max),--String to be parsed
@Delimiternchar(1)--Delimiter to use to parse the string
)
RETURNS @RtnTable table(
idint,
Valnvarchar(max))
AS
begin
Declare @Itemnvarchar(max)--Holds indivialual items parsed
--from the string
Declare @StartPos int--Start position of the next item in the string
Declare @EndPos int--End position of the next item in the string
Declare @Pos int--Position of the next delimiter
Declare @LenDelimiter int--Lengh of the delimiter
Declare @LastItem bit--Indeicates whether this is the last item in
--the string
Declare @LenString int--Length of the string to be parsed
Declare @ItemNoint--Item number
--Initialize variables
Set @StartPos = 1
Set @LastItem = 0
Set @LenString = Len(@String)
Set @LenDelimiter = 1--Len(@Delimiter) -- len() doesn't work if the delimiter is a space!
set @ItemNo= 1
--The following loop parses through the @String parameter to extract
--the items. It inserts one row in the RtnTable table for each item.
while @LastItem = 0
begin
--Get the position of the next delimiter
Set @Pos = CHARINDEX(@Delimiter, @String, @StartPos)
--If @Pos is 0, then we have reached the last item in the string, so
--process the last item, then exit the loop.
if @Pos > 0
Set @EndPos = @Pos
else
begin
Set @EndPos= @LenString + 1
Set @LastItem = 1
end
--Extract the item from the string and trim any leading or trailing blanks.
Set @Item = Rtrim(LTrim(Substring(@String, @StartPos, @EndPos-@StartPos)))
--Insert the item into the return table
insert @RtnTable values(@ItemNo, @Item)
--If this was the last item, then exit the loop, else
--increment the @StartPos and @ItemNo and go through again.
if @LastItem = 1
break
else
begin
set @ItemNo = @ItemNo + 1
Set @StartPos = @EndPos + @LenDelimiter
end
end
return
end
GO
which is called via cross apply same as above, and this gives timings of:
SQL Server Execution Times:
CPU time = 344 ms, elapsed time = 385 ms.
I also found this link, suggested by Eralper in the link above, and created a function to split the strings using XML:
CREATE FUNCTION dbo.fn_Split_XML
(@delimited nvarchar(max)
,@delimiter nchar(1)
)
RETURNS @t TABLE
(id int identity(1,1)-- Id column can be commented out, not required for sql splitting string
,val nvarchar(max)
)
AS
BEGIN
declare @xml xml set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
selectr.value('.','varchar(max)') as item
from@xml.nodes('//root/r') as records(r)
RETURN
END
GO
which gives the fastest timings of:
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 331 ms.
This suggests that this is not a good place to use a TALLY table, that recursive CTE gives reasonable performance, WHILE loops have their place, but XML is fastest.
Which isn't necessarily what I expected.