Monday, January 20, 2014

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;

image

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'

image

No comments:

Post a Comment