how much index are reasonable on a table?

I have a table with about 25 colums. In my application, we have provided an
interface through which , search can be made on different column, either
alone or with combination of different columns.
Now there are about 10 columns , upon which search can be made, either
indicidually colum, or combined colums.
Now in order to have a fast search, I have to apply index on different
columns.
Is it ok to apply index on all the 10 columns that are used in search or
what else is the solution to have a quick search. I cant change Database
schema.examnotes (SOHAIL MALIK@.discussions.microsoft.com)
writes:
> I have a table with about 25 colums. In my application, we have provided
> an interface through which , search can be made on different column,
> either alone or with combination of different columns. Now there are
> about 10 columns , upon which search can be made, either indicidually
> colum, or combined colums.
> Now in order to have a fast search, I have to apply index on different
> columns.
> Is it ok to apply index on all the 10 columns that are used in search or
> what else is the solution to have a quick search. I cant change Database
> schema.
Not all indexes might be useful. Say for instance that one column
you can search for is a gender column. If the search is for "all males",
then that index will not help anyway.
If searching for "all" is not meaningful, you may to want to constrain
users by saying "you must include one of A, B, C, D or E in the search",
where A, B, C, D and E are the column on which you have indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||so it means, i should apply index on all 10 columns, that are used in search
.
"Erland Sommarskog" wrote:

> examnotes (SOHAIL MALIK@.discussions.microsoft.com)
> writes:
> Not all indexes might be useful. Say for instance that one column
> you can search for is a gender column. If the search is for "all males",
> then that index will not help anyway.
> If searching for "all" is not meaningful, you may to want to constrain
> users by saying "you must include one of A, B, C, D or E in the search",
> where A, B, C, D and E are the column on which you have indexes.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||In addition to Erland's suggestions, look at my answer in the
"paralel-universe" post. ;)
ML|||SOHAIL MALIK (SOHAILMALIK@.discussions.microsoft.com) writes:
> so it means, i should apply index on all 10 columns, that are used in
> search.
Nah, what I tried to imply that you should investigate whether all these
indexes are meaningful, and particularly whether searching for all rows
is meaningful.
The result of this investigate could very well be that you should index
all ten columns. I don't the table, nor the data distribution, so I
cannot say.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

how much index are reasonable on a table?

I have a table with about 25 colums. In my application, we have provided an
interface through which , search can be made on different column, either
alone or with combination of different columns.
Now there are about 10 columns , upon which search can be made, either
indicidually colum, or combined colums.
Now in order to have a fast search, I have to apply index on different
columns.
Is it ok to apply index on all the 10 columns that are used in search or
what else is the solution to have a quick search. I cant change Database
schema.Hi Malik
Changing the schema would be a better option. If you create too many indexes
it brings down the performance of the system.
Its not advisible to use too many indexes
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"SOHAIL MALIK" wrote:

> I have a table with about 25 colums. In my application, we have provided a
n
> interface through which , search can be made on different column, either
> alone or with combination of different columns.
> Now there are about 10 columns , upon which search can be made, either
> indicidually colum, or combined colums.
> Now in order to have a fast search, I have to apply index on different
> columns.
> Is it ok to apply index on all the 10 columns that are used in search or
> what else is the solution to have a quick search. I cant change Database
> schema.|||The OP sort of suggests changing the schema is not an option.
Indexes can affect performance negatively - that's true, yet in this
particular case they pretty much seem to be the only option.
Malik,
Consider using indexed views if appropriate, and/or full-text indexing (for
character-based columns) - both as an addition to existing indexes.
Of course another good option is to simply use the profiler and let the
index tuining wizard suggest appropriate indexes based on your workload.
ML|||Hi Malik,
As suggested indexed Views would be better choice. Create a unique clustered
index on the key numeric column. And add non clustered indexes on the other
columns.
Thanks,
Siva
"ML" wrote:

> The OP sort of suggests changing the schema is not an option.
> Indexes can affect performance negatively - that's true, yet in this
> particular case they pretty much seem to be the only option.
> Malik,
> Consider using indexed views if appropriate, and/or full-text indexing (fo
r
> character-based columns) - both as an addition to existing indexes.
> Of course another good option is to simply use the profiler and let the
> index tuining wizard suggest appropriate indexes based on your workload.
>
> ML