SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index/Statistic Order


Index/Statistic Order

Author
Message
david.alcock
david.alcock
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 1204
Hi all.

I've wrote a blog post to ask a question on how statistics are created for new indexes. Essentially I found a clustered index which is composed of two varchar columns, when I looked at the statistics the columns were the opposite way round.

I did a bit of testing on this which I've shown in the post but to summarise it seems that when I modify a column order when creating a clustered index the statistic column order remains the same, as it were basically. For a non clustered index however the statistics reflect the index after the column order modification.

So, why the difference!? :-) Is it the way stats are created at index creation for the different types?

http://sqlclarity.blogspot.co.uk/2017/07/a-question-on-index-and-statistic.html

As always, many thanks!

David

'Only he who wanders finds new paths'
Jacob Wilkins
Jacob Wilkins
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7627 Visits: 10243
I think you're just seeing another symptom of the oddity/quirk/bug with sys.stats_columns reported in this Connect item: https://connect.microsoft.com/SQLServer/feedback/details/1163126/unexpected-stats-column-id-behavior

If you run DBCC SHOW_STATISTICS for the statistics in question, you should see the change in order correctly reflected in both the density vector and the histogram (i.e., the density vector should have a row for LastName, and a row for Lastname, FirstName after the change, while the histogram should show LastName values).

The statistics themselves have changed as expected. It's the stats_column_id in sys.stats_columns that is incorrect, and that's what the GUI uses to display order for the columns.

It's a bit annoying, but at least the statistics themselves are correct; it would be nice if MS responded to the Connect item, though.

As mentioned in one of the threads linked from the Connect item, it seems that for a clustered index's stats, the stats_column_id just reports ordinal position of the columns with respect to their column_id; on that note, I see this behavior even if the first clustered index creation is on LastName,FirstName; it doesn't seem tied to recreating the clustered index.

It does seem limited to clustered index statistics (whether inline with table creation or not). If I leave the table a heap, and create a multi-column statistics object on the two columns, the stats_column_id behaves as documented.

As the workarounds on that Connect item note, for querying and reporting purposes, you could just substitute key_ordinal from sys.index_columns for stats on clustered indexes. Again, annoying, but fortunately not a massive problem Smile

We can hope that if we start upvoting that Connect item MS will maybe pay some attention, but with zero responses in the 27 months it's been open, I'm not holding my breath Smile

Cheers!
david.alcock
david.alcock
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1307 Visits: 1204
Yes, that's exactly it - thank you!

'Only he who wanders finds new paths'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search