如何在 SQL Server 中将拆分函数转换为内联表值 udf? [英] How can I convert split function to inline table valued udf in SQL server?

问题描述

假设我有这个查询(伪):

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

在 fn_split 为每一行运行的地方，我想使用内联表值 udf，这样性能会更好.

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

注意:fn_split 只需通过 、 进行拆分来创建表:

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

但是查看内联表值 udf 结构 :

But looking at inline table valued udf structure :

create FUNCTION [dbo].[fn_...]
(
    ...
)
RETURNS table
AS
RETURN  SELECT ...(!!!)

它应该立即返回 select 作为第一条语句！

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

但是如果我的 UDF 看起来像:

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

问题:

我无法立即返回 select ，但仍然想将 fn_split 更改为内联表值 udf.

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

我该怎么做?

推荐答案

问题在于您的拆分功能.它在 RBAR 时尚.您应该使用基于集合的拆分器.这是 Jeff Moden 的 DelimitedSplit8k，这是最快的分离器之一:

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

注意:请务必查看更新功能的文章

有关更多拆分函数，请阅读亚伦·伯特兰爵士 (Sir Aaron Bertrand) 撰写的这些文章:

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

• 以正确的方式拆分字符串 – 或者下一个最佳方式
• 拆分字符串:后续工作
• 拆分字符串:现在使用更少的 T-SQL
• 比较字符串拆分/连接方法
• 处理整数列表:我的方法
• 拆分整数列表:另一个综述
• 有关拆分列表的更多信息:自定义分隔符、防止重复和维护顺序
• 从 SQL 中的字符串中删除重复项服务器

这篇关于如何在 SQL Server 中将拆分函数转换为内联表值 udf?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！