I’m in the process of creating a post about statistics and came across something in SQL Server Management Studio that was driving me crazy. I thought I’d post it here so that others might not waste an hour or two like I did puzzling over this.

When you create in index in SQL Server, SQL automatically creates a statistic for the columns in the index. In the screenshot below, you can see my table named SJSTest, to which I have added a clustered index called, somewhat unoriginally, ClusteredIndex. When you expand the Statistics folder, you can see SQL has automatically created a statistic with the same name as the index.

The clustered index is composed of Col2, Col1, and Col3:

We expect SQL Server to create a statistic with those columns and in that order. But if we look at the properties of the statistic we see this:

What’s going on? Why are the columns in the statistic not in the same order as the columns in the index? Well, it turns out, they are. If we look on the Details page, we see the density vector is, in fact, created as Col2, Col1, Col3, which is the order of the columns in the index:

At the time, I happened to be flipping back and forth between the property pages of indexes and statistics a lot and this really confused me. In the property page for an index, the columns are listed in the order the are in the index, but on the property page for statistics, the columns are displayed in the order they exist in the table.

I’m using SSMS version 13.0.15600.2. It would be nice if they made in the statistics property page behave the same as the index property page in a future version.

I have created a Connect item for this. If you agree, please vote for it.