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!

Well, the thing is that: `I was under the impression that if no ORDER BY is given, a SELECT should default to the Clustered Index for sorting` is just not true. Without an `ORDER BY` the optimizer can choose whatever index for ordering — Lamak, Mar 23 '17 at 14:30
I see. Then it was my misconception. Thanks for the prompt feedback. — stubs, Mar 23 '17 at 14:35
https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/ — Sean Lange, Mar 23 '17 at 14:37

score 5 · Accepted Answer · answered Mar 23 '17 at 14:31

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.

score 2 · Answer 2 · answered Mar 23 '17 at 14:32

2

I can't explain why you are seeing this but I do know that no order is guaranteed unless an order by clause is specified as per the below link:

https://msdn.microsoft.com/en-us/library/ms188385.aspx

answered Mar 23 '17 at 14:32

D. Hislop

181
3

SELECT without ORDER not Sorting on Clustered Index

2 Answers2