“What will be the order of the result set of SELECT statement on clustered indexed table when ORDER BY clause is not used?”
I was asked this question by one of the smartest tech team manager 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 incidence I have observed that, when parallelism is used to return the query results, many times due to 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 clustered index. This paragraph is written assuming there is ORDER BY clause specified in the SELECT clause.
I am in strong favor of 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, user wins with faster results.
Reference : Pinal Dave (http://blog.SQLAuthority.com)