I have a query that I execute on a sqlconnection and create a reader from the SqlCommand.ExecuteReader. I'm doing a simple Select * from table. I am not using an order by, but the results from the query when run in management studio always come back in the same order. 1 2 3 4 But when I do a reader.Read() the order is not the same. I see a random record being pulled out. 3 2 4 1 Anyone have an idea of why this might happen?
1
-
1Because you aren't using an order by clause. Without using one, you get results back in an arbitrary order. SSMS and the reader might give different orderings for a number of reasons.Esoteric Screen Name– Esoteric Screen Name2013-06-20 16:12:47 +00:00Commented Jun 20, 2013 at 16:12
Add a comment
|
1 Answer
If you don't say ORDER BY, ADO.NET is free to return the rows in any order it wants. Why do you complain?
People often incorrectly assume that without an ORDER BY the order of the rows would be the clustered index... or if they use a GROUP BY that it will be in this order... or other arbitrary rules.
These assumptions sometimes hold, sometimes not and always rely on implementation details and specific execution plans.
The only way to get a guaranteed ordering of your results is to use ORDER BY. Anything else is wrong, or you don't care about the order.
BTW, this doesn't always imply a performance hit for sorting. SQL Server is quite clever and if it knows the rows are already sorted (e.g. because it reads them out of an index based upon your ORDER BY columns), it will do nothing.
