Assume SQL Server 2012 Standard edition.
My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).
The clustered index (also primary key) is an identity column.
The application using this table has a screen where the user can search on most of the columns. One search field on the screen, which is required, has the option to search starts with or contains, resulting in either
WHERE ABC LIKE 'something%' -- starts with
WHERE ABC LIKE '%something%' -- contains
The actual queries are parameterized, unlike my examples here.
The other search fields do a starts with search just like the first example above, but they are not required. So, any combination of these fields can be searched on resulting in a dynamic where clause.
Given this information, what indexes should be created for optimal performance?
Bearing in mind that I'm new to query performance tuning, my naive strategy for this scenario alone is to create a non-clustered index for each column and using full text search for the column that has the contains search option. I'd love to hear why or why not that's a bad idea and what a better approach would be.
It's known to me that full text searching is how to optimize the case of a "contains" search.
I'm much more interested in the other aspect of the problem: how to optimize for the other search fields which may or may not be present in any given query predicate. The details surrounding the field that can benefit from a full text index are included in my question only to help paint a more complete picture of my particular situation.