/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

When SQL Server uses non-clustered indexes 2. Statistics

2010-10-27
*****************************************************/

set nocount on
go


create table dbo.Data
(
	ID int not null identity(1,1),
	Field int not null,
	Data char(100) not null,
	primary key(ID)
)
go


declare
	@I int = 0

begin tran
	while @I < 100000
	begin
		insert into dbo.Data(Field, Data)
		values(@I % 1000, 'A')
		
		select @I += 1
	end
commit
go

create index IDX_Data_Field
on dbo.Data(Field)
go

update Statistics dbo.Data with fullscan
go

dbcc show_statistics('dbo.Data',IDX_Data_Field)
go	

set statistics profile on
go

-- Enable "Display actual execution plan 

select COUNT(*) from dbo.Data 
where Field in (8,9, 10, 11)

