Sql-server – Are multiple column index used in single column where clause

indexsql servert-sql

I have a table with a defined non clustered index on 3 columns.

    [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)

Best Answer

The index can be SEEKed if you search on Col1 (first column).

It can possibly be scanned if you search for the other columns, 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).