一千萬個為什麽

搜索

SQL Server 2008の矛盾した結果

私はランダムにエラーを引き起こしている生産にいくつかのコードをリリースしました。私はすでに質問をしていたやり方を完全に変えて問題を修正しました。しかし、それは私が最初に何が問題を引き起こしていたのか分からないので、誰かが答えを知っているかどうか疑問に思っていたことに私は気になります。私はストアドプロシージャ內で次のクエリを持っています。私はネストされた関數呼び出しとそのようなものでクエリを行うのは良い習慣ではないというコメントを探しているわけではありません:-)。なぜそれが一貫して機能しないのかを本當に知りたいだけです。クエリの関數が無作為に數値以外の値を返し、結合にエラーが発生します。しかし、すぐにクエリを再実行すると正常に動作します。

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

最佳答案

「ランダムに」異なるものを返す理由は、SQL Serverがクエリを最適化する方法と、それらがどこで短絡するかと関係しています。

問題を解決する方法の1つは、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條件によって除外された行にあります。なぜそれを変換しようとしますか?問題は変換がどこで起こるかです。

変換はクエリプランによって異なります。これは、問題のテーブルcscfがいつ読み込まれるかによって異なります。すでにメンバーになっている場合は、それが読み取られ、照會の最初のステップとして変換される可能性があります。それから、エラーが発生します。別のシナリオでは、別のテーブルがフィルタリングされ、変換される前に行が削除される可能性があります。

いずれにしても、私のアドバイスは:

  • クエリで暗黙的な変換を行うことは絶対に避けてください。
  • 明示的なコンバージョンにはcaseステートメントを使用します。
  • WHERE句を使用してデータをフィルタリングしてコンバージョンを機能させないでください。 case文を使用します。

轉載註明原文: SQL Server 2008の矛盾した結果