SQL it is more flexible, but it cannot slide btree on inner dimensions ( like VFP ).

With
CREATE INDEX IDX_CustSub ON Jobs(cCustCode, cSubCode)
MSSQL use the index for

WHERE cCustCode=
WHERE cCustCode= AND cSubCode=

Then, if you want a fast

WHERE cSubCode=

you have to add a second index.

>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.