My query returns a query result which appears to be sorted, though there is no order by statement available. This could be coz of the clustered index.
Is it wise to depend upon index for sorting?
Is it a best practice to rely on index for sorting?
Thanks
If you need a particular sort order best practice is to specify it using an ORDER BY clause.
For what it's worth, almost every database implementation I've ever used will exhibit some reasonably repeatable sort order despite no ORDER BY clause, but the behavior is a side effect of how the database works under the covers. The fact of the matter is, databases (again, the ones I've worked with) do not guarantee the sort order unless you specify it, so without specifying it you are at the mercy of the database implementation and any side effects it may cause. Basically, if you need records to be returned in a completely predictable way you should specify it.
My query returns a query result which appears to be sorted, though there is no order by statement available.
This is because the data is returned based on the order the data was inserted. In order to guarantee the order will always be consistent, you need to define an ORDER BY clause.
An index can speed up how fast the query is ordered, but it has no impact if there is no ORDER BY clause that uses the column(s) with indexes on them.
You can't rely on the order you get results back in unless you use ORDER BY. This depends on your database, I guess, but specifying an order when you need one is always best practices.
Regardless of the other details, anytime you care about the order of results from a SQL query, the only dependable thing to do is include an ORDER BY clause to ensure you get the order you want. As an alternative, you can (of course) retrieve in random order and sort it later -- but having SQL do the job is usually a better idea. The primary exception is if you need to do something like populating a list and want the user to be able to re-sort based on different columns. In this case, you're going to have sorting (probably in JS) on the client side anyway, so you might as well just use it.
Yes, the data appears pre-sorted because of the clustered index. A clustered index means that the rows are physically arranged in the order of the index, which in most (not all) cases is how they will be retrieved.
If there is no clustered, index, rows will be retrieved in the same order that they were inserted.
No, it is not wise to depend on this. If you decide to do a JOIN, DISTINCT, or make any other change to the query, you can easily break the sorting order. On the other hand, if you use an ORDER BY and SQL Server knows that the data is already in that order, it will optimize out the redundant sort and therefore cost you nothing.
Therefore, always ORDER BY, even if it looks like you don't need it.
Data may appear to be sorted, but you can't rely on that. Suppose you have some of the data you need in RAM, while the rest is on disk still. The system could go parallel and start returning the data that's in memory, while the rest is fetched from the disk and returned. You simply can't rely on it and should most definitely order your data if you need it ordered.