A Clustered Indexed Table still needs ORDER BY to promise result order. I declare the order the reader or application requires.
A team manager asked me what order a clustered table would return without ORDER BY. It is easy to mistake an observed ordered scan for a contract. A chosen access path, parallel work, or another plan can change what the client sees.
CREATE TABLE #OrderDemo (id int NOT NULL PRIMARY KEY CLUSTERED, note varchar(12));
INSERT INTO #OrderDemo VALUES (3,'third'),(1,'first'),(2,'second');
-- No promised presentation order:
SELECT id, note FROM #OrderDemo;
-- Explicit presentation order:
SELECT id, note FROM #OrderDemo ORDER BY id;
DROP TABLE #OrderDemo;The second query requests ascending id. The first may look identical on this tiny test. That observation does not make its presentation order guaranteed.
Keep physical design and output order separate
The optimizer chooses a plan from estimates and available alternatives; it does not promise the fastest possible result. A clustered index can support useful access and ordering, but the query must still declare the output order it needs.
I’ve preferred clustered primary keys in many designs. That preference isn’t a requirement for every table. A primary key can be nonclustered, and a heap can suit a particular workload.
For stable paging, I include a unique tie-breaker when sort values repeat. The same consideration applies to repeatable TOP selection.
A clustered index is not an output-order contract, it is part of the physical table design.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
Well the answer would be a lot simpler…
You didnt ask for an order, so you wont get one. The data in a relational DB is by definition not ordered. You want ordered data, then ASK for it ;)
You can even go 1 step futher..
Create view foobar as
select top 100 percent * from foo order by bar
select * from foobar
The above statement will not be a sorted resultset from foo. You did not ASK for an order, so the queryplan can actually drop the order request from the view. Even if this DID work on some older Versions of SQL Server, it is not an expected result.
You didnt ask for an order in your final querry, that means there is no need to order the result before returning it to you…
One Response to “SQL SERVER – Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used”
Reference to the above statement i disagree with Pinal Dave and i think more attention is needed ,what i say, if you don’t include order by clause and your table is clustered indexed on one or more columns although you add disorder records, SQL will display them in sequence because we have got a clustered index on column/aolumns. Moreover, Adding a clustered index to the table physically reorders the data pages, putting them in physical order based on the indexed column/columns.
I agree with Pinal Dave.
In the most cases, the query resultset that don’t include ORDER BY clause and the table is clustered indexed returns the expected sequence that index are created (ASC or DESC), but there are some cases that doesn’t occur. I have experiment a lot of cases like that. SQL Server returns the resultset more efficiently and speedily, except when forced by a hint or other customizations (at least according query optimizer). This is the general rules.
About clustered index, they don’t physically reorders the data pages, they just do this in logical level reordering the sequence of b-tree pointers in the leaf nodes.
About the ORDER BY clause in views, there are a huge discussion between MCPs and MVPs to decide if this is a BUG or not. The facts are: This “problem” is caused by the SQL 2005 query optimizer, which ignores the TOP (100) PERCENT clause when returning the resultset. However, any other value of the TOP clause will work. Of course sorted views are inefficient and you should do the ORDER BY clause in the query that calls the view. Microsoft said that was a misconception about the ORDER BY clause in views and subqueries. In these cases, the ORDER BY and TOP clauses are not for ordering but only for select the rows that must be returned. In the early versions of SQL, the query optimizer is less efficiently as SQL 2005. But if this is right, why a TOP clause with a different value works fine? Even you test using a value greater than the number of rows in your table the results are ordered. Microsoft publishes a temporary hotfix for this supposed “bug” in https://support.microsoft.com/en-us/help/926292/fix-when-you-query-through-a-view-that-uses-the-order-by-clause-in-sql but this requires the compatible level 80.
Following on from Luciano, it’s not a bug. The reason why a TOP clause with a different value (eg 97 PERCENT or 1000 rows), even if the value exceeds the number of rows in your table AT THAT TIME, is because if you have a table with, for example, 2000 rows, then selecting the top 95 percent with an order by, does actually mean something. Same goes for selecting the top 1000 records.
The order by, in both cases, is not for ordering the result of the view, but is for instructing SQL Server which top records you want. Unfortunately (it’s been widely discussed on many sites) the order by clause technically serves two purposes – ordering of the resultset as well as instructing SQL Server how you want the records ordered for things such as a TOP clause.
Take the same logic and you can see why the OVER clause in SQL 2005 has the ability to have an ORDER BY.
I’m having issues with the ordering of a clustered index at present.
Assume you have a table with one column of type INT which is the primary key. When you insert say 10000 rows in DESC order and then do a select * from that table with no order by clause, it returns the data in order from 1 to 10000.
Do the same thing, but on on a temporary/variable table and it’s out of order.
With smaller datasets, it works just fine on the temp/variable tables.
This is on SQL2000 SP4. I tested the same code in 2005 and the ordering is correct in both instances.
Does anyone know why I would get a different sort order in this instance?