I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side).
The query filters the table by two columns (both
bit), and sorts by a third column (an integer). Note that the query names the columns (it doesn't actually use "*"). The "TOP 1000" is intentional and is part of the production query:
The query is generated--not sure why it generates the way it does.
SELECT TOP 1000 * FROM MyTable m WHERE m.IsFlag1 != 1 AND m.IsFlag2 != 1 ORDER BY m.SomeId
I have a covering index, with the main columns being
The execution plan shows and Index seek on this index, and then a sort. The sort takes up 97% of the cost of the query according to the execution plan.
I tried adding
SomeId as a main column of the index (the third column), but the execution plan remained the same.
I then tried adding a covering index using only SomeId as a main column, leaving the original index in place. The execution plan then does an index scan on the new query, and the operator cost is significantly lower (and is functionally much faster).
All that said, I'd like to optimize this query as much as possible. Is there a way to modify the index so that it simply does a seek?