sql-server - 如何在持久计算列中使用变量？

Question

我有一个持久计算列，用于保存DATETIME2从FLOAT时间列（[Potentially Bad Time]）计算得出的值。我正在处理当时所有组件的下溢和溢出，DATEADD但目前我不得不在整个公式中不断重新计算相同的值，因为我不知道如何将它们存储在变量中！实际实现也支持年、月和日，但有 210 行，所以这里是一个仅使用时间组件的缩减版本

CREATE TABLE Sales
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [Potentially Bad Time] FLOAT NOT NULL,
    CONSTRAINT PK_Sales PRIMARY KEY
    (
        Id ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE Sales
ADD [Time] AS
    DATEADD
    (
        HOUR,
        IIF
        (
            ROUND([Potentially Bad Time] / 10000, 0, 1) < 0,
            --Subtracts underflowing hours.
            ROUND([Potentially Bad Time] / 10000, 0, 1),
            IIF
            (
                ROUND([Potentially Bad Time] / 10000, 0, 1) >= 24,
                --Adds overflowing hours.
                ROUND([Potentially Bad Time] / 10000, 0, 1) - 24,
                0
            )
        ),
        DATEADD
        (
            MINUTE,
            IIF
            (
                ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100 < 0,
                --Subtracts underflowing minutes.
                ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100,
                IIF
                (
                    ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100 >= 60,
                    --Adds overflowing minutes.
                    ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100 - 60,
                    0
                )
            ),
            DATEADD
            (
                SECOND,
                --If the seconds value is greater than or equal to 60, this adds on the overflowing seconds to the relevant DATETIME2 component(s).
                IIF
                (
                    ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100 < 0,
                    --Subtracts underflowing seconds.
                    ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100,
                    IIF
                    (
                        ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100 >= 60,
                        --Adds overflowing seconds.
                        ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100 - 59,                 
                        0
                    )
                ),
                DATEADD
                (
                    MILLISECOND,
                    IIF
                    (
                        [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1) < 0,
                        --Subtracts underflowing milliseconds.
                        [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1),
                        IIF
                        (
                            [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1) > 9999999,
                            --Adds overflowing milliseconds.
                            [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1) - 9999999,
                            0
                        )
                    ),
                    DATETIME2FROMPARTS
                    (
                        --Year (Fixed for brevity of example)
                        1990,
                        --Month (Fixed for brevity of example)
                        12,
                        --Day (Fixed for brevity of example).
                        31,
                        --Hour
                        IIF
                        (
                            ROUND([Potentially Bad Time] / 10000, 0, 1) < 0,
                            0,
                            IIF
                            (
                                ROUND([Potentially Bad Time] / 10000, 0, 1) >= 24,
                                23,
                                ROUND([Potentially Bad Time] / 10000, 0, 1)
                            )
                        ),
                        --Minute
                        IIF
                        (
                            ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100 < 0,
                            0,
                            IIF
                            (
                                ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100 >= 60,
                                59,
                                ROUND([Potentially Bad Time] / 100, 0, 1) - ROUND([Potentially Bad Time] / 10000, 0, 1) * 100
                            )
                        ),
                        --Second
                        IIF
                        (
                            --If the seconds value is less than 0, truncates it to 0.
                            ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100 < 0,
                            0,
                            --If the seconds value is greater than or equal to 60, this truncates it at 59.
                            IIF
                            (
                                ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100 >= 60,
                                59,
                                ROUND([Potentially Bad Time], 0, 1) - ROUND([Potentially Bad Time] / 100, 0, 1) * 100
                            )
                        ),
                        --Millisecond
                        IIF
                        (
                            [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1) < 0,
                            0,
                            IIF
                            (
                                [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1) > 9999999,
                                9999999,
                                [Potentially Bad Time] - ROUND([Potentially Bad Time], 0, 1)
                            )
                        ),
                        3
                    )
                )
            )
        )
    )
PERSISTED

例如，如何将 放入一个名为以保存多次计算[Time] - ROUND([Time], 0, 1)的变量中？@milliseconds

我尝试了标量函数，因此参数可以有效地成为变量，但是持久计算列不支持使用标量函数（无论如何它们对性能来说都是个坏消息！）。

我也在公式和公式DECLARE @milliseconds FLOAT = [Time] - ROUND([Time], 0, 1)之间尝试过AS，但这是一个语法错误。

标签： sql-servertsqlcalculated-columnspersisted-column

Answer 1

您需要添加选择以将值分配给您的变量。

DECLARE @milliseconds FLOAT = ( select [Time] - 
ROUND([Time], 0, 1) from yourtable);