Index Intersection column reorder


In SQL Server 2008 Query Performance Tuning Distilled (Apress) Page 136:

The author talks about reordering the columns of an existing index so that it can be used as part of an index-intersection.

I what cases do we need to reorder columns?
Mr_ShawAsked:
Who is Participating?
 
crangryCommented:
I agree with morgulo, but I think your other question is about index intersection.

If you are using more than 1 table in your query, then the intersection for those tables should be on indexes that are ordered correctly.

So, if you have 2 tables:

tab1 -> index ix1 (col2,col1)
and
tab2 -> index ix2 (col1,col2)

And you are trying to join columns like this:

select *
from tab1  t1
inner join tab2 t2
on t1.col2 = t2.col2
and t1.col1 = t2.col1

There is a risk that the query plan might use index ix1 on tab1, and a table scan on tab2.

Switching the column order of the index ix2 to (col2,col1) would help the query optimizer to pick the optimal plan, and use both indexes to join the tables.

0
 
morguloCommented:
No, you must change column order only in index, ie. If you heavly using query:
select * from tab where col1 = ? and col2 = ?
you should create index on col1 and col2. But if you use another query
select * from tab where col2=?
then you should create index on columns (col2,col1) because both queries will use the index. Index won't be used if first index columns aren't used in where.
0
 
Mr_ShawAuthor Commented:
yes it was about intersections
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.