set nocount on
go
select is_auto_create_stats_on, is_auto_update_stats_on from sys.databases where database_id = db_id()
go
-- Create a new table.
create table dbo.MyTestTable (id int not null)
alter table dbo.MyTestTable add constraint pk_MyTestTable primary key clustered(id)
go
-- Show that there are no stats for the primary key of the newly created table (as expected).
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go
begin tran
-- Increase rowcount from zero to greater than zero, causes the stats on the table to become out of date.
insert dbo.MyTestTable(id) select top 100 row_number() over (order by message_id) from sys.messages
-- Show that stats DO NOT exist after the insert.
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go
-- Cause the stats creation to occur (assumes is_auto_create_stats_on and is_auto_update_stats_on are on).
declare @i int; select @i = count(*) from dbo.MyTestTable where id between 1 and 1 option (recompile)
-- Show that stats DO exist after the query.
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go
-- Rollback the INSERT so the data no longer exists.
rollback
go
-- Show that stats STILL exist after the query.
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go
if object_id('dbo.MyTestTable') is not null drop table dbo.MyTestTable
go