Is default ordering affected by clustered index?

Question

My colleague and I are debating whether the default ordering of rows from a SELECT statement (without specifying any ORDER BY) is controlled by the clustered index or not.

He demoed this by creating a clustered index on a column with DESC, then SELECT would return the result according to that column as if there is a hidden ORDER BY column DESC.

But I doubt this holds true as the table grows, even given we only ever INSERT into this table and never any UPDATE nor DELETE, however I can't prove this to him.

Can anyone show a demo disproving this?

We were testing this on Microsoft SQL Server, but the question is for any DBMS.

In my case, there is only just one clustered index on the table.

Answer 1

here's a demo

Yano is of course correct about order not being guaranteed without an order by. This has been written about 6 trillion times across the span of the internet for most if not all of the major relational database systems.

One of the really tricky things is when you're dealing with parallel execution plans, and/or ordering by non-determinsitic columns.

Contextually, I'm running SQL Server 2022, in a database using compatibility level 160. I have MAXDOP set to 8, and Cost Threshold For Parallelism set to 50.

This should produce a table populated with around 5 million rows.

CREATE TABLE
    dbo.disordered
(
    id bigint NOT NULL PRIMARY KEY,
    random_dates date
);
INSERT
    dbo.disordered WITH (TABLOCK)
(
    id,
    random_dates
)
SELECT
    id = ROW_NUMBER() OVER (ORDER BY @@SPID),
    random_dates = DATEADD(DAY, x.severity, SYSDATETIME())
FROM
(
    SELECT
        m.*
    FROM sys.messages AS m
    CROSS APPLY
    (
        SELECT TOP (15)
            m2.*
        FROM sys.messages AS m2
    ) AS m2
) AS x;

Here are a couple queries where running them repeatedly will produce results in different orders.

non-deterministic ordering

SELECT TOP (100)
    d.*
FROM dbo.disordered AS d
ORDER BY
    d.random_dates;

You should see a parallel plan for this. If you don't, the issue may not present itself.

Here are a couple examples of the results changing:

different results with no ordering

SELECT TOP (500000)
    d.*
FROM dbo.disordered AS d
WHERE EXISTS
(
   SELECT
       1/0
   FROM dbo.disordered AS d2
   WHERE d.random_dates >= d2.random_dates
);

Again, you should see a parallel plan for this:

And here are examples of some differently-ordered results:

two different problems

Of course, the problem isn't parallelism, the problem is that we asked for results to be orered:

1. By a non-unique column
2. With no ordering whatsoever

We could solve the problem by adding a unique column to the order by clause as a secondary (tie-breaking) ordering element. The underlying message is just an extension of what Yano said: ordering isn't guaranteed without an order by clause, and is also not guaranteed when a unique tie-breaker is not included in the order by clause.

Answer 2

Without ORDER BY clause, the order of rows is not guaranteed. It could be the same one day, and different another day.

Is default ordering affected by clustered index?

Oftentimes, the data are read from clustered index, which then leaves them in that order. But you can't rely on that.

Can anyone show a demo disproving this?

I am not sure I can, since the order of rows returned is not guaranteed. But I would try something like this:

CREATE TABLE MyTable (
    Id INT NOT NULL IDENTITY PRIMARY KEY,  /*Primay key automatically also creates clustered index on this key*/
    [Name] VARCHAR(500)  
);
INSERT INTO MyTable([Name])
VALUES ('Anna'), ('Bethy'), ('Cindy'), ('Dora'), ('Emily'), ('Francine'), ('Gina'), ('Holly');
/Resultset ordered by clustered index/
SELECT * FROM MyTable;
/Notice the index is created in DESCENDING order./
CREATE NONCLUSTERED INDEX IX_MyTable_Name ON MyTable ([Name] DESC);
/Resultset ordered in reverse/
SELECT * FROM MyTable;