Hi , Could you please help to clarify this indexes or point to a good source ?
Table T1 has Col1 Char(5)
Primary Index IX1 on Col1
Unique Index IX2 on Col1 and Col3
Is this column order correct ? or Col1 then Col3, Col2 and col4 is a better order ? Or Column order doesn't matter ?
Instead of above two indexes can we have just a single composite unique index with col1+col3 ? If it is the case can the primary key can use that composite index partially ? Any performance consideration ?
When do you plan to upgrade to 10? With 10 you can include a column on an index. Meaning you have your primary key index on col1 and include col3. So the uniqueness is enforced on col1, but col3 is, also, in your index. Though, is it really worth the extra space? If col1 uniquely identifies each row does having an index on col1, col3 really help your performance at all or does it just slow down your inserts/updates? The only way I see a different index helping you is if the leading column is one of the other columns in your table and you do not know the value of col1.