SQL Server Statistics AUTO_UPDATE_STATISTICS

It is known that the necessities for SQL Server auto updating statistics is as followed

• If the table has no rows, statistics is updated when there is a single change in table.
• If the number of rows in a table is less than 500, statistics is updated for every 500 changes in table.
• If the number of rows in table is more than 500, statistics is updated for every 500+20% of rows changes in table.
  

However, it is a little tricky that when does the auto-updating action triggered actually.

Have seen a word on one's blog like this, 

'I mplicitly create and update statistics with the default sampling rate (in the SELECT, INSERT, DELETE, and UPDATE commands,  use of a column in a query condition such as a WHERE or JOIN clause causes statistics to be created or updated if necessary  when automatic statistics creation and update is enabled) '

I have done some tests for discovering the triggering mechanism. However, I failed to get a completed conclusion finally.

So here will I display the tests and the results for opening the subject for discussion

drop table test

--create test table

create table test

(

      id int ,

      name nvarchar(200)

)

sp_helpstats test --no statistics, whether created manually or automatically

create statistics stats_testIDontest(id)with fullscan --create a statistics manually on column 'id'

sp_helpstats test --one statistics

DBCC show_statistics ('test','stats_testID') --NULL

 

 

select * from test --void

insert into testvalues(0,'test0')

insert into testvalues(1,'test1')

insert into testvalues(2,'test2')

go --insert into 3 rows for the first tme. That is from voidto 3 rows, so the statistics should be updated

DBCC show_statistics ('test','stats_testID') --null yet

set statisticsprofile on

set statisticsprofile off

select * from test --statistics is null, however I don't know why the EstimateRows = 3 = Rows...?

select name from test--null

select id from test--null

select name from testwhere name = 'test0' --null, however another statistics on column 'name' hasbeen auto created.

sp_helpstats test --new auto stat, key = name

DBCC show_statistics ('test','_WA_Sys_00000002_173876EA') --3 rows, latest.

select * from test where id = 0 --updated. 3 rows, latest.

--then, insertnew 500 rows

declare @i int

set @i = 1;

while @i <= 500

begin

      insert intotestvalues(@i,N't'+convert(nvarchar,@i))

      set @i =@i+ 1

end

set statisticsprofile on

select * from test where id = 0; --unchanged

select * from test where id = 1; --unchanged;

select * from test where id = 3; --unchanged;

select * from test where id = 4; --unchanged;

select * from test where id = 5; --unchanged;

select * from test where id = 6; --unchanged;

select * from test where id = 7; --unchanged;

select * from test where id = 8; --unchanged;

select * from test where id = 9; --unchanged;

select * from test where id = 10; --unchanged;

select * from test where id = 11; --unchanged;

select * from test where id = 21; --unchanged;

select * from test where id = 101; --unchanged;

select * from test where id = 201; --unchanged;

select * from test where id = 301; --unchanged;

select * from test where id = 401; --updated; 503 rows, latest.

select * from test --unchanged

select * from test where name = 't' --updated '_WA_Sys_00000002_173876EA'; 503 rows, latest.