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

Composite index and index seek
2010-10-20
*****************************************************/
set nocount on
go

create table dbo.CompositeIndex
(
	ID int not null,
	Placeholder char(100) null,
	Column1 int not null,
	Column2 varchar(48) not null,
	Column3 varchar(48) not null,
	
	constraint PK_CompositeIndex
	primary key clustered(Id)
)
go

declare
	@I int
select @I = 0

begin tran
	while @I < 250000
	begin
		insert into dbo.CompositeIndex(
			ID, Column1, Column2, Column3)
		values(
			@I, @I % 200, 
			CONVERT(varchar(48), NewID()),
			REPLICATE(CHAR(65 + @I % 26),10)
		)
		
		select @I += 1
	end
commit
go

create index IDX_CompositeIndex_Column1_Column3
on dbo.CompositeIndex(Column1, Column3)
go

/* Let's enable execution plan and 
test when index seek can be used */
select * from dbo.CompositeIndex
where Column1 = 100 and Column3 like 'A%'
go

select * from dbo.CompositeIndex
where Column1 > 190 and Column3 like 'A%'
go

select * from dbo.CompositeIndex
where Column1 = 100
go

select * from dbo.CompositeIndex
where Column2 like 'A%'
go
