Sort Order of results for Select WITHOUT WHERE or ORDER BY Clause

Question

I have a table with a PK clustered index as well as other indexes on it, both unique and non-unique. If I issue (exactly):

SELECT * FROM table_name

or

SELECT col1, col2 FROM table_name

in what order will the rows be returned?

This is the first question in an interview questionnaire a customer has forwarded us. Here are the instructions:

If the answer to this question is incorrect, terminate the interview immediately! The individual, regardless of their stated ability does not understand SQL-Based relational database management systems. This is SQL-101 logic for the past 25+ years. The correct answer is: “unknown/random/undetermined because no ORDER BY clause was specified as part of the query”.

I am somehow not convinced that this is actually correct. All comments welcome.

Thanks,

Raj

Answer 1

Even if a table has a primary key/clustered index, you can't be sure about the order of rows. Although in the execution plan there will be an index/heap scan at the end, if query is performed in parallel on many cores, the resulting dataset won't be sorted due to parallel streams merge plan step.

You probably won't see it on small databases, but try creating one with many files on separate harddrives and run a simple query on a multicore machine. Most likely you'll get results "partialy sorted" by ID - i.e. there will be blocks where rows are sorted, but blocks will be retrieved in semi-random order.

Answer 2

The instructions speak to SQL at a conceptual level, at which the result of a query is a relationship, and relationships are unordered. Moving from the conceptual to the actual, the reason no implicit ordering is defined in the SQL standard is so RDBMSs are free to return whatever order is most efficient for their implementation.