SQL SERVER – Default Statistics on Column – Automatic Statistics on Column

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 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 (https://blog.sqlauthority.com)