Persisted Computed Columnがなぜか不明瞭になった件

何故これがダメなのかが分からない

先日の消費税計算のComputed Columnを、Persisted指定したら何故かエラーになりました。

テーブル 'Orders' の計算列 'SalesTax' を保存できません。この列は不明確です。

これを解決します。

前回のスキーマ定義

前回のスキーマは以下のような感じでした。

テーブル

CREATE TABLE [dbo].[Orders]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [CustomerId] INT NOT NULL, 
    [BillingDate] DATE NOT NULL, 
    [TaxableAmount] INT NOT NULL,
    [TaxFreeAmount] INT NOT NULL DEFAULT (0),
    [SalesTax] AS ISNULL([dbo].[ufnCalcSalesTax]([TaxableAmount], [BillingDate]), 0)
)

関数

CREATE FUNCTION [dbo].[ufnCalcSalesTax]
(
    @amount int,
    @date date
)
RETURNS INT
AS
BEGIN
    RETURN (
        CAST(@amount *
            CASE
            WHEN @date >= '2014-4-1' THEN 0.08
            WHEN @date >= '1997-4-1' THEN 0.05
            WHEN @date >= '1989-4-1' THEN 0.03
            ELSE 0.0
            END AS INT)
    );
END

これのSalesTaxをPersisted指定しようと思い、テーブル定義にPersistedを、関数にWITH SCHEMABINDINGを指定しました。

当然これで問題なく動作するだろうと思っていたのですが、現実は先頭に書いたように、

テーブル 'Orders' の計算列 'SalesTax' を保存できません。この列は不明確です。

と、エラー。不明瞭?特に不明瞭な箇所はないような?

暗黙の型変換があるとダメっぽい

所々でコメントアウトして動作させてみると、どうやら関数内の

@date >= '2014-4-1'

の部分の有無が原因になっている様子です。

そういう事であれば、下記のように修正したらアッサリと通過しました。
どうやら暗黙の型変換がある場合は注意した方がよさそうですね。

CREATE FUNCTION [dbo].[ufnCalcSalesTax]
(
    @amount int,
    @date date
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN (
        CAST(@amount *
            CASE
            WHEN @date >= convert(date, '20140401', 112) THEN 0.08
            WHEN @date >= convert(date, '19970401', 112) THEN 0.05
            WHEN @date >= convert(date, '19890401', 112) THEN 0.03
            ELSE 0.0
            END AS INT)
    );
END

ちなみに

Visual StudioのSSDT(SQL Server Data Tool)を用いている場合は、プロジェクトのプロパティよりCode Analysisを有効にすれば、上記の型変換は警告されます。

f:id:mrgchr:20170724200728p:plain