How to avoid index scans in SQL Server 2005? I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.

Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.

In my query I have to use the CellRow column in the WHERE clause like CellRow &gt; 3 to get data from CellValue field. The problem is when I do it like this then it is always table scan that makes my query very slow.

I have also checked it by putting non-clustered index on CellRow field but still index scan because the CellRow field don't have so much values, it has only 1 to 5 values each time. I can't use CellID in the where clause.

I am using many tables in my query like 

Table_1 is joining with table_2 by using clustered index column and table 2 is joining with table_3 by using non-clustered index column and table_3 is joining with Cell table by using non-clustered index.

The ending story is, I am getting different values from all the tables including cell table but when I use CellRow field in the WHERE clause, I am getting index scan. 

Any solution :)

Thank you so much!
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