Suppose I have a table
SortTest with fields
Key2 need to perform the following query:
SELECT TOP 1 Data1, Data2 FROM SortTest WHERE Key1 = @key1 AND Key2 = @key2 ORDER BY Sort1, Sort2
In order to opimise it, I created an index for the following sequence
Key1, Key2, Sort1, Sort2
But the execution plan still shows an index scan instead of a seek, for it cannot sort effectively on a field sequence that does not start an index. In order, therefore, to optimise the query, I had to add they keys to the
ORDER BY clause, which are, of course, redundant:
SELECT TOP 1 Data1, Data2 FROM SortTest WHERE Key1 = @key1 AND Key2 = @key2 ORDER BY Key1, Key2, Sort1, Sort2
The query now works as expected, but I should like to know whether it can be optimised in a more elegant way.
I realized that when I simplified the queries above, there was a grave mistake. One of the condition had an
=, so the real queries (that produce the different plans) have this instead:
WHERE Key1 = @key1 AND Key2 = IN (@key2a, @key2b, ...)
which explains (that the
key2 values in the result are not fixed and thus the different
ORDER BY can result in different output) and the plan difference.
Let thank every body for their help and apologies for the confusion.