Why the sort order of the query output changes when the server opts for Parallelism ? RRS feed

  • Question

  • Suppose, I have a table of the following structure - 
    Col1 int PK + Clustered Index 
    Col2 int Non Clustered Index 
    Col3 int Non Clustered Index 
    Col4 int 

    Now, If I query the table in the following way - 
    SELECT Col4 FROM dbo.table WHERE Col2 = {Some Value} AND Col3 = {Some value} 

    it generates the same no. of rows but in different sort order. The points I have noted are - 
    1. The order of the output is random - ASC / DESC / NO Specific Order 
    2. The execution plan shows that the query uses parallelism 
    3. The same query gives consistent output on SQL Server 2005. 

    I am totally stumped why this is happening. Can someone please clarify this ?

    Thursday, October 21, 2010 2:50 PM

All replies

  • I have concluded with the following details for the above stated queries -

    1. SQL guarantees no specific order of the query output unless its specified by an ORDER BY clause in the SELECT statement. The result is fetched with the best possible execution plan at the time of execution. Hence, the order of the output is random.

    2. The execution plan shown the use of parallelism just because it might be using it for better performance on the server on which its run.

    3. Still don't know why .. :(


    Hope, this might be of some help to others facing a similar behavior of the query. Also, please correct me if I have any mis conception.


    Thursday, January 27, 2011 11:16 AM