SELECT without ORDER not Sorting on Clustered Index

Question

A bit confused on this one. I was under the impression that if no ORDER BY is given, a SELECT should default to the Clustered Index for sorting.

But if I run the below, it uses the NON-Clustered index instead.

DROP TABLE TEST_TABLE
if NOT EXISTS(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'TEST_TABLE')
BEGIN
    CREATE TABLE TEST_TABLE (
        [ID]    int     NOT NULL IDENTITY(1,1),
        [col1]  int     NOT NULL,
        [col2]  int     NOT NULL

        CONSTRAINT [PK_TEST_TABLE] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
END

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TEST_TABLE]') AND name = N'TEST_TABLE_cols')
    DROP INDEX [TEST_TABLE_cols] ON [dbo].[TEST_TABLE]

CREATE UNIQUE NONCLUSTERED INDEX [TEST_TABLE_cols] ON [dbo].[TEST_TABLE] 
(       [col1] ASC,
        [col2] ASC      
)WITH (FILLFACTOR = 60) ON [PRIMARY];
GO
--GO

EXEC ('insert into test_table select 1, 0')
EXEC ('insert into test_table select 2, 0')
EXEC ('insert into test_table select 10, 0')
EXEC ('insert into test_table select 9, 0')
EXEC ('insert into test_table select 8, 0')
EXEC ('insert into test_table select 6, 0')
EXEC ('insert into test_table select 7, 0')
EXEC ('insert into test_table select 5, 0')
EXEC ('insert into test_table select 3, 0')
EXEC ('insert into test_table select 4, 0')

select * from test_table

It results with the below ...

ID          col1        col2
----------- ----------- -----------
1           1           0
2           2           0
9           3           0
10          4           0
8           5           0
6           6           0
7           7           0
5           8           0
4           9           0
3           10          0

If someone could explain it would be most appreciated!

Answer 1

I was under the impression that if no ORDER BY is given, a SELECT should default to the Clustered Index for sorting.

While in practice that might appear to be the case (if the rows are simple read off storage in order, that is what you get) there is no such guarantee anywhere. A small change in the query or in the query plan could easily changing things.

If you do not specify an order, then the results have no order.