SQL Server 2008结果不一致

Question

我刚刚将一些随机导致错误的代码发布到生产环境中。我已经通过完全改变我进行查询的方式修复了这个问题。然而，这仍然困扰着我，我不知道是什么导致了这个问题，所以我想知道是否有人知道答案。我在一个存储过程中有以下查询。我不是在寻找关于使用嵌套函数调用进行查询的好做法的评论:-)。我真的很想找出为什么它不能持续工作。随机地，查询中的函数将返回一个非数字值，并导致连接错误。但是，如果我立即重新运行查询，它就可以正常工作。

SELECT      cscsf.cloud_server_current_software_firewall_id,
                dbo.fn_GetCustomerFriendlyFromRuleName(cscsf.rule_name, np.policy_name) as rule_name,
                cscsf.rule_action,
                cscsf.rule_direction,
                cscsf.source_address,
                cscsf.source_mask,
                cscsf.destination_address,
                cscsf.destination_mask,
                cscsf.protocol,
                cscsf.port_or_port_range,
                cscsf.created_date_utc,
                cscsf.created_by
    FROM        CLOUD_SERVER_CURRENT_SOFTWARE_FIREWALL cscsf
    LEFT JOIN   CLOUD_SERVER cs
    ON          cscsf.cloud_server_id = cs.cloud_server_id
    LEFT JOIN   CLOUD_ACCOUNT cla
    ON          cs.cloud_account_id = cla.cloud_account_id
    LEFT JOIN   CONFIGURATION co
    ON          cla.configuration_id = co.configuration_id
    LEFT JOIN   DEDICATED_ACCOUNT da
    ON          co.dedicated_account_id = da.dedicated_account_id
    LEFT JOIN   CORE_ACCOUNT ca
    ON          da.core_account_number = ca.core_account_id
    LEFT JOIN   NETWORK_POLICY np 
    ON          np.network_policy_id = (select dbo.fn_GetIDFromRuleName(cscsf.rule_name))
    WHERE       cs.cloud_server_id = @cloud_server_id
    AND         cs.current_software_firewall_confg_guid = cscsf.config_guid
    AND         ca.core_account_id IS NOT NULL
    ORDER BY    cscsf.rule_direction, cscsf.cloud_server_current_software_firewall_id

如果您注意到连接

ON          np.network_policy_id = (select dbo.fn_GetIDFromRuleName(cscsf.rule_name))

调用函数。

下面是该函数：

ALTER FUNCTION [dbo].[fn_GetIDFromRuleName]
(
    @rule_name              varchar(100)
)
RETURNS varchar(12)
AS
BEGIN
    DECLARE     @value      varchar(12)

        SET @value = dbo.fn_SplitGetNthRow(@rule_name, '-', 2)
        SET @value = dbo.fn_SplitGetNthRow(@value, '_', 2)
        SET @value = dbo.fn_SplitGetNthRow(@value, '-', 1)

    RETURN      @value
END

然后调用这个函数：

ALTER FUNCTION [dbo].[fn_SplitGetNthRow]
(
    @sInputList     varchar(MAX),
    @sDelimiter     varchar(10) = ',',
    @sRowNumber     int = 1
)
RETURNS varchar(MAX)
AS
BEGIN
    DECLARE     @value      varchar(MAX)

    SELECT      @value = data_split.item
                        FROM
                        (
                            SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as row_num FROM dbo.fn_Split(@sInputList, @sDelimiter)
                        ) AS data_split
                        WHERE
                        data_split.row_num = @sRowNumber

    IF          @value IS NULL
        SET     @value = ''

    RETURN      @value  
END

它最终调用这个函数：

ALTER FUNCTION [dbo].[fn_Split] (
    @sInputList VARCHAR(MAX),
    @sDelimiter VARCHAR(10) = ','
) RETURNS @List TABLE (item VARCHAR(MAX))
BEGIN
    DECLARE @sItem VARCHAR(MAX)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
        BEGIN
            SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
            IF LEN(@sItem) > 0
                INSERT INTO @List SELECT @sItem
        END

    IF LEN(@sInputList) > 0
        INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
END

Answer 1

它“随机”返回不同内容的原因与SQL Server优化查询的方式以及它们被缩短的位置有关。

解决此问题的一种方法是更改fn_GetIDFromRuleName的返回值：

return (case when isnumeric(@value) then @value end)

或者，更改联接条件：

on np.network_policy_id = (select case when isnumeric(dbo.fn_GetIDFromRuleName(cscsf.rule_name)) = 1)
                                       then dbo.fn_GetIDFromRuleName(cscsf.rule_name) end)

潜在的问题是评估的顺序。" case“语句修复该问题的原因是，它在转换之前检查数值，并且SQL Server保证case语句中的计算顺序。需要注意的是，在转换像"6e07“或"1.23”这样的数字时，您可能仍然会遇到问题，这些数字是数字，而不是整数。

为什么它有时会起作用？显然，查询执行计划正在发生变化，或者是静态的，或者是动态的。失败的案例可能位于WHERE条件排除的行上。为什么它要尝试进行转换？问题是转换发生在哪里。

WHere转换的发生取决于查询计划。这又可能取决于何时读取所讨论的表cscf。如果它已经在成员中，则可能会读取它并尝试将其转换为查询中的第一步。然后你就会得到错误。在另一种情况下，可以对另一个表进行筛选，并在转换行之前将其删除。

无论如何，我的建议是：

• 在查询中从不进行隐式转换。对于显式conversions.
• Do，
• 使用case语句，而不是依赖WHERE子句来筛选数据以使转换生效。使用case语句。