One thing that I have seen repeatedly is that (too) many people believe that if you have a clustered index on a table, that you can run a SELECT statement without an ORDER BY clause from that table and the results will be in the order of the clustered index key. Nope, this is absolutely false. It may happen a lot. In your experience, it may even happen all the time. But without that ORDER BY clause your results are not guaranteed to be in any particular order.

So, with the thought of “Code that proves this walks, everything else is just talk”, let me prove it. The following code creates a temporary table with a single integer column, and a clustered primary key is on that column. It is populated with one million rows of sequential numbers. A few SELECT statements are run, and then an unordered SELECT statement. Most of the time, the result set from this SELECT statement will not be in order (if it happens to be in order, just run the script again). All it takes is to have this produce results one time that aren’t ordered to prove that you always need the ORDER BY clause to guarantee an ordered result set.

Source code

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
   DROP TABLE #Test;
CREATE TABLE #Test (RowID INT PRIMARY KEY CLUSTERED);
;WITH
TENS      (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1
                                CROSS JOIN TENS t2
                                CROSS JOIN TENS t3),
MILLIONS  (N) AS (SELECT 1 FROM THOUSANDS t1
                                CROSS JOIN THOUSANDS t2),
TALLY     (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
                    FROM MILLIONS)
INSERT INTO #Test
SELECT N FROM TALLY;
 
SELECT TOP (5) * FROM #Test WHERE RowID > 5000;
SELECT TOP (5) * FROM #Test WHERE RowID > 7000;
SELECT TOP (5) * FROM #Test WHERE RowID > 9000;
 
SELECT * FROM #Test;
 
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
   DROP TABLE #Test;

(Edit: I forgot to add the results of the queries.) Here are the full results for the 1st three queries, and the first few rows of the one that “should” be in clustered index order. Which, as you can see, is NOT in order.

Source code

RowID
-----------
5001
5002
5003
5004
5005
 
(5 row(s) affected)
 
RowID
-----------
7001
7002
7003
7004
7005
 
(5 row(s) affected)
 
RowID
-----------
9001
9002
9003
9004
9005
 
(5 row(s) affected)
 
RowID
-----------
935489
935490
935491
935492
935493
935494