When an index covers the query and the order by columns form a prefix subset of the index keys, the rows are returned directly from the nonclustered index leaf pages. If the columns do not form a prefix subset of the index keys, a worktable is created and sorted.
With a nonclustered index on au_lname, au_fname, au_id of the authors table, this query can return the data directly from the leaf pages:
select au_id, au_lname from authors order by au_lname, au_fname
