CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2indexing
Registered User
Registered User

Reputation: 1564

Approach to index on Multiple Join columns on same Table?

I've many tables joining each other and for a perticular table I've multiple columns on joining condition.

For e.g.

select a.av, b.qc
TableA a INNER JOIN TableB b 
ON (a.id = b.id and a.status = '20' and a.flag='false' and a.num in (1,2,4))

how should be the approach.

1.   CREATE NONCLUSTERED INDEX N_IX_Test
                 ON TableA (id,status,flag,num)
                 INCLUDE(av);

2.   CREATE NONCLUSTERED INDEX N_IX_Test1
                 ON TableB (id)
                 INCLUDE(qc);

This two approaches I could think off, everytime i see multiple columns for same table on joining condition i make it as composite index and add select list column to include is it fine?

Upvotes: 0

Views: 4518

Answers (2)

Markus Winand
Markus Winand

Reputation: 8716

Different join algorithms need different indexing. Your indexing approaches are only good for nested loops joins, but I guess hash join might be a better option in that case. However, there is a trick which makes an index useful for nested loops as well as for hash join: put the non-join predicates first into the index:

CREATE NONCLUSTERED INDEX N_IX_Test
             ON TableA (status,flag,id,num)
             INCLUDE(av);

num is still last because it's not an equality comparison.

This is just a wild guess, exact advice is only possible if you provide more info such as the clustered indexes (if any) and also the execution plan.

References:

Upvotes: 0

Gary Walker
Gary Walker

Reputation: 9134

If id is a unique key in each table, there is no benefit to the join (harmful in fact) from adding more fields to the index.

Now if ID is not unique and not well distributed and by the using the extra columns, you are making a covering index then yes, you are making an index that will make for fast selects. However the covering index maintenance itself is an extra load on SQL server. Hard to tell from your example if this is what your are saying.

So if ID unique or at least not many duplicates for a given ID, I would be reluctant to add covering indexes unless a large percentage of your queries can be satisfied by selecting from the covering index.

Upvotes: 1

Related Questions