Automatic update of outdated statistics

• Revenant

  SSC-Forever

  Points: 42467

  More actions

  February 26, 2016 at 12:45 pm

  #1861535

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

  Thanks, Uwe!

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  March 9, 2016 at 4:56 pm

  #1864155

  Wow, what a question.

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  March 10, 2016 at 11:52 am

  #1864347

  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: 1946

  More actions

  September 19, 2016 at 9:30 am

  #1901656

  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.