1

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)

1
  • Does your table have a lot of other columns? In other words, will the SELECT * require a key lookup, or is it really just those three columns plus maybe an identity column?
    – BCM
    Oct 12, 2020 at 16:55

1 Answer 1

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.