question about indexes

Using SS2000. We have a table that we use mostly for reporting. It has about
150 columns in it. Many different queries are run off of it which use a lot
of different columns in the where and order clauses. Is there any limit to
the number of indexes that can be put on a table? Does performance start to
suffer at some point if too many columns are indexed? I think I read
somewhere that if a column is used in a clustered index then it shouldn't be
used in a nonclustered index. Is that true?
Thanks,
Dan D.See inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table?
1 clustered index and 249 non-clustered indexes
Does performance start to suffer at some point if too many columns are
indexed?
Every new index you create will slow down all inserts and deletes, but may
improve some selects, updates and deletes with where clauses
I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
Not necessarily... Use the clustered index to support range searches or
values with many duplicates..
There is never a need to have 2 indexes with the same keys however.
> Thanks,
> Dan D.
>|||> Is there any limit to the number of indexes that can be put on a table
Yes. I believe it's 253 non-clustered indexes plus 1 clustered index.
Statistics count as a non-clustered index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table? Does performance start
to
> suffer at some point if too many columns are indexed? I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
> Thanks,
> Dan D.
>sql