So, my conclusion from that is, the advantage of having 10 indexes on a single column is that a larger number of queries will find an index it can use. The disadvantage being it will have to do checks against the index seek results for each other column filter in the query.
The advantage of 5 indexes with 4 columns each(the first column being different for each index) is that some queries (using all or some of the columns in index order) can find all the results in a single seek. The disadvantage is that now, for a query to use one of the 5 indexes, it must be referencing one of the 5 first columns, otherwise it cannot use any of the indexes.
Am accurate in saying that?
Yes and yes
Also, there is another aspect of this issue we haven't talked about yet: Is there any difference in the maintenance of the two index structures? Will reindexing 10 indexes with single columns take longer than fewer indexes with more columns? Which index structure will slow down inserts more?
Of the top of my head, not sure, but the more places a column appears, the more places if has to be changed and the slower the change can be. That said, test it. I've never noticed a major performance degradation from 5 or 6 indexes. Once that gets to 15 or so, I start worrying.
Your system may be different, so set up some tests and see what the effect is.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass