Soldato
- Joined
- 25 Mar 2004
- Posts
- 15,990
- Location
- Fareham
Hi all,
I have a table with approx 7 million rows and 20~ columns.
During queries, a dynamic SQL query is built based on input on a front-end website, and then the table is queried with the parameters.
What is the best indexing strategy here? I have a primary key which is an auto incrementing identity integer field, and this is also used for my Clustered Index.
For my other indexes should I have one index for the other fields which are queried commonly, or should I have separate indexes for those? I'm currently using separate indexes for each field.
A couple of my columns use full text indexes as I need to query values which may be in the middle of the column value, would it be appropriate to use this full text index across other columns in the table or are the non-clustered indexes better?
I have a table with approx 7 million rows and 20~ columns.
During queries, a dynamic SQL query is built based on input on a front-end website, and then the table is queried with the parameters.
What is the best indexing strategy here? I have a primary key which is an auto incrementing identity integer field, and this is also used for my Clustered Index.
For my other indexes should I have one index for the other fields which are queried commonly, or should I have separate indexes for those? I'm currently using separate indexes for each field.
A couple of my columns use full text indexes as I need to query values which may be in the middle of the column value, would it be appropriate to use this full text index across other columns in the table or are the non-clustered indexes better?