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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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 |
