I have a table named
Cell which has columns like
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 > 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!
CREATE TABLEbatches for these tables. Your description is very hard to follow.
CellRowis just not very selective - 5 possible values, 100'000 rows = roughly 20'000 rows for each value. SQL Server's query optimizer probably recognizes this and figures it's easier and more efficient to do a index scan. The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2% or max. 5% of the rows for each query
CellRow, Cell_IDto benefit this type of range query (and adding a new unique NCI for