Ndtyjky

Assuming I have this query ( pseudo) :

Select T.a,

       T.b, 

       (select top 1 element from fn_split(c,',') where element=T.element)

From largeTable T

Where fn_split runs for each row , I would like to use inline table valued udf so , that performance will be better.

NB : fn_split just create a table via splitting via , :

But looking at inline table valued udf structure :

create FUNCTION [dbo].[fn_...]

(

    ...

)

RETURNS table

AS

RETURN  SELECT ...(!!!)

It should return the select right away as the first statement !

But what if my UDF looks like :

CREATE FUNCTION [dbo].[FN_Split] 

(

    @InDelimitedString varchar(max),

    @InDelimiter       varchar(10) 

)

RETURNS 

    @tblArray TABLE 

    (

        ElementID  smallint IDENTITY(1,1), 

        Element    varchar(1000)

    )



AS

BEGIN



    DECLARE @StrPos smallint,

    @StrStart smallint,

    @DelimiterLength smallint



    SET @DelimiterLength = LEN(@InDelimiter)    



    WHILE LEN(@InDelimitedString) > 0

        BEGIN

         --Removed for clarity . do some CHARINDEX  manipulation ETc.

        END



    RETURN 

END

Question :

I can't return select right away , but still , I want to change the fn_split to inline table valued udf.

How can I do it ?

The problem is with your split function. It is doing the split in an RBAR fashion. You should use a set-based splitter. Here is the DelimitedSplit8k by Jeff Moden, which is one of the fastest splitter there is:

CREATE FUNCTION [dbo].[DelimitedSplit8K](

    @pString VARCHAR(8000), @pDelimiter CHAR(1)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

)

,E2(N) AS (SELECT 1 FROM E1 a, E1 b)

,E4(N) AS (SELECT 1 FROM E2 a, E2 b)

,cteTally(N) AS(

    SELECT TOP (ISNULL(DATALENGTH(@pString), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

,cteStart(N1) AS(

    SELECT 1 UNION ALL 

    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString, t.N, 1) = @pDelimiter

),

cteLen(N1, L1) AS(

SELECT 

    s.N1,

    ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1),0) - s.N1, 8000)

FROM cteStart s

)

SELECT 

    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),

    Item       = SUBSTRING(@pString, l.N1, l.L1)

FROM cteLen l

Note: Be sure to look into the article for the updated function

For more split functions, read these articles by Sir Aaron Bertrand:

• Split strings the right way – or the next best way


• Splitting Strings : A Follow-Up


• Splitting Strings : Now with less T-SQL


• Comparing string splitting / concatenation methods


• Processing a list of integers : my approach


• Splitting a list of integers : another roundup


• More on splitting lists : custom delimiters, preventing duplicates, and maintaining order


• Removing Duplicates from Strings in SQL Server