Automatic update of outdated statistics

  • Revenant

    SSC-Forever

    Points: 42467

    It took me over two hours to figure this one out - my personal record, I think.

    Thanks, Uwe!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Wow, what a question.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question. Thanks for the effort Uwe.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Marek Grzymala

    SSCommitted

    Points: 1890

    An interesting thing happens after changing your Id column to:

    Id INT IDENTITY (1, 1) PRIMARY KEY NOT NULL, and using the clustered index stats, instead of the non-clustered index:

    On Version 2012: the stats based on the new clustered index (PK__Customer_...) don't get updated, exactly how you described here[/url]

    On Version 2014: the 500 + 20% modification threshold suddenly starts taking effect and both of the outdated stats (ix_Customer_ZIP and PK__) get updated, as defined in BOL, and the uniqueness of the returned results no longer matters ...

    So for SQL 2014 this part of your explanation: "... behavior is independent of clustered or non clustered index. Only the UNIQUENESS of an index ... is relevant!" seems to be no longer true.

Viewing 4 posts - 16 through 19 (of 19 total)

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