How does one use Variables in a Persisted Computed Column?

Question

I have a persisted computed column I am using to hold a DATETIME2 calculated from a FLOAT time column ([Potentially Bad Time]). I am handling underflow and overflow of all of the components of the time using DATEADD but currently am having to keep recalculating the same values throughout the formula because I can't figure out how to store them in a variable! The actual implementation also supports year, month and day but is 210 lines so here is a cut-down version using just time components

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

How can I put, for example, the [Time] - ROUND([Time], 0, 1) in a variable named @milliseconds to save calculating it multiple times?

I've tried a Scalar Function so the parameter could effectively be the variable but then Persisted Computed columns don't support the use of Scalar Functions (and they're bad news for performance anyway!).

I've also tried DECLARE @milliseconds FLOAT = [Time] - ROUND([Time], 0, 1) between the AS and the formula, but that's a syntax error.

It's unclear what you're asking. Can you produce a minimal, self-contained example and ask your question about that? — David Browne - Microsoft, May 24 '19 at 16:59
You can't. You have to repeat the expression multiple times. There is no way of avoiding it. You can't declare variables inside a computed column specification expression or have the sub expression as another computed column referenced in the main one. — Martin Smith, May 24 '19 at 17:00
Is there at least some way of getting the database engine to recognize the repeated calculations and optimize them into one? The query to add this column to a table containing approximately 50 million rows has been running for 3 hours 30 minutes now and I somehow need to make this and other queries complete in a 1 hour deployment window! — Matt Arnold, May 24 '19 at 17:37
This calculation is extremely unlikely to be responsible for more than a few seconds of that time even if executed 150 million times — Martin Smith, May 24 '19 at 18:00
I let it run whilst I was away and it ran for 5 hours and 14 minutes and failed with the exception `The transaction log for database is full due to 'Log_Backup'`. I then ran it against another database with 637,535 rows in that table and it completed in 21 seconds without error. Looks like it's not the calculation that's slow, I've just ran an SP_WHO2 against the server when it's running and it appears to be blocked by another SPID. However, killing that SPID resulted in the following error upon re-run: `The connection is broken and recovery is not possible. The connection is marked b...`. — Matt Arnold, May 28 '19 at 09:24
It's ran for 50 minutes now with no other SPIDs blocking it (according to `SP_WHO2`). By my calculations, it should have completed in approximately 27 minutes based upon the 21 second execution time for 637,535 records. I'm going to see if taking out the under/overflow logic speeds it up. — Matt Arnold, May 28 '19 at 10:19

score 0 · Answer 1 · answered May 24 '19 at 17:40

0

You need to add select to assign a value to your variable.

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

answered May 24 '19 at 17:40

Dwight Reynoldson

950
8
12

Where would I put the declaration so that it can be used in the computed column? – Matt Arnold May 24 '19 at 17:44
I would use it as part of a stored procedure/trigger which stores that value per id in a second table. This also means you can very quickly reference that value/update it using joins rather than re-calculating it over and over. – Dwight Reynoldson May 24 '19 at 18:00

How does one use Variables in a Persisted Computed Column?

1 Answers1