database - Is SQL order by clause guaranteed to be stable ( by Standards)

Question

I am using following query to query DB which have order by on two columns.

SELECT a,b,c from Table1 Order By a asc, b asc;

My question is, Is the sorting guaranteed to be stable (by Standards) or not. Though it doesn't make any sense for it to be, not be stable, But i ask this because I read on net that

The standard does not prevent the use of a stable sort, but it also does not require it.

Answer 1

The sort is not guaranteed to be stable. I think the SQL Server documentation has a good explanation of how to achieve a stable sort:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met: The underlying data that is used by the query must not change. That is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a single transaction using either snapshot or serializable transaction isolation. For more information about these transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL). The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

The simplest way to understand that a sort is not stable is to go back to the definition of a table. Tables are inherently unordered in SQL. So, there is no ordering to fall back on for "stability".

As a second consideration, the sorting may be implemented in parallel. In most parallel sorts, common keys are brought together with no information about their original order (unless that is implemented in the sort key, either explicitly or implicitly).