Hi
In a table A (currently holding approx. 4700 rows but will
expand during use) the primary key is a combination of
three columns col1, col2, col3.
The primary key is indexed with a clustered index
(FillFactor 90).
On the columns col1, col2 an nonclustred index have been
added (FillFactor 90) too, this because I guess these two
columns together form a foreign key to another table B
(currently holding approx 1000 rows - number will raise).
The two indexes are both sorted ascending for all values.
My assumption is that the nonclustered index are of no use
and only adds overhead to inserts etc., since both col1
and col2 allready are indexed (in same sequence and sort
order) in the clustered index, so a query on col1 and/or
col2, and a join between table A and B can already use the
clustered index values for the col1, col2 to speed up the
query and the join using a merge join.
Infact the DBCC SHOWSTATISTIC returns the exact same
result (density etc.) for both the clusterd and
nonclustered index (propably because the nonclustered
index after having scanned its only structure will point
to the clustered index anyway and use this afterwards - to
my recollection).
But does the extra col3 of the clustered index make it
useless for queries/order by/joins on only col1, col2?
or
Can I - with a good nights sleep in mind - just delete the
nonclustered index as it is superflous?
Kind regards
Jakob PerssonJacob
On which column(s) do you have clustered index?
What is mostly WHERE caluse in your queries?
If all three columns are participated in your query you probably want to
consider using COVERING index.
A clustered index is more useful where you try to retrive a set of data ( a
lot of rows) on other hand a non clustered index will be more useful where
you retrieve a single row.
Again it is depends upon you requriments, you will have to monitor your
query to find out an appropritate indexes
SET STATISTICS IO may be useful ,also look at excution plan of the query.
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>|||Jakob,
>But does the extra col3 of the clustered index make it
>useless for queries/order by/joins on only col1, col2?
No. The index with col1, col2, col3 is just as useful as the one with col1, col2. So your thinking
is spot on. One thing is of course if col1 and col2 are very narrow and col3 is very wide, but that
is obvious.
Another, more important issue, is that the index on col1, col2 is an NC index which has other
characteristics than a clustered index. The optimizer might use this index to cover queries (or
partially cover), which can have significant performance gains compared to a clustered index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>
