yuvipoy (12/14/2013)
...where he can scroll through the records of 1 million rows... .
[sarcasm ON]
If the company can afford to pay someone to scroll through one million rows, they'll definitely have the time to way a few minutes each time the sort order is changed...
[sarcasm OFF]
More honestly: I'd question the business need to return a million rows in the first place. The time it takes to transfer the data will be significant.
Example: let's assume all 100 columns are defined as integer. Then one row will use 400byte. So you're transferring almost 400MB each time the sort order is changed.
Assuming the hardware hosting the SQL Server does provide a large enough amount of memory, data might be cached in memory leading to a faster return of the rows in a different order.
But again: What's the purpose of it?