Help on understanding multiple columns on an index?
- by Xaisoft
Assume I have a table called "table" and I have 3 columns, a, b, and c.
What does it mean to have a non-clustered index on columns a,b?
Is a nonclustered index on columns a,b the same as a nonclustered index on columns b,a? (Note the order).
Also, Is a nonclustered index on column a the same as a nonclustered index on a,c?
I was looking at the website sqlserver performance and they had these dmv scripts where it would tell you if you had overlapping indexes and I believe it was saying that having an index on a is the same as a,b, so it is redundant. Is this true about indexes?
One last question is why is the clustered index put on the primary key. Most of the time the primary key is not queried against, so shouldn't the clustered index be on the most queried column. I am probably missing something here like having it on the primary key speeds up joins?
Great explanations. Should I turn this into a wiki and change the title index explanation?
