In my code, I am creating a temp table using select * into #tempTable. Temp table contains the huge number of records and I need to retrieve data from it many times in my code so I created a clustered index on it. But somehow I am not getting data in correct order as per created index.

I know I can do it simply by putting the order by clause, but my question is why I am not getting data in the correct order even if I am forcing the optimizer to use the particular index?

Below is my sample code:

----------- Sample code ----------------------------

;with CTE1
as (
    select col1
        ,-- - -- - - from table1


    select col1
        ,-- - -- - - from table2
        -- few more tables in union --
    , CTE2
as (
    select *
        ,RowNumber = RowNumber() 
            over (order by case 
                    when @sortOrder = 0
                    then case 
                            when @sortColumn = 'Date'
                            then [tdate]......some more dynamic order by conditions..........
                    end asc
    from CTE1
    inner join
    from TABLE3 on -- some conditions
select *
from CTE2
into #TempTable;

Then add the clustered index:

create clustered index IX_TempTable_RowNumber on #TempTable (RowNumber asc);

select *
from #TempTable with (index (IX_TempTable_RowNumber));

----------------- End of sample code ---------------------

Even I checked the table scheme and index details using

exec tempdb.dbo.sp_help N'#TempTable';

select *
from tempdb.sys.indexes
where name = N'IX_TempTable_RowNumber'

And it seems correct to me. What am I missing here?

  • 1
    Why are you trying to go to this much trouble to prevent typing ORDER BY? – Aaron Bertrand Jul 13 '15 at 14:31
  • @AaronBertrand : I know ORDER BY clause is the only solution. I am just discussing it to understand it about result set and order of data, returned by SQL Server. – Ashwini Mohan Jul 13 '15 at 16:31
  • 2
    The reason is simple: if you do not include an ORDER BY, SQL Server is free to return the results in any order it deems most efficient. Even with a specific index being used, that doesn't mean that the results will be returned from that index sorted by the key(s) in that index. Read #3 here. – Aaron Bertrand Jul 13 '15 at 16:33
  • As for the reasons behind this, one is (I think) that the pages of the index may not be aligned, in same order as the key of the index (due to page splits and other reasons). But there are probably many other reasons that this can happen - when there is no ORDER BY. – ypercubeᵀᴹ Jul 13 '15 at 16:39
  • 3
    @ypercube I don't even know how useful it is to know why, except to know that you can only guarantee order by adding order by. As SQL Server adds more features and the optimizer becomes more complex, there will be more exceptions (and underlying reasons), not fewer. :-) – Aaron Bertrand Jul 13 '15 at 16:41

You have to use an ORDER BY. Adding a hint to use a specific index does not guarantee any ordering of the resultset:


  • I was having impression that if there is a clustered index and because data has been physically sorted according to clustered index, ideally I should get the data in same way as it has been stored physically if I do "select * from #TempTable". – Ashwini Mohan Jul 13 '15 at 16:28

Not the answer you're looking for? Browse other questions tagged or ask your own question.