Hello everyone,

After reading up on SQL server indexing, I remain as confused as ever :)

I have two columns in a table, cCustCode and cSubCode, both 4 characters. I want to perform lookups on both columns and in Fox, I would not think twice about creating two separate tags on these column. After reading the SQL documentation, I get the *impression* that if I create a single index that includes both columns (a compound index) ie.,

CREATE INDEX IDX_CustSub ON Jobs(cCustCode, cSubCode)

then if I SELECT with the WHERE filtering on either cCustCode or cSubCode (but never both), SQL server will be able to use the single index specified above. Obviously, if I created an index expression in Fox that used cCustCode + cSubCode, this index would be of no use doing a SEEK of a cSubCode value only.

If anyone can confirm this behaviour, it would be appreciated. If SQL server can indeed take advangtage of a single index for any column in the index expression, is it likely to be any less efficient than separate indexes on the same column?

I don't want to create SQL server indexes if they aren't necessary. Any help with this would be really great !

Thank you.



Angie.