February 26, 2016 at 12:45 pm
It took me over two hours to figure this one out - my personal record, I think.
Thanks, Uwe!
March 9, 2016 at 4:56 pm
Wow, what a question.
March 10, 2016 at 11:52 am
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
September 19, 2016 at 9:30 am
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 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply