It’s an interesting question that I’d never considered before. I had an instinct about the answer, but the scientist in me wanted to test and prove it, so here we go. The scenario and code are similar to my original post, so this should be a quick read for many of you 🙂

The Setup

Start with a copy of the AdventureWorks2012 database, which you can download from CodePlex, on a 2012 SP1 instance, and confirm that the Auto Update Statistics option is enabled:

Validate the current statistics and modifications; the statistics should be current since we just created the indexes, and modifications should be 0 since we haven’t run any inserts, updates, or deletes:

/*
Note: I refer to this query throughout the post as "the sys.stats query", rather than including it multiple times!
*/
SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'Sales.TestSalesOrderDetail');

Current statistics and no modifications

Excellent, we’re starting with a clean slate.

The Test

Let’s first add a little bit of data (1000 rows), to up our modification count:

When we run our sys.stats query again, we see the 1000 modifications for both indexes, because both indexes had rows added:

Statistics and modifications after adding 1000 rows

Great. Now add a new column to the table…let’s pretend that we have a new ProductID, but we want to keep the existing one for historical reasons, so our new column is, creatively, NewProductID:

ALTER TABLE [Sales].[TestSalesOrderDetail] ADD [NewProductID] INT;

Did adding this column cause statistics to update? This was our main question, and if we run our sys.stats query we see:

Statistics and modifications after adding new column

Nothing changed… Adding a new column to the table does not invoke an automatic update to statistics. Nor should it. We haven’t modified data in any existing statistics. We would certainly expect a change in query plans, for existing queries that are modified to use this new column, but adding a new column to a table does not cause any existing statistics for the table to update.

More Testing (because it’s good practice to keep proving out what we expect)

If we check sys.stats, we see that our nonclustered index, IX_TestSalesOrderDetail_ProductID, has three more modifications than it had previously, but not enough data has changed overall for statistics to be invalidated.

And there you have it. Adding a column to a table will not cause an automatic update to statistics to occur. Automatic updates occur only when the threshold for modifications has been exceeded for a statistics key.