I have a table with a defined non clustered index on 3 columns.
CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable]
(
[Col1] ASC,
[Col2] ASC,
[Col3] ASC
)
Will it be used if i do a request with a where clause on a single column or do I need to create a new index on the specified column?
e.g. :
SELECT TOP 1000 * from MyTable WHERE Col2 in (1,2,3)
The index can be SEEKed if you search on Col1 (first column), which implies that it can also be used if you search for Col1 AND Col2.
It can possibly be scanned if you search for Col2 (i.e. the other column(s)) but not Col1. But if it is depends on whether it covers the index, estimated selectivity and stuff like that. And even if it would be used, a scan is never as effective as a seek (rest being equal).
