SQL 表值函数(fn_Split) --- IN(表值函数) 与 CHARINDEX、LIKE 性能测试

• 什么是表值函数
  当调用的时候，结果集是一张表结果集

--无参数
CREATE  FUNCTION tvpoints ( )
RETURNS TABLE
AS
RETURN
    ( SELECT    *
      FROM      tb_users
    );

• 以上 RETURN 后 返回的是一张 查询表的结果集

--有参
/****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 2022/4/12 14:57:35 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[fn_Split]
    (
      @Input NVARCHAR(MAX) ,
      @Separator NVARCHAR(MAX) = ',' ,
      @RemoveEmptyEntries BIT = 1
    )
RETURNS @TABLE TABLE
    (
      [Id] INT IDENTITY(1, 1) ,
      [Value] NVARCHAR(MAX)
    )
AS
    BEGIN 
        DECLARE @Index INT ,
            @Entry NVARCHAR(MAX);
        SET @Index = CHARINDEX(@Separator, @Input);

        WHILE ( @Index > 0 )
            BEGIN
                SET @Entry = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index - 1)));
        
                IF ( @RemoveEmptyEntries = 0 )
                    OR ( @RemoveEmptyEntries = 1
                         AND @Entry <> ''
                       )
                    BEGIN
                        INSERT  INTO @TABLE
                                ( [Value] )
                        VALUES  ( @Entry );
                    END;
            
                SET @Input = SUBSTRING(@Input,
                                       @Index + DATALENGTH(@Separator) / 2,
                                       LEN(@Input));
                SET @Index = CHARINDEX(@Separator, @Input);
            END;
    
        SET @Entry = LTRIM(RTRIM(@Input));
        IF ( @RemoveEmptyEntries = 0 )
            OR ( @RemoveEmptyEntries = 1
                 AND @Entry <> ''
               )
            BEGIN
                INSERT  INTO @TABLE
                        ( [Value] )
                VALUES  ( @Entry );
            END;

        RETURN;
    END;

• [dbo].[fn_Split] 分割表函数，

  @Input 需要分割的字符
  @Separator 分割的字符中特殊字符进行分割
  @RemoveEmptyEntries 索引位置
  @TABLE 分割后存入表类型中

• 场景：

• A表 TREE_CODE字段是 存的值是 ,91175,36035,24916, 类型string 对应 多条B表

• B表ID字段是 存的值是 91175、36035、24916、类型int 那么如何 让 A表 TREE_CODE字段 关联 B表ID字段
  

• [fn_Split] 分割表函数 变成表结果集,91175,36035,24916,

SELECT  LI.TREE_CODE ,
        LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )
        INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) 
        ON LT.ID IN (91175,36035,24916)
        /*一般方式IN 把 LI.TREE_CODE(,91175,36035,24916,)中的字符变成INT 类型 如LT.ID IN (91175,36035,24916)
        对于数据量大的一般会些一个 [fn_Split]  分割表函数
        */
WHERE   LI.ID = 66888;

--[fn_Split]  分割表函数
SELECT  LI.TREE_CODE ,
        LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )
        INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) 
		ON LT.ID IN (SELECT Value FROM [dbo].[fn_Split](LI.TREE_CODE,',', 1) )
WHERE   LI.ID = 66888;

LT.ID IN (SELECT Value FROM [dbo].[fn_Split](LI.TREE_CODE,',', 1) )
--影响性能的地方
--要去进入fn_Split 函数内部逻辑进行处理 ，最后以表结果的形式输出
--LT.ID IN （） IN的匹配逻辑是全表搜索

• 以上这种方式 性能会很慢，占用资源多
• 所以有下面两种方式
• CHARINDEX判断是否存在包含 ，速度比调用fn_Split 表值函数快3-5倍

SELECT  LI.TREE_CODE ,
        LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )
        INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) 
		ON CHARINDEX(','+ CONVERT(VARCHAR(15), LT.ID)+ ',',LI.TREE_CODE) > 0
		/*影响性能的地方
		--CHARINDEX 匹配是否存在 W_D_PACKING_LIST 全表匹配
		--有强制转换 VARCHAR
		--匹配 后有 判断
		*/
WHERE   LI.ID = 66888;

• LIKE模糊查询，大数据情况下 速度比CHARINDEX 快0.8倍

--用LT.ID 去模糊匹配 LI.TREE_CODE 
--LT.ID 要转VARCHAR类型 并且 一定要加',' 因为LI.TREE_CODE 里面是以','分割的
SELECT  LI.TREE_CODE ,
        LT.ID
FROM    W_D_PACKING_LIST_ITEM LI WITH ( NOLOCK )
        INNER JOIN W_D_PACKING_LIST LT WITH ( NOLOCK ) 
		ON LI.TREE_CODE LIKE '%,'+ CONVERT(VARCHAR(15), LT.ID)+ ',%'
		/*影响性能的地方
		--LIKE 模糊匹配W_D_PACKING_LIST 全表匹配，如果字段值格式不统一很容易弄错 
		--有强制转换 VARCHAR
		*/
WHERE   LI.ID = 66888;

视图是一个虚拟表，不支持在其中声明变量。可以在存储过程或函数中声明变量，然后在视图中使用这些存储过程或函数。
如果你想在视图中使用变量，你可以考虑使用用户定义函数（UDF）来实现类似的功能。UDF允许你在函数内部声明和使用变量，并将其结果作为视图的一部分返回。
使用UDF在视图中声明变量：

CREATE FUNCTION dbo.GetTestGradeInfor()
RETURNS TABLE
AS
RETURN
(
    DECLARE @HU NVARCHAR(10) = 'NCCSAD';
    SELECT DISTINCT PROJ_CODE, PROCESS_ID, GRADE
    FROM PROCESS_TEST
    WHERE PROJ_CODE = @HU AND GRADE IS NOT NULL AND GRADE <> '-'
)

CREATE VIEW V_TEST_GRADE_INFOR
AS
SELECT *
FROM dbo.GetTestGradeInfor()

总结：

• 如果数据量不大，追求最稳定方式 选 [fn_Split] 分割表函数
• 如果数据量大，追求性能，相对稳定的方式 选CHARINDEX
• 追求高性能选LIKE，注意字段值的格式统一