Why do I get an unordered resultset, although my table has a clustered index?

Although the presence of a clustered index often seems to produce an ordered resultset, there is no guarantee that this will work in all cases. If you execute a SELECT statement without an explicit ORDER BY, SQL Server will try to return the information in the fastest possible way. This might be or might be not equal to the order of the clustered index keys.

For example, there might already be data pages in the buffer pool present and SQL Server might opt to return those first before reading the remaining data from disk. Another example might be when parallelism is involved and more than one CPU is used to return the rows.

If you need to rely on the return of an sorted resultset, you need to have an explicit ORDER BY criteria.


Leave a comment

Your email address will not be published.