During the SQL Server Training, I frequently noticed confusion in people in terms of Statistics. Many people have no idea on how Statistics works. There are so many misconceptions with respect to Statistics. I recently had an interesting conversation with one attendee who believed that Statistics only exists on Column if there is an Index on the Column, or if we explicitly create Statistics on it.
The truth is, Statistics can be in a table even though there is no Index in it. If you have the auto- create and/or auto-update Statistics feature turned on for SQL Server database, Statistics will be automatically created on the Column based on a few conditions. Please read my previously posted article, SQL SERVER – When are Statistics Updated – What triggers Statistics to Update, for the specific conditions when Statistics is updated.
Let us see one example where we could observe how Statistics is created automatically.
/* In this example we will see effect of unused index on updating database We will create unused indexes on table and see the performance degradation for insert */ USE AdventureWorks GO ALTER&nbsp;DATABASE AdventureWorks SET AUTO_CREATE_STATISTICS ON; GO -- Create Table CREATE TABLE StatsTable (ID INT, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(100)) GO -- Insert One Hundred Thousand Records INSERT INTO StatsTable (ID,FirstName,LastName,City) SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID, 'Bob', CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith' ELSE 'Brown' END, CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino' WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles' ELSE 'Houston' END FROM sys.all_objects a CROSS JOIN sys.all_objects b GO /* Now Check the statistics on the Table As the table is just created there should not be any statistics on it and will display "This object does not have any statistics or indexes." */ sp_helpstats 'StatsTable', 'ALL' GO
From the example above, it is very clear that if the auto-update Statistics setting is enabled in the database, it will create the necessary Statistics based on the columns where certain conditions applied.
-- Run following few queries on the table SELECT * FROM StatsTable WHERE ID = 110 GO SELECT * FROM StatsTable WHERE City = 'Houston' GO /* Now Check the statistics on the Table again You will see two different statistics created on respective columns used in WHERE clause. */ sp_helpstats 'StatsTable', 'ALL' GO
/* Now let us try with multiple Column in WHERE clause */ SELECT * FROM StatsTable WHERE ID = 110 AND City = 'Houston' AND FirstName = 'Bob' GO /* Now Check the statistics on the Table again You will see it will create statistics for the column used in WHERE clause if it was not created earlier. */ sp_helpstats 'StatsTable', 'ALL' GO -- Clean up Database DROP TABLE StatsTable GO
Reference: Pinal Dave (http://blog.SQLAuthority.com)