SQLServer indexing for search critieria

In a sqlserver 2008 database we have a开发者_JAVA百科 table with 10 columns. In a web application the user interface is designed to allow the user to specify search criteria on some or all of the columns. The web application calls a stored procedure which dynamically creates a sql statement with only the specified options in the where clause, then executes the query using sp_executesql.

What is the best way to index these columns? We currently have 10 indexes, each one with a different column. Should we have 1 index with all 10, or some other combination?

The bible on optimizing dynamic search queries was written by SQL Server MVP Erland Sommarskog:

http://www.sommarskog.se/dyn-search.html

For SQL Server 2008 specifically:

http://www.sommarskog.se/dyn-search-2008.html

There is a lot of information to digest here, and what you ultimately decide will depend on how the queries are formed. Are there certain parameters that are always searched on? Are there certain combinations of parameters that are usually requested together? Can you really afford to create an index on every column (remember that not all will [edit] necessarily be used even if multiple columns are mentioned in the where clause, and additional indexes are not "free" - you pay for them in maintenance)?

A compound index can only be used when the leftmost key is specified in the search condition. If you have an index on (A, B, C) it can be used to search values WHERE A =@a, WHERE A=@a AND B=@b, WHERE A=@a AND C=@c or WHERE A=@a AND B=@b AND C=@c. But it cannot be used if the leftmost key is not specified, WHERE B=@b or WHERE C=@c cannot use this index. Therefore 10 indexes each in a column can each be used for on specific user criteria, but 1 index on 10 column will only be useful if the user includes the criteria on the first column and useless on all other cases. At least this is the 10000ft answer. There are more details if you start to digg into it.

For a comprehensive discussion of your problem and possible solutions, see Dynamic Search Conditions in T-SQL.

It completely depends on what the data are: how well they index (eg an index on a column with only two values isn't going to help you much), how likely they are to be searched on, and how likely they are to be search on together.

In particular, if column A is queried a lot, and column B tends only to be queried when also querying column A, a compound index over (A, B) will make queries that search for particular values of both columns very fast, and also give you the benefits of a single index on A (but not B) for free.

It is possible that one index per column makes sense for your data, but more likely not. There will probably be a better trade-off taking into account the nature of your data and schema.

Personally I would not bother using a stored procedure to create dynamic SQL. There are no performance benefits compared to doing it in whatever server-side scripting language you're using in the webapp itself, and the language you're writing the webapp in will almost always have more flexible, readable and secure string handling functions than SQL does. Generating SQL strings in SQL itself is an exercise in pain; you will almost certainly get some escaping wrong somewhere and give yourself an SQL-injection security hole.

One index per column. The prioblem is that you have no clue about the queries, and this is the most generic way.

In my experience, having combined indexes does make the queries faster. In this case, you can't have all possible combinations.

I would suggest doing some usage testing to determine which combinations are used most frequently. Then focus on indexes that combine those columns. If the most frequent combinations are:

C1, C2, C3 C1, C2, C5

... then make a combined index on C1 and C2.