>>In VFP I would have indexed the columns the user might query on. Fox would build the bitmap for each column in the where, and then AND or OR them as the where clause conditions stated. Have you tried making single indexes per column without including other columns (not covered)? SQL Server does a similar thing as VFP - it does an index seek on each column referenced in the WHERE, then it would Merge Join those results, then do a Key Lookup to get the record data - if you have a clustered primary key.
>
>Each of the field have their own single index. Those ones do not have included fields. There is also indexes which combined various fields.

Your users can decide to query on any combination of up to two dozen columns? My preferred use case is to build dynamic SQL and allow the user to query any way they want. SQL will build all the execution plans and cache them based on the queries your users construct. Take the user's query and sort the columns alphabetically. That will increase the chance of reusing an existing execution plan.

My understanding is use INCLUDE to let SQL return column data from the index without accessing the table - covering the query. That is going to be complex if you have 2 dozen indexes.

If your user says field1 = 'text1' and field2 = 'text2' then you only need simple indexes on field1 and field2. You do not need a composite index. Having the composite index allows SQL to use it. In this example

CREATE NONCLUSTERED INDEX [xfield2field1] ON [dbo].[testtable] ([field2], [field1])
CREATE NONCLUSTERED INDEX [xfield1] ON [dbo].[testtable] ([field1])
CREATE NONCLUSTERED INDEX [xfield2] ON [dbo].[testtable] ([field2])

With this query

SELECT * FROM [dbo].[testtable] where field2='text2'

the execution plan shows that it used index xfield2field1. I suppose SQL uses the first index it can find. After deleting the xfield2field1 index, the above sql used index xfield2 which is what I would have preferred. It's basically how Fox optimizes. I do not see any large benefit to it using xfield2field1.

It also can use xfield2field1 to handle this query.

select * from [dbo].[testtable] where field1='text1'

If you do not have the simple index on field1 it will advise you to add it. Why? Are composite indexes not good enough?

There may be a little benefit in using one composite index to handle several queries. How long would it take sql to search the composite index of 16 keys to find the 10th key is the one to use? It must have to bypass 14 of the keys to find the two keys needed below.

select * from [dbo].[testtable] where field1='text1' and field16='text16'

Is one composite index of 16 keys better than 16 simple (non-composite) indexes? In your case, you have two dozen keys. So that means you'd need at least 2 composite indexes (16 keys in one and 8 in the other). Are two composite indexes better than 2 dozen simple indexes? My *guess* would be to add only the 2 dozen simple indexes, avoid composite keys and not INCLUDE any other columns.

If you have 2 composite indexes and your user queries field1='text1' and field24='text24'

SQL will have to exclude 22 keys across 2 composite indexes. It will have to merge join the results. Can that be any faster than using indexes field1 and field24 and merge joining the results? The latter sounds simpler to me. What about 3 indexes? Is that only one merge join?

The 24 indexes will do exactly what I expect. For 2 columns it will do 2 index seeks and 1 merge join, for 3 it will do three index seeks and two merge joins. I have not done any timing tests. Maybe 2 fully covered composite indexes are better? Can SQL update two composite indexes faster than 24 simple indexes? That may be a benefit too. It would be a nice article contrast 2 fully covered composite indexes versus 24 non-covered simple indexes versus 24 fully covered simple indexes.