Home Forums SQL Server 2014 Development - SQL Server 2014 To Index a Computed Column... RE: To Index a Computed Column...

  • December 19, 2017 at 8:20 pm

    #1972832

    The "Deterministic" rules are pretty nasty when it comes to CONVERT and implicit converts.  That's one of the many reasons why dates represented as integers are so frowned upon.  Here are the rules according to Books Online.

    The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

    Function Comments

    CAST

    Deterministic unless used with datetime, smalldatetime, or sql_variant.

    CONVERT

    Deterministic unless one of these conditions exists:

    • Source type is sql_variant.
    • Target type is sql_variant and its source type is nondeterministic.
    • Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

    Here's your PERSISTED computed column using the intended formula (365/12 was incorrect... it needed to be 365/12.0 or 30.416666 to meet the requirements according to the ROUND used in the original formula).  I'm not sure that I agree with that formula but that's up to you.  The requirement to return the number of months as an INT required yet another CONVERT.


    CREATE TABLE dbo.FctCoverageLengthDetail
            (
             CoverageEffectiveDateKey   INT
            ,CoverageEndDateKey         INT
            ,CalculatedMonths        AS CONVERT(INT,ROUND(
                                            DATEDIFF(dd
                                                    ,CONVERT(DATETIME,CONVERT(CHAR(8),CoverageEffectiveDateKey),112)
                                                    ,CONVERT(DATETIME,CONVERT(CHAR(8),CoverageEndDateKey)      ,112)
                                                    )/30.416666 --(365/12.0)
                                            ,0)) PERSISTED
            )
    ;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)