How statistics work for indexes.

There were some interesting questions about statistics for Indexes:
- When Index statistics are created?
- Are Index statistics combined or separate for each column?
- If statistics are separate are they created all together?

In this post I'll try to answer these questions.

_______________________________________________________

At first will verify that my test database has right settings:

USE [TestModes] GO SELECT is_auto_create_stats_on, is_auto_update_stats_on FROM sys.databases WHERE name = 'TestModes';

Result shows that my database is set to Auto-Create and Auto-Update Statistics:






Now will create a table, fill it with some data, build an index and look for statistics on a table:

CREATE TABLE ABC(ID INT IDENTITY(1,1), F CHAR(1)); GO INSERT INTO ABC(F) VALUES ('A'),('B'),('C'),('D'),('E'), ('F'),('G'),('H'),('I'),('J'), ('K'),('L'),('M'); GO CREATE INDEX NCLIX_ABC ON ABC (ID,F); GO SELECT t.name as Table_Name, c.name as Column_Name, s.name as Stat_Name, STATS_DATE(t.object_id,s.stats_id) AS Last_Updated FROM sys.tables as t INNER JOIN sys.columns as c ON t.object_id = c.object_id INNER JOIN sys.stats_columns as sc ON sc.object_id = t.object_id and sc.column_id = c.column_id INNER JOIN sys.stats as s ON s.object_id = t.object_id and sc.stats_id = s.stats_id WHERE t.name = 'ABC';

As you can see, after index creation statement, SQL engine auto created statistics for that particular index:

Here we can answer first question: Statistics for Indexes are created at the time of Index creation.

Now will take a look at these statistics:

DBCC SHOW_STATISTICS (ABC,NCLIX_ABC);

From that output you can see that statistics are generated only on the very first column of the index.

That is our answer for the second question: Statistics collected separately for all columns within an index.

Now will try to use the index we created and check statistics after:

SELECT * FROM ABC WHERE ID = 5 and F = 'F'; GO SELECT t.name as Table_Name, c.name as Column_Name, s.name as Stat_Name, STATS_DATE(t.object_id,s.stats_id) AS Last_Updated FROM sys.tables as t INNER JOIN sys.columns as c ON t.object_id = c.object_id INNER JOIN sys.stats_columns as sc ON sc.object_id = t.object_id and sc.column_id = c.column_id INNER JOIN sys.stats as s ON s.object_id = t.object_id and sc.stats_id = s.stats_id WHERE t.name = 'ABC';

As the result of that new statistic for a column "F" has been created.

 So, to answer our questions: Statistics for Indexes are created at index creation and only for first column. Statistics for other columns are created on demand when any query try to use these columns.



Just do not forget to clean out after yourself:

DROP TABLE ABC;