Indexing Computed Columns

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714093

    Comments posted to this topic are about the item Indexing Computed Columns

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71158

    Really interesting question, thanks Steve

    forgot that  the results of this datetime function is non-deterministic....

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Carlo Romagnano

    SSC-Insane

    Points: 21642

    Stewart "Arturius" Campbell - Thursday, November 8, 2018 10:53 PM

    Really interesting question, thanks Steve

    forgot that  the results of this datetime function is non-deterministic....

    DATEPART not always is nondeterministic.
    e.g. datepart(day,'20180131') is deterministic because it returns always 31
    e.g. datepart(dw,'20180131') is NONdeterministic, because it depends from localization.

  • Jeff Moden

    SSC Guru

    Points: 993645

    I believe you meant "not" rather than "now".

    However, your first example is also non-deterministic.  Here's the proof.

    Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME);
    GO
    ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(day,'20180131') PERSISTED;

    Msg 4936, Level 16, State 1, Line 1
    Computed column 'WhichDay' in table 'SalesOrderHeader' cannot be persisted because the column is non-deterministic.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Shayn Thomas

    SSCertifiable

    Points: 5353

    nice question steve, it had me stumped

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Carlo Romagnano

    SSC-Insane

    Points: 21642

    Jeff Moden - Saturday, November 10, 2018 8:00 AM

    I believe you meant "not" rather than "now".

    However, your first example is also non-deterministic.  Here's the proof.

    Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME);
    GO
    ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(day,'20180131') PERSISTED;

    Msg 4936, Level 16, State 1, Line 1
    Computed column 'WhichDay' in table 'SalesOrderHeader' cannot be persisted because the column is non-deterministic.

    Here an example where it is deterministic:
    DROP table dbo.SalesOrderHeader
    Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME NOT NULL);
    ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(d,SomeDate) PERSISTED;
    create index idx_SalesOrderHeader ON SalesOrderHeader(WhichDay)

    Commands completed successfully.

    The difference is the second input parameter:
    with string is non-deterministic
    with a valid  datetime is deterministic

  • pbelter

    SSC Rookie

    Points: 28

    Carlo Romagnano - Wednesday, November 14, 2018 6:43 AM

    Jeff Moden - Saturday, November 10, 2018 8:00 AM

    I believe you meant "not" rather than "now".

    However, your first example is also non-deterministic.  Here's the proof.

    Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME);
    GO
    ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(day,'20180131') PERSISTED;

    Msg 4936, Level 16, State 1, Line 1
    Computed column 'WhichDay' in table 'SalesOrderHeader' cannot be persisted because the column is non-deterministic.

    Here an example where it is deterministic:
    DROP table dbo.SalesOrderHeader
    Create table dbo.SalesOrderHeader (RN INT IDENTITY(1,1),SomeDate DATETIME NOT NULL);
    ALTER TABLE dbo.SalesOrderHeader ADD WhichDay AS datepart(d,SomeDate) PERSISTED;
    create index idx_SalesOrderHeader ON SalesOrderHeader(WhichDay)

    Commands completed successfully.

    The difference is the second input parameter:
    with string is non-deterministic
    with a valid  datetime is deterministic

    Wow, that just gave me a headache.LOL But hey I forgot the deterministic rule. OLD AGE is A bummer.
    Thanks for the schooling!
    Paul

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply