Unordered Results in SQL

Unordered results in SQL

Some SQL Server examples where the exact same execution plan can return differently ordered results are

1. An unordered index scan might be carried out in either allocation order or key order dependant on the isolation level in effect.
2. The merry go round scanning feature allows scans to be shared between concurrent queries.
3. Parallel plans are often non deterministic and order of results might depend on the degree of parallelism selected at runtime and concurrent workload on the server.
4. If the plan has nested loops with unordered prefetch this allows the inner side of the join to proceed using data from whichever I/Os happened to complete first

sql unordered output (result)

If you are using SQL*Plus, you have some control over formatting via SQL*Plus specific commands. For example:

SET PAGESIZE 9999
SET LINESIZE 120
COL equipo_local FORMAT A12

See the SQL*Plus Reference manual for more SQL*Plus commands.

SET HEADING OFF
SET TRIMSPOOL ON

etc.

If you want rows returned in a particular sequence, add an ORDER BY clause to your query.

How does SELECT return a unsorted result set?

SQL tables represent unordered sets. There is no ordering in a table, unless a user-specifies the ordering. SQL result sets represent unordered sets, unless there is an order by at the outermost level. This is how the language is defined.

So, when you do a select with no order by, then you are not guaranteed the ordering of the rows. In many cases, the ordering will look like insertion order, but you cannot guarantee this.

There are numerous things that can affect the ordering returned by a select, such as:

• Multi-threaded databases have independent processes reading the data.
• The underlying page cache mechanism is not required to read the pages in order.
• Deletions and subsequent insertions mix up rows.
• The query might be satisfied from an index, not a table scan.

If you want rows in a particular order, use order by.

How to get unsorted result from a select query that uses an 'in'?

Any dataset your query is working with is by default unordered, whether it is a physical table or a derived one. Whatever order the server uses to read rows from it while actually executing the query is out of your control. That means you cannot reliably specify the order to be "same as in that subquery". Instead, why not just have a specific order in mind and specify it explicitly in the main query with an ORDER BY? For instance, like this:

SELECT *
FROM tblQuestionTable
WHERE Paper='HTML'
  AND QuestionId IN (SELECT QuestionId FROM tblTestOverview WHERE TestId=1)
ORDER BY QuestionId
;

Having said that, here's something that might be close to what you are looking for. The ROW_NUMBER function assigns row numbers to the derived dataset in an undetermined order (ORDER BY (SELECT 1)). It may or may not be the order in which the server has read the rows, but you can use the assigned values to order the final result set by:

SELECT q.*
FROM tblQuestionTable AS q
INNER JOIN (
  SELECT
    QuestionId,
    rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
  FROM tblTestOverview
  WHERE TestId = 1
) AS o
ON o.QuestionId = q.QuestionId
ORDER BY o.rn ASC
;

Why are my SQL queries yielding a different order?

SQL tables represent unordered sets. SQL results sets are unordered, unless there is an explicit ORDER BY corresponding to the outermost SELECT.

Period.

If you don't have an ORDER BY, you should have no expectations on the ordering of the results. The ordering can change from one run to another.

And, even if you have an ORDER BY, if you have ties (i.e. keys with the same values), then they can be ordered arbitrarily from one run to the next.

Is there way to have ordered output without ORDER BY clause in MS SQL

You asked:

Is there a way to have ordered output without an ORDER BY clause in MS SQL

No.

Unfortunately, there is not.

SQL (in all its flavors) is a declarative scheme for manipulating sets of rows of tables. Sets of things like rows have no inherent order.

If SQL, in any flavor, without ORDER BY statements, happens to deliver rows in an order you expect, it's a coincidence. It's pure luck. Formally speaking, without ORDER BY SQL delivers rows in an unpredictable order."Unpredictable" is like "random," but worse. Random implies you might get a different order every time you run the query: you can catch that kind of thing in system test. Unpredictable means you get the same order each time until you don't. Now you don't.

This is in the basic nature of SQL. Why? The optimizers in modern database servers are really smart about delivering exactly what you asked for as fast as possible, and they get smarter with every release. SQL Server 2016's optimizer probably noticed some opportunities to use concurrency for your queries, or something like that, and took them.

Your choices here are all unpleasant:

• Roll back the database upgrade. That may or may not fix the problem.
• Make emergency fixes to your software load and roll them out ahead of schedule.
• Live with the wrongly-ordered reports until you can fix your software.

I wrote a long answer here so you can have useful arguments to present to your corporate overlords about your situation. I don't envy you when you explain this.

Unordered result using order by query

How did you query rows from the table created with your displayed statement?

While the ORDER BY in the displayed query should do its job properly, that's just sorting physical rows in the newly created table. But the physical order of rows is subject to change. VACUUM and other background processes can reorder rows freely. Any write access to the table will do so, too.

Plus, more importantly, SELECT * FROM tbl is not bound to return rows in current physical order. While returning rows in physical order is typically the fastest way, and Postgres will typically just do that, nothing of the sort is guaranteed. You have to append another ORDER BY to get sorted rows (which defies the purpose of testing the current physical sort order).

Test the current physical order of rows like this:

SELECT ctid, * FROM tbl ORDER BY ctid;

Explanation for ctid:

• How do I decompose ctid into page and row numbers?

And verify the original SELECT in the question by running it without CREATE TABLE AS, and you'll see perfectly sorted rows ...

Related:

• Subtract two records of the same column in a table
• Select last n rows without use of order by clause

Is the order of the result of SELECT DISTINCT ... WHERE ... random?

SQL queries return results as an unordered set, unless the outermost query has an order by.

On smaller amounts of data, the results look repeatable. However, on larger systems -- and particularly on parallel systems -- the ordering may be based on hashing algorithms, when nodes complete, and congestion on the network (among other factors). So, you can in fact see different orderings each time you run.

Are big-query results always ordered, that is: using OFFSET makes sense to skip rows?

I am just curious if without using "order" clause the table is always deterministic/consequetive or can the results duplicate if they're returned indeed at random.

No. SQL tables represent unordered set of rows. There is no inherent ordering of the rows. Unless an order by clause is specified, there is no guarantee that two consequent executive of the same query would yield an indentical result. The database is free to return the rows in whatever order it likes.

As a consequence, the results of a query with a row-limiting clause but no order by clause are not deterministic. Do add an order by clause the these queries, or you will sooner or later run into suprising and hard-to-debug behaviors.

---

Related Topics