ORDER BY Is Required

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.

Summary
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.