0

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)

2
  • Will it be used if i do a request with a where clause on a single column Index may be used for searching by its prefix, not by middle expression part - so the answer is 'no'. But this index may be used for fullscanning by compact index instead of wide table scan, especially when the index is covering for the query. or do I need to create a new index on the specified column? It depends.. may be reasonable, maybe not. – Akina Oct 12 '20 at 10:49
  • 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? If so, SQL could decide to scan your nonclustered index instead of scanning the table and then filtering. – BCM Oct 12 '20 at 16:55
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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