composite index

Hi

I have a table with a 3 column composite cluster index in sql 2005

table1 ( a uniqueidentifier, b uniqueidentifier, c int, d varchar(1000))

the composite index is on a,b,c

when I write a query

select * from table1 where a = 'asd' and b = 'afasddf'

the query analyzer uses cluster index seek

but if I use the table in a join

select * from table1 inner join table2 on table1.a = table2.a

and table1.b = 'sdsfds'

the query optimizer is using a cluster index scan on b, I can't limit the query on table1.b in a join?

thanks

Pauli

create an index on t1.b should do it.

e.g.

create table #t1(i uniqueidentifier, j uniqueidentifier, k int, primary key(i,j,k))
create table #t2(i uniqueidentifier, j uniqueidentifier, k int, primary key(i))
create index ix_ on #t1(j)
go
set showplan_text on
go

declare @.j uniqueidentifier
set @.j=newid()

select * from #t1 t1 join #t2 t2 on t1.i=t2.i and t1.j = @.j
go
set showplan_text off
go
drop table #t2,#t1

|||

paulixml wrote:

the query optimizer is using a cluster index scan on b, I can't limit the query on table1.b in a join?

Apparently in your particular case the optimizer has concluded that doing the join and then filtering the output is more expensive than first performing a table scan and filtering the intermediate output and then doing a join on a much smaller (hopefully) set.

It all really depends the existing indexes in BOTH your tables table1 and table2 (and not just the availability of the index on [a,b,c] in table1), AND, of course, on the actual data - hence, on the statistics available to the optimizer. One could easily cook up various column value distributions for your existing tables/indexes where the very same query will produce very different execution plans.