Part of an index Usage

Does anyone know if SQL is smart enough to that it has an index already setup if you only use part of the keys in the index? For example, Lets say field1,field2,field3 are setup as an index on a table, and you want to do a read on just field2 and field3. Does it do a serial read of does it use the index that is setup with field1,field2 and field3? Thank you all for all your help in advance.
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
the index on (field1+field2+field3) Will be used only for these searches
- field1 alone
-field1 + field2
- field1+field2 + field3

may use it partially for  (field1 + field3) using a bookmark  provided no other index which covers these two

anything emse it uses a scan
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
In order for the SQL query optimizer to select the multi-column index, ALL three columns would need to appear in the condition--typically in the same order as in the index definition. If you are missing one, the QO is likely to perform a full scan unless you have another index that satisfy the criteria (e.g., an index on field2, field3, or both).
Just check it at: Display estimated execution plan in MS SQL Server Management Studio.
All Courses

From novice to tech pro — start learning today.