Naveen PK (8/5/2013)
If 'order by 1 ' is commented/removed, I still get same result set in different order.
In other words wrt to performance, does Between puts the column in sorted order n get the result set? or does it check row by row?
SQL Server will NOT create a plan that first sorts the entire table and then uses the sorted list to find the matching "subset" of data.
However, if there is an index available on the column for the BETWEEN, then SQL Server might decide to use that index to evaluate the BETWEEN quickly (by using a seek to directly locate the first matching row, and then scanning until after the last matching row). Or it might not. That depends on lots of other factors - the optimizer is a complex beast that considers a lot of options, tries to predict their cost, and then picks the one that's probably cheapest.
Adding an ORDER BY could result in the optimizer changing from a plan that does not use the index to one that does. Or the optimizer could still choose not to use that index, and instead check all rows and then sort the matching rows.
EDIT: Obviously, in the case of this question, there is no index available. Here, SQL Server will always scan the table, check each row, and then either sort the result if there's an ORDER BY clause, or return it unsorted otherwise.