When a query is processed using index-only access, will the result set be sorted based on the index sequence or do I still need to specify an "order by"?

table:
empid
last
first
salary


unique index:
empid asc
last asc


query:
select empid, last from table where empid between 1000 and 2000


Will result be sorted by empid+last?

Per relational semantics, you always need to specify an ORDER BY if that is what you want otherwise there is no guarantee that the rows will always be returned in the desired order. While an ORDER BY may not be necessary in some cases, that could change in future, including if the number of rows in the table changes and DB2 chooses a different access plan.

If DB2 can avoid a sort by using index only access, then specifying ORDER BY will not cause an additional sort to happen because of the ORDER BY. DB2 is smart enough to know when a sort is not needed even when an ORDER BY is specified, so there is no reason to omit the ORDER BY.