Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
661 views
Welcome To Ask or Share your Answers For Others

1 Answer

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).


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...