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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mr_ShawAuthor Commented:
yes it was about intersections
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.