Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 218

Table 12-4. Update Statistics Threshold for Number of Changes

Number of Rows Threshold for Number of Changes

0 > 1 insert

<500 > 500 changes

>500 500 + 20 percent of row changes

Row changes are counted as the number of inserts, updates, or deletes in the table.
Using a threshold reduces the frequency of the automatic update of statistics. For example, consider the following
table ( --autoupdates in the download):

IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;

CREATE TABLE dbo.Test1 (C1 INT);

CREATE INDEX ixl ON dbo.Test1(C1);

INSERT INTO dbo.Test1
(C1)
VALUES (0);

After the nonclustered index is created, a single row is added to the table. This outdates the existing statistics
on the nonclustered index. If the following SELECT statement is executed with a reference to the indexed column in
the WHERE clause, like so, then the auto update statistics feature automatically updates statistics on the nonclustered
index, as shown in the session output in Figure 12-29 .

SELECT C1
FROM dbo.Test1
WHERE C1 = 0;