2

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.

1

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.

1

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.