SQL Server two columns in index but query on only one

Question

In legacy code I found an index as follows:

CREATE CLUSTERED INDEX ix_MyTable_foo ON MyTable
(
    id ASC,
    name ASC
)

If I understand correctly, this index would be useful for querying on column id alone, or id and name. Do I have that correct?

So it would possibly improve retrieval of records by doing:

select someColumn from MyTable where id = 4

But it would do nothing for this query:

select someColumn from MyTable where name = 'test'

Legra

Yes you're understanding is absolutely correct - any multi-column index is only useful if you're querying on the (n) left-most columns - in your case, either on id alone, or on id and name - but not on name alone. – Viens

what if my query is like select someColumn from MyTable where id=id and name ='test' would the clustered index of any benefit? – Closehauled

Answer 1

Yes, you are right. But in case when you have table with many columns:

A
B
C
D
..
F

where your primary key index is for example (A), if you have second index like (B,C), the engine may decide to use it if you are using query like this:

CREATE TABLE dbo.StackOverflow
(
    A INT
   ,B INT 
   ,C INT 
   ,D INT 
   ,E INT
   ,PRIMARY KEY (A)
   ,CONSTRAINT IX UNIQUE(B,C)
)

SELECT A     
      ,C 
FROM dbo.StackOverflow
WHERE C = 0;

So, if an index can be used as covering, the engine may use it even you are not interested in some of the columns, because it will decide that less work (less reads) will be performed.

In your case, it seems that a PK on id column is a better choice with combination with second index on the name column.

Vespine

If my where clause mentions both columns, but the where clause order is not the same as in the index, the index is still applied right? Example: select 1 from MyTable where name='test' and id=4 – Legra

Yes, the index will be used. The clause order in this can does not matter. – Vespine