Default sort order for a select query in SQL Serve

Question

Is there a difference between default sort order for a select query in SQL Server 2005 and SQL Server 2012?

I have a table variable without a primary key. When I execute a select query on the table variable in SQL Server 2005, the records are selected and displayed in alphabetical order as per one of the columns. In SQL Server 2012, the records are displayed in the same order as in the parent table.

Answer 1

There is no default sort order. Unless you specify it in the ORDER BY clause, there is no guarantee that the result will be returned the same way all the time.

You might observe that the result is order by the PK or the clustered index, but that's not always going to be the case.

---

You might want to read this:

Without ORDER BY, there is no default sort order by Alexander Kuznetsov

Answer 2

The MSDN says:

Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.

So in your ORDER BY clause if you are not providing the sorting order then it would be sorted in ASCENDING order by default in 2005 and 2012 respectively.

However if you are not specifiying the ORDER BY clause then the sorting order is not defined and it is indetermined.