column positions in an index | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

column positions in an index

Hi, just wondering whether there would be a performance gain/loss whilst querying tables with different combinations of columns that make up an index. For example: create table #tempind (
colA varchar(50),
colB varchar(50),
colA varchar(50)
)
CREATE UNIQUE CLUSTERED INDEX [BASE_CLUSTERED_INDEX] ON [#tempind] ([colA], [colB], [colC]) WITH FILLFACTOR = 90, PAD_INDEX OR CREATE UNIQUE CLUSTERED INDEX [BASE_CLUSTERED_INDEX] ON [#tempind] ([colB], [colC], [colA]) WITH FILLFACTOR = 90, PAD_INDEX OR CREATE UNIQUE CLUSTERED INDEX [BASE_CLUSTERED_INDEX] ON [#tempind] ([colC], [colB], [colA]) WITH FILLFACTOR = 90, PAD_INDEX etc.. effectively have 9 different index combinations in this case.
If all the columns are of same datatype and size, then the order of the columns will be decided on the factor that – which column is more frequently used in the joins, order by and where clauses (for selecting large range values) if you create the index as "…ON [#tempind] ([colA], [colB], [colC])" and order the table as colb, colc, cola., you may not get the full use of the index. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
It is in most cases a bad idea to create a composite clustered index. Remember that the clustering index keys are stored as bookmarks with every nonclustered index on the same table. So essentially, you’re widening those nonclustered index greatly. That in turn means you can store less index rows on an index page and thus SQL Server has to perform more reads to fulfill a request. Apart from this, put the most selective column in the first position. Statistics are stored only for the first column of a composite index and if that column isn’t very selective, the optimizer might choose to ignore the index at all and go straight for a scan. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Ideal Condition for a clustered index is…
1) Unique
2) Narrow
3) Static
4) Ever-Increasing Order Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
]]>