“What will be the order of the result set of a SELECT statement on clustered indexed table when the ORDER BY clause is not used?”
I was asked this question by one of the smartest tech team managers in my company. I really enjoyed answering the question.
The common misconception is:
If ORDER BY clause is not explicitly specified and table has clustered index it will return the resultset in order of how clustered index is built.
Above theory is true in most of the cases. However, SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest. In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index.
There are few incidences I have observed that, when parallelism is used to return the query results, many times due to a different speed and work load on different CPU the resultset is not according to clustered index. SQL Server Parametrization and SQL Server cache can return the results which are not built by a clustered index. This paragraph is written assuming there is an ORDER BY clause specified in the SELECT clause.
I am in strong favor of the clustered index, which are primary key. There is not a single table in my database server farm which does not have clustered index (usually primary key, but not necessarily). This article is just stating the observation in certain conditions.
SQL Server uses clustered index to return the resultset efficiently and speedily. If SQL Server can return resultset faster in comparisons to clustered index SQL Server will use faster method. Either way, the user wins with faster results.
Reference: Pinal Dave (https://blog.sqlauthority.com)