Here’s what the statistics look like on the table in Object Explorer. If you want to verify which column is in each auto-created statistic, you can do that with this query: SELECT s.name, s.auto_created, s.user_created, as colname FROM sys.stats AS s JOIN sys.stats_columns as sc on s.stats_id=sc.stats_id and s.object_id=sc.object_id JOIN sys.columns as c on sc.object_id=c.object_id and sc.column_id=c.column_id WHERE s.object_id=OBJECT_ID('agg.
(That is pretty much limited to weirdoes like Share Point.) In rare situations, manually creating a multi-column statistic or a filtered statistic can improve performance…
These are not considered ‘user created’ even though our user query was the cause of them being auto-generated.
(“User created” means someone ran a CREATE STATISTICS command.) SQL Server can now use the statistic on Gender and the statistic on First Name Id for future queries that run. First Name By Year table has a clustered primary key, and here is the statistic that was created along with that index: If columns are important enough to index, SQL Server assumes that it’s also important to estimate how many rows would be returned by that index when you query it.
Whenever you create an index in SQL Server, it creates a statistic associated with that index. You can’t drop statistics associated with indexes (unless you drop the index). SQL Server does a really good job creating single-column statistics automatically.
Statistics will continue to be created on single columns when queries run as long as the “Auto Create Statistics” database property remains on.