clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nickThe only case I can think of is when the NC covers the query and you have lo
w selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than t
he CL pages for the
corresponding data-range over the column. However, I suspect that this is th
e PK and possibly even
some surrogate key, so an NC index on this column will probably not cover th
at many queries.
I've seen stranger things with commercial software databases... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typic
al
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single recor
d
> lookup (nonCIX)?
> TIA - nick|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typic
al
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single recor
d
> lookup (nonCIX)?
> TIA - nick|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important
example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, a
nd probably SQL Server
will pick the NC index over the most narrow column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...[vbcol=seagreen]
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:|||Tibor Karaszi wrote:
>
> which is a case where the index covers the query, but possibly an importan
t example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query,
and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan