I often see folks assume rows will be returned in order by the clustered index when ORDER BY is not specified in the query. This is despite the fact that the SQL Server Books Online ORDER BY topic specifically states “The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.”
For those that want to save a few keystrokes and omit the needed ORDER BY, here’s one example that shows rows may be returned in an order other than the clustered index.
USE tempdb; GO CREATE DATABASE Demo ON (NAME='Demo_Primary1', FILENAME='D:\SqlDataFiles\Demo_Primary', SIZE=10MB) , (NAME='Demo_Primary2', FILENAME='D:\SqlDataFiles\Demo_Primary2', SIZE=10MB) LOG ON (NAME='Demo_Log', FILENAME='L:\SqlLogFiles\Demo_Log.ldf', SIZE=1MB); GO USE Demo; GO CREATE TABLE OrderTest ( ID int, Name VARCHAR(50), OtherData char(5000) ); CREATE CLUSTERED INDEX cdx_OrderTest ON OrderTest(ID); GO --load 512 odd numbers WITH t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)) ,t1K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e) INSERT INTO OrderTest WITH(TABLOCKX) SELECT num, 'test', 'test' FROM t1K WHERE num % 2 = 1; GO ----load 512 even numbers WITH t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)) ,t1K AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e) INSERT INTO OrderTest WITH(TABLOCKX) SELECT num, 'test', 'test' FROM t1K WHERE num % 2 = 0; GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; GO SELECT * FROM dbo.OrderTest; /* ID Name OtherData 2 test test 4 test test 6 test test 8 test test 17 test test 19 test test 21 test test 23 test test 25 test test 27 test test ... */ SELECT * FROM dbo.OrderTest ORDER BY ID; /* ID Name OtherData 1 test test 2 test test 3 test test 4 test test 5 test test 6 test test 7 test test 8 test test 9 test test 10 test test ... */ GO
The reason the query without the ORDER BY returns rows in a different order than the clustered index logical order is because SQL Server chose to scan the table in physical allocation order using the IAM (Index Allocation Map) instead of following the clustered index linked list. For details of the allocation and page linkage of this table, you can use the undocumented (don’t use in in prod) sys.dm_db_database_page_allocations table-valued function in SQL 2012 and later:
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'OrderTest'), 1, 1, 'DETAILED') WHERE page_type_desc = 'DATA_PAGE';
The IAM scan was done here due to no ordering requirement for this query and the allocation order scan was deemed more efficient, and permissible in the READ UNCOMMITTED isolation level because data integrity isn’t needed. Other factors can also affect the ordering of results, including available indexes, execution plan operators, parallelism, and other concurrent activity.
Remember that SQL Server strives to execute queries as efficiently as possible as long as it adheres to the data contract. The chosen plan and storage engine internals that vary by SQL Server version and edition will influence ordering of results. The ordering of rows is by happenstance unless ORDER BY is specified.