1

While querying

select * from sys.stats

I get this result

enter image description here

In the Books Online it is written

enter image description here

But looking at results I can see that there are also some stats which are not created neither automatically nor by a user. Then how were this stats generated? Or who created them?

Thanks for your time!

2

Those are the statistics created when index was created. Quoting from BOL Statistics

The Query Optimizer creates statistics for indexes on tables or views when the index is created. These statistics are created on the key columns of the index. If the index is a filtered index, the Query Optimizer creates filtered statistics on the same subset of rows specified for the filtered index. For more information about filtered indexes, see Create Filtered Indexes and CREATE INDEX (Transact-SQL).

  • Thanks for the answer, but they are created by the SQL Server too, right? Then why they are not marked as auto_created statistics? Why Query Optimizer is not considered a part of SQL Server? – igelr Oct 10 '18 at 7:23
  • 2
    Yes they are created by SQL Server but the two columns that you are looking track altogether different thing. One track stats created by "auto create stats" functionality and other manually by the user. Auto create stats is different from what SQL Server is doing here when index is created. – Shanky Oct 10 '18 at 7:31

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.