Why engine choose Index Scan instead of Index Seek for Group By query?

Question

I have query:

SELECT
      M_nvarchar_3
    , MA_nvarchar_40                    
    , W_nvarchar_4
    , LB_nvarchar_2               
    , SUM(ST_decimal_13_3)        
    , SUM(CW_decimal_13_3)
    , SUM(STO_decimal_13_3)    
    , MAX(GJ_nvarchar_7)     
FROM [dbo].[MES]
WHERE L2 = ''
GROUP BY M_nvarchar_3
         , MA_nvarchar_40                    
         , W_nvarchar_4
         , LB_nvarchar_2;

I have created NONCLUSTERED FILTERED INDEX like:

CREATE NONCLUSTERED INDEX IX_M_nvarchar_3_MA_nvarchar_40_W_nvarchar_4_LB_nvarchar_2
ON [dbo].[MES]
(
    M_nvarchar_3 ASC
    , MA_nvarchar_40 ASC
    , W_nvarchar_4 ASC
    , LB_nvarchar_2  ASC
   
)
INCLUDE
(
    ST_decimal_13_3
    ,CW_decimal_13_3
    , STO_decimal_13_3
    , GJ_nvarchar_7
)
WHERE L2 = '';

and has in above query NONCLUSTERED INDEX SCAN. I was thinking that in this case got NONCLUSTERED INDEX SEEK. Could you advice ?

Answer 1

The filtered index isolates the set of rows where L2 = ''. Your query is asking to take that entire subset of rows (the matching rows are subset of the table but also all the rows in the index) and aggregate them with the GROUP BY clause. You would do so by looking at the entire filtered index, or in other words scanning it, especially so since it's covering for this query. If you are getting a scan of the filtered index, then it's the optimal thing to do in this case.