>>>>Whether the clustered key is primary or not is irrelevant. Even it the clustered index is not unique, it would do the same. However the key will be padded with an additional unique identifier. Even with heap tables (tables without a clustered index) it still would do a key lookup that refers to the actual row number.
>>>
>>>Thanks for clearing that up.
>>
>>Welcome
>>
>>
>>>>The cards would be different if you execute a query
>>>>
>>>>SELECT * FROM TestTable WHERE Field2 = "Test" AND Field1 = "Me"
>>>>
>>>>This is because in the scenario of single field indexes, SQL server might decide (depending on the internal statistiscs and uniqueness of either field) to use two indexes, having to merge them before getting the result bitmap.
>>>>
>>>>In the case of an compound index, it would only have to process the one index and will find the result much quicker in that as well (less I/O).
>>>
>>>But how can anyone create all the compound indexes to handle every combination of 24 columns? It's possible by creating a script, but that seems insane. That's what I'm trying to tell Michel. I said I do not see any benefit to it using xfield2xfield1 and you confirmed that it would end up doing a full index scan. So again, single field indexes look like the way to go.
>>
>>In general, you are correct. Creating an index on each individual field is the only way to make sure that it can be used in the optimal way if you do not know the nature of the queries to be asked.
>>
>>however, if you have fields frequently used in joins a compound key can be very helpful. (e.g dep_pk, dep_departmentname)
>>
>>>>It is not that simple. Compound indexes can be very helpful in cases where it is fully covering. SQL server then never has to touch the table. In the example above.
>>>>
>>>>SELECT Field1, Field2 FROM testable WHERE Field2 = "Test"
>>>>
>>>>With the proposed compound index on field2,field1 the query is now fully covered by the compound index. SQL server will not touch the table at all and will return the result in the most efficient way possible in SQL server.
>>>
>>>But to handle 24 columns, Michel will have to have every permutation of the columns as separate indexes.
>>
>>yes, that's why I think it does not make sense.
>>
>>>So you are agreeing trying to build composite keys to handle 24 permutations is a bad idea?
>>
>>In general, yes I agree. The only thing you will achieve is that in stead of SQL server doing a table scan, it will do a full index scan. Since the index would be smaller, it still would be a bit faster, but still take a lot of time if you have multi-mullion tables
>>
>>I usually use compound indexes when tuning particular queries whether or not achieving full indexes coverage.
>
>And Michel would need an index hint to tell SQL not to use the compound keys. Or better yet, always use the index hint so it uses the keys he expects?

I've played with hints a lot, but in these kind of scenarios they might be hurting more than it helps. You can really screw up the performance by using hints. In most scenarios SQL server is smart enough to determine the execution plan.... and if its not, its most likely because you do not have the right indexes or statistics.

Walter,