How to avoid index scans in SQL Server 2005?
Any solution :)


No, not really.

As you said yourself - CellRow is just not very selective - 5 possible values, 100'000 rows = roughly 20'000 rows for each possible value. 

SQL Server's query optimizer recognizes this and probably figures it's easier and more efficient to do a index scan rather than a seek for 20'000 rows. 

The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2%, 3% or max. 5% of the rows for each query.

PS: Check your execution plan - does it get the values straight from the index, or does it need a "Key Lookup" step to go get the data?? 

You didn't mention what data types your columns are - if the CellValue isn't too big, you could add it to the index (or at least include it in the index) to avoid costly key lookups:

CREATE INDEX IX_CellRow_CellValues
ON dbo.Cell(CellRow) INCLUDE(CellValue)


You'd still have the index scan, though