Statistics that are neither auto_created nor user_created

Posted on

Question :

So far, I was under the impression that there are two types of statistics objects in SQL Server:

  • automatically-created statistics, which are created by SQL Server on index columns or single columns to improve performance, and

  • user-created statistics, which are created manually with a CREATE STATISTICS statement.

Thus, I was quite surprised to see the following query:

SELECT s.name, s.stats_id, s.auto_created, s.user_created
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
       INNER JOIN sys.tables AS t ON c.object_id = t.object_id
 WHERE t.name = 'mytable'

yield the following result:

name                    stats_id   auto_created   user_created
--------------------------------------------------------------
PK__mytable__7A73B1B8       1          0              0
SomeIndex                   2          0              0
SomeIndexedColumn1          3          0              0
SomeIndexedColumn2          4          0              0
SomeIndexedColumn3          5          0              0
SomeColumn                  6          0              1

What are those statistics, which are neither auto_created nor user_created? I’ve looked at the documentation of sys.stats, but did not find an answer there.

Answer :

Those are statistics created with the creation of an index.

I guess they somehow infer that the statistics haven’t been created as statistics but as a side effect of an index.

I’ve noticed this in my databases before and they just seem to always be index-related. I can’t find any documentation proving this though.

Yes statistics can be created in 3 ways:

1) Statistics created due to index creation. These statistics have the index name

2) Statistics created by Optimizer(Column statistics). Starts with WA*

3) User defined statistics which are created with CREATE STATISTICS command by the DBA

ref : https://harshdba.wordpress.com/category/sql-server-statistics/

Leave a Reply

Your email address will not be published.