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.
It's similar to following a recipe: you need to know the ingredients and what to do with ingredients, but you also need to know in which order do the tasks. If the database follows a different order of operations, the performance of the query can decrease dramatically.
The SQL ORDER BY KeywordThe ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
This is not a bug. A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified.
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With