Can a query derive benefit from a multicolumn index for single-column filtering?

Assume that an index has been created using three columns. Will SQL Server use the index if the 2nd column is used for filtering?

I asked this question recently at a session, as expected, answer of majority was “No”. Many think that multicolumn index is not beneficial unless all columns are used for filtering or columns from left-to-right, in order, are used. Order is important but SQL Server do leverage the index even for 2nd and 3rd columns. If your answer for the question above is “No”, here is a simple example for understanding it.

USE AdventureWorksDW2012

GO

-- creating an index using 3 columns

CREATE INDEX IX_DimProduct_1 ON dbo.DimProduct

    (EnglishProductName, WeightUnitMeasureCode, SizeUnitMeasureCode)

-- filter using the first column

SELECT ProductKey, EnglishProductName

FROM dbo.DimProduct

WHERE EnglishProductName = 'Road-750 Black, 44'

-- filter using the second column

SELECT ProductKey, EnglishProductName

FROM dbo.DimProduct

WHERE WeightUnitMeasureCode = 'G'

-- filter using the third

SELECT ProductKey, EnglishProductName

FROM dbo.DimProduct

WHERE SizeUnitMeasureCode = 'CM'

-- cleaning the code

DROP INDEX IX_DimProduct_1 ON dbo.DimProduct

This code creates an index using three columns. As you see, the SELECT statements use the columns used for the index; first SELECT uses EnglishProductName, second uses WeightUnitMeasureCode, and third uses SizeUnitMeasureCode. Have a look on query plans;

Plans clearly show that SQL Server leverages the index for all three SELECT statements. However, note the way it has been used. For the first SELECT, Index Seek has been used but Index Scan for the second and third. This means, filtering with left-most columns gets more benefits but filtering with right-most columns do not get it optimally.

See below query, filtering starts on the left-most column and two columns are used. As you see, it is “Index Seek”; Index is used optimally.

-- filter using the first and second columns

SELECT ProductKey, EnglishProductName

FROM dbo.DimProduct

WHERE EnglishProductName = 'Road-750 Black, 44' 

    AND WeightUnitMeasureCode = 'G'