SQL SERVER – Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used

“What will be the order of the result set of SELECT statement on clustered indexed table when ORDER BY clause is not used?”

I was asked this question by one of the smartest tech team manager in my company. I really enjoyed answering the question.

The common misconception is:
If ORDER BY clause is not explicitly specified and table has clustered index it will return the resultset in order of how clustered index is built.

Above theory is true in most of the cases. However SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest. In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index.

There are few incidence I have observed that, when parallelism is used to return the query results, many times due to different speed and work load on different CPU the resultset is not according to clustered index. SQL Server Parametrization and SQL Server cache can return the results which are not built by clustered index. This paragraph is written assuming there is ORDER BY clause specified in the SELECT clause.

I am in strong favor of clustered index which are primary key. There is not a single table in my database server farm which does not have clustered index (usually primary key, but not necessarily). This article is just stating the observation in certain conditions.

SQL Server uses clustered index to return the resultset efficiently and speedily. If SQL Server can return resultset faster in comparisons to clustered index SQL Server will use faster method. Either way, user wins with faster results.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used

  1. 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…

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

  3. 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 http://support.microsoft.com/kb/926292 but this requires the compatible level 80.

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

  5. 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?

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s