I had a little more fun... again using 17,000+ tokens.

I put the CTE version inside the original function by creating 200 character tokens using the original method (sort of), assuming...

My theory on the XML problem was that it was actually a CLR call-out. Tally seems to work well, it shows an up front execution cost that is not...

I ran the three types again, with "SET EXECUTION TIME ON" and...

...8788 tokens:

XML: CPU time = 203722 ms

Tally: CPU time = 47 ms

Original: CPU time = 686 ms

arturv (3/4/2011)

---

Hi guys,

why dont we keep it simple?

CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)

INSERT INTO #test ( id, name )

VALUES ( 101, 'Apple' ),

...

That function looks pretty similar to the one from the original article, which I called "fn_Split". It does fine, too, and it will work on older versions of SQL...

The tally table method seems to work fine, but the estimated and actual execution plans show a pretty severe penalty. My guess is that the initial file I/O and...

Norbert, try to fix it the way you want it, and post your code if you get stuck. I intentionally attempted to keep the last token if the delimiter...

CTE version, made more like the SUBSTRING version:

CREATE FUNCTION [dbo].[fnStringSplit_CTE]

(

@SourceString VARCHAR(MAX),

@Delim CHAR(1)

)

RETURNS @Values TABLE

(

VALUE VARCHAR(MAX)

)

AS

BEGIN

with split(i, token, remainder) as

(select 1

, left(@SourceString,charindex(@delim,@SourceString)-1)

, LTRIM(right(@SourceString,len(@SourceString)-CHARINDEX(@delim,@SourceString)))

union all

select i + 1

,case when charindex(@delim,remainder)...

I ran the XML, CTE, and the SUBSTRING functions through and checked the estimated to actual execution plans. The estimated shows the XML_Reader is, by far, the...

I am fairly certain that CTE will be faster...

/*sql server 2008 CTE split and join fun*/

/*split begins*/

if object_id(N'tempdb..#split') is not null drop table #split;

if object_id(N'tempdb..#joined') is not null drop table...

You can use charindex with substring() but it is slower than parsename. I don't know why. I would love to see the code behind them to understand why.

I actually tested 4 tiny int conversions and 1 int conversions and binary always beat 'em in conversion costs (cheaper on the CPU and the same size). If you...

I think a 4 byte binary field is the best. You can use substring function to pick out the octets and it takes the least amount of work to...