CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduressql-order-by
Chakri
Chakri

Reputation: 151

OrderBy clause is resulting different result sets when order column having same data

We have a stored proc to return set of records based on Page Number and Page Size. Sorting is being done by a column "CreateDateTime". If value of CreatedDateTime is same for all the records, it is giving the results sets in different orders. The behavior is inconsistent.

Some Portion of Code:

SET @FirstRec  = ( @PageNo - 1 ) * @PageSize 
SET @LastRec   = ( @PageNo *@PageSize + 1 )   

SELECT * 
FROM 
( 
      select ROW_NUMBER() OVER (ORDER BY CreatedDateTime)     
       AS rowNumber,EMPID 
      From Employee
) as KeyList 
WHERE rowNumber > @FirstRec AND rowNumber <  @LastRec

Please provide some inputs on this.

Upvotes: 4

Views: 6063

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

If you need a reproducible order, then you need to ensure that you specify enough columns in your ORDER BY, such that (the combination of all columns listed in the ORDER BY) is unique for every row. E.g. add EmpID (if that's a primary key) to act as a "tie-breaker" between rows with equal CreatedDateTime values.

Upvotes: 1

Graham Griffiths
Graham Griffiths

Reputation: 2216

If the values in the column you ORDER BY are all the same, then there is no guarantee that they will be retrieved in the same order. You can ORDER BY a second column - perhaps the unique id if there is one? (I have called it UniqueId in the code below). This would ensure the order is always the same.

SELECT * 
FROM 
( 
      select ROW_NUMBER() OVER (ORDER BY CreatedDateTime, UniqueId)     
       AS rowNumber,EMPID 
      From Employee
) as KeyList 
WHERE rowNumber > @FirstRec AND rowNumber <  @LastRec

Upvotes: 0

Sebastian Meine
Sebastian Meine

Reputation: 11813

This is "by design"

SQL Server (or any RDBMS) does not guarantee results to be returned in a particular order if no ORDER BY clause was specified. Some people think that the rows are always returned in clustered index order or physical disk order if no order by clause is specified. However, that is incorrect as there are many factors that can change row order during query processing. A parallel HASH join is a good example for an operator that changes the row order.

If you specify an ORDER BY clause, SQL Server will sort the rows and return them in the requested order. However, if that order is not deterministic because you have duplicate values, within each "value group" the order is "random" for the same reasons mentioned above.

The only way to guarantee a deterministic order is to include a guaranteed unique column or column group (for example the Primary Key) in the ORDER BY clause.

Upvotes: 9

Related Questions